Mortgage vs Rent Template
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 of 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 maitenance 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 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 wether 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.
