Question

Please answer A, B, C with excel thank you!

You are choosing between two projects. The cash flows for the projects are given in the following table (5 million) Project Year 0 Year 1 Year 2 - $50 $23 -$102 $20 $40 Year 3 Year $18 $19 $59 a. What are the IRRs of the two projects? b. If your discount rate is 5.2%, what are the NPVs of the two projects? c. Why do IRR and NPV rank the two projects differently?

Answer 1

Campo- New Microsoft Office Excel Worksheet - Microsoft Excel Page Layout Formulas Data Review View Calibri B I 11 - - A > A A U - E E E Dr Wrap Text E Merge & Center - Alignment Percentage $ - % , Number . Conditional Format Formatting as Table Styles Cell Styles Insert Delete Format AutoSum Clear 2 Clear Sort & Find & Filter Select Editing Cells Home Insert * Cut La Copy Paste Format Painter Clipboard J3423 F 3416 3417 3418 3419 3420 3421 Font fax =IRR(13418:13422) G M N O P a R s 1- Year Year cash flow project B cash flow project A -50 -102 1 20 18 40 3422 17 59 IRR = Using IRR function in MS excel irr(13418:13422) IRR = Using IRR function in MS excel IRR(K3418:K342 2) 20.95% 20.18% Year Year 3423 3424 3425 3426 3427 3428 3429 cash flow project A -50 cash flow project B O -102 1 20 3430 NPV =Using NPV NPV(5.2%,L3 function in 427:L3430)+L $18.33 MS excel 3426 NPV =Using NPV NPV(5.2%,13427:13 function in MS excel 430)+13426 3431 $45.13 3432 3433 3434 Project A Project B IRR Sheet25 Sheet22 Sheet23 Sheet24 Sheet26 Sheet27 Sheet28 Sheet29

New Microsoft Office Excel Worksheet - Microsoft Excel - Home Cut Insert Page Layout Formulas Data Review View Percentage Eg Copy Dr E EEE Wrap Text Merge & Center - - 3 Insert Delete Format E AutoSum" A 2 Clear clear Sort & Find & Filter Select $ - % - H D . Conditional Format Formatting as Table Styles , Calibri - 11 - A A B IU - > A E Font O fx =IRR(L3418:L3422) G Cell Styles Alignment Number Cells Editing O р a R s Paste Format Painter Clipboard N3423 F 3416 3417 3418 3419 3420 3421 Year Year cash flow project A -50 cash flow project B -102 1 20 18 40 3422 17 59 IRR = Using IRR function in MS excel irr(13418:13422) IRR = Using IRR function in MS excel IRR(K3418:K342 2) 20.95% 20.18% Year Year 3423 3424 3425 3426 3427 3428 3429 cash flow project A -50 23 cash flow project B 0 -102 1 20 3430 NPV =Using NPV NPV(5.2%,L3 function in 427:L3430)+L $18.33 MS excel 3426 NPV =Using NPV NPV(5.2%,13427:13 function in MS excel 430)+13426 3431 $45.13 3432 3433 3434 Project A Project B 1 Sheet27 Sheet28 Sheet29 IRR Sheet25 Sheet24 Sheet26 Sheet22 Sheet23 Circular References Ready BO 100% 0 +

1 1 - ) 1 AutoSumAY A Eg Copy 2 Clear clear Sort & Find & Filter Select Editing O P a R s CODE New Microsoft Office Excel Worksheet - Microsoft Excel Home Insert Page Layout Formulas Data Review View Cut Calibri - 11 - A A Dr Wrap Text Currency Paste B IU - > A E E Format Painter E EEE Merge & Center - $ - % , . Conditional Format Cell Insert Delete Format Formatting as Table Styles Clipboard Font Alignment Number Styles Cells J3431 - fx =NPV(5.2%,13427:13430)+13426 F G M N 3416 3417 Year cash flow project A Year cash flow project B 3418 -50 3419 1 3420 40 3421 3422 17 IRR = Using IRR = Using IRR IRR function IRR(K3418:K342 3423 function in MS excel irr(13418:13422) 20.95% in MS excel 2) 20.18% 3424 3425 Year cash flow project A Year cash flow project B 3426 -50 O -102 3427 23 1 20 3428 18 3429 3430 4 -102 20 18 59 17 NPV =Using NPV NPV(5.2%,L3 function in 427:L3430)+L $18.33 IMS excel 3426 NPV =Using NPV NPV(5.2%,13427:13 function in MS excel 430)+13426 3431 $45.13 3432 Project A Project B IRR 3433 3434 ™ Ready K Sheet24 Sheet25 Sheet26 Sheet27 Sheet28 Sheet29 Sheet22 Sheet23 Circular References 100% A V +

New Microsoft Office Excel Worksheet - Microsoft Excel E REZERDERA Σ AutoSum" Α Currency Eg Copy Paste $ ,00 % , Number Insert Delete Format Conditional Format Formatting as Table Styles Cell Styles 2 Clear Sort & Filter Editing Find & Select Home Insert Page Layout Formulas Data Review View Cut Calibri - 11 - A A Dr Wrap Text Format Painter BI U A E E Merge & Center - Clipboard Font Alignment M3431 foc =NPV(5.2%,L3427:L3430) +L3426 F G 1- Year cash flow project A -50 Cells N 0 р a R s 3416 3417 Year cash flow project B -102 3418 1 20 18 40 3419 3420 3421 3422 17 59 IRR = Using IRR function in MS excel irr(13418:13422) IRR = Using IRR function in MS excel IRR(K3418:K342 2) 20.95% 20.18% Year Year 3423 3424 3425 3426 3427 3428 3429 3430 cash flow project A -50 23 cash flow project B 0 -102 1 20 4 NPV =Using NPV NPV(5.2%,L3 function in 427:13430)+L $18.33 MS excel 3426 NPV =Using NPV NPV(5.2%,13427:13 function in MS excel 430)+13426 3431 $45.13 3432 Project B 3433 3434 Ke Project A 1 Sheet27 Sheet28 IRR Sheet25 2 Sheet22 Sheet23 Sheet24 Sheet26 Sheet29

1-
Year	cash flow project A		Year	cash flow project B
0	-50		0	-102
1	23		1	20
2	18		2	40
3	19		3	51
4	17		4	59
IRR = Using IRR function in MS excel	irr(I3418:I3422)	20.95%		IRR = Using IRR function in MS excel	IRR(K3418:K3422)	20.18%
2-
Year	cash flow project A		Year	cash flow project B
0	-50		0	-102
1	23		1	20
2	18		2	40
3	19		3	51
4	17		4	59
NPV =Using NPV function in MS excel	NPV(5.2%,I3427:I3430)+I3426	$18.33	NPV =Using NPV function in MS excel	NPV(5.2%,L3427:L3430)+L3426	$45.13
	Project A	Project B
IRR	1	2
NPV	2	1
this difference in ranking between two methods is due to occurance of cash flow from highest to lowest in project A and lowest to highest in project B. Another cause of difference in ranking is of amount of initial investment which is almost double in case of project B in comparison to A