Please clearly and thoroughly explain the steps on how you determine the expected levered-before-tax-annual rate of return on your capital and please determine the portion of the return that is expected from the annual cashflows and the portion that is expected as a result of property price appreciation. The Excel Spreadsheet is included as an attached image for the information. Thank You in advance!

Essentials Of Investments
11th Edition
ISBN:9781260013924
Author:Bodie, Zvi, Kane, Alex, MARCUS, Alan J.
Publisher:Bodie, Zvi, Kane, Alex, MARCUS, Alan J.
Chapter1: Investments: Background And Issues
Section: Chapter Questions
Problem 1PS
icon
Related questions
Question

Please clearly and thoroughly explain the steps on how you determine the expected levered-before-tax-annual rate of return on your capital and please determine the portion of the return that is expected from the annual cashflows and the portion that is expected as a result of property price appreciation. The Excel Spreadsheet is included as an attached image for the information. Thank You in advance!           

Apartment Complex
Inputs:
Acquisition Price
Expected 1st Year NOI
"Going in" CAP
CAP over (below) market
Required Rate of Return
Expected NOI Growth
Terminal NOI Growth
Acquisition Price + Closing Costs $
End of year
0
1
Total
2
3
4
5
6
7
8
Amortization schedule:
0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
I
$2,890,000 Down Payment
$149,991 Mortgage Rate
5.19% Maturity
0.50% Levered Required Return
8.50% Minimum Required DCR
3.50% Expected Treasury Yield
2.90% Expected LT Inflation.
2,962,250 Expected Risk Premium (bp)
NOI
$149,991
$155,241
$160,674
$166,298
$172,118
$178,142
$184,377
$190,830
$100,717
$98,206
$95,524
$92,660
$89,601
$86,334
$82,845
$79,119
$75,140
$70,890
$66,350
$61,503
$56,325
$50,796
$44,890
$38,583
$31,847
$24,653
$16,969
$8,764
$1,271,714
P
P
Acquisition/Disposition
($2,962,250)
$3,195,084
Total PV:
NPV:
IRR:
Outputs:
$ 1,481,125 Terminal CAP
6.80% Loan Amount
20 Mortgage Payment (Annual)
11.00% DCR
$62,502
$66,752
$71,292
$76,139
$81,317
$86,846
$92,752
$99,059
$105,795
$112,989
$120,672
$128,878
1.2 Mimimum DCR Satisfied?
4.30% Mortgage Balance at Sale
2.90%
400
CF
($2,962,250)
$149,991
$155,241
$160,674
$166,298
$172,118
$178,142
$184,377
$3,385,915
Ending balance
$1,481,125
$36,925 $1,444,200
$39,436
$1,404,763
$42,118
$1,362,645
$44,982
$1,317,663
$48,041 $1,269,622
$2,606,659
-$355,591
6.45%
$51,308 $1,218,315
$54,797
$1,163,518
$58,523
$1,104,995
$1,042,493
$975,741
$904,449
I + P
$828,310
$746,993
$660,146
$567,394
$468,335
$362,540
$249,551
$128,878
$0
$1,481,125 $2,752,839
PV of CF
($2,962,250)
$138,241
$131,870
$125,793
$119,996
$114,466
$109,191
$104,160
$1,762,942
5.900% Closing Fees
$1,481,125 Comission
$137,642 Closing Costs $
1.09 Selling Expenses $
No
$1,104,995
DS
($137,642)
($137,642)
($137,642)
($137,642)
($137,642)
($137,642)
($137,642)
($1,242,637)
Total PV:
NPV:
IRR:
Levered CF
$ (1,481,125)
$12,349
$17,599
$23,032
$28,656
$34,476
$40,500
$46,735
$2,143,277
$1,055,775
($425,350)
6.17%
2.50%
4.00%
72,250
133,128
PV of Levered CF
($1,481,125)
$11,125
$14,284
$16,841
$18,876
$20,460
$21,653
$22,510
$930,025
Transcribed Image Text:Apartment Complex Inputs: Acquisition Price Expected 1st Year NOI "Going in" CAP CAP over (below) market Required Rate of Return Expected NOI Growth Terminal NOI Growth Acquisition Price + Closing Costs $ End of year 0 1 Total 2 3 4 5 6 7 8 Amortization schedule: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 I $2,890,000 Down Payment $149,991 Mortgage Rate 5.19% Maturity 0.50% Levered Required Return 8.50% Minimum Required DCR 3.50% Expected Treasury Yield 2.90% Expected LT Inflation. 2,962,250 Expected Risk Premium (bp) NOI $149,991 $155,241 $160,674 $166,298 $172,118 $178,142 $184,377 $190,830 $100,717 $98,206 $95,524 $92,660 $89,601 $86,334 $82,845 $79,119 $75,140 $70,890 $66,350 $61,503 $56,325 $50,796 $44,890 $38,583 $31,847 $24,653 $16,969 $8,764 $1,271,714 P P Acquisition/Disposition ($2,962,250) $3,195,084 Total PV: NPV: IRR: Outputs: $ 1,481,125 Terminal CAP 6.80% Loan Amount 20 Mortgage Payment (Annual) 11.00% DCR $62,502 $66,752 $71,292 $76,139 $81,317 $86,846 $92,752 $99,059 $105,795 $112,989 $120,672 $128,878 1.2 Mimimum DCR Satisfied? 4.30% Mortgage Balance at Sale 2.90% 400 CF ($2,962,250) $149,991 $155,241 $160,674 $166,298 $172,118 $178,142 $184,377 $3,385,915 Ending balance $1,481,125 $36,925 $1,444,200 $39,436 $1,404,763 $42,118 $1,362,645 $44,982 $1,317,663 $48,041 $1,269,622 $2,606,659 -$355,591 6.45% $51,308 $1,218,315 $54,797 $1,163,518 $58,523 $1,104,995 $1,042,493 $975,741 $904,449 I + P $828,310 $746,993 $660,146 $567,394 $468,335 $362,540 $249,551 $128,878 $0 $1,481,125 $2,752,839 PV of CF ($2,962,250) $138,241 $131,870 $125,793 $119,996 $114,466 $109,191 $104,160 $1,762,942 5.900% Closing Fees $1,481,125 Comission $137,642 Closing Costs $ 1.09 Selling Expenses $ No $1,104,995 DS ($137,642) ($137,642) ($137,642) ($137,642) ($137,642) ($137,642) ($137,642) ($1,242,637) Total PV: NPV: IRR: Levered CF $ (1,481,125) $12,349 $17,599 $23,032 $28,656 $34,476 $40,500 $46,735 $2,143,277 $1,055,775 ($425,350) 6.17% 2.50% 4.00% 72,250 133,128 PV of Levered CF ($1,481,125) $11,125 $14,284 $16,841 $18,876 $20,460 $21,653 $22,510 $930,025
Expert Solution
steps

Step by step

Solved in 5 steps

Blurred answer
Knowledge Booster
Financial Planning
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, finance and related others by exploring similar questions and additional content below.
Similar questions
  • SEE MORE QUESTIONS
Recommended textbooks for you
Essentials Of Investments
Essentials Of Investments
Finance
ISBN:
9781260013924
Author:
Bodie, Zvi, Kane, Alex, MARCUS, Alan J.
Publisher:
Mcgraw-hill Education,
FUNDAMENTALS OF CORPORATE FINANCE
FUNDAMENTALS OF CORPORATE FINANCE
Finance
ISBN:
9781260013962
Author:
BREALEY
Publisher:
RENT MCG
Financial Management: Theory & Practice
Financial Management: Theory & Practice
Finance
ISBN:
9781337909730
Author:
Brigham
Publisher:
Cengage
Foundations Of Finance
Foundations Of Finance
Finance
ISBN:
9780134897264
Author:
KEOWN, Arthur J., Martin, John D., PETTY, J. William
Publisher:
Pearson,
Fundamentals of Financial Management (MindTap Cou…
Fundamentals of Financial Management (MindTap Cou…
Finance
ISBN:
9781337395250
Author:
Eugene F. Brigham, Joel F. Houston
Publisher:
Cengage Learning
Corporate Finance (The Mcgraw-hill/Irwin Series i…
Corporate Finance (The Mcgraw-hill/Irwin Series i…
Finance
ISBN:
9780077861759
Author:
Stephen A. Ross Franco Modigliani Professor of Financial Economics Professor, Randolph W Westerfield Robert R. Dockson Deans Chair in Bus. Admin., Jeffrey Jaffe, Bradford D Jordan Professor
Publisher:
McGraw-Hill Education