Contents
What is CAGR?
CAGR is the measure of an investment’s growth over time. CAGR includes compound interest and is thus superior to average annual returns.
Compound annual growth rate (CAGR) is the return required to grow an investment’s beginning value to its future value over a given number of years.
There are four methods to calculate CAGR for a portfolio.
- CAGR formula
- RRI Excel function
- RATE Excel function
- GEOMEAN Excel function
Example Portfolio Returns
We will show an example of each method using the following Sample Portfolio. Assume the following portfolio for five years. The beginning investment was $1,000.
| Sample Portfolio | Balance | Annual Return |
|---|---|---|
| Beginning Investment | $1,000 | – |
| Year 1 | $ 900 | -10.0% |
| Year 2 | $1,100 | 22.2% |
| Year 3 | $1,400 | 27.3% |
| Year 4 | $1,200 | -14.3% |
| Year 5 | $1,600 | 33.3% |
Note the average annual return for the sample portfolio is 11.7%. This a simple mean of adding the returns and dividing by five. Average annual return is not the correct way to measure the portfolio return.
1. CAGR Formula
The first way to calculate compound annual growth rate is with the formula. The CAGR formula is:
- CAGR = compound annual growth rate
- Future Value = future value, or the ending value
- Present Value = present value or the beginning value
- n = number of years
Using the sample portfolio numbers, the CAGR would be the following:
- FV = $1,600
- PV = $1,000
- n = 5
The sample portfolio CAGR is 9.86%.
Using Excel to solve compound annual growth rate
The following three methods use Excel to calculate the compound annual growth rate for a portfolio. These are better methods than using the formula. Excel has many financial functions that calculate annual returns including RRI, RATE, and GEOMEAN functions.
For more information on Excel financial functions, see Time Value of Money Using Excel Financial Functions.
2. RRI Excel function
The second way to calculate CAGR is using the RRI Excel function. The RRI function returns an equivalent interest rate for the growth of an investment. It only requires a beginning investment, an ending investment, and the number of periods.
=RRI(nper,pv,fv)
nper – number of periods for the investment
pv – present value of the investment
fv – future value of the investment
nper – 5
pv – $1,000
fv – $1,600
Note: on the RRI function, present value should be a positive number.
The results of the RRI function should equal the return using the CAGR formula of 9.86%.
3. RATE Excel function
The third way to calculate CAGR is using the Excel RATE function. RATE is a financial function that returns the interest rate per period of an annuity. You can use RATE to calculate the periodic interest rate, then multiply as required to derive the annual interest rate. The RATE function calculates by iteration.
=RATE (nper, pmt, pv, [fv], [type], [guess])
- nper – number of periods
- pmt – The payment made each period.
- pv – The present value, or total value of all loan payments now.
- fv – [optional] The future value, or desired cash balance after last payment. Default is 0.
- type – [optional] When payments are due. 0 = end of period. 1 = beginning of period. Default is 0.
- guess – [optional] Your guess on the rate. Default is 10%.
For this problem:
- nper – 5
- pmt – 0
- pv – -$1,000 (negative)
- fv – $1,600
- type – 0
- guess – 0
Note: on the RATE function, present value should be a negative number. See the example, C3 is the present value and it is negative in the RATE function.
The results for RATE should equal the return using the formula of 9.86%.
4. GEOMEAN Excel function
The fourth way to calculate the CAGR is to the GEOMEAN function. The GEOMEAN is the geometric mean. To calculate the geometric mean, you need the list of annual returns. Based on the sample portfolio returns we need:
- -10.0%
- 22.2%
- 27.3%
- -14.3%
- 33.3%
The GEOMEAN requires positive numbers only so we need Annual Returns + 1. See the example below:
The GEOMEAN function gives us the same return of 9.86% that we calculated before.
Calculate CAGR Tutorial Video
The following video shows how to calculate compound annual growth rate using the formula, the RRI function, and the RATE function.
CAGR Excel File
Here is the file that we created in the video.
CAGR Calculator
Use the following CAGR Calculator to quickly solve all compound annual growth rate problems. You only need present value, future value, and number of years.
For the sample portfolio, the present value is $1,000, the future value is $1,600, the years are 5. Enter the present value as positive. The CAGR Calculator gives the same return as before of 9.86%.
Jeff Mankin teaches financial literacy and Excel. He is the founder of Finally Learn.
