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

Question

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

I added a desired output. the script above gives it as a triangle, too. — James, Feb 14, 2019 at 16:26

Laughing Vergil · Accepted Answer · 2019-02-14 17:53:04Z

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

I received this output:

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.

Kjetil S. · Accepted Answer · 2019-02-14 22:45:33Z

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. — Laughing Vergil, Feb 18, 2019 at 20:46

Stack Exchange Network

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

2 Answers 2

Your Answer

Not the answer you're looking for? Browse other questions tagged
sql-server
sql-server-2008
or ask your own question.

Hot Network Questions

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

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged sql-serversql-server-2008 or ask your own question.

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
sql-server
sql-server-2008
or ask your own question.