Search code examples
excelexcel-formulapivot-tableexcel-2013vba

Automate compound annual growth rate (CAGR) calculation

I have a list of firms' revenue and employment (historical and forecast). I want to calculate the compound annual growth rate (CAGR) from 2014 to the latest available year. I used the following formula,

=(C17/C12)^(1/COUNT(C13:C17))-1

While the formula works, it is not extensible. Meaning, I would have to manually code this for every company (there are thousands) and for every attribute beyond revenue and employment (there are many others). In addition, some firms have different end years, i.e., some firms' forecast data ends with year 2017, some with 2018, etc...

Could any of the following be potential solutions? Are there others?

  1. VBA
  2. PivotTable
  3. PowerPivot
Solution

  • I created a PowerPivot table. The following PowerPivot DAX formulas worked for me,

    first year revenue = CALCULATE(SUM(source[revenue]),source[year]=2014)

last year revenue = CALCULATE(sum(source[revenue]),filter(source,source[year]=max(source[year])))

Forecast Revenue CAGR = ([last year revenue]/[first year revenue]) ^(1/(max([year])-2014))-1

    These expressions are extensible, i.e., they carry for every company in the sheet.