Using the assumptions in the box at the top solve the rest of the spreadsheet.
(b) BEIR (To calculate the Break Even Interest Rate (BEIR), the ATIRR must first be calculated as if there were no financing.) NO LOAN (Change 80 to 0%. All other variables are constant.) ASSUMPTIONS: $1.600,000 $2,000,000 $190,000 3.00% 0.00% 0.00% Tax Considerations: Building Value Depreciation Tax rate 27.5 years 36.00% Asking Price NOI year 1 Growth-NOI Loan-to-Value Loan Interest Loan term Payments per year Appreciation rate Holding Period Selling costs 0 year 0 3.00% 5 years 0.00% of sale price Equity Loan Annual Loan Payment Mortgage Balance year 5 SUMMARY LOAN INFORMATION: 1 2 End of Year Payment Mortgage Balance Interest Principal Year 1 2 2 4 NOI Debt Service Before-tax Cash Flow NOI Less: Interest Depreciation Taxable income Tax (Savings) After-tax Cash Flow 5 B Cash flow from sale in year Sala Pace 212 x fx . B C D E 5 Cash flow from sale in year Sales Price Sales costs Mortgage Balance Before-tax cash flow Original Cost Basis Accumulated Depreciation Adjusted Basis Capital Gain Depreciation Recap Total Gain on Salac Tax from Sale After-tax cash flow from sale 0 1 2 2 4 5 all 1 2 3 5 5 EQUITY 7 Year 8 BTCF 9 BTIRR on Equity 0 -1 Year 2 ATCF 13 ATIRR on Equity 14 15 Break-even Interest Rate 16 17 18 19 20 21 22 23 (c) 224 225 226 REM The break even interest rate (BIR) is the level of interest rate at which the effect of borrowed funds on the investors' equity retum, and therefore the leverage effect switches from positive to negative. More specifically: If the mortgage interest rate is HIGHER than BIR Negative Leverage If the mortgage interest rate LOWER than BIR = Positive Leverage (a) To answer this it is helpful to prepare the following summary: IRR No loan 70% loan 80% loan Before After tax Loan Cost No loan 70% loan 80% loan Incremental cost of loan Before tax After tax 1 (b) BEIR (To calculate the Break Even Interest Rate (BEIR), the ATIRR must first be calculated as if there were no financing.) NO LOAN (Change 80 to 0%. All other variables are constant.) ASSUMPTIONS: $1.600,000 $2,000,000 $190,000 3.00% 0.00% 0.00% Tax Considerations: Building Value Depreciation Tax rate 27.5 years 36.00% Asking Price NOI year 1 Growth-NOI Loan-to-Value Loan Interest Loan term Payments per year Appreciation rate Holding Period Selling costs 0 year 0 3.00% 5 years 0.00% of sale price Equity Loan Annual Loan Payment Mortgage Balance year 5 SUMMARY LOAN INFORMATION: 1 2 End of Year Payment Mortgage Balance Interest Principal Year 1 2 2 4 NOI Debt Service Before-tax Cash Flow NOI Less: Interest Depreciation Taxable income Tax (Savings) After-tax Cash Flow 5 B Cash flow from sale in year Sala Pace 212 x fx . B C D E 5 Cash flow from sale in year Sales Price Sales costs Mortgage Balance Before-tax cash flow Original Cost Basis Accumulated Depreciation Adjusted Basis Capital Gain Depreciation Recap Total Gain on Salac Tax from Sale After-tax cash flow from sale 0 1 2 2 4 5 all 1 2 3 5 5 EQUITY 7 Year 8 BTCF 9 BTIRR on Equity 0 -1 Year 2 ATCF 13 ATIRR on Equity 14 15 Break-even Interest Rate 16 17 18 19 20 21 22 23 (c) 224 225 226 REM The break even interest rate (BIR) is the level of interest rate at which the effect of borrowed funds on the investors' equity retum, and therefore the leverage effect switches from positive to negative. More specifically: If the mortgage interest rate is HIGHER than BIR Negative Leverage If the mortgage interest rate LOWER than BIR = Positive Leverage (a) To answer this it is helpful to prepare the following summary: IRR No loan 70% loan 80% loan Before After tax Loan Cost No loan 70% loan 80% loan Incremental cost of loan Before tax After tax 1
