Hello all,
I need to start using NPV and IRR calculations to review projects. I haven't had to use them since studying CIMA and these projects don't fall into the same category as one outflow and then regular or irregular cash flows. The cash flows are in months and are not every month.
I have tried using the NPV calculation in excel and think this may actually be working but IRR does not work due to my cash flows starting with a positive.
Example cash flows are above.
The NPV calculation that I am using in excel is =NPV((1+C26)^(1/12)-1,E24:K24)+D24 C26 is 10%, E24:K24 is 1/5/16 to 1/11/16 and then D24 is 1/4/16. Should this be D24:K24 instead of E24:K24 and then adding D24 on the end? I have this formula from someone else's example sheet?
The IRR calculation is =XIRR(D24:K24,D19:K19,0.1) D24:K24 is the net cash flows and D19:K19 is the date. This doesn't work as the first net cash flow is a positive and not a negative.
1/4/16 | 1/5/16 | 1/6/16 | 1/7/16 | 1/8/16 | 1/9/16 | 1/10/16 | 1/11/16 | |
In Flows | 600000 | 0 | 0 | 0 | 0 | 0 | 3100000 | 400000 |
Out Flows | -200000 | 0 | 0 | -1900000 | -1200000 | -100000 | -80000 | 0 |
Total | 400000 | 0 | 0 | -1900000 | -1200000 | -100000 | 3020000 | 400000 |
Net Cash Flow | 620000 |
Any help would be appreciated.
Replies (2)
Please login or register to join the discussion.
Don't have time now but
Hi Rikos,
Don't have time to look closely at this but have you considered making your first inflow on 2/4/16 to separate it from the first outflow? Don't think it would have much of an effect on the rate returned.
Regards,
Ian
I'm rusty on NPV and IRR calcs
but I think you should be using XNPV for your NPV calculation and I would agree with Ian re the XIRR calc or you could create an inital outflow of, say, -0.01, also on 1st April.