Techniques to calculate CAGR in Financial Models using Formulas and Functions in Excel

Ever tried to calculate the Compound Annual Growth Rate (CAGR) and found it hard? You're not alone! In this blog post, hosted by  Babatope Agbeyo from dbrownconsulting, we dive deep into the art and science of calculating CAGR using Excel.

What is CAGR?

Babatope began by breaking down CAGR. In essence, CAGR measures the mean annual growth rate of an investment over a specified period longer than one year. Unlike the Annual Growth Rate (AGR), which only looks at year-on-year growth, CAGR provides a smoothed annual rate that helps to visualize consistent growth over time.

CAGR vs. AGR

The key difference? Time frame and compounding. CAGR accounts for compounding growth over multiple periods, while AGR is a snapshot of year-on-year growth. Babatope explained this with clear examples, ensuring everyone could see why CAGR is often the preferred metric in financial models.

The Formula

Now, onto the math. Babatope broke down the CAGR formula step-by-step:

Babatope showed how to derive this formula, making it easy for even the most math-averse among us to follow.

Demo Time: Excel in Action

Next came the practical part. Babatope guided us through various methods to calculate CAGR in Excel:

1. Average Annual Growth Rate: Using simple averages of year-on-year growth rates.

2. Formula Method: Applying the CAGR formula directly in Excel.

3. RRI Function: A lesser-known Excel function that simplifies CAGR calculation.

4. Power Function: For those who love a good math trick.

5. Rate Function: Another built-in Excel function that's surprisingly handy.

Babatope’s demos were live, interactive, and included downloadable exercise files so participants could follow along. He emphasized avoiding manual entry mistakes by using smart Excel functions, ensuring accuracy in our financial models.

Pros and Cons for using CAGR

CAGR isn’t just a fancy acronym; it’s a powerful tool. Babatope shared real-world applications, from measuring investment returns to estimating company growth and market size. These are some advantages of using CAGR:

• Easy to Calculate: CAGR is straightforward to compute using basic formulas or built-in functions in Excel.
• Reflects Compound Growth: It considers the effects of compounding, providing a more accurate picture of growth over time compared to simple annual growth rates.
• Useful for Long-Term Analysis: It is particularly useful for measuring the return on investment or growth rate over multiple years, making it ideal for long-term financial analysis.
• Average Annual Growth Rate: CAGR gives an average growth rate per year over a specified period, smoothing out the volatility and providing a single rate of return.

However, he also pointed out its limitations, like smoothing over period irregularities and assuming constant growth, which may not always reflect reality. Some of its limitations include:

• Smooths Out Growth: While CAGR provides a smooth average rate, it can mask year-to-year volatility, making it appear as though growth was steady when it may not have been.
• Assumes Constant Growth: CAGR assumes that growth occurred at a constant rate, which is often not the case in real-world scenarios where growth can fluctuate significantly.
• Ignores External Factors: It does not account for irregularities or external factors that may have affected growth during the period, such as economic downturns, market disruptions, or other anomalies.
• Only Two Extremes: CAGR only considers the beginning and ending values, ignoring the data points in between. This can lead to a misleading representation of actual growth patterns.

 Q&A: Real-time Problem Solving

The session wrapped up with a lively Q&A. Participants had their specific questions answered, from clarifications on the formulas to discussions on advanced Excel functions. Babatope’s expertise shone through, offering practical solutions and advice.

Closing Thoughts

Adewunmi closed the webinar with information on upcoming training sessions, including the Financial Modelling Academy’s next cohort and specialized courses like project finance modeling. For those looking to delve deeper, the webinar was just the beginning of their learning journey.