Question

Consider the following cash flows (see the attached Excel sheet) and answer the following questions.

Show your work in Excel.

a) What is the net present value (NPV) if the opportunity cost of cost of capital is 10%?

b) Add an outflow of $2000 at time 0 and lower the opportunity cost of capital to 6%. Now, what is the net present value?

B A Parta) Year Cash Flow $2,000 $2,000 $1,500 $2,500 $4,000 5 Cost of Capit 10% NPV

Answer 1

Solution 1) Calculation of net present value (NPV) if the opportunity cost is 10%.

Year	Cash Flow
0	2000.00
1	2000.00
2	0.00
3	1500.00
4	2500.00
5	4000.00

Following are the steps to be followed on Microsoft Excel:

Step 1: Click on "FORMULAS" tab at the top of Microsoft Excel
Step 2: Select the option "Financial"
Step 3: Under "Financial" select the option "NPV"
Step 4: Insert Rate = 0.1    Value 1 = {2000;0;1500;2500;4000}

NPV = $7136.67

$7136.67 will be the Present Value of the Cash Flows from 1^st Year to 5^th Year.

As there is also a Cash Inflow at year 0, that amount has to be added to the above Net Present Value.

Therefore, the final answer is $7136.67 + $2,000 = $9136.37

Alternatively, Net Present Value can also be calculated as:

Year	Cash Flow $	Discounting Factor @ 10%	Present Value of Cash Flows $
0	2,000.00	1.0000	2,000.00
1	2,000.00	0.9091	1,818.18
2	-	0.8264	-
3	1,500.00	0.7513	1,126.97
4	2,500.00	0.6830	1,707.53
5	4,000.00	0.6209	2,483.69
		Net Present Value	9,136.37

Solution 2) Calculation of net present value (NPV) after adding an outflow of $2,000 at Time 0 and lowering the opportunity cost to 6%.

Year	Cash Flow $
0	-2,000.00
1	2,000.00
2	-
3	1,500.00
4	2,500.00
5	4,000.00

Following are the steps to be followed on Microsoft Excel:

Step 1: Click on "FORMULAS" tab at the top of Microsoft Excel
Step 2: Select the option "Financial"
Step 3: Under "Financial" select the option "NPV"
Step 4: Insert Rate = 0.06    Value 1 = {2000;0;1500;2500;4000}

NPV = $8,115.49

$8,115.49 will be the Present Value of the Cash Flows from 1^st Year to 5^th Year.

As there is also a Cash Outflow at year 0, that amount has to be subtracted to the above Present Value.

Therefore, the final answer is $8,115.49 - $2,000 = $6,115.49

Alternatively, Net Present Value can also be calculated as:

Year	Cash Flow $	Discounting Factor @ 6%	Present Value of Cash Flows $
0	-2,000.00	1.0000	-2,000.00
1	2,000.00	0.9434	1,886.79
2	-	0.8900	-
3	1,500.00	0.8396	1,259.43
4	2,500.00	0.7921	1,980.23
5	4,000.00	0.7473	2,989.03
		Net Present Value	6,115.49