Calculating Compound Annual Growth Rate :: SAS(R) Visual Analytics 7.3: User's Guide

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.