Fin 4713: Chapter 15: A Basic Building Proforma Name:___________________
Use the Skeleton Spreadsheet as a starting point and create a spreadsheet that shows
the results of Example1 and Example2.
Note: The blue shaded portions are user input areas. All of the missing parts are
generated by appropriate formulas.
Starting with the results of Example 2 displayed, complete the following questions. After
each question, return to the starting conditions of Example 2.
1. Leverage: Demonstrate this building has positive leverage by entering Mortgage
LTV’s of, 0, 25%, 50%, 75%
Mortgage LTV After Tax IRR Before Tax IRR Year 1 DCR Year 1 C on C
0% 8.5% 11.3% N/A 9.14%
25% 9.3 12.2 3.99 9.00
50% 10.8 13.9 1.99 8.73
75% 14.3 17.9 1.33 8.00
Positive leverage means that the IRR (which is the return on equity, ROE) increases as
the LTV is increased. The above table demonstrates this relationship.
2. At what mortgage rate does this building show a neutral leverage position?
Solve by trial and error, approx. 10.7%. This is higher than the after tax return on
assets (ATIRR for 0% LTV) due to the tax deductibility of mortgage interest and the
effect of loan fee and prepayment penalty.
3. If the minimum allowable DCR in Year 1 is 1.75, what is the maximum LTV on this
building?
By trial and error (or goal seek), LTV ≅ 57%
4. Breakeven Analysis. At what vacancy rate does DCR (Year 1) = 1.0?
By trial and error (or goal seek), Vacancy rate ≅ 35.5%
5. Obama Tax Plan? Because this building is owned by a rich person, let’s give him a
tax increase. Use the following rates: Marginal Tax Bracket = 39%, Depn Recovery =
39%, Capital Gain Rate = 25%. What is the IRR and NPV of the building?
Current Obama
IRR 13.3% 11.5%
NPV 43,216 -17,094
