Part III Levered ATIRR The facts are the same as before. But now Mr. Beyer is go
ID: 2751268 • Letter: P
Question
Part III
Levered ATIRR
The facts are the same as before. But now Mr. Beyer is going to finance the acquisition, because after seeing the projected impact of income taxes on his BTIRR (his 12.20% IRR became 8.867%), he has decided to add debt leverage. He obtains a loan from the Actuarially Correct Life Insurance Company. The loan is funded at the time the property is acquired.
The terms and conditions of the constant pay mortgage (CPM) loan are:
* Annual rate of 7%; monthly payments; 25-year amortization schedule.
* The loan amount is not given here. You must calculate the loan amount based on the following: The loan’s first year’s debt service coverage ratio (DSCR), which is the ratio of the NOI to the loan constant, is 1.3855145x. (Round the loan amount to the nearest $100 for this exercise.)
* The lender charges an upfront “commitment fee” of 1% of the loan amount. This fee is paid at the time of loan closing, is capitalized, and is amortized evenly by Mr. Beyer over the anticipated five year holding period.
* The loan contains a “due on sale” clause: The loan must be repaid when Mr. Beyer sells the property.
* The loan has a prepayment penalty of 1½% of the loan balance at the time of prepayment. The prepayment penalty is treated as interest in the year in which it is paid, and so it is deductible for purposes of calculating income tax in that year.
SHOWN IN EXCEL
1. What’s Mr. Beyer’s levered ATIRR, that is, what’s the ATIRR to Mr. Beyer’s equity taking into account the effect of the financing? Before adding debt, Mr. Beyer’s equity was 100% of the purchase price. Now it is the difference between the purchase price and the loan amount that you have calculated.
2. What is the impact of adding debt to the IRR? In other words, what is the increase or decrease to the equity IRR; by how many basis points did the mortgage financing increase or decrease Mr. Beyer’s ATIRR?
3. Now, directing your attention to the lender, what’s the lender’s BTIRR? (It’s not 7%.)
Part 2 of 3
ATIRR Unlevered: Income taxes are taken into account in the calculation of IRR.
This is a continuation Part I. The additional information is:
75% of Mr. Beyer’s purchase price of $92,000,000 is attributable to the improvements (the building) and will be depreciated on a 39-year straight-line basis. As you know, depreciation is a deduction for purposes of calculating taxable income. (The 25% portion of the price attributable to land is, of course, not depreciable.)
The front-end closing costs of $600,000 are capitalized and amortized evenly over his anticipated five-year holding period. Closing costs are a deductable item.
The income tax (the tax on each year’s taxable income from operations) rate is 36%.
Gains Tax: His capital gain (the net sales proceeds in excess of his depreciated basis) is segmented into (a) the net sales proceeds in excess of his purchase price, which is taxed at a rate of 15%, and (b) the amount of depreciation he has taken during his ownership, which is taxed at a rate of 20%.
The $3,500,000 capital expenditure in the fifth year will not be depreciated by Mr. Beyer since it is made during the same period as his disposition of the property.
What is Mr. Beyer’s after-tax IRR (ATIRR)?
Shown in Excel
(PART 1 of 3)
Unlevered BTIRR problem
The property is a specialty retail property (which is interesting, but not relevant to the solution). Mr. Beyer is planning to acquire the property, hold it for five years and then sell it at the end of the fifth year.
* The first year net operating income (NOI) is projected to be $8,460,750. Mr. Beyer is projecting that, based on increasing base rents and increasing percentage rents, his NOI will increase at a compounded annual rate of 4%.
* His closing costs (legal, recording, appraisal, environmental, engineering, title, etc.) will come to $600,000. These closing costs will be expended simultaneous to taking title.
* In the fifth year of the holding period, in order to prepare the property for disposition, Mr. Beyer intends to spend $3,500,000 in property upgrade, and he builds in that assumption to his numbers. This capital expenditure is, of course, an increase in his equity investment.
* Mr. Beyer projects his sale to the next purchaser at the end of the fifth year. The sales price will be determined by utilizing a residual or terminal capitalization rate of 9.15% of the projected sixth year NOI (rounded to the nearest $100,000). After all, the next buyer is buying future income, not past income. But Mr. Beyer also projects a cost of sale (legal, marketing, brokerage) of an amount equal to 2% of the gross sales price. This cost of sale will be deducted from his gross sale price to arrive at net sales proceeds.
If all of Mr. Beyer’s projections are accurate, and if he pays $92,000,000 for this property, what will be his before tax IRR (BTIRR)?
Explanation / Answer
Amount_Financed 61,06,576.29 (=8460750/1.3855145) Rate 7.00% Term (years) 25 Monthly Payment 43160.01 (=PMT(Rate/12,Term__years*12,-Amount_Financed)) 43160.01 (=Amount_Financed*(Rate/12)/(1-(1/(1+Rate/12)^(Term__years*12)))) MortgageBalance at end of year 5 5566886.38 (=(Monthly Payment/(Rate/12))*(1-1/(1+Rate/12)^(20*12)) Cap Rate 9.15% Property value 9,20,00,000 NOI in yr 1 84,60,750 NOI growth rate 4% BookValue in year t+1= BookValue in year t-depreciation Depreciation 0 1 2 3 4 5 Property BookValue 69000000 67230769.2 65461538.5 63692307.7 61923076.9 60153846.2 Depreciation((=69000000/39)) 1769230.77 1769230.77 1769230.77 1769230.77 1769230.77 1769230.77 0 1 2 3 4 5 6 NOI 84,60,750 8799180 9151147.2 9517193.088 9897880.812 10293796 Closing Cost -600000 Purchase Price -9,20,00,000 112500503.2 (Property Value in yr5 =NOI in year 6/CapRate Net Sales Proceeds 110250493.1 (=Property value -cost of sales=.98*Property value yr5) Amortised closing costs -120000 -120000 -120000 -120000 -120000 (=-600,000/5) Mortgage Cash Flow - - - - - - (Mortgage cash flow in yr5 Depreciation(=0.75*Puechase Price/39) -1769230.77 -1769230.77 -1769230.77 -1769230.77 -1769230.77 =-(MortgageBalance at end of year 5+Monthly Payment)) Capex -3500000 Cash Flow to equity before tax -9,26,00,000 84,60,750 87,99,180 91,51,147 95,17,193 11,66,48,374 (=cash flow 1-5 yr=NOI+Net sales proceeds +Capex) Before tax IRR(=IRR(F22:K22)) 12.20% (=IRR(Cash Flows to equity before tax yr0-5)) Income tax -2365746.923 -2487581.723 -2614289.915 -2746066.435 -2883114.015 (=(NOI-Amort closing costs-deprecn)*(income tax rate)) Capital Gain tax -2737573.972 (=(Net sales proceeds-Property Value at purchase)*15%) depreciation recapture tax -10469331.41 (=(Property Value in yr5-Property BookValue in yr 5)*20% Total Taxes paid 0 -2365746.923 -2487581.723 -2614289.915 -2746066.435 -16090019.4 (=sum of above taxes paid) Cash Flow to equity after tax -9,26,00,000 79,84,234 82,00,829 84,26,088 86,60,357 10,24,47,585 (=Cash Flow to equity before tax+Total Taxes paid+add back non cash charges) After tax IRR(=IRR(F28:K28)) 9.23% (=IRR(Cash Flows to equity after tax yr0-5)) (+ Taxes because I hv alraady taken the negative sign) 0 1 2 3 4 5 6 NOI 84,60,750 8799180 9151147.2 9517193.088 9897880.812 10293796 Closing Cost -600000 Purchase Price -9,20,00,000 112500503.2 (Property Value in yr5 =NOI in year 6/CapRate Commitmment fee -61,066 Net Sales Proceeds 110250493.1 (=Property value -cost of sales=.98*Property value yr5) Amortised fee -12213 -12213 -12213 -12213 -12213 Amortised closing costs -120000 -120000 -120000 -120000 -120000 (=-600,000/5) Mortgage Cash Flow 61,06,576.29 -5,17,920.13 -5,17,920.13 -5,17,920.13 -5,17,920.13 -60,84,806.51 (Mortgage cash flow in yr5(=-(MortgageBalance at end of year 5+12*Monthly Payment)) Prepayment penatly -83,503.30 1% of MortgageBalance at end of year 5 Depreciation(=0.75*Puechase Price/39) -1769230.77 -1769230.77 -1769230.77 -1769230.77 -1769230.77 Capex -3500000 Cash Flow to equity before tax -8,65,54,489 79,42,830 82,81,260 86,33,227 89,99,273 11,40,63,567 (=cash flow 1-5 yr=NOI+Net sales proceeds +Capex+Mortgage Cash flow) Before tax IRR(=IRR(F22:K22)) 13.11% (=IRR(Cash Flows to equity before tax yr0-5)) Income tax -2361350.188 -2483184.988 -2609893.18 -2741669.7 -2848656.094 (=(NOI-Amort closing costs-deprecn-comm fee-prepaypenalty)*(income tax rate)) Capital Gain tax -2737573.972 (=(Net sales proceeds-Property Value at purchase)*15%) depreciation recapture tax -10469331.41 (=(Property Value in yr5-Property BookValue in yr 5)*20% Total Taxes paid 0 -2361350.188 -2483184.988 -2609893.18 -2741669.7 -16055561.48 (=sum of above taxes paid) Cash Flow to equity after tax -8,65,54,489 74,70,710 76,87,306 79,12,565 81,46,834 9,98,97,237 (=Cash Flow to equity before tax+Total Taxes paid+add back non cash charges) After tax IRR(=IRR(F28:K28)) 10.04% (=IRR(Cash Flows to equity after tax yr0-5)) (+ Taxes because I hv alraady taken the negative sign)