Question

please help with explanation

An investor would like to purchase a new apartment property for $2 million. However, she faces the decision of whether to use 70 percent or 80 percent financing. The 70 percent loan can be obtained at 10 percent interest for 25 years. The 80 percent loan can be obtained at 11 percent interest for 25 years. NOI is expected to be $190,000 per year and increase at 3 percent annually, the same rate at which the property is expected to increase in value. The building and improvements represent 80 percent of value and will be depreciated over27.5 years (1+ 275 per year). The project is expected to be sold after five years. Assume a 36 percent tax bracket for all income and capital gains taxes. a. What would the BTIRR and ATIRR be at each level of financing (assume monthly mortgage amortization)? ATIRR % BTIRR 70% Loan 80% Loan % b. What is the break-even interest rate (BEIR) for this project? %

Answer 1

Input
Investment	$ 20,00,000
Building & Improvements	$ 16,00,000
Depreciation Rate (%)	3.64%
Tax (%)	36%
Option 1 - Loan Amount	$ 14,00,000
Option 1 - Interest Rate	10%
Option 2 - Loan Amount	$ 16,00,000
Option 2 - Interest Rate	11%

Assuming Debt Repayment schedule and interest calculation as below

Option 1 - 70% Finance @ 10% interest
Year	0	1	2	3	4	5
Opening Balance		$                       -	$ 13,44,000	$ 12,88,000	$ 12,32,000	$       11,76,000
Addition		$       14,00,000	$                -	$                -	$                -	$                       -
Repayment		$             56,000	$       56,000	$       56,000	$       56,000	$             56,000
Closing Balance		$       13,44,000	$ 12,88,000	$ 12,32,000	$ 11,76,000	$       11,20,000
Interest @ 10%		$          1,37,200	$   1,31,600	$   1,26,000	$   1,20,400	$          1,14,800

Option 2 - 80% Finance @ 11% interest
Year	0	1	2	3	4	5
Opening Balance		$                       -	$ 15,36,000	$ 14,72,000	$ 14,08,000	$       13,44,000
Addition		$       16,00,000	$                -	$                -	$                -	$                       -
Repayment		$             64,000	$       64,000	$       64,000	$       64,000	$             64,000
Closing Balance		$       15,36,000	$ 14,72,000	$ 14,08,000	$ 13,44,000	$       12,80,000
Interest @ 11%		$          1,72,480	$   1,65,440	$   1,58,400	$   1,51,360	$          1,44,320

Part A - BTIRR and ATIRR for Option 1 and 2

Option 1 - 70% Finance @ 10% interest
Year	0	1	2	3	4	5
NOI		$          1,90,000	$   1,95,700	$   2,01,571	$   2,07,618	$          2,13,847
Interest		$          1,37,200	$   1,31,600	$   1,26,000	$   1,20,400	$          1,14,800
Profit Before Tax		$             52,800	$       64,100	$       75,571	$       87,218	$             99,047
Proceeds from Sale Before Tax						$       23,18,548
Unpaid Mortgage Balance						$       11,20,000
Before Tax Cash Flow	$          -6,00,000	$             52,800	$       64,100	$       75,571	$       87,218	$       12,97,595
NOI		$          1,90,000	$   1,95,700	$   2,01,571	$   2,07,618	$          2,13,847
Depreciation		$             58,182	$       58,182	$       58,182	$       58,182	$             58,182
Interest		$          1,37,200	$   1,31,600	$   1,26,000	$   1,20,400	$          1,14,800
Profit After Tax		$              -5,382	$         3,788	$       11,129	$       18,583	$             26,154
Proceeds from Sale After Tax						$       20,99,144
Unpaid Mortgage Balance						$       11,20,000
After Tax Cash Flow	$          -6,00,000	$              -5,382	$         3,788	$       11,129	$       18,583	$       10,05,297
BTIRR	24.16%
ATIRR	11.56%

Option 2 - 80% Finance @ 11% interest
Year	0	1	2	3	4	5
NOI		$          1,90,000	$   1,95,700	$   2,01,571	$   2,07,618	$          2,13,847
Interest		$          1,72,480	$   1,65,440	$   1,58,400	$   1,51,360	$          1,44,320
Profit Before Tax		$             17,520	$       30,260	$       43,171	$       56,258	$             69,527
Proceeds from Sale Before Tax						$       23,18,548
Unpaid Mortgage Balance						$       12,80,000
Before Tax Cash Flow	$          -4,00,000	$             17,520	$       30,260	$       43,171	$       56,258	$       11,08,075
NOI		$          1,90,000	$   1,95,700	$   2,01,571	$   2,07,618	$          2,13,847
Depreciation		$             58,182	$       58,182	$       58,182	$       58,182	$             58,182
Interest		$          1,72,480	$   1,65,440	$   1,58,400	$   1,51,360	$          1,44,320
Profit After Tax		$            -40,662	$     -27,922	$     -15,011	$        -1,924	$                7,261
Proceeds from Sale After Tax						$       20,99,144
Unpaid Mortgage Balance						$       12,80,000
After Tax Cash Flow	$          -4,00,000	$            -40,662	$     -27,922	$     -15,011	$        -1,924	$          8,26,404
BTIRR	27.73%
ATIRR	11.92%

Part B - Break even interest rate.

Assume zero debt scenario, the ATIRR is arrived at as shown below.

NOI		$          1,90,000	$   1,95,700	$   2,01,571	$   2,07,618	$          2,13,847
Depreciation		$             58,182	$       58,182	$       58,182	$       58,182	$             58,182
Interest		$                       -	$                -	$                -	$                -	$                       -
Profit After Tax		$          1,31,818	$       88,012	$       91,769	$       95,639	$             99,626
Proceeds from Sale After Tax						$       20,99,144
Unpaid Mortgage Balance						$                       -
After Tax Cash Flow	$       -20,00,000	$          1,31,818	$       88,012	$       91,769	$       95,639	$       21,98,769
ATIRR	5.98%

Break-even interest rate is ATIRR / (1-Tax) = 5.98% / (1-36%) = 9.35%