Question

Please use excel, need help with functions because I am only used to using a financial calculator, thanks!

The following cash-flows have two IRRs. Draw a graph of the NPV of these cash flows as a function of the discount rate. Then use the IRR function to identify the two IRRs. Would you invest in this project if the opportunity cost were 20%?

Year	Cash Flow
0	-500
1	600
2	300
3	300
4	200
5	-1,000

Answer 1

The excel snip is as shown:

First step is to graph NPV for different values of discount rates. I have taken arbitrary values of discount rates at a step of 2% from each other, starting from 0%. For each value, you need to then calculate NPV using the npv function. In excel, just type '=' (equals sign) and then type npv. In brackets you need to input two values. First, is the discount rate for which you need to reference the adjacent cell and second, is the series of cash flows. Here you need to be careful of two things:

1) Make sure to fix the cash flow reference cells using the F4 key so that when you drag this formula down, your cash flow values stay fixed and only the discount rate varies.

2) You would need to add the zeroth period discount rate (-500) separately as the npv formula starts discounting from very first cash flow that it encounters. Your formula would look as shown below:

After this just drag the formula down and insert a scatter plot.

Next step is to calculate IRR's which is nothing but the discount rate at which NPV is zero. Excel has a IRR function which takes in two values, the series of cash flows and a guess rate. Guess rate is just a ball park figure around which the IRR is expected to lie. From the plotted graph we can see that NPV is zero at around 10% mark and then at the 60% mark. So, simply by using a few guess rates around 10% and 60% we can get the required IRRs. The implementation of IRR function is as shown:

Finally, should the project be invested in at 20% discount rate? Yes, because at this point NPV is comfortably above zero as can be seen from our graph.