Question

Can you do this with Excel Please?

2. Robert Washington, president of the Sunshine Hotel, is considering two investments projects. Only one of the two will be selected this year. Information regarding each project is as follows: Project 1: Renovate an existing motel for $1,000,000. After tax, free cash flows are expected to be 200,000 per year for twenty years. Project 2: Build a new motel for $4,000,000. After tax, annual cash flows for twenty years are expected as follows: Free Cash Flow Year -200,000 -50,000 200,000 600,000 1,100,000 5-20 Assume a discount rate of 12 percent. Required: 1) Based on the NPV model, which of the two projects would be selected? 2) Based on the IRR model, which of the two projects would be selected? 3) Which model would you prefer, why?

Answer 1

rate positively ..excel formula used is also provided.

В C E G 1 2 Year PV of 1 Project 2 PV of 2 Project 1 PVIF @ 12% B3 D3 B4* D4 3 0 -1*1000000 =-4*1000000 C3*D3 1 4 A3+1 C4 D4 200000 -200000 =D3/1.12 5-A4+1 200000 D4/1.12 B5* D5 C5 D5 -50000 6 -A5+1 7 A6+1 B6* D6 -D5/1.12 C6* D6 200000 200000 C7*D7 200000 D6/1.12 B7*D7 600000 8A7+1 C8*D8 200000 1.1*1000000 -D7/1.12 B8*D8 - 1.1*1000000 C9*D9 -D8/1.12 B9*D9 -A8+ 1 200000 C10 D10 10A9+1 200000 =1.1*1000000 -D9/1.12 B10*D10 11 A10+1 D10/1.12 -D11/1.12 D12/1.12 C11* D11 - 1.1*1000000 B11*D11 200000 200000 - 1.1*1000000 -B12 D12 C12 D12 12A11+1 13A12+1 200000 -1.1*1000000 B13 D13 C13*D13 14A13+1 B14 D14 - 1.1*1000000 D13/1.12 C14*D14 200000 15 -A14+1 1.1*1000000 -D14/1.12 -B15* D15 C15* D15 200000 16A15+1 C16* D16 - 1.1*1000000 -D15/1.12 B16 D16 200000 17 A16+1 - 1.1*1000000 200000 D16/1.12 -B17*D17 C17*D17 18 A17+1 200000 - 1.1*1000000 D17/1.12 -B18* D18 C18*D18 19 A18+1 B19* D19 - 1.1*1000000 C19*D19 -D18/1.12 200000 20 A19+1 - 1.1*1000000 B20 D20 D19/1.12 C20*D20 200000 1.1*1000000 -D20/1.12 B21*D21 -C21* D21 21A20+1 200000 - 1.1*1000000 B22 D22 =D21/1.12 22 A21+1 200000 C22 D22 23 A22+1 =1.1*1000000 =D22/1.12 B23 D23 C23*D23 200000 | =SUM(F3:F23) =SUM(E3:E23) 24 25 26 NPV project 1 27 NPV project 2 28 IRR project 1 29 IRR project 2 -E24 Prpject 2 should be selcted F24 IRR (B3:B23) Prpject 1 should be selcted =IRR(C3:C23)

Year	Project 1	Project 2	PVIF @ 12%	PV of 1	PV of 2
0	-1000000	-4000000	1.0000	(1,000,000.00)	(4,000,000.00)
1	200000	-200000	0.8929	178,571.43	(178,571.43)
2	200000	-50000	0.7972	159,438.78	(39,859.69)
3	200000	200000	0.7118	142,356.05	142,356.05
4	200000	600000	0.6355	127,103.62	381,310.85
5	200000	1100000	0.5674	113,485.37	624,169.54
6	200000	1100000	0.5066	101,326.22	557,294.23
7	200000	1100000	0.4523	90,469.84	497,584.14
8	200000	1100000	0.4039	80,776.65	444,271.55
9	200000	1100000	0.3606	72,122.00	396,671.03
10	200000	1100000	0.3220	64,394.65	354,170.56
11	200000	1100000	0.2875	57,495.22	316,223.71
12	200000	1100000	0.2567	51,335.02	282,342.60
13	200000	1100000	0.2292	45,834.84	252,091.61
14	200000	1100000	0.2046	40,923.96	225,081.79
15	200000	1100000	0.1827	36,539.25	200,965.89
16	200000	1100000	0.1631	32,624.33	179,433.83
17	200000	1100000	0.1456	29,128.87	160,208.78
18	200000	1100000	0.1300	26,007.92	143,043.55
19	200000	1100000	0.1161	23,221.36	127,717.45
20	200000	1100000	0.1037	20,733.35	114,033.44
				493,888.72	1,180,539.48
NPV project 1		493,888.72	Project 2 should be selected
NPV project 2		1,180,539.48
IRR project 1		19.43%	Project 1 should be selected
IRR project 2		15.02%
we would prefer project 2 as it has higher NPV and NPV is better than IRR.