Objective C IRR calculation compared to Excel IRR function

Question

The discussion below provides codes in Objective C IRR calculation that is consistently incorrect when compared to Excel IRR function. Does anyone know why there is such a difference?

The code used is offered in this previous discussion:

Calculate IRR (Internal Rate Return) and NPV programatically in Objective-C

The test cash flow I am using to calculate the IRR is the following: "Print tempCashFlow: ( "-5099701.25", "-22503.796875", "-22503.79296875", "-22503.79296875", "-20907.26171875", "-17899.7421875", "-17899.7421875", "-17899.7421875", "-14660.69140625", "-12447.80078125", "-12447.796875", "-12018.1640625", "-5991.81640625", "-5991.81640625", "-5991.81640625", "-2885.875", "1653.125", "1653.125", "1653.125", "8307.328125", "11110408.45703125" )

The above temporary cash flow contains quarterly figures over a 5-year period (i.e. 20 periods plus the time zero period... so 21 figures in the cash flow).

The code delivers 15.2% IRR instead of circa 16.0% produced by Excel IRR function. I also tested it manually, and I believe 16% is the correct answer. Can anyone help understand what may be wrong with the code? I cannot see any issues. It also appears to be identical to code promoted on this link as well:

http://www.codeproject.com/Tips/461049/Internal-Rate-of-Return-IRR-Calculation

PS: I've also noticed that the margin of error increases dramatically if I reduce the cash flow to a 2-year period instead of a 5-year period (e.g. something like 35% (incorrect) versus 45% IRR (correct answer)

Answer 1

The IRR for that series of cash flows as reported by Excel's IRR function is 3.803961% (but that assumes they were annual cash flows). That C algorithm you reference returns 3.803961%, as well. So, there is no discrepancy between these two calculated values. And if I apply that rate as I sum the NPV of all of those annual cashflows, I get zero, verifying that 3.803961% is correct IRR.

You haven't shared how you're using either Excel IRR or this function's to handle quarterly cash flows, but I suspect that you may be simply multiplying this C function's resulting value by four, which is not correct. You have to apply quarterly compounding to it. If I do the former, I reproduce your incorrect value of 15.215842%, but if I do the latter, I get the correct value, 16.106276%. And, again, verifying this by calculating the cumulative NPV of these cash flows, I get zero, confirming that 16.1% is the correct IRR for this series of quarterly cash flows.