CAGR formula

This is the formula many of us financial analysts use in practice:

=(B2/A2)^(1/C2)−1

Rarely needs to be any more complicated than that.

SG

SG

SGIII wrote:

This is the formula many of us financial analysts use in practice:

https://discussions.apple.com/content/attachment/6e4f28b6-cab4-4fc0-b2cf-c5825daeca6c

=(B2/A2)^(1/C2)−1

Rarely needs to be any more complicated than that.

SG

This is the same formula I gave. If you read the tip on the POWER function I included in my response (screencapped from the in app help files) it is clearly indicated that =POWER(x,y) achieves the same result as x^y. The only difference is when I wrote out the formula in this format in my response, I included an extra, and perhaps unnecessary, set of parenthesis to clearly indicate that the integer “1” is subtracted from the entire result of the exponent operation, and not part of the exponent itself.

In regards to this formula not being a pre-set function within Numbers, Numbers includes most, but not all of the functions that Excel does, but it does not add any that I am aware of. In the finance category, Numbers contains all of the functions that Excel does. Excel does not have CAGR either. If they had functions for every formula used in finance, this app would consume as much space as your Operating System. Spreadsheet apps like Numbers and Excel give us the tools to construct any formula we need to create. They do not offer functions to calculate every ratio.

With regards to DATEDIF being used to find length of time, it’s a perfectly acceptable alternative to using YEARFRAC as I suggested, as it is equally accurate:

YEARFRAC exists solely for this purpose whereas DATEDIF can be used to find months, days, etc, so it requires 3 arguments whereas YEARFRAC is slightly simpler, requiring only 2 arguments. But they will both get you where you need to go.

One point I would stress is that if your data lists beginning and end dates, and you are using Numbers to calculate the length of time, this must be a number value in years, not a duration. The best way to do this is with YEARFRAC.

Simply using “end date - start date” to determine length of time will give a duration value, which will produce an incorrect result.

DUR2DAYS÷365 or DUR2WEEKS÷52 can also be used to convert a duration value to years, but will be less accurate.

joeljpope wrote:

thanks, you would think they would have a CAGR formula

If you use the simple, short form that most of us use in practice there really is not that much need for a special function for this particular task. Short. Simple.

SG

I have given the form we analysts almost always actually use in practice. It's simple, compact and gets the job done. It works in both Excel and Numbers.

There are often other ways to do a particular thing, of course, some more complicated than others. But most of us don't use them unless they clearly add value.

SG

It’s the exact same form though. That’s the point I am trying to make. I gave a thorough response, starting with the Wikipedia definition of CAGR (the LaTeX formula included in my response is a screen cap from same) and ending with a formula that will be recognized by any spreadsheet application. I could have gone with either the “^” operator or the POWER function, and opted for the latter only because both the number and the exponent contain both a divisor and a dividend. Thus, I felt the POWER function was simpler than using parenthesis. The middle portion of my response detailed the mathematical process to get us there. It was a full answer. If a person just wants the final result, they can skip to the end of my answer, or read yours, both of which are the same.

I too analyze data for a living, though not financial data (I work in aviation) and I was unfamiliar with this acronym CAGR before this question was posed. But once I looked it up on Wikipedia I recognized that it was a simple mathematical formula that could be done by any spreadsheet. Anyone else looking up a formula on Wikipedia will also find a LaTeX formula - I believe showing the steps to get from that to a spreadsheet formula does add value.

That’s a valid point. I first found this site through Googling my question, and expect that if someone Googles a question which I answer here, my answer (more specifically, the thread) will come up. So I try to provide as full an answer as I can, assuming that it may be used not just by the person asking the question, but by others who searched the web for a similar question. I think your answers are great - you have shown me some methods of doing things that were simpler than what I was doing, and I have modified some of my documents accordingly. But I also believe a full answer can add value to any question.

Yes indeed. Full. But not too complicated. It's a tough needle to thread. Best regards, SG

Yes, a very full answer. And not wrong, of course. But it does end up suggesting a form different from mine, and thus from what practitioners actually use in the real world of financial analysis, though I suppose there are always exceptions. CAGR is a simple and very common calculation in the financial world. Not sure it really deserves to be made to seem too complicated. I'd save that for IRR, MIRR, bond math, those kinds of things, which really do get confusing and complicated.

SG