I have mentioned the formula of it along with attached a paper using the methodology and a sample dataset in MS Excel format. I have to obtain the Compound Growth Rate of area in pulses in 2014 starting from 2000 (file PULSES.xlsx).
Compound Annual Growth Rate (CAGR)
The following functional form is used to estimate the
growth in area, production and productivity: Yt = Yo (1 +r)t
Transforming this to logarithmic form,
In Yt = In Yo + t In (1+r).
Here, Yt is the variable for which growth is calculated,
r is the compound growth rate and In is the natural
logarithm.
Now, let In Yo = b1and In (1+r) =b2
Therefore, the above equation becomes, In Yt = b1 + b2 t
Now, b1 and b2 are estimated by Ordinary Least Square (OLS)
method and the CAGR is given by, r = (antilog b2 -1) × 100 ............. (Sendhil, 2012, p.355).
cite: Sendhil, R. (2012). Production and export performance of onions–An exploratory study. Agric. Sitn. Ind, 69(7), 355-362.
Hi there,
You might find following resources helpful to calculate what you are after:
https://www.extendoffice.com/documents/excel/2596-excel-average-compound-growth-rate.html
https://support.office.com/en-us/article/Calculate-a-compound-annual-growth-rate-CAGR-3ccb7cd3-39b3-49ee-8b38-c19972607dfa
http://www.investopedia.com/ask/answers/071014/what-formula-calculating-compound-annual-growth-rate-cagr-excel.asp
cheers,
Hi Suman,
Not sure what you mean by calculating CAGR using Excel if you are estimating the growth rate using regression analysis, but ...
Your data shows 49,060 hectares in 2000, and 21,024 hectares in 2014. That's a decline of approximately 5.9% per year. You have written out the formula already. One way to calculate would be to input the formula into Excel using a starting value for r, and then using the Goal Seek function (Data --> What if Analysis --> Goal Seek) to find the value of r that makes the value of "hectares in 2014" equal to 21,024.
You might find the resources suggested by the previous respondent useful as well.
Good luck!
- Badges
- Science topic
- Similar topics
- Engineering
- Modeling