Excel's NPV and IRR function

I was calculating NPV to analyze certain cashflows, and noticed that my values were different from those from our financial math component. I figured that our formula for NPV was computing it the same way as Excel.

It was easy to figure out that Excel uses the following formula:

NPV = C0/(1+r) + C1/(1+r)^2 + ... + Cn/(1+r)^(n+1),

here, C0, C1, ... Cn - cashflow amounts for corresponding periods. We have n periods C1 to Cn and an initial payment C0.

A standard NPV forumla is a little bit different:

NPV = C0 + C1/(1+r) + ... + Cn/(1+r)^n.

Here, our initial payment has value C0 not C0/(1+r), because it's in present time. As you can see, "true" NPV = Excel's NPV multiplied by (1+r).

This article shows another way to calculate "true" NPV using Excel's NPV. It's to compute Excel's NPV for inflow cashflow amounts (i.e. C1 to Cn), then add an initial payment C0.

It's obvious that Excel's IRR function would not be affected by this discrepancy even so it's based on NPV formula. Remember, IRR is such rate r, that NPV(r)=0. When "true" NPV is equal to zero, then Excel's NPV is also equal to zero. Therefore, Excel's IRR is Ok in that sense.

Excel's IRR function uses "plain vanilla" Newton-Raphson root finding algorithm. This method has poor global convergence characteristics, which, in plain English, means that its ability to find roots depends very much on the initial guess. For example, consider this cashflow: 100,-10,-110. Excel's IRR function without guess finds a correct IRR = 10%. However, if you give it a bad guess, say -2, then it can't find IRR.

If you read my earlier IRR analysis post, then you know why. It's because Excel IRR algorithm has no idea about the singularity at rate equal to -1.