Feedback (last time the writer did not pass pass this subject)
Feedback (last time the writer did not pass pass this subject)
Hi Vivian,
Nice commercial property in Bondi. Good that you estimated the price when it was unavailable, but I think the price would be much higher than AUD2m due to the location.
Good low loan rates, but they look like they're all owner-occupier rather than investor loans which are needed here.
Inflation history is good, but it's a pity that you didn't use ILB data with up to date ILB breakeven yields which is a better predictor of the future.
Good references and screenshots.
There are no graphs shown for Q4 and 5 & 6 - this is a serious problem.
In Q4 and 5 the formulas for the loan are not correct, you should use the annuity formula as shown in class. The loans are not paid off at the end and many of the formulas are hard-coded.
The Q5 property valuation is not correct, there's no use of the perpetuity formula and no fall in property price at month 36.
Assessment Information
Assignment 1 - How required returns affect prices: a commercial property funded by a loan
Due: Wednesday 22 March 2023, 11:59pm Sydney time.
Weighting: 40%
Length: One spreadsheet
Format: Short answer numerical spreadsheet exercise (use the template to put your answers to Q1-6)
Template:
The template attached (GMBA8022 T2-2023 Assignment 1 Template) should be adapted to suit your property and loan. The numbers in the existing yellow cells have been determined using the formulas in the first row. This hint should enable you to set up the rest of the spreadsheet and then start playing around with it. Do not use the exact numbers used in this template in your assignment which you submit for marking. Your assignment should be based on your unique selected property, loan and other details asked for in the below questions.
Detailed instructions:
You intend to buy a property or portfolio of investment properties to rent to tenants. The property asset(s) will be partly funded (say 80%) by a bank loan with the remaining portion funded by your own equity. The bank loan will be interest-only (IO) for the first 5 years before it switches to being principal and interest (P&I) for the remainder of the term.
Question 1 (5% mark): Find a competitive interest rate on a business loan (or home loan) in your country. Interest rates that include all fees are best. In Australia this is called the comparison rate. In other countries, feel free to choose from loans with no or low fees and ignore loans with high fees. State the current interest rate per annum and the term (maturity) of the loan in years. Include a screenshot of these details and provide a link to the source.
Question 2 (10% mark): Estimate the future long term inflation rate in your country based on the breakeven inflation rate using inflation-linked bonds. Some central banks have data which outline the breakeven rate. If these are not available in your country, use a nearby country's breakeven inflation rate, together with your country's central bank's target, the past history, market forecasts or other means. Explain your reasoning and include a screenshot of relevant supporting data as well as a link to the source.
Question 3 (5% mark): Find a property or properties worth more than $1m for sale such as commercial property like an office or warehouse, or a number of residential properties. Include a screenshot of the price and provide a link to the source.
Question 4 (30% mark): Make a spreadsheet showing the value of the loan liability and the property asset every month. Show the total loan payment and its principal and interest components every month. Also show the property assets net rental payments every month. Make some assumptions about the cost which will reduce the gross rent. These 6 items are best entered as columns in a spreadsheet with the months as rows. Continue the months rows until the end of the loan term. Use the following assumptions:
The loan can be valued using the annuity formula.
Note the assumptions included in the spreadsheet (you will need to update these to reflect your own case).
The loan interest rate is variable but the interest rate is expected to remain constant throughout the life of the loan.
The property can be valued as a growing perpetuity of monthly rents.
The propertys current price is that found in the previous question.
The propertys expected capital return is equal to the inflation rate found in the previous question. For example, if the inflation rate was 3.6% pa then the monthly inflation rate would be 0.3% per month. This rate would be the propertys capital return (price increase) per month and also the increase in net rent per month. Therefore, if the property price this month was $500,000 and the rent was $750/month, then the next month the price will be $501,500 (=500,000*(1+0.003)^1) and the rent will be $752/month (=750*(1+0.003)^1).
The propertys net rental yield (gross rent less costs of renting this month divided by property price last period) is equal to the loan interest rate per month less the inflation rate per month plus a 0.05 percentage point (5bps) risk premium per month. For example, say the loan interest rate was 4.8% pa and the inflation rate was 3.6% pa, then the monthly net rental yield would be 0.15% (=4.8%/12 - 3.6%/12 + 0.05%) per month. So if the property price found in the previous question was initially $500,000 then the net rent in the first month would be $750 (=500,000*0.0015).
The total required return of the property per month (the r in the perpetuity with growth formula) is the sum of the capital return and the net rental yield per month. The growth rate of rents (the g in the perpetuity with growth formula) will be the inflation rate per month.
The loan-to-valuation ratio (LVR) is initially 80% but it changes afterwards as the loan is paid down and the property price rises by the capital return.
Ignore taxes.
State any other assumptions that you use in your model as necessary.
Include a graph similar to Graph 4 from Thurner and Dwyer (2013), based on figures relevant to your loan:
https://www.rba.gov.au/publications/bulletin/2013/sep/4.html
Question 5 (30% mark): Copy the table made in the previous question, but vary your answer based on a surprise interest rate increase by the central bank which raises interest rates by 0.48 percentage points per annum, which is 0.04 percentage points (or 4 basis points) on a monthly basis, a moment after the 84th monthly loan payment, which is 7 years after the loan was issued.
Include a graph with lines depicting your property price and loan outstanding on the primary y-axis and the 30-year time period on the x-axis. Assume that the:
The loan interest rate per month rose by 0.04 percentage points compared to your answer in the previous question.
Loan repayments were adjusted from the 85th loan payment onward to reflect the changed interest rate. So the total, interest and principal loan payments will change but the loan term is unchanged.
Dollar rents and expected monthly rental growth rates remain unchanged.
The risk-free rate (rf) used to find the propertys total required return (=rf + riskPremium = netRentalYield + capitalReturn) increased by 0.04 percentage points compared to your answer in the previous question. The risk premium remained unchanged. This should result in a property price change between months 83 and 84 to make the total required return equal the expected total return (=netRentalYield + capitalReturn) in the future so the property is always fairly priced.
Ignore taxes.
State any other assumptions that you use in your model as necessary.
Question 6 (20% mark): Suppose that you couldn't afford the higher mortgage payments due to the rate rise detailed in the previous question. Your bank is willing to negotiate a new P&I loan with you at the higher interest rate, with the same P&I monthly loan payments as before (which you could just afford, prior to the rate increase), but you'll have to pay it off over a longer time (more months).
Copy the table made in the previous question but make amendments to show the new longer-maturity loan at the higher interest rate.
Hint: You're likely to need more rows at the bottom of your loan schedule table since the loan will take longer to pay off.
Include a graph with lines depicting your loan outstanding on the primary y-axis and the time period on the x-axis.
State any other assumptions that you use in your model as necessary.
References:
Thurner, M., Dwyer, A. (2013). Partial Mortgage Prepayments and Housing Credit Growth, RBA Bulletin September Quarter 2013. Available at: https://www.rba.gov.au/publications/bulletin/2013/sep/4.html [Accessed 16 Jun. 2020]
