Question

An investor is considering buying a rental duplex with land valued at $30,000 and the building valued at $150,000. Straight-line depreciation over 27 1⁄2 years will be taken. The investor will be actively involved in the management of the property. He is in a 30% tax bracket.

Assume potential gross income of $44,000 in year one, vacancy of 12% and Operating expenses equal to 40% of Effective gross income. Gross potential income is expected to increase by 2% each year over the holding period.

A lender will make a 20-year loan equal to 75 percent of the total value of the property at 9 percent interest with monthly payments. Assume that there is 3 percent inflation related to total property value each year the investor owns the property and that there is a 4% commission paid (selling expenses) in the year of sale.

Assume that the investor’s after tax required rate of return is 12% and will hold the property for three years. Use the 25% tax rule where: for capital gain -- (tax rate >25% use marginal tax rate of 15%); for depreciation recapture-(tax rate >25% use marginal tax rate of 25%).

Calculate the following:

a) the after tax cash flow from sale of the asset in year2?

n)NPV

c)IRR

d) DCR for year 1

Answer 1

There seems typo, the intention was to hold property for 3 year while the question is being asked that after tax cash flow from sale of asset in year 2. Hence while answering all question the calculation is carried out considering that property is sold in year 2

Operating cash flow calculation for Year-1 & Year -2 :

Particulars	Year-1	Year-2
Potential gross income	44,000.00	44,880.00
12% Vacancy	5,280.00	5,385.60
Effective gross income	38,720.00	39,494.40
Operating expenses	15,488.00	15,797.76
Interest year-1	12,047.40	11,810.24
Depreciation	5,227.00	5,454.55
Taxable Income	11,184.60	11,886.40
Tax @ 30%	3,355.38	3,565.92
Income after tax	7,829.22	8,320.48
Add : Depreciation	5,227.00	5,454.55
Cash flow	13,056.22	13,775.02

Cash flow for sale of asset at end of Year 2

Particulars	Amount	Basis of Calculation
Property Acquisition Value	180,000.00
Appreciation for year-1	5,400.00	180000*3%
Property value at end of year-1	185,400.00
Appreciation for year-2	5,562.00	185400*3%
Property value at end of year-2	190,962.00
Less : Commission	7,638.48	190962*4%
Net Realization from property sale	183,323.52
Less : Net cost of asset after depreciation	169,318.45	130000-5227-5454.55+30000
Capital gain / Depreciation recapture	14,005.07
Tax @ 15%	2,100.76	14005.07*15%
Net Cash flow from sale	181,222.76
Loan repayment	129,706.52
Net Cash flow from Sale	51,516.24

NPV Calculation : Using Excel formula of NPV

Year	Amount	Basis
0	(45,000.00)	180000*45%
1	13,056.22
2	65,291.26	13775.02+51516.24
NPV	$18,707.13

IRR - Using Excel IRR function

Year	Amount	Basis
0	(45,000.00)	180000*45%
1	13,056.22
2	67,022.65	13775.02+51516.24
IRR	37.41%

Debt Service Ratio : 1.59 Time (23,232/14,575.56)

Income before interest & Depreciation / (Interest + Principal)

Income before interest and depreciation : 23,232

Debt Service for Year-1 : 14575.56 (1214.63*12)

Working notes :

Depreciation is not calculated on land hence only cost of building will be depreciation. As per IRS, MARCS method to be used for depreciation purpose where useful life for property would be 27.5 year with mid month convention for first year. The depreciation table is as under for year-1 & year-2

Year	Opening Balance	Straight line Depreciation rate	Depreciation	Closing Balance	Remark
1	150,000.00	3.484667%	5,227.00	144,773.00	Mid Month Convention
2	144,773.00	3.636364%	5,454.55	139,318.45

Working notes - EMI calculation & Loan Schedule

Using excel PMT function : 1214.63 EMI works out =+PMT(0.75%,240,180000*75%,0,0)

Loan schedule to work out interest and loan balance at the end of year 2

Installment	Op balance	Interest	EMI	Principal repayment	Closing Balance
1	135,000.00	1,012.50	1,214.63	202.13	134,797.87
2	134,797.87	1,010.98	1,214.63	203.65	134,594.22
3	134,594.22	1,009.46	1,214.63	205.17	134,389.05
4	134,389.05	1,007.92	1,214.63	206.71	134,182.34
5	134,182.34	1,006.37	1,214.63	208.26	133,974.08
6	133,974.08	1,004.81	1,214.63	209.82	133,764.25
7	133,764.25	1,003.23	1,214.63	211.40	133,552.85
8	133,552.85	1,001.65	1,214.63	212.98	133,339.87
9	133,339.87	1,000.05	1,214.63	214.58	133,125.29
10	133,125.29	998.44	1,214.63	216.19	132,909.10
11	132,909.10	996.82	1,214.63	217.81	132,691.29
12	132,691.29	995.18	1,214.63	219.45	132,471.84
13	132,471.84	993.54	1,214.63	221.09	132,250.75
14	132,250.75	991.88	1,214.63	222.75	132,028.00
15	132,028.00	990.21	1,214.63	224.42	131,803.58
16	131,803.58	988.53	1,214.63	226.10	131,577.48
17	131,577.48	986.83	1,214.63	227.80	131,349.68
18	131,349.68	985.12	1,214.63	229.51	131,120.17
19	131,120.17	983.40	1,214.63	231.23	130,888.94
20	130,888.94	981.67	1,214.63	232.96	130,655.98
21	130,655.98	979.92	1,214.63	234.71	130,421.27
22	130,421.27	978.16	1,214.63	236.47	130,184.80
23	130,184.80	976.39	1,214.63	238.24	129,946.55
24	129,946.55	974.60	1,214.63	240.03	129,706.52