2012 Finals: Investment Property

Description
This workbook is taken from the Finals of the 2012 Modeloff financial modeling world championships.

Follow the instructions in order to complete the model:

INTRODUCTION

You have decided to buy an apartment in Saratoga for US$1,600,000 and rent it out to take advantage of San Francisco’s thriving rental market!

Income and Expense

As a result of the investment going into the area, you hope to sell the apartment in the next 10 years for double what you paid for it. For the purposes of this model, assume that the property value will grow at a constant % per annum with growth compounding. Further assumptions are as follows:

 Rental income is estimated to be $83,200 per annum
 Agency fees will be 9% of rent per annum
 Rental income will increase annually by 2% in the first two years and by 5% thereafter.

You also estimate you will have to spend at least 5% of rental income per annum maintaining the property, and you have set a budget of $10,000 to furnish the apartment prior to renting it.

Borrowing

ABC bank has offered you a 25 year mortgage with:

 A fixed interest rate of 5% on the outstanding principle for the duration of the loan.
 A bank charge of a $10,000 arrangement fee.
 A maximum loan of $900,000

The loan allows early repayment of principal and you intend to use all free cash flow to pay down principal at the end of each year. You will contribute the remaining funds required to complete the purchase.

Tax

 Rental income is taxable
 Interest expense, letting agent fees and maintenance expenses are all tax deductible.
 Assume a tax rate of 35% for your analysis and that tax is paid promptly at year end.

When you sell the property, capital gains tax will be payable. To calculate your capital gain on sale, compare your total purchase price (which will include the house purchase price, mortgage fees, and furniture cost) to the sale price. The difference is the capital gain on the property. Assume a capital gains tax rate of 20%.

The Model

You have decided to build a financial model of this situation for number of reasons. Firstly, the bank has offered you the mortgage based on the condition that you provide them with a yearly rental income cash flow forecast. They want to ensure that the apartment will generate enough rental income to comfortably
service the mortgage. You also want to use the financial model to calculate the estimated return on your investment and to calculate the best time to exit your investment to maximise your IRR.

Allotted time: 20 minutes

Once finished, feel free to upload your model to your own dedicated author channel!