Question

Assume that you are an investment analyst preparing an analysis of an investment opportunity for a client. Your client is considering the acquisition of an apartment complex from a developer at the point in time when the apartments are ready for first occupancy. You have developed the following information.

1) Number of units = 40

2) First year market rent per unit = $430 per month

3) Rent is projected to increase by 8% each year

4) Annual vacancy rate = 3% of PGI

5) Annual collection loss = 2% of PGI

6) Annual operating expense = 35% of EGI

7) Miscellaneous yearly income (parking and washers/dryers) = $800

8) Annual miscellaneous income is expected to remain constant

9) Purchase price = $2,000,000

10) Estimated value of land = $600,000

11) Anticipated mortgage terms: a) Loan to value ratio = .80 b) Interest rate = 6% c) Years to maturity = 25 d) Points charged = 3 e) Prepayment penalty = 2% of outstanding balance f) Level payment, fully amortized g) Fixed interest rate, monthly payments

12) Anticipated holding period = 4 years

13) Proportion by which property is expected to appreciate during the holding period -- 5.5% a year

14) Estimated selling expenses as proportion of future sales price = 5%

15) Marginal income tax rate for the client = 28%

16) It is assumed that the property is put into service on January 1st and sold on December 31st

17) Assume the client is "active" in the property management

18) It is assumed that the client has an adjusted gross income of $95,000 and has no other passive income not offset by other passive losses (for each year of the anticipated holding period)

19) Client's minimum required after tax rate of return on equity = 12.5% Calculate:

a. The before-tax and after-tax cash flows for each year of the holding period and the before-tax and after-tax equity reversion.

Please show work!

Answer 1

1.a.After-tax proceeds of Sale of property at end of Yr. 4	ATCF	BTCF
Purchase Cost   (Bldg. +Land)	2000000
Less: Acc. Depn.(50909*4) (Bldg.)	203636
Carrying value(Purchase value-Acc. Depn.)	1796364
Appreciated value of property(2000000*1.055^4)	2477649
Less: Selling expenses(5% on above)	123882
Net sale value	2353767	2353767
Gain on sale(Net sale value-Carring Value)	557403
Tax on gain	156073
ATCF on sale(net sale value-Tax on gain)	2197694
Less: Loan bal repaid*1.02(Prepay. Penalty)	1504600	1504600
Proceeds of sale	693094	849167

Loan to value ratio=0.80

so, loan amt.= 2000000*0.80= $ 1600000

Monthly pmt. On the loan=

Pmt.=1600000/((1-1.005^-300)/0.005)=

10308.82

Year	0	1	2	3	4
1.Purchase price	-2000000
1.a.After-tax proceeds of Sale of property at end of Yr. 4(as per wkgs.)					2197694
No.of units		40	40	40	40
2.Rent revenue(PGI)--(430*12) & *1.08 every yr.		206400	222912	240745	260004.6
3.Vacancy loss(PGI*3%)		-6192	-6687.36	-7222.35	-7800.137
4. Collection loss(PGI*2%)		-4128	-4458.24	-4814.9	-5200.091
5.Annual opg. Exp.(2+3+4)*35%		-68628	-74118.2	-80047.7	-86451.52
6. Misc. Yrly. Income(800*12)		9600	9600	9600	9600
7. Depn. (2000000-600000)/27.5 yrs.		-50909	-50909	-50909	-50909
8. Annual interest expenses		-95225.25	-93468.63	-91603.66	-89623.67
9.EBT(sum2 to 8)		-9082.338	2870.443	15747.26	29620.05
10.Tax at 28%((*28%)		2543.05	-803.72	-4409.23	-8293.62
11.Net income(9+10)		-6539.28	2066.72	11338.03	21326.44
12. Add back: depn.(Row 7)		50909	50909	50909	50909
13.ATOCF		44369.81	52975.81	62247.12	72235.53
14.Total annual FCFs(1+1.a+13)	-2000000	44369.808	52975.81	62247.12	2269930
15. PV F at 12.5%(1/1.125^ Yr.n)	1	0.88889	0.79012	0.70233	0.62430
16. PV at 12.5%(14*15)	-2000000	39439.829	41857.43	43718.14	1417106
17.NPV of investment(Sum of Row 16)	-457879
a. Before-tax cash flows(1+1.a.(wkgs.)+2+3+4+5+6+8	-2000000	41826.753	53779.53	66656.35	929696.14
b. After-tax cash flows=FCF (Row 14)	-2000000	44369.808	52975.81	62247.12	2269930
BT Equity Reversion:
net selling price	2353767
Less: Loan bal repaid*1.02(Prepay. Penalty)	1504600
BT Equity Reversion	849167
After-tax Equity Reversion:
net selling price	2353767
Less: Loan bal repaid*1.02(Prepay. Penalty)	1504600
Less: tax on gain	156073
After-tax Equity Reversion	693094

Monthly loan amortisation schedule:
No.of mths.	Mthly. Pmt.	Tow. Int.	Tow. Loan	Loan bal.
0				1600000
1	10308.82	8000	2308.82	1597691
2	10308.82	7988.46	2320.36	1595371
3	10308.82	7976.85	2331.97	1593039
4	10308.82	7965.19	2343.63	1590695
5	10308.82	7953.48	2355.34	1588340
6	10308.82	7941.70	2367.12	1585973
7	10308.82	7929.86	2378.96	1583594
8	10308.82	7917.97	2390.85	1581203
9	10308.82	7906.01	2402.81	1578800
10	10308.82	7894.00	2414.82	1576385
11	10308.82	7881.93	2426.89	1573958
12	10308.82	7869.79	2439.03	1571519	95225.25
13	10308.82	7857.60	2451.22	1569068
14	10308.82	7845.34	2463.48	1566605
15	10308.82	7833.02	2475.80	1564129
16	10308.82	7820.64	2488.18	1561641
17	10308.82	7808.20	2500.62	1559140
18	10308.82	7795.70	2513.12	1556627
19	10308.82	7783.13	2525.69	1554101
20	10308.82	7770.51	2538.31	1551563
21	10308.82	7757.81	2551.01	1549012
22	10308.82	7745.06	2563.76	1546448
23	10308.82	7732.24	2576.58	1543872
24	10308.82	7719.36	2589.46	1541282	93468.63
25	10308.82	7706.41	2602.41	1538680
26	10308.82	7693.40	2615.42	1536064
27	10308.82	7680.32	2628.50	1533436
28	10308.82	7667.18	2641.64	1530794
29	10308.82	7653.97	2654.85	1528139
30	10308.82	7640.70	2668.12	1525471
31	10308.82	7627.36	2681.46	1522790
32	10308.82	7613.95	2694.87	1520095
33	10308.82	7600.47	2708.35	1517387
34	10308.82	7586.93	2721.89	1514665
35	10308.82	7573.32	2735.50	1511929
36	10308.82	7559.65	2749.17	1509180	91603.66
37	10308.82	7545.90	2762.92	1506417
38	10308.82	7532.09	2776.73	1503640
39	10308.82	7518.20	2790.62	1500850
40	10308.82	7504.25	2804.57	1498045
41	10308.82	7490.23	2818.59	1495227
42	10308.82	7476.13	2832.69	1492394
43	10308.82	7461.97	2846.85	1489547
44	10308.82	7447.74	2861.08	1486686
45	10308.82	7433.43	2875.39	1483811
46	10308.82	7419.05	2889.77	1480921
47	10308.82	7404.60	2904.22	1478017
48	10308.82	7390.08	2918.74	1475098	89623.67
		369921.2			369921.2