My ultimate goal is to calculate money from previous years as 2019 dollars.
I've got these numbers from the BLS, and create the CPI_U in sql like so:
create table CPI_U (year int, dec decimal(4,2), annual_avg decimal(4,2))
insert into CPI_U (year, dec, annual_avg)
values
(2000, 3.4,3.4),
(2001, 1.6,2.8),
(2002, 2.4,1.6),
(2003, 1.9,2.3),
(2004, 3.3,2.7),
(2005, 3.4,3.4),
(2006, 2.5,3.2),
(2007, 4.1,2.8),
(2008, 0.1,3.8),
(2009, 2.7,-0.4),
(2010, 1.5,1.6),
(2011, 3.0,3.2),
(2012, 1.7,2.1),
(2013, 1.5,1.5),
(2014, 0.8,1.6),
(2015, 0.7,0.1),
(2016, 2.1,1.3),
(2017, 2.1,2.1),
(2018, 1.9,2.4)
I am then building a triangle like so:
with cpi_triangle as (
select c1.year, c1.dec as c1, c2.dec as c2, c3.dec as c3, c4.dec as c4, c5.dec as c5,
c6.dec as c6, c7.dec as c7, c8.dec as c8, c9.dec as c9, c10.dec as c10,
c11.dec as c11, c12.dec as c12, c13.dec as c13, c14.dec as c14, c15.dec as c15,
c16.dec as c16, c17.dec as c17, c18.dec as c18, c19.dec as c19, c20.dec as c20
from cpi_u c1
left join cpi_u c2 on c1.year + 1 = c2.year
left join cpi_u c3 on c1.year + 2 = c3.year
left join cpi_u c4 on c1.year + 3 = c4.year
left join cpi_u c5 on c1.year + 4 = c5.year
left join cpi_u c6 on c1.year + 5 = c6.year
left join cpi_u c7 on c1.year + 6 = c7.year
left join cpi_u c8 on c1.year + 7 = c8.year
left join cpi_u c9 on c1.year + 8 = c9.year
left join cpi_u c10 on c1.year + 9 = c10.year
left join cpi_u c11 on c1.year + 10 = c11.year
left join cpi_u c12 on c1.year + 11 = c12.year
left join cpi_u c13 on c1.year + 12 = c13.year
left join cpi_u c14 on c1.year + 13 = c14.year
left join cpi_u c15 on c1.year + 14 = c15.year
left join cpi_u c16 on c1.year + 15 = c16.year
left join cpi_u c17 on c1.year + 16 = c17.year
left join cpi_u c18 on c1.year + 17 = c18.year
left join cpi_u c19 on c1.year + 18 = c19.year
left join cpi_u c20 on c1.year + 19 = c20.year)
select *,
1 * (1 + isnull(c1,0)/100)* (1 + isnull(c2,0)/100)* (1 + isnull(c3,0)/100)* (1 + isnull(c4,0)/100)* (1 + isnull(c5,0)/100)
* (1 + isnull(c6,0)/100)* (1 + isnull(c7,0)/100)* (1 + isnull(c8,0)/100)* (1 + isnull(c9,0)/100) * (1 + isnull(c10,0)/100)
* (1 + isnull(c11,0)/100)* (1 + isnull(c12,0)/100)* (1 + isnull(c13,0)/100)* (1 + isnull(c14,0)/100) * (1 + isnull(c15,0)/100)
* (1 + isnull(c16,0)/100)* (1 + isnull(c17,0)/100)* (1 + isnull(c18,0)/100)* (1 + isnull(c19,0)/100) * (1 + isnull(c20,0)/100) as adj_factor
from cpi_triangle
The triangle looks like this:
year c1 c2 c3 c4 c5 c6 c7 c8 c9 c10 c11 c12 c13 c14 c15 c16 c17 c18 c19 c20 adj_factor
2000 3.40 1.60 2.40 1.90 3.30 3.40 2.50 4.10 0.10 2.70 1.50 3.00 1.70 1.50 0.80 0.70 2.10 2.10 1.90 NULL 1.494493
2001 1.60 2.40 1.90 3.30 3.40 2.50 4.10 0.10 2.70 1.50 3.00 1.70 1.50 0.80 0.70 2.10 2.10 1.90 NULL NULL 1.445353
2002 2.40 1.90 3.30 3.40 2.50 4.10 0.10 2.70 1.50 3.00 1.70 1.50 0.80 0.70 2.10 2.10 1.90 NULL NULL NULL 1.422590
2003 1.90 3.30 3.40 2.50 4.10 0.10 2.70 1.50 3.00 1.70 1.50 0.80 0.70 2.10 2.10 1.90 NULL NULL NULL NULL 1.389250
2004 3.30 3.40 2.50 4.10 0.10 2.70 1.50 3.00 1.70 1.50 0.80 0.70 2.10 2.10 1.90 NULL NULL NULL NULL NULL 1.363346
2005 3.40 2.50 4.10 0.10 2.70 1.50 3.00 1.70 1.50 0.80 0.70 2.10 2.10 1.90 NULL NULL NULL NULL NULL NULL 1.319792
2006 2.50 4.10 0.10 2.70 1.50 3.00 1.70 1.50 0.80 0.70 2.10 2.10 1.90 NULL NULL NULL NULL NULL NULL NULL 1.276395
2007 4.10 0.10 2.70 1.50 3.00 1.70 1.50 0.80 0.70 2.10 2.10 1.90 NULL NULL NULL NULL NULL NULL NULL NULL 1.245263
2008 0.10 2.70 1.50 3.00 1.70 1.50 0.80 0.70 2.10 2.10 1.90 NULL NULL NULL NULL NULL NULL NULL NULL NULL 1.196218
2009 2.70 1.50 3.00 1.70 1.50 0.80 0.70 2.10 2.10 1.90 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 1.195023
2010 1.50 3.00 1.70 1.50 0.80 0.70 2.10 2.10 1.90 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 1.163606
2011 3.00 1.70 1.50 0.80 0.70 2.10 2.10 1.90 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 1.146410
2012 1.70 1.50 0.80 0.70 2.10 2.10 1.90 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 1.113019
2013 1.50 0.80 0.70 2.10 2.10 1.90 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 1.094414
2014 0.80 0.70 2.10 2.10 1.90 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 1.078241
2015 0.70 2.10 2.10 1.90 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 1.069683
2016 2.10 2.10 1.90 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 1.062247
2017 2.10 1.90 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 1.040399
2018 1.90 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 1.019000
Problem: I feel like this is really inelegant. A lot of smart people are going to see the final product and if they look at my methodology, I want them to think I'm smart, too.
Question: What do you think is the best method to calculate inflation year over year?
challenge mode: do it without the lag function (I don't have access to that function yet).
Desired Output:
year dec annual_avg adj_factor
2000 3.40 3.40 1.4944930
2001 1.60 2.80 1.4453530
2002 2.40 1.60 1.4225900
2003 1.90 2.30 1.3892500
2004 3.30 2.70 1.3633460
2005 3.40 3.40 1.3197920
2006 2.50 3.20 1.2763950
2007 4.10 2.80 1.2452630
2008 0.10 3.80 1.1962180
2009 2.70 -0.40 1.1950230
2010 1.50 1.60 1.1636060
2011 3.00 3.20 1.1464100
2012 1.70 2.10 1.1130190
2013 1.50 1.50 1.0944140
2014 0.80 1.60 1.0782410
2015 0.70 0.10 1.0696830
2016 2.10 1.30 1.0622470
2017 2.10 2.10 1.0403990
2018 1.90 2.40 1.0190000
Best Answer
This is actually pretty simple if you remember that adding logarithms of numbers is the same as multiplying numbers. Using this code:
I received this output:
However, these values come out differently than your list. So, I went to check things. It appears that your list is suffering from rounding error accumulation, and this list is actually more precise. I tested with a larger capacity value with this code:
With testing for 2000, 2001, and 2002, my output for the inflation adjustment was:
Based on these simple tests, it definitely appears that the EXP...LOG method is more precise than your current calculations.