Question

5.

A Company is comparing three alternative investments in Field 1, Field 2 and Field 3. This company opts to undertake the analysis using a discount rate of 10%. The estimated cash flows for each investment are as follow:

Years	Field 1 Cash Flow $	Field 2 Cash Flow $	Field 3 Cash Flow $
0	-75,000	-175,000	-475,000
1	30,000	25,000	150,000
2	20,000	25,000	150,000
3	20,000	25,000	150,000
4	15,000	25,000	75,000
5	10,000	25,000	75,000
6	5,000	25,000	50,000
7	0	25,000	20,000
8	0	25,000	20,000
9	0	25,000	20,000
10	0	25,000	20,000

Using the standard discount factor method (end-of-year cash flow) and interest rate of 10%. Calculate:

1. NPVs of the future cash flow for each project.           

  

2. Profit to investment ratio (PI) for each project (assuming all investments is on Year 0 and production is started from Year 1).    

                                                       

c. Calculate IRR for each project and evaluate the attractiveness of each project and decide which project should be chosen by the company.

I think excel can be used. Thanks

Answer 1

NPV can be found using NPV function in EXCEL

=NPV(rate, Year1 to Year10 cashinflows)-Year0 cashoutflow

rate=10%

Project with highest NPV should be accepted

PI=NPV(rate, Year1 to Year10 cashinflows)/Year0 cashoutflow

Project which is more higher than 1 will be accepted

=IRR(values)

=IRR(Year0 to Year10 cashflows)

IRR which is most higher than discount rate of 10%, should be accepted

Please find the table where I explianed each capital budheting tool

In all the above three capital budgeting tools, Field 3 has highest NPV, PI and higher IRR>discount rate.Hence, they should accept Field 3