Question

Prepare an Excel spreadsheet to present your financial decisions for two projects using NPV, IRR and the Payback Period techniques.

There are two mutually exclusive projects A and B.  Both projects require an investment of $10 million but the timing is different for the rest of the expected net cash flows.

For project A                           For project B

Period 0 =    - $10.0 m            - $10.0 million

Period 1 =           6.5                       3.5 m

Period 2              3.0                        3.5

Period 3 =           3.0                          3.5

Period 4 =           1.5                          3.5

Total Inflow = $14.0m                $14.0m

Prepare 3 possible scenarios for each project.  Use the 5% discount rate (WACC) for a forecast if these are low risk projects, 10% if we think they have a normal amount of risk and 15% if we decide that these are high risk projects.

Answer 1

Formulas Used:-

Year	Project A	Project B
0	-10000000	-10000000
1	6000000	3500000
2	3000000	3500000
3	3000000	3500000
4	1500000	3500000
Payback	=2+(-B2-SUM(B3:B4))/B5	=2+(-C2-SUM(C3:C4))/C5
IRR	=IRR(B2:B6)	=IRR(C2:C6)
Disc. Rate	0.05	0.1	0.15
NPV A	=NPV(B11,$B$3:$B$6)+$B$2	=NPV(C11,$B$3:$B$6)+$B$2	=NPV(D11,$B$3:$B$6)+$B$2
NPV B	=NPV(B11,$C$3:$C$6)+$C$2	=NPV(C11,$C$3:$C$6)+$C$2	=NPV(D11,$C$3:$C$6)+$C$2

Decision Rule:-

Payback Period = Project A should be Choose because it has lower payback period

NPV= When Discount rate is 5% the project B should be choose. When Discount rate is 10% the project A should be choose and When Discount rate is 15% the project A should be choose.

IRR = at Any Discount Rate the project A should be choose because it has Higher IRR.