3 ways to calculate CAGR in Microsoft Excel

You’ve come across the term CAGR and want to know how to calculate it in Excel? This post gives you three different ways to do so in Microsoft Excel. But, first, let’s understand what CAGR is. Skip ahead if you’re already familiar with this or scroll down to the end of the post to get hold of the Excel file with the calculations.

What is CAGR?

Compound annual growth rate or CAGR, as it is more lovingly known as, is simply the rate at which something grows over a period of years taking into account the effect of compouding. Have I lost you?

Let’s understand this with a simple example which we’ll also use in this tutorial. The GDP of India in 2003 was Rs. 30,058 billion and in 2012 this grew to Rs. 58,137 billion. Without going into the calculations, the CAGR over 2003 to 2012 is 7.6%. This means that if the GDP grew at 7.6% every year from 2003, then in 2012 the GDP would be Rs. 58,137 billion.

It is important to note that CAGR is an imaginary term. It serves purely as an indicator that tells you what a number (in this case GDP) has grown over a period of time assuming that it grew at a steady rate. In reality the actual growth rates are likely to be different for each year.

If you’re looking to understand CAGR in much greater detail, I recommend reading Investopedia’s article Compound Annual Growth Rate: What You Should Know .

CAGR formula

Before we dive into Excel, let’s understand the how calculate the compound annual growth rate. The formula is:

CAGR = (Ending value / Beginning value)^(1/n) - 1

where n is the number of years

Calculating CAGR in Excel

Method 1: The direct way

This method needs no major explanation. We can use the formula above to calculate the CAGR. In our example, it looks something like this:

=(O$5/F$5)^(1/9)-1

where:

  • O$5 = Ending value
  • F$5 = Beginning value

Method 2: The POWERful way

Instead of plugging in the formula manually, you can also use the POWER function in Excel to calculate the CAGR. This does make the formula look slightly cleaner, although I don’t usually prefer this method. In our example, it looks something like this:

=POWER(O$5/F$5,1/9)-1

This function replaces the use of ^ in Method 1. If you’re not familiar with POWER function, check out the help article on POWER function on the Microsoft site .

Method 3: Using RATE to get the rate

Another much lesser used method but very handy and clean way to calculate the CAGR is by using the RATE function. This is the method I prefer and usually tend to use when calculating the CAGR. Although the syntax for RATE looks rather complicated, you only need to bother about three of them viz. nper, pv and fv.

RATE(nper, pmt, pv, [fv], [type], [guess])

where:

  • Nper. Required. The total number of payment periods in an annuity.
  • Pmt. Required. The payment made each period and cannot change over the life of the annuity. Typically, pmt includes principal and interest but no other fees or taxes. If pmt is omitted, you must include the fv argument.
  • Pv. Required. The present value — the total amount that a series of future payments is worth now.
  • Fv. Optional. The future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (the future value of a loan, for example, is 0).
  • Type. Optional. The number 0 or 1 and indicates when payments are due.

To calculate the CAGR in our example, the formula will be:

=RATE(9,,-F$5,O$5)

Surprised at how clean this looks? Remember to put the Beginning value as negative number or else the formula will give a #NUM! error.

So, which of the three methods do you prefer? Or do you have a different way to calculate the CAGR?

Download the Excel file with examples

Source: techtites.com

Category: Bank

Similar articles: