Question

Please show details of solving. Thank you RES 3200

Bob buys a property that costs $5,000,000.

Year	NOI
1	$300,000
2	$315,000
3	$330,000

Bob will own the property for two years.

The NOI from the property for years 1-3 is to the right:

Bob will sell the property at the end of year 2 at a cap rate that is 50 basis points lower than the cap rate at which he bought the property.

Assume Bob finances his purchase with a 50% LTV Fixed Rate IO loan at an annual rate of 5% with annual compounding and annual payments. What is Bob’s annualized IRR for the investment in question?

Answer 1

a. Calculation of sale value of property at the end of year 2

Cap rate while purchasing = NOI of year 1 / Value of property = $300,000/$5,000,000 = 6%

Cap rate while selling = 6% - 50 basis points = 6%-0.5% = 5.5%

Sale value of property at the end of year 2 = NOI of year 3 / Cap rate (deriving this equation from the above)

= $330,000/5.5% = $ 6,000,000

b. Calculation of annual payment on mortgage:

Value of property = $5,000,000

LTV (loan to value) = 50%

Thus, Loan value = $5,000,000*50% = $2,500,000

Interest rate = 5%

Type of Loan = IO or Interest only

Thus, interest paid per annum = $2,500,000 * 5% = $125,000

c. Cash-flows for two years:

Year	Property Cost	NOI	Interest	Sale Value	Loan Repayment	Net Cash-flows
0	(2,500,000)					(2,500,000)
1		300,000	(125,000)			175,000
2		315,000	(125,000)	6,000,000	(2,500,000)	3,690,000

Note:

a. Since loan was taken for $2,500,000, net cash-outflow in year 0 is $2,500,000.

b. Since this is a interest only loan, on sale of the property, the loan principal outstanding of $2,500,000 to be repaid. Hence it is a cash-outflow in end of year 2.

d. Annualised IRR for the investment:

IRR is the rate of return at which NPV of the project equals zero. IRR can be found using trial and error method.

Lets first compute the NPV at a discount rate of say 20%.

NPV at 20% discount rate = -2500000*1+175000*(1/(1+20%))+3690000*(1/(1+20%)^2) = -2500000+145833+2562500 = $208,333

Since the NPV is positive and we need NPV to be zero, lets take a higher discount rate, say 26%.

NPV at 26% discount rate = -2500000*1+175000*(1/(1+26%))+3690000*(1/(1+26%)^2) = -2500000+138889+2324263= -$36,848

At discount rate of 20%, NPV is $208,333 and at discount rate of 26%, NPV is -$36,848. For the 6% movement in NPV (26%-20%), NPV moved by $245,181 ($208,333-(-$36,848)). Thus, for NPV to be zero, NPV to be moved by -36,848 and the corresponding movement in rate required =36848/245181*6% = -0.90%

Thus, IRR = 26%-0.90% = 25.098%.

Due to round-off, NPV at 25.10% discount rate may not be exactly zero and could be in few thousands. To compute exact IRR, excel function IRR can be used which returns the IRR as 25.04% as follows: