IRR formula help

[TwoAce]

Hi all,

I need an IRR of 1% and use this formula in cell B1: =IF(A1<1%;"x";0)
Now I need to figure out what amount to add in cell B1 to get the IRR up to 1%.
Assume the IRR data is in cells C1 to C10 (dates) and D1 to D10 (amounts).
Does anyone know how to put this in the formula?

Thanks!
Rob

 

[joeu2004]

I need an IRR of 1% and use this formula in cell B1: =IF(A1<1%;"x";0)
Now I need to figure out what amount to add in cell B1 to get the IRR up to 1%. Assume the IRR data is in cells C1 to C10 (dates) and D1 to D10 (amounts). Does anyone know how to put this in the formula?

The mathematical formula to use is provided in the XIRR help page. The IRR is the rate that causes the NPV to be zero.

I assume the value in B1 (that is, "x") is intended to be added to the last cash flow, D10 on date C10. So B1 should be:

=IF(A1<1%; -SUMPRODUCT(D1:D9/(1+1%)^((C1:C9-C1)/365))*(1+1%)^((C10-C1)/365) - D10; 0)

Caveat: If you add the non-zero result in B1 to D10, you might find that A1 is still not exactly 1%, assuming A1 is =XIRR(D1:D10; C1:C10). In my example, it is 0.999999940395356%. The difference is due to approximation errors inherent in the XIRR algorithm. For example, Excel XIRR stops iterating when the derived IRR is within 0.000001% of the IRR derived in the previous iteration, according to my interpretation of the XIRR help page. (Details in Microsoft help pages are notoriously unreliable.)

 

[joeu2004]

PS....

Caveat: If you add the non-zero result in B1 to D10, you might find that A1 is still not exactly 1%, assuming A1 is =XIRR(D1:D10; C1:C10). In my example, it is 0.999999940395356%. The difference is due to approximation errors inherent in the XIRR algorithm. For example, Excel XIRR stops iterating when the derived IRR is within 0.000001% of the IRR derived in the previous iteration, according to my interpretation of the XIRR help page. (Details in Microsoft help pages are notoriously unreliable.)

I just had wanted to make the point that the condition A1<1% might still be true, even after adding the derived value in B1.

Of course, in the real world, we should never expect exactly 1% anyway, if only because monetary amounts must be rounded, for example to the cent.

So it would be better to change the conditional expression to something like:

=IF(ROUND(A1,2)<1%; ...)

I use 2 decimal places in order to round to the "whole" percentage; that is, to zero percentage decimal places. Remember that 1.23% is the decimal number 0.0123.

 

[TwoAce]

Hats of to you sir, it worked like a charm! Thanks a lot. I did it by trial and error before, but with your help it is much better!