Question

Please use Excel functions like PV

A company is considering two capital budgeting projects. Each project requires an initial outlay of $4,000,000. Cash inflows for each project are given in the table below. Each project has a life of 3 years. The company uses the NPV method to evaluate capital budgeting projects and its required rate of return is 9%. Here are the cash inflows from the projects.

Project A         Project B        

Year 1                                                 $1,900,000            0               

Year 2                                                 $1,900,000            0               

Year 3                                                 $1,900,000      $6,000,000               

1. Perform all relevant calculations to determine NPV for each project. Show all work and perform all time value computations using the time value functions in Excel.
2. If the projects are independent, which, if any, should the company accept? Why?
3. If the projects are mutually exclusive, which, if any, should the company accept? Why?
4. One of the company’s managers states “To me, no matter what else we do, Project B needs to be our first choice because it has the largest cash inflows of $6,000,000”. Comment on this manager’s proposal, considering the tenets of NPV and based on your computations.

Answer 1

First Let us calculate the NPV using discount factors:

Project A
Year	CF	Discount Factor			Discounted CF
0	-4000000	1/1.09^0	=	1	-4000000*1	=	-4000000
1	1900000	1/1.09^1	=	0.917431193	1900000*0.91743119266055	=	1743119.266
2	1900000	1/1.09^2	=	0.841679993	1900000*0.84167999326656	=	1599191.987
3	1900000	1/1.09^3	=	0.77218348	1900000*0.772183480061064	=	1467148.612
					NPV	=	809459.8654
Project B
Year	CF	Discount Factor			Discounted CF
0	-4000000	1/1.09^0	=	1	-4000000*1	=	-4000000
1	0	1/1.09^1	=	0.917431193	0*0.91743119266055	=	0
2	0	1/1.09^2	=	0.841679993	0*0.84167999326656	=	0
3	6000000	1/1.09^3	=	0.77218348	6000000*0.772183480061064	=	4633100.88
					NPV	=	633100.8804

• If projects are independent, we can choose both the projects As NPV of both is positive which implies both are able to recover the costs
• If projects are mutually exclusive, we can only choose one project so As NPV of Project A is greater we will pick that over B
• Even though project B has highest CF, it occurs in year 3 and in present value terms it results in lower NPV so the statement given by the manager is incorrect

Now let's achieve the same NPVs using Excels PV function:

Project A		Inputs to PV formula	Explanation of the inputs
PV	48,09,459.87	=PV(rate, nper, pmt, [FV], [Type]) =PV(0.09,3,-1900000,0)	Rate of 9% is put in decimal terms nper is the number of periods of annual Cf which is 3 pmt is the annual CF which is 190000 FV is the future value which is 0 Type is by default end of the period so it can be left blank Now if we put a positive figure in pmt, it will imply inflows but the PV value will be negative as this formula works in the principle of how much out flow can be recovered if these payments are achieved. But as we need to get a positive PV and later subtract the actual investment from it, we purposely enter a negative value
Investment	40,00,000.00
NPV=PV- Investment	8,09,459.87
Project B
PV	46,33,100.88	=PV(rate, nper, pmt, [FV], [Type]) =PV(0.09,3,0,-6000000)	pmt is the annual CF which is 0 for first two years so we put PMT as 0 We put FV as -6000000 to represent the CF for year 3 Reason for negative FV is the same as the for negative pmt in case of investment A
Investment	40,00,000.00
NPV=PV- Investment	6,33,100.88

We see that eve by using excel functions, our analysis will remain the same.