# Shantanu's Blog

Corporate Consultant

## Compound Annual growth Rate (CAGR)

You can use the IRR function to calculate the internal rate of return for a series of values. But the XIRR function is used to determine the internal rate of return when there are a number of irregular payments associated with an investment. The XIRR function is provided as part of the Analysis ToolPak add-in. If you put money into your portfolio, and take some out, then in and out etc. etc. then we're NOT talking about a buy-and-hold return. That makes the calculation of Annualized return more complicated, but there's an MS Excel command for this:
The XIRR command. It goes like this:

=XIRR(A1:A50,B1:B50,0.10)

where the range A1:A50 contains the cash flows
and the range B1:B50 contains the dates
and 0.10 is an initial guess (like 0.10 meaning 10%)
_____

The compound annual growth rate (CAGR) is computed by the formula:
[(Pv / P0) (1/n)]-1
where Pv = Present value
P0 = Beginning value
n = Number of periods
The result is multiplied by 100 to obtain a percentage.

CAGR, is the year over year growth rate applied to an investment
or other part of a activity over a multiple-year period.
The formula for calculating CAGR using a spreadsheet is
=(Current Value/Base Value)^(1/# of years) - 1
To change the number into a percentage, multiplying by 100.

current value _____ 100
base value _______ 80
number of years ___ 10
Excel formula_____ =((B1/B2)^(1/B3)-1)
Result: __________ 0.023
To change to a percent
=B4*100
Result: 2.26

Thanks, I was looking for the CAGR formula!

<< Home