Year
1
2
3
4
5
NOI
475,000
489,250
503,928
519,045
534,617
Debt Service
0
0
0
0
0
Before-tax Cash Flow
475,000
489,250
503,928
519,045
534,617
Equity Participation
0
0
0
0
0
Cash Flow after Participation
475,000
489,250
503,928
519,045
534,617
NOI
475,000
489,250
503,928
519,045
534,617
Less: Interest
0
0
0
0
0
Depreciation
102,564
102,564
102,564
102,564
102,564
Participation
0
0
0
0
0
Taxable Income
372,436
386,686
401,363
416,481
432,053
Tax (Savings)
104,282
108,272
112,382
116,615
120,975
ATCF after Participation
370,718
380,978
391,546
402,431
413,642
Cash flow from sale in year
5
Sales Price
6,000,000
Sales costs
0
Mortgage Balance
0
Before-tax cash flow
6,000,000
Participation in Gain
0
BTCF after Participation
6,000,000
Sales Price
6,000,000
Sales Costs
0
Participation
0
Original Cost Basis
5,000,000
Accumulated Depreciation
512,821
Adjusted Basis
4,487,179
Capital Gain
1,512,821
Tax from Sale
423,590
After-tax cash flow from sale
5,576,410
EQUITY
Year
0
1
2
3
4
5
BTCF after Participation
(5,000,000)
475,000
489,250
503,928
519,045
6,534,617
BTIRR on Equity
13.10%
Year
0
1
2
3
4
5
ATCF
(5,000,000)
370,718
380,978
391,546
402,431
5,990,052
ATIRR on Equity
9.70%
Break-even Interest Rate
13.47%
(b) continued – Projected cost of participation.
Cost of Participation
Year
0
1
2
3
4
5
Debt service
408,915
408,915
408,915
408,915
408,915
Loan balance
3,531,141
Participation
26,434
32,134
38,005
44,052
50,281
Loan amount
-3,750,000
Cash flows to lender
-3,750,000
435,349
441,049
446,920
452,967
3,990,337
IRR on Loan
10.95%
IRR
Problem 12-3
(a)
Asking Price
$1,500,000
Rent year 1
$120,000
Growth-NOI
3.00%
Loan-to-Value
70.00%
Loan Interest
8.00%
Loan term
25
years
Appreciation rate
3.00%
Holding Period
5
years
Selling costs
0.00%
of sale price
Required DCR
1.20
DCR
1.23
1.27
1.31
1.35
1.39
Cash flow from sale in year
5
Sales Price
1,738,911
Sales costs
0
Mortgage Balance
968,876
Before-tax cash flow
770,035
BTIRR on Equity
Year
0
1
2
3
4
5
BTCF
(450,000)
22,751
26,351
30,059
33,878
807,847
BTIRR on Equity
16.66%
In this case the DCR is greater than 1.23. Thus, the first-year NOI is 23% higher than necessary to support the debt service.
Based on this criteria, the loan would probably be acceptable.
(b) The maximum loan amount would be $1,067,478
Step 1, Calculate the payment:
(c) (Change 8 to 10% and 3 to 5%. All other variables are constant.)
Asking Price
$1,500,000
Rent year 1
$120,000
Growth-NOI
5.00%
Loan-to-Value
70.00%
Loan Interest
10.00%
Loan term
25
years
Appreciation rate
5.00%
Holding Period
5
years
Selling costs
0.00%
of sale price
Required DCR
1.20
Equity
450,000
Loan
1,050,000
Annual Loan Payment
114,496
Mortgage Balance
988,720
year
5
Year
1
2
3
4
5
NOI
120,000
126,000
132,300
138,915
145,861
Debt Service
114,496
114,496
114,496
114,496
114,496
BTCF
5,504
11,504
17,804
24,419
31,364
DCR
1.05
1.10
1.16
1.21
1.27
Cash flow from sale in year
5
Sales Price
1,914,422
Sales costs
0
Mortgage Balance
988,720
Before-tax cash flow
925,703
BTIRR on Equity
Year
0
1
2
3
4
5
BTCF
(450,000)
5,504
11,504
17,804
24,419
957,067
BTIRR on Equity
18.25%
The DCR is now much less than 1.2 and it is barely above 1.0. This does not provide a safety margin for the lender. It is not
likely that this loan would be made.
Problem 12-4
(REFER TO TEMPLATE 12_4.XLS)
ASSUMPTIONS:
Purchase Price
2,500,000
NOI
200,000
Loan to Value Ratio
80.00%
Loan Interest Rate
12.00%
Payments per Year
12
Annual Payment Increase
10.00%
Required DCR
1.25
Holding Period
5
years
Year
1
2
3
4
5
NOI
200,000
200,000
200,000
200,000
200,000
Debt Service
160,000
176,000
193,600
212,960
234,256
DCR
1.25
1.14
1.03
0.94
0.85
Beginning
Ending
Loan to
Month
Balance
Interest
Principal
Payment
Balance
Value Ratio
0
2,000,000.00
80.00%
1
2,000,000.00
20,000.00
(6,666.67)
13,333.33
2,006,666.67
80.27%
2
2,006,666.67
20,066.67
(6,733.33)
13,333.33
2,013,400.00
80.54%
3
2,013,400.00
20,134.00
(6,800.67)
13,333.33
2,020,200.67
80.81%
4
2,020,200.67
20,202.01
(6,868.67)
13,333.33
2,027,069.34
81.08%
5
2,027,069.34
20,270.69
(6,937.36)
13,333.33
2,034,006.70
81.36%
6
2,034,006.70
20,340.07
(7,006.73)
13,333.33
2,041,013.43
81.64%
7
2,041,013.43
20,410.13
(7,076.80)
13,333.33
2,048,090.23
81.92%
8
2,048,090.23
20,480.90
(7,147.57)
13,333.33
2,055,237.80
82.21%
9
2,055,237.80
20,552.38
(7,219.04)
13,333.33
2,062,456.85
82.50%
10
2,062,456.85
20,624.57
(7,291.24)
13,333.33
2,069,748.08
82.79%
11
2,069,748.08
20,697.48
(7,364.15)
13,333.33
2,077,112.23
83.08%
12
2,077,112.23
20,771.12
(7,437.79)
13,333.33
2,084,550.02
83.38%
13
2,084,550.02
20,845.50
(6,178.83)
14,666.67
2,090,728.85
83.63%
14
2,090,728.85
20,907.29
(6,240.62)
14,666.67
2,096,969.48
83.88%
15
2,096,969.48
20,969.69
(6,303.03)
14,666.67
2,103,272.50
84.13%
16
2,103,272.50
21,032.73
(6,366.06)
14,666.67
2,109,638.56
84.39%
17
2,109,638.56
21,096.39
(6,429.72)
14,666.67
2,116,068.28
84.64%
18
2,116,068.28
21,160.68
(6,494.02)
14,666.67
2,122,562.30
84.90%
19
2,122,562.30
21,225.62
(6,558.96)
14,666.67
2,129,121.25
85.16%
20
2,129,121.25
21,291.21
(6,624.55)
14,666.67
2,135,745.80
85.43%
21
2,135,745.80
21,357.46
(6,690.79)
14,666.67
2,142,436.59
85.70%
22
2,142,436.59
21,424.37
(6,757.70)
14,666.67
2,149,194.29
85.97%
23
2,149,194.29
21,491.94
(6,825.28)
14,666.67
2,156,019.57
86.24%
24
2,156,019.57
21,560.20
(6,893.53)
14,666.67
2,162,913.10
86.52%
25
2,162,913.10
21,629.13
(5,495.80)
16,133.33
2,168,408.89
86.74%
26
2,168,408.89
21,684.09
(5,550.76)
16,133.33
2,173,959.65
86.96%
27
2,173,959.65
21,739.60
(5,606.26)
16,133.33
2,179,565.91
87.18%
28
2,179,565.91
21,795.66
(5,662.33)
16,133.33
2,185,228.24
87.41%
29
2,185,228.24
21,852.28
(5,718.95)
16,133.33
2,190,947.19
87.64%
30
2,190,947.19
21,909.47
(5,776.14)
16,133.33
2,196,723.32
87.87%
31
2,196,723.32
21,967.23
(5,833.90)
16,133.33
2,202,557.22
88.10%
32
2,202,557.22
22,025.57
(5,892.24)
16,133.33
2,208,449.46
88.34%
33
2,208,449.46
22,084.49
(5,951.16)
16,133.33
2,214,400.62
88.58%
34
2,214,400.62
22,144.01
(6,010.67)
16,133.33
2,220,411.30
88.82%
35
2,220,411.30
22,204.11
(6,070.78)
16,133.33
2,226,482.08
89.06%
36
2,226,482.08
22,264.82
(6,131.49)
16,133.33
2,232,613.56
89.30%
37
2,232,613.56
22,326.14
(4,579.47)
17,746.67
2,237,193.03
89.49%
38
2,237,193.03
22,371.93
(4,625.26)
17,746.67
2,241,818.30
89.67%
39
2,241,818.30
22,418.18
(4,671.52)
17,746.67
2,246,489.81
89.86%
40
2,246,489.81
22,464.90
(4,718.23)
17,746.67
2,251,208.05
90.05%
41
2,251,208.05
22,512.08
(4,765.41)
17,746.67
2,255,973.46
90.24%
42
2,255,973.46
22,559.73
(4,813.07)
17,746.67
2,260,786.53
90.43%
43
2,260,786.53
22,607.87
(4,861.20)
17,746.67
2,265,647.73
90.63%
44
2,265,647.73
22,656.48
(4,909.81)
17,746.67
2,270,557.54
90.82%
45
2,270,557.54
22,705.58
(4,958.91)
17,746.67
2,275,516.44
91.02%
46
2,275,516.44
22,755.16
(5,008.50)
17,746.67
2,280,524.94
91.22%
47
2,280,524.94
22,805.25
(5,058.58)
17,746.67
2,285,583.53
91.42%
48
2,285,583.53
22,855.84
(5,109.17)
17,746.67
2,290,692.69
91.63%
49
2,290,692.69
22,906.93
(3,385.59)
19,521.33
2,294,078.29
91.76%
50
2,294,078.29
22,940.78
(3,419.45)
19,521.33
2,297,497.74
91.90%
51
2,297,497.74
22,974.98
(3,453.64)
19,521.33
2,300,951.38
92.04%
52
2,300,951.38
23,009.51
(3,488.18)
19,521.33
2,304,439.56
92.18%
53
2,304,439.56
23,044.40
(3,523.06)
19,521.33
2,307,962.62
92.32%
54
2,307,962.62
23,079.63
(3,558.29)
19,521.33
2,311,520.92
92.46%
55
2,311,520.92
23,115.21
(3,593.88)
19,521.33
2,315,114.79
92.60%
56
2,315,114.79
23,151.15
(3,629.81)
19,521.33
2,318,744.61
92.75%
57
2,318,744.61
23,187.45
(3,666.11)
19,521.33
2,322,410.72
92.90%
58
2,322,410.72
23,224.11
(3,702.77)
19,521.33
2,326,113.49
93.04%
59
2,326,113.49
23,261.13
(3,739.80)
19,521.33
2,329,853.30
93.19%
60
2,329,853.30
23,298.53
(3,777.20)
19,521.33
2,333,630.50
93.35%
(a)
(b)
Problem 12-5
(b)
Interest accrues at 10%. On $1,000,000 this is $100,000 per year or $8,333.33 per month. In this case, payments do not
cover the interest, although the loan would not be amortized in 30 years. For this reason the future value of the loan will be
(c)
Using the same approach as above we can get the balance after 5 years.
(d)
To amortize the loan over the remaining 10 years, we can use a financial calculator as follows:
Problem 12-6
Purchase price
$1,000,000
Loan amount
$900,000
Interest rate
8.00%
Initial NOI
$100,000
Loan term
20
Growth in NOI
3.00%
Monthly payment
$7,527.96
Base for participation
$100,000
Annual payment
$90,335.53
Participation % of NOI
50.00%
Terminal cap rate
10.00%
Participation in price increase
50.00%
Resale price
$1,343,916
Purchase price
$1,000,000
Increase in value
$343,916
Participation in sale
$171,958
Loan balance at resale
$620,466
Year
NOI
Excess over base
Participation
Debt service
Lender cash flow
0
-$900,000
1
100,000
0
0
90,336
90,336
2
103,000
3,000
1,500
90,336
91,836
3
106,090
6,090
3,045
90,336
93,381
4
109,273
9,273
4,636
90,336
94,972
5
112,551
12,551
6,275
90,336
96,611
6
115,927
15,927
7,964
90,336
98,299
7
119,405
19,405
9,703
90,336
100,038
8
122,987
22,987
11,494
90,336
101,829
9
126,677
26,677
13,339
90,336
103,674
10
130,477
30,477
187,197
90,336
897,998
*
11
134,392
IRR
9.95%
*Includes participation in NOI, Resale, and loan balance
Problem 12-7
(a)
Purchase price
$1,000,000
Loan amount
$900,000
Interest rate
9.00%
Initial NOI
$100,000
Loan term
20
Growth in NOI
3.00%
Monthly payment
$8,097.53
Base for participation
$100,000
Annual payment
$97,170.40
Participation % of NOI
50.00%
Terminal cap rate
10.00%
Conversion percentage
60.00%
Resale price
$1,343,916
Conversion value
$806,350
Loan balance at resale
$639,233
Lender cash flow if no default
$806,350
(lender gets higher of conversion value or loan balance)
Lender cash flow if default allowed
$806,350
(lender gets property if less than loan balance at resale)
Year
Debt service
Lender cash flow
0
-$900,000
1
97,170
97,170
2
97,170
97,170
3
97,170
97,170
4
97,170
97,170
5
97,170
97,170
6
97,170
97,170
7
97,170
97,170
8
97,170
97,170
9
97,170
97,170
10
97,170
903,520
*
IRR
10.15%
*Includes debt service plus either loan balance, conversion value or default proceeds.
Note: In this case the lender would want to convert to ownership in the property because 60% of the resale value is
greater than the loan balance.
(b)
Purchase price
$1,000,000
Loan amount
$900,000
Interest rate
9.00%
Initial NOI
$100,000
Loan term
20
Growth in NOI
3.00%
Monthly payment
$8,097.53
Base for participation
$100,000
Annual payment
$97,170.40
Participation % of NOI
50.00%
Terminal cap rate
10.00%
Conversion percentage
60.00%
Resale price
$1,000,000
Conversion value
$600,000
Loan balance at resale
$639,233
Lender cash flow if no default
$639,233
(lender gets higher of conversion value or loan balance)
Lender cash flow if default allowed
$639,233
(lender gets property if less than loan balance at resale)
Year
Debt service
Lender cash flow
0
-$900,000
1
97,170
97,170
2
97,170
97,170
3
97,170
97,170
4
97,170
97,170
5
97,170
97,170
6
97,170
97,170
7
97,170
97,170
8
97,170
97,170
9
97,170
97,170
10
97,170
736,403
*
IRR
8.88%
*Includes debt service plus either loan balance, conversion value or default proceeds.
Note: In this case the lender would not want to convert because the mortgage balance after 10 years is greater than
(c)
Purchase price
$1,000,000
Loan amount
$900,000
Interest rate
9.00%
Initial NOI
$100,000
Loan term
20
Growth in NOI
3.00%
Monthly payment
$8,097.53
Base for participation
$100,000
Annual payment
$97,170.40
Participation % of NOI
50.00%
Terminal cap rate
10.00%
Conversion percentage
60.00%
Resale price
$500,000
Conversion value
$300,000
Loan balance at resale
$639,233
Lender cash flow if no default
$639,233
(lender gets higher of conversion value or loan balance)
Lender cash flow if default allowed
$500,000
(lender gets property if less than loan balance at resale)
Year
Debt service
Lender cash flow
0
-$900,000
1
97,170
97,170
2
97,170
97,170
3
97,170
97,170
4
97,170
97,170
5
97,170
97,170
6
97,170
97,170
7
97,170
97,170
8
97,170
97,170
9
97,170
97,170
10
97,170
597,170
*
IRR
7.68%
*Includes debt service plus either loan balance, conversion value or default proceeds.
Problem 12-8
This question is like an example in the chapter.
(a) Reinvestment Rate = 6% + 1.5%
Problem 12-9
Problem 12-10