Question

Acme Oscillators is considering an investment project that has the following rather unusual cash flow pattern.

Year 0, Cash Flow $101

Year 1, Cash Flow -460

Year 2, Cash Flow 790

Year 3, Cash Flow -602.9

Year 4, Cash Flow 171.6

a. Calculate the​ project's NPV at each of the following discount​ rates: 00​%, 5​%, 10​%, 20​%, 30%​, 40​%, 50​%.

b. What do the calculations tell you about this​ project's IRR? The IRR rule tells managers to invest if a​ project's IRR is greater than the cost of capital. If Acme​ Oscillators' cost of capital is 88​%, should the company accept or reject this​ investment?

c. Notice that this​ project's greatest NPVs come at very high discount rates. Can you provide an intuitive explanation for that​ pattern?

How would you calculate the project's NPV at each discount rate using excel? I'm really struggling with this one. Thank you.

Answer 1

Given Data:

Year #	#	Cash Flow in $
Year 0	0	101
Year 1	1	-460
Year 2	2	790
Year 3	3	-602.9
Year 4	4	171.6

Solutions:

Part A:

Discount Rates: 0​%, 5​%, 10​%, 20​%, 30%​, 40​%, 50​%.

NPV calculation implemented in Excel:

The Cells D4 onwards to D7 is the same formula (Shown in the formula bar) copied and pasted and the Total NPV in Cell D9 is just the SUM(D3:D7).

Therefore varying the Discount Rates in cell G2 with the given Discount Rates:

Discount Rate	NPV
0%	-0.3000
5%	-0.1739
10%	-0.0518
20%	0.1319
30%	0.2717
40%	0.4429
50%	0.7037

PART B:

IRR is the discount rate where NPV = 0. Using the Goal Seek function from the "What-If" tools in "Data" Menu in Excel and setting the condition for Total NPV to be zero (Check Image below). We get the IRR to be 12.42%

Since the IRR is 12.42% which is less than the Cost of Capital 88%, hence according to the IRR rule, the investment in this project is to be rejected.

PART C:

As we can observe from the table in the PART A Answer, the projects NPV increases as Discount Rate Increases. This can be explained by considering that the cash flow in year 3 is a huge negative and as Discount Rate Increases, the absolute present value of year 3's cash flow decreases and hence the decrease in NPV due to that negative year 3 cash flow decreases.