How to calculate IRR between 2 numbers

Question

I want to consider 4 scenarios in google sheets. All deal with a periodic return over n periods

For instance EPS in 2010 was \$1 and EPS in 2020 was \$3. How to best calculate the annual return between these data points?

I tried this in Excel =((last/first)^(1/n-1)-1)

This formula breaks when I am changing either number to negative.

What can I do to fix it? Is there a grateful way to do it in Excel?

Should'nt it be =((last/first)^(1/(n-1))-1) in the case of positive numbers? but in any case the use of rates of return seems inappropriate for something which can negative as well as positive . — nbbo2, Commented Jun 7, 2020 at 14:19

AllBlooming · Accepted Answer · 2020-06-07 16:00:52Z

1

One way to handle this gracefully is to make these assumptions:

Turning a loss into a profit is considered a positive infinite return
Turning a profit into a loss is considered a negative infinite return
Turning zero to zero is considered a 0% growth
Turning zero into a profit is a positive infinite return
Turning zero into a loss is a negative infinite return
Turning a profit to zero is negative infinite return
Turning a loss into zero is positive infinite return
Turning a loss into a loss is considered a negative return, to the same amount as if both first and last were positive

Here's the formula I'm using in Google sheets to take care of that:

if(D3=0,if(D2=0,0,if(D2>0,"-∞","+∞")),if(D3>0,if(D2>0,(D3/D2)^(1/(n-1))-1,"+∞"),if(D2>=0,"-∞",-((D3/D2)^(1/(n-1))-1))))

Here's how it looks like:

answered Jun 7, 2020 at 15:50

AllBlooming

2731 silver badge7 bronze badges

$\begingroup$ why is the answer +infinity when you go from -1 to 3? $\endgroup$
– JAM
Commented Jun 10, 2020 at 2:09
$\begingroup$ It's positive infinity because results improved. You can sort companies by return (or growth rate), and then this one would be at the very top of the list. After all, they went from loss to profit. Even the ones that went from 0 to profit have +infinity, so going from -1 to 3 deserves even more. Just my personal view on usefulness, of course. You can define it as -infinity as well. My preference: +infinity if they perform terribly well, -infinity if they perform terribly bad. :-) Same reason why I give -1 to -3 a negative return. $\endgroup$
– AllBlooming
Commented Jun 10, 2020 at 3:39

Add a comment |

1 Answer 1