CAGR Formula in Excel: Calculate Your Rate of Return Easily and Automatically

One thing you quickly come to understand about Excel, as you learn the program, is that it is great for taking the legwork out of calculations you need to complete regularly. One great example to highlight this capability is calculating a compound annual growth rate (CAGR). Excel offers several options for automating the calculation of a CAGR, depending on the information you have available to calculate it.

You can learn about the many features Excel offers to simplify calculations like CAGR in a course covering Excel functions and formulas. But for now, let’s take a closer look at this formula specifically and the options for finding a result in Excel.

Understanding CAGR

If you are tracking an investment, whether for your personal finances or for professional purposes, you may already be familiar with the concept of a compound annual growth rate (CAGR).  This mathematical formula calculates a year-over-year “smoothed” rate of return for an investment.

Ultimately, this is to say that the result of the CAGR does not describe the reality of the return you achieved because it assumes annual compounding. It does, however, give you a good understanding of how your investment has performed year-over-year.  The method for calculating CAGR is as follows:

You may be thinking that looks simple enough to just complete with a standard calculator. If it doesn’t look familiar, you may be interested to get some training on this type of formula in a course on financial modeling. But either way, you can use Excel to set up this model for auto-calculation and save yourself some keystrokes.  If you are tracking a number of investments, or including CAGR among a range of other calculations you need to complete, this may be a much needed solution.

Additionally, we sometimes deal with investments that do not just sit and accrue interest. Instead we have a picture of cash flow with multiple transactions, which complicates the CAGR calculation.  In that case, Excel also has options to make life easier.

Let’s jump in and take a look at how it’s done.

A Simple Excel Formula for CAGR

You don’t necessarily need to use functions or any advanced features of Excel to set up your CAGR calculations. Instead, you can take a straightforward approach using formulas and cell references. As an example, take a look at the following spreadsheet.

This table shows the life of an investment over a four year period. This is assumed to continuously show the investment at the beginning of the period indicated. So at the first year, for instance, we have the principal investment. At the same time for the second year, we have the total with the interest accrued over the first year, and so on. The numbers used in this method should represent the investment at roughly the same time for each annual period.

Now, if you take a look at the formula, it should seem familiar. It applies the CAGR template depicted above and uses cell references for the numbers you need to plug in. Notice that the second reference to cell C2 uses a static reference for row 2 (indicated by $). That means that when the formula is copied down to new rows, it will continue to show the CAGR from the principal investment. In other words, the .119 or 11.9% rate of return shown in cell D9 represents the year-over-year CAGR over the entire four years of growth.

Note that, with this method, you will complete the formula as it is written in the first cell and copy it down to the others.  If this process is unfamiliar to you, you might want to brush up your general understanding of Excel with a course covering Excel essentials.

A More Flexible Option Using the XIRR function

In the real world, you might be trying to calculate CAGR from a more complex list of transactions, which are not necessarily periodic.  And in this case, you will be better served using Excel’s XIRR function to get your result. Consider the following example:

Here, we have records that track an investment from its principal value to a present value, following a number of transactions. For instance, you see that on March 13, 2012, this investment was partially liquidated, with a $450 withdrawal. Then a deposit of $300 was made to the fund about 7 months later.

In addition to these complicating factors, we are assuming here that we do not have the value as of early January 2013, which we would need to apply the basic CAGR formula. Instead we have a value as of April 13th.

So, in this case, you want to apply the XIRR function to calculate the internal rate of return, which will factor in the cash flow and return your CAGR.

In the example above, you can see the XIRR formula completed in cell F6. The format is simple, with a template that progresses as follows:

=XIRR(Transcactions, Dates)

You can also insert, as a third parameter, a guess regarding the outcome, but this is not required. For both the transactions and dates, note that you will be referencing the complete applicable range on the spreadsheet.

Ultimately, you should see that the XIRR function offers you a very helpful and straightforward way to find your rate of return even when you are working with a list of transactions that complicates using a mathematical formula. For that reason, functions like this one are very popular in business and used frequently by entrepreneurs and consultants to develop and display quick financial models when they need them. If you are interested in learning how to tap in to Excel for similar purposes, you may be interested to take an Excel hacking course that a group of consultants and entrepreneurs developed for real-world problem solving application.

Summary

As you continue working with Excel, you will open many doors that help you solve problems more quickly and easily than you might have imagined. Hopefully, this discussion of CAGR in Excel gives you an idea of the program’s capabilities and moves you down that path. Happy number crunching!