Question

An old two-flat can be purchased for $200,000 cash. The two units can bring in a total of $2,500 per month (allowing for normal vacancies). The total operating expenses for property taxes, repairs, gardening, and so forth are estimated to be $200 per month. For tax purposes, straight-line depreciation over a 20-year remaining life with to a zero salvage value will be used.

Of the total $200,000 cost of the property, $50,000 is the value of the land. Assume a 38% marginal income tax bracket (combined state and federal taxes) applies throughout the 20 years. This tax rate applies to ordinary income and capital gains/losses.

Since there is no growth expected in rents or expenses, depreciation is straight-line, and the tax rate doesn’t change, the ATCF’s for each year, 1 to 20, will be the same.

1a.

What after-tax IRR would you expect assuming that the property is held for twenty years and then sold for $50,000?

1b.

What after-tax IRR would you expect assuming that the property is held for twenty years and then sold for $150,000?

Do not use excel

Answer 1

Not using MS Excel. Using a calculator when required.

All amounts are annual amounts:

Revenues from rent = 2500*12 = 30,000
Operating expenses = 200*12 = 2,400
Depreciation per year = (200,000-50,000)/20 = 7,500
Before Tax cash flows = 30,000-2,400-7,500 = 20,100

After-tax cash flows for each of the 20 years = 20,100*0.62 = 12,462

1.a. At the end of the 20th year, the property is sold for 50,000.
The tax paid shall be 50000*0.38 = 19,000.
Net cash inflow = 50,000-19,000 = 31,000

IRR = The rate at which NPV = 0. To find IRR manually, we must use the trial and error method. The rate at which NPV = 0, will be our answer.
Let us take IRR at 5% and find NPV:

Year	Cash flow	Discounting Factor	Present Value = Cash flow * Discounting Factor	NPV
0	-2,00,000	1	-200000	-33012.36074
1	12,462	0.952380952	11868.5714
2	12,462	0.907029478	11303.4014
3	12,462	0.863837599	10765.1442
4	12,462	0.822702475	10252.5182
5	12,462	0.783526166	9764.30309
6	12,462	0.746215397	9299.33627
7	12,462	0.71068133	8856.51074
8	12,462	0.676839362	8434.77213
9	12,462	0.644608916	8033.11631
10	12,462	0.613913254	7650.58697
11	12,462	0.584679289	7286.2733
12	12,462	0.556837418	6939.30791
13	12,462	0.530321351	6608.86467
14	12,462	0.505067953	6294.15683
15	12,462	0.481017098	5994.43508
16	12,462	0.458111522	5708.98579
17	12,462	0.436296688	5437.12932
18	12,462	0.415520655	5178.2184
19	12,462	0.395733957	4931.63657
20	43,462	0.376889483	16380.3707

Evidently, the IRR taken is too high.
Let us try again with an IRR of 3%

Year	Cash flow	Discounting Factor	Present Value	NPV
0	-2,00,000	1	-200000	2567.040091
1	12,462	0.970873786	12099.0291
2	12,462	0.942595909	11746.6302
3	12,462	0.915141659	11404.4954
4	12,462	0.888487048	11072.3256
5	12,462	0.862608784	10749.8307
6	12,462	0.837484257	10436.7288
7	12,462	0.813091511	10132.7464
8	12,462	0.789409234	9837.61788
9	12,462	0.766416732	9551.08532
10	12,462	0.744093915	9272.89837
11	12,462	0.722421277	9002.81395
12	12,462	0.70137988	8740.59607
13	12,462	0.68095134	8486.0156
14	12,462	0.661117806	8238.8501
15	12,462	0.641861947	7998.88359
16	12,462	0.623166939	7765.9064
17	12,462	0.605016446	7539.71495
18	12,462	0.587394608	7320.1116
19	12,462	0.570286027	7106.90447
20	43,462	0.553675754	24063.8556

The IRR should be a little over 3%. After multiple trials and errors, we find that IRR is around 3.1265%. Here NPV is just less than 1.

Year	Cash flow	Discounting Factor	Present Value	NPV
0	-2,00,000	1	-200000	0.890789515
1	12,462	0.969682865	12084.1879
2	12,462	0.940284859	11717.8299
3	12,462	0.911778116	11362.5789
4	12,462	0.884135616	11018.098
5	12,462	0.857331158	10684.0609
6	12,462	0.831339333	10360.1508
7	12,462	0.806135507	10046.0607
8	12,462	0.781695788	9741.49291
9	12,462	0.757997011	9446.15876
10	12,462	0.735016714	9159.77829
11	12,462	0.712733113	8882.08005
12	12,462	0.691125087	8612.80084
13	12,462	0.670172155	8351.68539
14	12,462	0.649854455	8098.48622
15	12,462	0.63015273	7852.96332
16	12,462	0.611048305	7614.88398
17	12,462	0.592523071	7384.02251
18	12,462	0.574559469	7160.16011
19	12,462	0.557140472	6943.08457
20	43,462	0.54024957	23480.3268

1.b. At the end of the 20th year, the property is sold for 150,000.
The tax paid shall be 150000*0.38 = 57,000
Net cash inflow = 150,000-57,000 = 93,000

Using similar methods as above, IRR = 4.531189%

Year	Cash inflow	Discounting Factor	Present Value	NPV
0	-2,00,000	1	-200000	0.000005920
1	12,462	0.956652271	11921.8006
2	12,462	0.915183567	11405.0176
3	12,462	0.875512437	10910.636
4	12,462	0.837560961	10437.6847
5	12,462	0.801254595	9985.23476
6	12,462	0.766522028	9552.39751
7	12,462	0.733295038	9138.32277
8	12,462	0.701508363	8742.19723
9	12,462	0.671099569	8363.24283
10	12,462	0.642008926	8000.71524
11	12,462	0.614179297	7653.9024
12	12,462	0.587556019	7322.12311
13	12,462	0.5620868	7004.7257
14	12,462	0.537721613	6701.08674
15	12,462	0.514412602	6410.60985
16	12,462	0.492113984	6132.72447
17	12,462	0.47078196	5866.88479
18	12,462	0.450374631	5612.56865
19	12,462	0.430851914	5369.27655
20	1,05,462	0.412175461	43468.8485