Rent vs Mortgage is an excel tool that calculates the payments to a house mortgage loan and compares it to the opportunity cost of renting the same house.
The color coding of the model is the usual one. Inputs from the user are always depicted with a yellow fill, and calculations are depicted with white fill and black characters, finally, restricted cells are in light grey fill.
In the “Mortgage to Stay” tab, the user needs to fill the house price, the percentage of the down payment, the interest rate, the years to repay the loan (maximum up to 40 years), the annual maintenance cost as a percentage of the house price, the real house appreciation per year (that is the inflation-adjusted increase in the market price of the house), and the discount rate. After entering these inputs, The opening and closing balance of the loan, the interest, and principal payments are calculated for every month of the loan duration and presented in two graphs as well. Additionally, the house price appreciation is calculated, along with the maintenance of the house, and the cash flows in order to calculate the net present value of the mortgage. It is assumed that the house is sold at the end of the loan period. The net benefit / (cost) of the mortgage is calculated (in dollar terms without taking into account the time value of money), as well as the net present value which takes into account the time value of money.
In the “Mortgage to Rent” tab, previous inputs from the user have been kept in grey cells (house price, down payment, etc…), the user just needs to fill the capitalization rate (cap rate) and the occupancy rate which will be used to calculate the monthly rental price. Another input that has to be filled from the user is the rent cost to stay which can be changed depending on if the user already owns a house or whether he is renting a smaller or same place than the mortgaged house. The initial assumption is that you are renting a similar place. Similar metrics are calculated such as the net benefit / (cost) of the mortgage and the net present value.
In the last tab “Rent”, previous inputs from the user have been kept in grey cells (house price, down payment, etc…), the only input that needs to be filled is the yearly return on the investment of the down payment. It is assumed that the down payment used in the case of the mortgage is invested.
Finally, in the “Summary” tab, all the cases are presented (Mortgage to Stay, Mortgage to Rent, Rent) both in dollar terms and the time value of money terms. Additionally, for every interest rate level from 1% to 10% the benefit to rent is calculated (in cash terms and in Net Present Value terms) by pressing the “Recalculate Curve” button on the top left of the tab. You need to enable macros in order to be able to use the button. After pressing the button the values are automatically recalculated and presented as charts. Take into account that we are comparing in the graphs only the Mortgage to Stay and the Rent.
The way to proceed with the worksheet is first to fill the inputs in the yellow cells for the tabs “Mortgage to Stay”, “Mortgage to Rent” and “Rent” and then go to the “Summary” tab and press the “Recalculate Curve” button. Based on the interest rate of the loan you are able to see whether it is beneficial for you to rent a house or finance it with a mortgage instead. No taxes have been taken into account in the analysis.
Please keep in mind before downloading that this template contains Macros (with a filename ending .xlsm). Basically, the macro is to update the sensitivity table. Therefore, please check your MS Excel program first if it can run Macros or a .xlsm file to ensure that the template will work properly.
Similar Products
Other customers were also interested in...
Collateralized Mortgage Obligations Model
Collateralized Mortgage Obligations Model presents a simple model where mortgage backed securities a... Read more
Lending Company Financial Model – 5 Year Forecas...
This Financial model template presents a business scenario of a corporation engaged in granting loan... Read more
Crowdlending (P2P) Platform – 5 Year Financi...
Financial Model providing an advanced 5-year financial plan for a startup Crowdlending (Peer-to-Peer... Read more
Amortization Schedules Template (Loans, Mortgages,...
User friendly template including Amortization Schedules for Loans, Mortgages, Operating and Finance ... Read more
Debt Fund Excel Model
Explore the Debt Fund Excel Model, a comprehensive tool for evaluating new credit fund opportunities... Read more
Top 16 Google Sheet Templates
This is a bundle of all the most useful and efficient google sheet templates I have built over the y... Read more
Accounting Financial Model Bundle
This is a combined collection of Excel and Google Sheets financial model templates for Accounting an... Read more
Bundle – Business Financial Forecasting Mode...
The purpose of this Bundle of Business Forecasting and Financial Models is to assist Business Owners... Read more
Commercial Bank Financial Model
Commercial Banking Financial Model presents the case of a commercial bank with regulatory thresholds... Read more
Buy Now Pay Later DCF Model & Valuation (10 Ye...
The Buy Now Pay Later (BNPL) Company financial model is a comprehensive tool designed to analyze the... Read more
You must log in to submit a review.