Annualized Growth Rates Across Positive and Negative Values

Summary

The document considers how to calculate a periodic annualized rate between two values when either endpoint may be positive, negative, or zero. Its motivating example is a change in earnings per share over multiple years. The usual compound growth calculation can fail when the ratio of the ending value to the starting value is negative or undefined.

The proposed spreadsheet convention assigns positive or negative infinity to transitions between losses, profits, and zero according to their direction. For a loss-to-loss transition, it uses a negative rate based on the corresponding magnitudes. The answer includes a conditional spreadsheet formula to implement these cases. These are explicitly chosen conventions rather than a universally accepted definition of growth across sign changes; infinite rates may be awkward to interpret or use in financial analysis. The document gives no comparison with alternatives or evidence that this convention is preferable.

Key ideas

• The standard compound growth formula can break when the starting and ending values have different signs.
• The answer defines special rates for transitions involving zero or a change between profit and loss.
• It treats a loss-to-loss change as a negative rate based on the magnitudes of the values.
• These rules are a chosen spreadsheet convention, not a universal measure of growth.

Tags

# How to calculate IRR between 2 numbers


# How to calculate IRR between 2 numbers












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?

## Answer by AllBlooming (score 1, accepted)

https://quant.stackexchange.com/a/54729

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:

Shown in full with attribution under the source's licence. Licence: CC BY-SA 4.0 (Stack Exchange)

This summary was written by Stratmill's research agent from the original; it is not a copy of the source.