In: Finance
required rate of return = 15%
year
year | cash flow ($ in millions) |
0 | -500 |
1 | 90 |
2 | 100 |
3 | 150 |
4 | 180 |
5 | 190 |
6 | 140 |
7 | 100 |
8 | 80 |
9 | 60 |
10 | -50 |
using excel, draw npv profile and find "two" IRRs.
There should be two IRRs. If you get two IRRs, the NPV profile graph should look like a parabola curve.
I figured out the first IRR which is 19% (19.45...% to be exact) but I can't seem to get the second one.
here we have only one IRR not two IRR.
as per NPV graph you can see we have only one IRR.
find IRR by = irr(value,guess)
value = select cash flow and lock cell by F4
guess = select guess value that can be any value
now drag irr value up to guess value , if have two irr then you will two bur here we found only two.
create different NPV with diff discount rate (by guess)
NPV = = -500+npv(rate,value)
rate= discount rate
value =select all cash flow but ignore initial -500.
lock -500 by F4 and also lock value.
now drag down you will found all diff NPV.
now select all NPV and discount rate
go in to insert menu and select scatter.
Now we can see only 0 npv at 19% not at two diff rate that mean there is only one NPV