CODEWITHSUNDEEP
excelexcel-formulapivot-tableexcel-2013vba
cona
cona

Reputation: 189

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

Upvotes: 0

Views: 1896

Answers (2)

cona
cona

Reputation: 189

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.

Upvotes: 0

pnuts
pnuts

Reputation: 59475

Could any of the following be potential solutions?

Yes.

Are there others?

Yes.

I would have to manually code this for every company

No, given a suitable data layout.

"from 2014 to the latest available year" seems to make Problem #1 not a problem.

Upvotes: 1

Related Questions