A compound annual growth rate (CAGR) measures the rate of return for an investment — such as a mutual fund or bond — over an investment period, such as 5 or 10 years. The CAGR is also called a "smoothed" rate of return because it measures the growth of an investment as if it had grown at a steady rate on an annually compounded basis. To calculate a CAGR, use the XIRR function.
If the XIRR function is not available and returns the #NAME? error, install and load the Analysis ToolPak add-in.
Example
The example may be easier to understand if you copy it to a blank worksheet.
|
|
|
A
|
B
|
|
Values
|
Dates
|
|
-10,000
|
January 1, 2008
|
|
2,750
|
March 1, 2008
|
|
4,250
|
October 30, 2008
|
|
3,250
|
February 15, 2009
|
|
2,750
|
April 1, 2009
|
|
Formula
|
Descripttion (Result)
|
|
=XIRR(A2:A6,B2:B6)
|
The compound annual growth rate (0.373362535 or 37.34%)
|
|
Note:
-
When you compare the CAGRs of different investments, make sure that each rate is calculated over the same investment period.
-
To view the number as a percentage, select the cell and then click Cells on theFormat menu. Click the Number tab, and then click Percentage in the Categorybox.
Explanation of XIRR function
Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic. To calculate the internal rate of return for a series of periodic cash flows, use the IRR function.
Syntax
XIRR(values,dates,guess)
Values is a series of cash flows that corresponds to a schedule of payments in dates. The first payment is optional and corresponds to a cost or payment that occurs at the beginning of the investment. If the first value is a cost or payment, it must be a negative value. All succeeding payments are discounted based on a 365-day year. The series of values must contain at least one positive and one negative value.
Dates is a schedule of payment dates that corresponds to the cash flow payments. The first payment date indicates the beginning of the schedule of payments. All other dates must be later than this date, but they may occur in any order. Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text.
Guess is a number that you guess is close to the result of XIRR.
If this function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in.
-
On the Tools menu, click Add-Ins.
-
In the Add-Ins available list, select the Analysis ToolPak box, and then clickOK.
-
If necessary, follow the instructions in the setup program.
