Calculating Compound Annual Growth Rate

Compound annual growth rate (CAGR) is an investing and business term for the effective constant year-over-year rate of return that produces a target result value at the end of multiple years, assuming that the CAGR is compounded at the end of each year. For example, you might use CAGR to compare trends over multiple years in revenue or in the number of units sold. For SAS Visual Analytics, you can calculate the yearly CAGR using the expression builder.
This example compares trends in the growth rate for yearly sales amounts between different product types or regions.
The basic data items are:
sales
This is a numeric measure with a currency format and a default aggregation of Sum.
ProductType
This is a string category data item.
RegionName
This is a string category data item.
TransactionDate
This is a date data item with a format of Month, Day, Year (MMDDYYYY).
The duplicate data item is:
TransactionDateYear
This data item is a duplicate of the TransactionDate data item, but with the Year format.
The calculated data items are:
BeginningYearNum
This data item should be a numeric type with a Float4.0 format and an aggregation of Minimum.
BeginningYearNum = Year('31DEC2010'd)
EndingYearNum
This data item should be a numeric type with a Float4.0 format and an aggregation of Minimum.
EndingYearNum = Year(‘transactionDate’n)
The aggregated measure data items are:
NumYears
This data item has a Float4.0 format.
NumYears = Min [_ByGroup_] ('EndingYearNum'n) - Min [_ByGroup_] ('BeginningYearNum'n)
BeginningValue
This data item needs to be set to the same currency format as the sales data item.
BeginningValue = PeriodWithDate(_Sum_, 'sales'n, 'transactionDate'n, _ByYear_, '31DEC2010'd)
EndingValue
This data item needs to be set to the same currency format as the sales data item.
EndingValue = Period(_Sum_, 'sales'n, 'transactionDate'n, _ByYear_)
NormalizedRatio
This data item has a Float12.2 format.
NormalizedRatio = 'EndingValue'n / 'BeginningValue'n
CAGR
This data item has a Percent format.
CAGR = ('NormalizedRatio'n Power ( 1 / 'NumYears'n ) ) – 1
To use the CAGR, you should add TransactionDateYear, CAGR, and any other categories of interest (for example, RegionName, ProductType, and so on) to a list table, a crosstab, or a graph.