CAGR Excel (Compound Annual Growth Rate)
Calculate CAGR using Excel
One of Excel’s most spectacular and useful features is the tool which it provides for calculating a CAGR. A CAGR measures the rate of return for an investment – such as a mutual fund or bond – over an investment period of say 5 or 10 years. The CAGR can also be referred to as a “smoothed” rate of return as it measures the growth of an investment as if it had grown at a steady rate on an annually compounded basis.
Related Items: CAGR Video Clip, IRR in Excel, IRR Calculator, NPV in Excel
To calculate a CAGR using Excel, one should use the XIRR function.
The XIRR Function Details
XIRR returns the internal rate of return for a schedule of cash flows that is not necessarily periodic. The XIRR function is composed of the main syntax namely values, dates and guess, and these are to be discussed as follows:
- Values: Is a series of cash flows which corresponds to a schedule of payments in dates. The first payment is optional, corresponding to a cost/payment occurring at the start of an investment. As a cost/payment, the first value has to be a negative; as the series of all values must contain at least one positive and one negative value. All succeeded payments are discounted on a 365-day basis.
- Dates: Is a schedule of payment dates which correspond to the cash flow payments. The first payment date indicates the beginning of the schedule of payments. All other dates must in obvious terms be later than this date, but may occur in any order. Enter dates by using the Date function, or as results of other formulas or functions.
- Guess: Is a number which you guess to be close to the result of the XIRR.
XIRR Function Notes
When using the XIRR function of Excel, as a user you will need to pay careful attention, noting the following:
- Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900. Microsoft Excel for the Macintosh uses a different date system as its default.
- Numbers in dates are truncated to integers.
- XIRR expects at least one positive cash flow and one negative cash flow; otherwise, XIRR returns the #NUM! error value.
- If any number in dates is not a valid date, XIRR returns the #VALUE! error value.
- If any number in dates precedes the starting date, XIRR returns the #NUM! error value.
- If values and dates contain a different number of values, XIRR returns the #NUM! error value.
- In most cases you do not need to provide guess for the XIRR calculation. If omitted, guess is assumed to be 0.1 (10 percent).
- XIRR is closely related to XNPV, the net present value function. The rate of return calculated by XIRR is the interest rate corresponding to XNPV = 0.
- Excel uses an iterative technique for calculating XIRR. Using a changing rate (starting with guess), XIRR cycles through the calculation until the result is accurate within 0.000001 percent. If XIRR can’t find a result that works after 100 tries, the #NUM! error value is returned.
- When comparing the CAGR’s of different investments, one should 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 the Format menu. Click the Number tab, and then click Percentage in the Category box.
With these amazing features and capabilities it is no surprise that the use of Excel to calculate CAGR is so popular among not only professionals but the home user as well. With the XIRR function, Excel can handle and accurately calculate any return of investment in terms of the compound annual growth rate. With the automatic CAGR function that Excel boasts, calculating large numbers is now as best as done. All you need to do is enter your values in the appropriate cells, and the sit back and relax while Excel does the rest.
Free Tips & Tricks Newsletter
Please fill in your details below to get regular Excel tips and tricks and a free Quick Reference Download!
Handy Tips & Tricks
AE News: Alumni Reward, spider charts, extract text from the last #-/, look for current month and year, training budgets
Alumni Reward, spider charts, extract text from the last #-/, look for current month and year, training budgets In this issue: LAST Advanced Excel course 2015 7-9 Dec Alumni reward In houses Courses– Use up your remaining training budget? Extract … Continue reading
Extract text from Excel cell after an instance of a character
Extract text from Excel cell AFTER an instance of a character If you need to extract a certain part of text, but the unique item is not consistent and possibly repeats itself you need to make a few adjustments to the … Continue reading
We have used AuditExcel over the last 5 years to develop large scale property modelling developments. All the templates are still in use and allow for intricate sensitivity analysis. As a result we are better able to assess projects and choose the optimal ones. Their response times also make it easy to rely on them for new developments.
– Property Modelling- Basil Read