1
$\begingroup$

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

  • Positive to Positive
  • Positive to Negative
  • Negative to Negative
  • Negative to Positive

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?

enter image description here

$\endgroup$
  • $\begingroup$ 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 . $\endgroup$ – noob2 Jun 7 at 14:19
1
$\begingroup$

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:

Spreadhsheet for IRR

| improve this answer | |
$\endgroup$
  • $\begingroup$ why is the answer +infinity when you go from -1 to 3? $\endgroup$ – JAM Jun 10 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$ – FutForFut Jun 10 at 3:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.