SS13 - Real Estate Valuation question, capital, mortgage, knowledge, following

IAmNeil

Try the following question to test your knowledge. I will post the answers later.

1. NOI is 64,000
2. Price is 525,000
3. Improvements make up 85.9% of the price. Using a 27.5-year life, SLD gives 16,399 annual tax depreciation.
4. Equity cont.: 131,250
5. Debt cont.: 30-year 8% fixed rate mortgage for 393,750. Monthly payment: 2,889.20. LTV ratio: 75%
6. 36% marginal income tax; 20% capital gains tax; 25% recaptured depreciation tax.
7. Required after-tax return: 12%
8. Holding period: 4 years. NOI expected to grow over holding period 5% annually. Market value at end of year 4 is 777,924. Selling costs are 7% of sales price (market value). Outstanding loan balance at end of year 4 is 378,862.
9. Annual compounding used for the equity TVM calculations.

A. Determine taxes payable (years 1 through 4)
B. Determine after-tax cash flow (years 1 through 4)
C. Determine after-tax equity reversion (end of year 4)
D. Calculate NPV of the investment (12%)
E. Calculate IRR of the investment
F. Explain using the NPV and IRR rules why an investor would or would not invest in this real estate venture.

iteracom

let me have a try

tax:
8581.818182 10629.81818 12780.21818 15038.13818

CFAT:
52528.98182 53680.98182 54890.58182 56160.66182

ERAT:
285822.5469


calculated on a excel during work time......just 4 fun~



Edited 1 time(s). Last edit at Thursday, May 20, 2010 at 05:20AM by coshair.

stalkey

Remember seeing this. Do we have to know how to calc an amortisation schedule here....ie when calulating Income tax payable from NOI - how do we get the interest paid portion?

yalo

For calculating after-tax cf (NOI - Annual Debt Service - Tax payable = After-tax CF)
how would you calculate the debt service to get the after-tax CF?



Kalo1: You would get the interest payment by first finding out the interest expense on each month then subracting the monthly payment to get the amount that will go into your principal (which would be a much smaller number than the interest expense at the beginning).

Then subtract the principal paid portion from the loan. Now you have your lowered principal. With that you multiple the interest for month two from the lowered principal (the 2nd month interest would be lower than the first as it is from a lowered principal amt).

Now you have the 2nd month interest payment and subtract that from your monthly payment of 2,889.20 to get the amount that will go into lowering your principal. repeat the process for month 3 to 12. Then you'll get your interest paid for the year. It is very unlikely that the test will ask you to caculate this.. maybe to the 2nd or 3rd month (if it is asking for monthly after tax cf, or 2nd, 3rd year if asking for yearly after-tax cf).

DSquaredSlim

Answers to help:

A

(NOI - Dep. - Interest paid) * marginal tax rate = Income tax payable

N = 360
I = 8/12
PV = 393,750
PMT = 2,889.20

Interest Yr 1 = 31,381
Yr 2 = 31,108
Yr 3 = 30,812
Yr 4 = 30,492

Yr 1 5839.15 (64,000 – 16,399 – 31,381) * 0.36
Yr 2 7089.43 (64,000 – 16,399 – 31,108) * 0.36
Yr 3 8405.47
Yr 4 9790.83


B

NOI - Debt service - Tax payable (from A) = ATCF
where Debt service = Mortgage payment x 12 2889.2 * 12 = 34,670.38

Yr 1 = 23,491 (64,000 – 34,670.38 – 5,839.15)
Yr 2 = 25,441
Yr 3 = 27,484
Yr 4 = 29,627

C

CAPITAL GAINS:
(Sales price - Selling costs) - (Purchase price - Accumulated dep.) - Recaptured dep*

(777,924 - (777,924 * 0.07)) - (525,000 - 65,596) - 65,596 = 198,469

*property appreciates over holding period, depreciation must be recaptured

TAXES:
Tax on capital gain:
198,469 * 0.20 = 39,694

Tax on recap depreciation:
65,596 * 0.25 = 16,399

Total taxes due on sale:
16,399 + 39,694 = 56,093

AFTER TAX EQUITY REVERSION:
Sales price - Selling expenses - Balance on debt - Total taxes due on sale

777,924 - (777,924 * 0.07) - 344,607 - 56,093 = 288,514

D

Yr 1 23,491 / 1.12
Yr 2 25,441 / (1.12)^2
Yr 3 27,484 / (1.12)^3
Yr 4 (29,627 + 288,514) / (1.12)^4

Sum: 263,022.44
Equity outlay: 131,250
NPV: 131,752.44

E. Use calculator to obtain IRR ~ 37.14%

F. Decision is to buy based on NPV and IRR, which are greater than zero and 12%, respectively.

tarunajwani

ahh~~~ so thats how debt service is calculated.

firat

<reposted



Edited 1 time(s). Last edit at Tuesday, May 25, 2010 at 06:28AM by joseph213.

Swanand

Thanks ASM299 but do you have the calc to get the below interest payments. The book omits this which makes me think if this came up in the exam they will give you the interest payments (if interest only) and also the debt servicing value when getting to ERAT.

Interest Yr 1 = 31,381
Yr 2 = 31,108
Yr 3 = 30,812
Yr 4 = 30,492

Cheers

ayaz_mahmud369

I don't think interest per year can be calculated without a full schedule, or by doing an amortization function with your calculator, right?

bolligerallstar

it can be done with the Amort button on your calc.

Use
PV=-393750 N=360 I/Y=8/12
CPT PMT
=2889.20

2nd Amort
P1=1 Down Arrow
P2=12
BAL=390460.75
PRN=3289.25
INT=31381

P1=13
P2=24
PRN=3562.25
INT=31108

25,36
PRN=3857.92
INT=30812

37,48
4178
30492

CP