Assume you are buying an existing apartment complex and have made the following
ID: 2620558 • Letter: A
Question
Assume you are buying an existing apartment complex and have made the following assumptions:
Data
Acquisition Cost
$5,000,000
Project Length
5
Year 1 Rental Income
$560,000
Rental Income Annual Growth
3%
Year 1 Expenses
$200,000
Expenses Annual Growth
2%
Ordinary Tax Rate
28%
Capital Gains Tax Rate
15%
Annual Appreciation Rate of Property Market Value
3%
30-year Fixed Fully Amortizing Loan (monthly payments)
Term in Years
30
Interest Rate (Annualized)
6%
LTV
75%
The proceeds of the loan are received at time 0 and the first operating revenue is received one year later, at the end of year 1. For simplicity, assume there is no depreciation. You plan to sell the property at the end of year 5.
a) Construct an amortization table for the loan.
b) Calculate the before tax and after-tax IRR of your investment.
Data
Acquisition Cost
$5,000,000
Project Length
5
Year 1 Rental Income
$560,000
Rental Income Annual Growth
3%
Year 1 Expenses
$200,000
Expenses Annual Growth
2%
Ordinary Tax Rate
28%
Capital Gains Tax Rate
15%
Annual Appreciation Rate of Property Market Value
3%
30-year Fixed Fully Amortizing Loan (monthly payments)
Term in Years
30
Interest Rate (Annualized)
6%
LTV
75%
Explanation / Answer
The balance of 44 is to be avoided as it has come up from roundin off error. the above solution has been done on an excelsheet.
Theamount of equal Annnual Installments have come from afinancial calculator. Put in+/- 5,000,000 PV,then insert 30 as N,put in 6 as I/Y and then calculate the PMT.
1(b)
To calculate the Pre tax & Post Tax IRR, in the financial calculator use the CF mode. put in +/- 5000000 as CF0 and put the yearly data as given above in the next CF as CF1,CF2,CF3,CF4,CF5 with frequencyfor all as 1 and then press CPT and then IRR. The appreciated value at the end of year 5 will also come in year 5 along with the revenue.
Year 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 (A) Principle/Amount Outstanding 50,00,000 49,36,756 4869717 4798656 4723332 4643488 4558853 4469140 4374045 4273243 4166394 4053133 3933077 3805818 3670923 3527935 3376367 3215705 3045403 2864883 2673532 2470700 2255698 2027796 1786220 1530149 1258714 970992.6 666008.1 342724.6 (B) Add : Interest 300000 296205.4 292183 287919.4 283399.9 278609.3 273531.2 268148.4 262442.7 256394.6 249983.6 243188 235984.6 228349.1 220255.4 211676.1 202582 192942.3 182724.2 171893 160411.9 148242 135341.9 121667.8 107173.2 91808.93 75522.82 58259.55 39960.49 20563.48 (C ) Total amount Due (A+B) 53,00,000 5232961 5161900 5086576 5006732 4922097 4832384 4737289 4636487 4529638 4416377 4296321 4169062 4034167 3891179 3739611 3578949 3408647 3228127 3036776 2833944 2618942 2391040 2149464 1893393 1621958 1334237 1029252 705968.6 363288.1 (D ) Total Equal Amount Paid 3,63,244 363244 363244 363244 363244 363244 363244 363244 363244 363244 363244 363244 363244 363244 363244 363244 363244 363244 363244 363244 363244 363244 363244 363244 363244 363244 363244 363244 363244 363244 (E) Payment Made Towards Principal (D-B) 63,244 67038.64 71060.96 75324.62 79844.09 84634.74 89712.82 95095.59 100801.3 106849.4 113260.4 120056 127259.4 134894.9 142988.6 151567.9 160662 170301.7 180519.8 191351 202832.1 215002 227902.1 241576.2 256070.8 271435.1 287721.2 304984.4 323283.5 342680.5 (F) Balance Outstanding (A-E) 49,36,756 4869717 4798656 4723332 4643488 4558853 4469140 4374045 4273243 4166394 4053133 3933077 3805818 3670923 3527935 3376367 3215705 3045403 2864883 2673532 2470700 2255698 2027796 1786220 1530149 1258714 970992.6 666008.1 342724.6 44.071