Investigate under what conditions it is financially better to rent or buy a house by comparing the present value of buying to the present value of renting

For this purpose you are to create an Excel spreadsheet which will:

allow the user to input relevant assumptions

present the relevant results comparing the present value of buying a house to the present value of renting.

The relevant input assumptions are:

savings rate (per annum) the interest rate which the user can earn on savings over the long term (e.g. 1020 years)

house purchase price (assume purchased today) buying costs (as % of purchase price)

deposit on the house

term of mortgage (years) the number of years over which the home loan will be fully amortised (ie fully repaid at end of the term)

mortgage interest rate (per annum) enter as nominal annual interest rate, compounded monthly, as this is the normal approach that Australian banks take when quoting mortgage rates

annual running costs for house ownership (starting amount)

rate of increase in running costs (per annum)

period until house sold (years)

selling costs (as % of sale price)

rate of growth in rent (per annum)

rental yield (initial % and long term %, and number of years over which it will change from initial to long term %), which is measure of investment return on property

Assume all regular cashflows such as mortgage payments, running costs and rental payments, are made annually at the end of the year

Buying and selling costs for the house, although comprised of many different items some of which are fixed costs and others which are variable, to be treated as a % of the purchase/sale price respectively

Unless otherwise specified (e.g. rental yield), all rates remain constant throughout the period until house is sold.

Calculation logic:

Mortgage payments should be calculated as a fixed dollar per annum so that the loan is paid off by the end of the term of the mortgage, using the relevant mortgage interest rate.

Note: Calculate the regular mortgage payment in a manner consistent with the model assumptions (that is, assumed payable annually at the end of the year) using an appropriate effective interest rate (that is, annual effective interest).

Future house price in year t should be calculated as Rent in year t / Rental yield in year t. This approach to predicting the future sale price of the house is a rearrangement of the equation commonly used to determine rental yield (= rental income / market value). This approach is reasonable as rental yield tends to be stable in the long term. It also maintains consistency (correlation) between rents and market value of houses over the long term.

Rent to increase with annual growth in rent

Rental yield to increase evenly each year from initial % to long term % over nominated period of years (input), then remain constant.

Present value should be calculated using the savings rate (input) as the interest rate. This is appropriate in this case since the savings rate might be considered a reasonable measure of the cost of money as the user might be expected to save the difference in the regular cashflows under the two options (rent or buy).