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

  • $\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$
    – nbbo2
    Jun 7, 2020 at 14:19

1 Answer 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:


Here's how it looks like:

Spreadhsheet for IRR

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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