USING EXCEL MAKE A SPREADSHEET

Problem Statement You are advising a client who is considering investing in rental property.

They wish to purchase the property and hold the investment for approxi- mately 20 years. Your client will liquidate assets from their current portfolio

worth 100, 000 and put this money towards the deposit or down payment for the property. The remainder of the cost of purchasing the property will be financed via a commercial mortgage. Two residential properties have been identified as potential purchases. Property A has a current selling price of e360, 000. Property B has a current selling price of e285, 000. Whilst owning either of these properties, your client intends to rent it out. They have the option of using the property for short-term or long-term rentals. Data from the rental market in the area suggests the following for the first year. Property A would command a monthly rent of e2100 per month as a long term let. As a short term let, Property A would recieve e4000 per month in the peak tourist season of May to October and e600 per month in the off-season. Property B would command a rent of 1, 600 per month as a long term let. As a short term let, Property B would recieve e2600 per month in the peak tourist season of May to October and e300 per month in the off-season.

You may assume that either property can be purchased and let immedi- ately starting January of next year. Your research also shows that income

from short-term rentals tend to go up by 5% per annum effective. The local laws allow long- term rents to be increased by 4% every two years. At the end of the 20 years, your client will sell the property. The Central Statistics Office has recently published a report showing that house price inflation across the country has remained at 3% per annum for several years. Furthermore, the report shows that no change in the level of house price inflation is expected in the near future.

During the rental period, your client expects to incur general manage- ment and maintenance expenses on the property. These have been estimated

at e50 and e400 per month on the long and short term. They believe that

1

efficiencies can be achieved over time which will enable it to maintain the level of expenses constant over the full term of the mortgage. The mortgage rates on a 20 year mortgage are 50% < LTV 60% 4.5% 60% < LTV 75% 5.0% where LTV denotes the ratio of loan-to-value and the interest rate quoted is the annual effective rate. The mortgages are capital repayment mortgages. Where the mortgage is paid on a monthly basis, with each payment repaying the monthly interest costs on the outstanding balance of the loan, and part of the outstanding balance on the loan. The borrower has the option of deferring the first payment till the beginning of the second year. The monthly repayment is calculated such that the loan is paid off after 240 months. Questions 1. Produce a loan schedule to illustrate the loan outstanding each month for the mortgages financing each purchase. 2. Produce a cashflow model to illustrate your clients expected cashflows over the life of the property for each of their four investment plans (buy A and let long-term, buy A and let short-term etc.). 3. Determine the net present value of the cashflows under each investment plan at each projected time period using an interest rate of 2.4% per annum.

4. Suppose a tax is introduced on short-term rental income that is collected annually in arrear. What is the lowest rate of tax at which long term lets become more profitable than short-term lets? 5. Summarise the advice you would give the client.Instructions

Your submission should be one spreadsheet, in any common filetype.