inflation calculation in MS SQL

I have the below requirement to fulfill. How to achieve this simplistically.

There is an inflation rate table as below

2009, 5%
2010, 5%
2011, 5%
2012, 5%
2013, 5%

I need to first get

2009, 5%, 5
2010, 5%, 5.25        - which is 5% over 5
2011, 5%, 5.5125      - which is 5% over 5.25
2012, 5%, 5.788125    - which is 5% over 5.5125
2013, 5%, 6.07753125  - which is 5% over 5.788125

And then get

2009, 5%, 5          , 5
2010, 5%, 5.25       , 10.25            = 5.25 + 5
2011, 5%, 5.5125     , 15.7625          = 5.5125 + 10.25 and so on
2012, 5%, 5.788125   , 21.550625
2013, 5%, 6.07753125 , 27.62815625

Answer

I think the best way to do this on SQLserver is to use a common table expression with recursion. I’m not 100% sure because I can’t test it here, but something along these lines as the example below. Btw I assume the rates in the table are stored as fractions, so 5% is 1.05 and 10.25% is 1.1025 etc.

WITH MyCompoundRates (TheYear, TheRate, CompoundRate)
AS
(
-- select one anchor record, starting point record
    SELECT
        TheYear,
        TheRate, -- I'm assuming "5%" is stored as value 1.05
        TheRate as CompoundRate
    FROM
        MyRatesTable
    WHERE
        TheYear = 2009 -- <- starting point for recursion
    UNION ALL
-- select recursive records, by linking them to a previous record
    SELECT
        r.TheYear,
        r.TheRate,
        r.TheRate * c.CompoundRate as CompoundRate -- calculate compound rate
    FROM
        MyRatesTable r
        JOIN MyCompoundRates c ON r.TheYear = c.TheYear+1 -- recursion! link a year to previous year
)
-- Statement that executes the CTE
SELECT
        TheYear,
        TheRate,
        CompoundRate
FROM
    MyCompoundRates