CAGR on Excel - can the first year start negative

[excelman444]

I am working on calculating CAGR on excel using RRI formula. When I try to compute the cell the #NUM! is appearing in the cell when the first term is <0. How can I make this work?

 

[Cubist]

I am working on calculating CAGR on excel using RRI formula. When I try to compute the cell the #NUM! is appearing in the cell when the first term is <0. How can I make this work?

The first argument is the number of periods. It must be a positive.

 

[excelman444]

The first argument is the number of periods. It must be a positive.

what about when I do present value. Does that have to be positive, that’s where my issue is coming from as present value is below 0 (minus number) and it ends up being #num! for this scenario. ie rri(3,-0.3%,7%) I am trying to calculate net unit growth ????

 

[Cubist]

1) RRI can't take a negative period because you'd be discounting backwards, meaning your PV is being discounted, so the "FV" isn't the future value but the n-periods before your PV.
2) The PV can't be negative because you can't take even root of a negative number (if you understood the CARG formula).

The RRI formula isn't built to handle case 2). One way to handle that is as follows...

First, take the absolute value of the negative PV: |-0.3%| = 0.3%
Next, find the distance difference between your original PV and FV: 7% - (-0.3%) = 7.3%
Then, your new FV is the absolute value of the PV + the difference: 0.3% + 7.3% = 7.6%

=RRI(3,0.3%,7.6%)

 

[excelman444]

Thank you- would this be accurate though and would the converting of negatives need to be noted in footnote?

 

[Cubist]

That's up to you but the idea is to shift the negative PV to a positive PV while keeping the same distance.

-0.3% to 7% has a distance of 7.3%
0.3% to 7.6% has a distance of 7.3%

This is one of the methods to handle such cases, but not standardized.

I find the goal-seek in this article a lot neater...

Calculating CAGR with Goal Seek in #Excel

when the starting point is a negative number… Calculating CAGR is not difficult, all we need is the starting value, ending value and the number of periods. Then we use the formula: CAGR = (E…

wmfexcel.com