Question

All answers must be entererd as formulas

CHAPTER 8 0 Seved Help Save & Exit Submit Consider the following two mutually exclusive projects. What is the IRR for each project? What is the crossover rate between the two projects? What is the NPV for each project at the various interest rates in cells C27 to C32? Annual cash flows: Year 0 Year 1 Year 2 Year 3 $ (24,000) 10,620 10,900 10,500 (24,000) 12,100 9,360 10,400 Complete the following analysis. Do not hard code values in your calculations. You must use the built-in Excel functions to answer this question. IRR (X) IRR (Y)

Answer 1

IRR is the internal rate of return at which present value of cash inflows are equal to initial investment.

IRR Calculation

	Project X	Project Y
Years	Cash flows	Cash flows
0	-$24,000	-$24,000
1	$10,620	$12,100
2	$10,900	$9,360
3	$10,500	$10,400
IRR	16.0%	16.1%

Formula

A 1 Project X Project Y Cash flows Cash flows 3 Years 4 0 -24000 -24000 12100 5 1 10620 10900 9360 6 2 10500 7 3 10400 =IRR(B4:B7) =IRR(C4:C7) IRR co

Cross over rate is the IRR of difference in cash flows of 2 projects.

Cross over rate calculation

	Project X	Project Y	Difference in
Years	Cash flows	Cash flows	Cash flows
0	-$24,000	-$24,000	$0
1	$10,620	$12,100	-$1,480
2	$10,900	$9,360	$1,540
3	$10,500	$10,400	$100
Cross over rate			10.2%

Formula

В C D 1 Project X Project Y Difference in 2 Cash flows Cash flows Cash flows Years -24000 4 0 B4-C4 -24000 10620 - B5-C5 5 1 12100 10900 6 2 9360 ЕВ6-Сб 7 3 10400 10500 ЕB7-C7 =IRR (D4 : D7 ) Cross over rate co

NPV is the difference between present value of cash flows and initial investment at the required rate of return.

NPV calculation

	Project X	Project Y
Years	Cash flows	Cash flows
0	-$24,000	-$24,000
1	$10,620	$12,100
2	$10,900	$9,360
3	$10,500	$10,400
Required return	NPV	NPV
0%	$8,020.00	$7,860.00
5%	$4,829.72	$4,759.54
10%	$2,319.65	$2,317.46
15%	$331.02	$380.36
20%	-$1,253.47	-$1,165.12
25%	-$2,521.60	-$2,403.84

Formula

A C Project X Project Y 2 Cash flows Cash flows 3 Years 4 0 -24000 -24000 10620 5 1 12100 6 2 9360 10900 7 3 10500 10400 NPV NPV 8 Required return |=NPV(A9,$C$4:$C$7) NPV(A10,$C$4: $c$7) |=NPV(A11,$C$4:$C$7) =NPV(A12,$C$4:$C$7) |=NPV(A13,$C$4:$C$7) |=NPV(A14,$C$4:$C$7) =NPV(A9,$B$4:$B$7) NPV(A10,$B$4:$B$7) =NPV(A11,$B$4:$B$7) |=NPV(A12,$B$4:$B$7) =NPV(A13,$B$4:$B$7) =NPV(A14,$B$4:$B$7) 9 0 10 0.05 11 0.1 12 0.15 13 0.2 14 0.25