FINANCE & REAL ESTATE

Examine the spreadsheet. It calculates the IRR for the same property with a mortgage (top) and without a mortgage (bottom). Cells B7 and B19 (highlighted in yellow) contain the initial investment and the IRRs that are calculated are the returns for the equity investor.

Answer the following questions:

A) The difference between cells B7 and B19 is $1,550,000. What does this number represent? (2 point)

B) What is the Going-In Cap Rate for this investment?

C) Why is the IRR for the first scenario higher than the second scenario?

D) Lets suppose that you lose a tenant in year 3, which lowers the NOI in years 3, 4 and 5 by $100,000 in each year. What is the new IRR for both scenarios?

E) Explain the outcome of D)

F) When you lose a tenant it is likely that the value of your property at sale will also decline, hence cells G6 and G18 will also be affected. Explain how and why the sale price will change and what the effect on the IRR is?

G) Which scenario is more risky? Explain.

H) Why will most real estate investors choose the scenario with a mortgage?

A14 fx Year: A B C D E F G H - J K L Scenario with a Mortgage: 0 1 2 3 4 5 $200,000.00 $240,000.00 $245,000.00 $250,000.00 $260,000.00 $125,000.00 $125,000.00 $125,000.00 $125,000.00 $125,000.00 $75,000.00 $115,000.00 $120,000.00 $125,000.00 $135,000.00 $1,400,000.00 = After Tax Equity Reversion: Profit for the Equity Investor. -$1,250,000.00 $75,000.00 $115,000.00 $120,000.00 $125,000.00 $1,535,000.00 1 2 Year: 3 NOI: 4 DS: 5 After Tax Cash Flows: 6 7 Total Cash Flows: 8 9 10 11 12 13 14 Year: 15 NOI: 16 DS: 17 After Tax Cash Flows: IRR = 10.83% Scenario without a Mortgage: 0 1 2 3 4 5 $200,000.00 $240,000.00 $245,000.00 $250,000.00 $260,000.00 $0.00 $0.00 $0.00 $0.00 $0.00 $200,000.00 $240,000.00 $245,000.00 $250,000.00 $260,000.00 $2,900,000.00 = After Tax Equity Reversion: Profit for the Equity Investor. -$2,800,000.00 $200,000.00 $240,000.00 $245,000.00 $250,000.00 $3,160,000.00 18 19 Total Cash Flows: 20 IRR = 9.05% 21 22 23 A14 fx Year: A B C D E F G H - J K L Scenario with a Mortgage: 0 1 2 3 4 5 $200,000.00 $240,000.00 $245,000.00 $250,000.00 $260,000.00 $125,000.00 $125,000.00 $125,000.00 $125,000.00 $125,000.00 $75,000.00 $115,000.00 $120,000.00 $125,000.00 $135,000.00 $1,400,000.00 = After Tax Equity Reversion: Profit for the Equity Investor. -$1,250,000.00 $75,000.00 $115,000.00 $120,000.00 $125,000.00 $1,535,000.00 1 2 Year: 3 NOI: 4 DS: 5 After Tax Cash Flows: 6 7 Total Cash Flows: 8 9 10 11 12 13 14 Year: 15 NOI: 16 DS: 17 After Tax Cash Flows: IRR = 10.83% Scenario without a Mortgage: 0 1 2 3 4 5 $200,000.00 $240,000.00 $245,000.00 $250,000.00 $260,000.00 $0.00 $0.00 $0.00 $0.00 $0.00 $200,000.00 $240,000.00 $245,000.00 $250,000.00 $260,000.00 $2,900,000.00 = After Tax Equity Reversion: Profit for the Equity Investor. -$2,800,000.00 $200,000.00 $240,000.00 $245,000.00 $250,000.00 $3,160,000.00 18 19 Total Cash Flows: 20 IRR = 9.05% 21 22 23