Calculating 5-year compound annual growth rate (CAGR) with missing values

Hi All,

I'm trying to calculate the 5-year compound annual growth rate, which is "(endvalue/beginning value)^(1/5) - 1", of a variable for different countries and for each year. Some countries have missing values for one or several years.

The growth rate I've calculated are all missing values, which should not be. I can't figure out where's the problem...

The data I use:

Code:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str3 CountryCode str22 Country int Year double var1
"AFG" "Afghanistan" 2000  .
"AFG" "Afghanistan" 2001 20
"AFG" "Afghanistan" 2002 16
"AFG" "Afghanistan" 2003  4
"AFG" "Afghanistan" 2004 10
"AFG" "Afghanistan" 2005 28
"AFG" "Afghanistan" 2006 16
"AFG" "Afghanistan" 2007 29
"AFG" "Afghanistan" 2008 27
"AFG" "Afghanistan" 2009 32
"AFG" "Afghanistan" 2010 14
"AFG" "Afghanistan" 2011 13
"AFG" "Afghanistan" 2012 32
"AFG" "Afghanistan" 2013 27
"AFG" "Afghanistan" 2014 10
"AFG" "Afghanistan" 2015 32
"AFG" "Afghanistan" 2016 31
"AFG" "Afghanistan" 2017  .
"AGO" "Angola"      2000 15
"AGO" "Angola"      2001  7
"AGO" "Angola"      2002  .
"AGO" "Angola"      2003  .
"AGO" "Angola"      2004  .
"AGO" "Angola"      2005  .
"AGO" "Angola"      2006  .
"AGO" "Angola"      2007  .
"AGO" "Angola"      2008  .
"AGO" "Angola"      2009  .
"AGO" "Angola"      2010  .
"AGO" "Angola"      2011  .
"AGO" "Angola"      2012  .
"AGO" "Angola"      2013  .
"AGO" "Angola"      2014  .
"AGO" "Angola"      2015  .
"AGO" "Angola"      2016  .
"AGO" "Angola"      2017  .
"ALB" "Albania"     2000  1
"ALB" "Albania"     2001 15
"ALB" "Albania"     2002  .
"ALB" "Albania"     2003  .
"ALB" "Albania"     2004  .
"ALB" "Albania"     2005  .
"ALB" "Albania"     2006  .
"ALB" "Albania"     2007  .
"ALB" "Albania"     2008  .
"ALB" "Albania"     2009  5
"ALB" "Albania"     2010 15
"ALB" "Albania"     2011 27
"ALB" "Albania"     2012  .
"ALB" "Albania"     2013  .
"ALB" "Albania"     2014  .
"ALB" "Albania"     2015  .
"ALB" "Albania"     2016  .
"ALB" "Albania"     2017  .
"ARG" "Argentina"   2000  .
"ARG" "Argentina"   2001  .
"ARG" "Argentina"   2002  .
"ARG" "Argentina"   2003  .
"ARG" "Argentina"   2004  .
"ARG" "Argentina"   2005  .
"ARG" "Argentina"   2006 10
"ARG" "Argentina"   2007  .
"ARG" "Argentina"   2008  .
"ARG" "Argentina"   2009  .
"ARG" "Argentina"   2010  .
"ARG" "Argentina"   2011  .
"ARG" "Argentina"   2012 18
"ARG" "Argentina"   2013  .
"ARG" "Argentina"   2014  .
"ARG" "Argentina"   2015  .
"ARG" "Argentina"   2016  .
"ARG" "Argentina"   2017  .
"ARM" "Armenia"     2000 33
"ARM" "Armenia"     2001  7
"ARM" "Armenia"     2002 21
"ARM" "Armenia"     2003 23
"ARM" "Armenia"     2004  8
"ARM" "Armenia"     2005  3
"ARM" "Armenia"     2006  9
"ARM" "Armenia"     2007  5
"ARM" "Armenia"     2008  4
"ARM" "Armenia"     2009 23
"ARM" "Armenia"     2010 16
"ARM" "Armenia"     2011 28
"ARM" "Armenia"     2012 21
"ARM" "Armenia"     2013 26
"ARM" "Armenia"     2014 10
"ARM" "Armenia"     2015 20
"ARM" "Armenia"     2016 10
"ARM" "Armenia"     2017 34
"AUS" "Australia"   2000 34
"AUS" "Australia"   2001 17
"AUS" "Australia"   2002 16
"AUS" "Australia"   2003 16
"AUS" "Australia"   2004 12
"AUS" "Australia"   2005  2
"AUS" "Australia"   2006 14
"AUS" "Australia"   2007  6
"AUS" "Australia"   2008 31
"AUS" "Australia"   2009  2
end

The code I use:

Code:

encode CountryCode, gen(ccode)
xtset ccode Year
order ccode, first
bysort ccode Year: gen var1_5CAGR = ((var1/L5.var1)^(1/5)) - 1

The results I got:

Code:

* Example generated by -dataex-. To install: ssc install dataex
clear
input long ccode str3 CountryCode str22 Country int Year double var1 float var1_5CAGR
1 "AFG" "Afghanistan" 2000  . .
1 "AFG" "Afghanistan" 2001 20 .
1 "AFG" "Afghanistan" 2002 16 .
1 "AFG" "Afghanistan" 2003  4 .
1 "AFG" "Afghanistan" 2004 10 .
1 "AFG" "Afghanistan" 2005 28 .
1 "AFG" "Afghanistan" 2006 16 .
1 "AFG" "Afghanistan" 2007 29 .
1 "AFG" "Afghanistan" 2008 27 .
1 "AFG" "Afghanistan" 2009 32 .
1 "AFG" "Afghanistan" 2010 14 .
1 "AFG" "Afghanistan" 2011 13 .
1 "AFG" "Afghanistan" 2012 32 .
1 "AFG" "Afghanistan" 2013 27 .
1 "AFG" "Afghanistan" 2014 10 .
1 "AFG" "Afghanistan" 2015 32 .
1 "AFG" "Afghanistan" 2016 31 .
1 "AFG" "Afghanistan" 2017  . .
2 "AGO" "Angola"      2000 15 .
2 "AGO" "Angola"      2001  7 .
2 "AGO" "Angola"      2002  . .
2 "AGO" "Angola"      2003  . .
2 "AGO" "Angola"      2004  . .
2 "AGO" "Angola"      2005  . .
2 "AGO" "Angola"      2006  . .
2 "AGO" "Angola"      2007  . .
2 "AGO" "Angola"      2008  . .
2 "AGO" "Angola"      2009  . .
2 "AGO" "Angola"      2010  . .
2 "AGO" "Angola"      2011  . .
2 "AGO" "Angola"      2012  . .
2 "AGO" "Angola"      2013  . .
2 "AGO" "Angola"      2014  . .
2 "AGO" "Angola"      2015  . .
2 "AGO" "Angola"      2016  . .
2 "AGO" "Angola"      2017  . .
3 "ALB" "Albania"     2000  1 .
3 "ALB" "Albania"     2001 15 .
3 "ALB" "Albania"     2002  . .
3 "ALB" "Albania"     2003  . .
3 "ALB" "Albania"     2004  . .
3 "ALB" "Albania"     2005  . .
3 "ALB" "Albania"     2006  . .
3 "ALB" "Albania"     2007  . .
3 "ALB" "Albania"     2008  . .
3 "ALB" "Albania"     2009  5 .
3 "ALB" "Albania"     2010 15 .
3 "ALB" "Albania"     2011 27 .
3 "ALB" "Albania"     2012  . .
3 "ALB" "Albania"     2013  . .
3 "ALB" "Albania"     2014  . .
3 "ALB" "Albania"     2015  . .
3 "ALB" "Albania"     2016  . .
3 "ALB" "Albania"     2017  . .
4 "ARG" "Argentina"   2000  . .
4 "ARG" "Argentina"   2001  . .
4 "ARG" "Argentina"   2002  . .
4 "ARG" "Argentina"   2003  . .
4 "ARG" "Argentina"   2004  . .
4 "ARG" "Argentina"   2005  . .
4 "ARG" "Argentina"   2006 10 .
4 "ARG" "Argentina"   2007  . .
4 "ARG" "Argentina"   2008  . .
4 "ARG" "Argentina"   2009  . .
4 "ARG" "Argentina"   2010  . .
4 "ARG" "Argentina"   2011  . .
4 "ARG" "Argentina"   2012 18 .
4 "ARG" "Argentina"   2013  . .
4 "ARG" "Argentina"   2014  . .
4 "ARG" "Argentina"   2015  . .
4 "ARG" "Argentina"   2016  . .
4 "ARG" "Argentina"   2017  . .
5 "ARM" "Armenia"     2000 33 .
5 "ARM" "Armenia"     2001  7 .
5 "ARM" "Armenia"     2002 21 .
5 "ARM" "Armenia"     2003 23 .
5 "ARM" "Armenia"     2004  8 .
5 "ARM" "Armenia"     2005  3 .
5 "ARM" "Armenia"     2006  9 .
5 "ARM" "Armenia"     2007  5 .
5 "ARM" "Armenia"     2008  4 .
5 "ARM" "Armenia"     2009 23 .
5 "ARM" "Armenia"     2010 16 .
5 "ARM" "Armenia"     2011 28 .
5 "ARM" "Armenia"     2012 21 .
5 "ARM" "Armenia"     2013 26 .
5 "ARM" "Armenia"     2014 10 .
5 "ARM" "Armenia"     2015 20 .
5 "ARM" "Armenia"     2016 10 .
5 "ARM" "Armenia"     2017 34 .
6 "AUS" "Australia"   2000 34 .
6 "AUS" "Australia"   2001 17 .
6 "AUS" "Australia"   2002 16 .
6 "AUS" "Australia"   2003 16 .
6 "AUS" "Australia"   2004 12 .
6 "AUS" "Australia"   2005  2 .
6 "AUS" "Australia"   2006 14 .
6 "AUS" "Australia"   2007  6 .
6 "AUS" "Australia"   2008 31 .
6 "AUS" "Australia"   2009  2 .
end
label values ccode ccode
label def ccode 1 "AFG", modify
label def ccode 2 "AGO", modify
label def ccode 3 "ALB", modify
label def ccode 4 "ARG", modify
label def ccode 5 "ARM", modify
label def ccode 6 "AUS", modify

Any help is appreciated!

Many thanks,
Craig

BJ Data Tech Solution

Home / Data Cleaning / Data management / Data Processing / Calculating 5-year compound annual growth rate (CAGR) with missing values
Calculating 5-year compound annual growth rate (CAGR) with missing values

0 Response to Calculating 5-year compound annual growth rate (CAGR) with missing values

Post a Comment

Home / Data Cleaning / Data management / Data Processing / Calculating 5-year compound annual growth rate (CAGR) with missing values Calculating 5-year compound annual growth rate (CAGR) with missing values

Related Posts with Calculating 5-year compound annual growth rate (CAGR) with missing values

0 Response to Calculating 5-year compound annual growth rate (CAGR) with missing values

Post a Comment

Home / Data Cleaning / Data management / Data Processing / Calculating 5-year compound annual growth rate (CAGR) with missing values
Calculating 5-year compound annual growth rate (CAGR) with missing values