# What is the best way to calculate year over year inflation?

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
``````
• Could you give an example output please? Feb 14, 2019 at 16:21
• I added a desired output. the script above gives it as a triangle, too. Feb 14, 2019 at 16:26

This is actually pretty simple if you remember that adding logarithms of numbers is the same as multiplying numbers. Using this code:

``````SELECT [Year],
[Dec],
[Annual_Avg],
CAST((SELECT EXP(SUM(LOG(1 + (dec/100)))) FROM ##CPI_U u WHERE u.year >= c.year) as decimal(9, 7)) adj_factor
FROM ##CPI_U c
``````

``````Year    Dec Annual_Avg  adj_factor
2000    3.40    3.40    1.4944944
2001    1.60    2.80    1.4453524
2002    2.40    1.60    1.4225910
2003    1.90    2.30    1.3892490
2004    3.30    2.70    1.3633454
2005    3.40    3.40    1.3197923
2006    2.50    3.20    1.2763949
2007    4.10    2.80    1.2452633
2008    0.10    3.80    1.1962183
2009    2.70    -0.40   1.1950233
2010    1.50    1.60    1.1636059
2011    3.00    3.20    1.1464098
2012    1.70    2.10    1.1130192
2013    1.50    1.50    1.0944142
2014    0.80    1.60    1.0782406
2015    0.70    0.10    1.0696831
2016    2.10    1.30    1.0622474
2017    2.10    2.10    1.0403990
2018    1.90    2.40    1.0190000
``````

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:

``````DECLARE @a decimal(28,26) = 1;
With A as (
SELECT TOP 100 PERCENT
1 + Dec/100.00000000 AS year_factor
FROM ##CPI_U
WHERE Year >= 2000
ORDER BY year desc
)
SELECT @a = @a * year_factor
FROM  A

SELECT Cast(@a as decimal(9,7))
``````

With testing for 2000, 2001, and 2002, my output for the inflation adjustment was:

``````2000  1.4944944
2001  1.4453524
2002  1.4225910
``````

Based on these simple tests, it definitely appears that the EXP...LOG method is more precise than your current calculations.

This recursive SQL also solves this, although I think the logarithmic solution was more elegant.

``````with c (y, d, a, adj) as (
select year, dec, annual_avg, (1+dec/100)*adj from CPI_U join c on year=y-1 union all
select year, dec, annual_avg, (1+dec/100)*1   from CPI_U
where year=(select max(year) from CPI_U)
)
select * from c order by 1;
``````

Try it at http://sqlfiddle.com/#!4/aad15/4 (I used Oracle because MS SQL Server was down there at the moment. Only small changes to the SQL should be needed)

Output:

``````Y       D      A      ADJ
2000    3.4    3.4    1.4944944156194961
2001    1.6    2.8    1.4453524329008667
2002    2.4    1.6    1.4225909772646326
2003    1.9    2.3    1.3892490012349927
2004    3.3    2.7    1.363345437914615
2005    3.4    3.4    1.3197922922697145
2006    2.5    3.2    1.2763948667985634
2007    4.1    2.8    1.2452632846815253
2008    0.1    3.8    1.1962183330274019
2009    2.7   -0.4    1.1950233097176841
2010    1.5    1.6    1.1636059490921948
2011    3.0    3.2    1.1464098020612756
2012    1.7    2.1    1.1130192253022093
2013    1.5    1.5    1.0944141841712973
2014    0.8    1.6    1.078240575538224
2015    0.7    0.1    1.069683110653
2016    2.1    1.3    1.062247379
2017    2.1    2.1    1.040399
2018    1.9    2.4    1.019
``````
• Nice - good to see alternate methods even if they aren't optimal this time. Who knows what the next challenge will need. Feb 18, 2019 at 20:46