Excel formula for cumulative costs in 5 year increments

I had a previous problem that was solved that was similar, but wasn’t a running total: Excel formula for declining number, in 5 year intervals

So this number is about budge forecasting. It needs to include the average annual inflation rate. The parts that are tricky for me, is that I need it to be in 5 year increments and it needs to be cumulative (meaning, it keeps adding the previous amounts with the current year).

For example, let’s say in 2020 the budget is $100. Average annual inflation rate is 3%. In 2021, the budget is $113. That running total in 2005 is 531 (so that means years 2020, 2021, 2022, 2023, 2024 is 100, 103, 106, 109, and 113 respectfully. So how would I create a repeating formula that tabulates like this that only shows every 5 years (in other words, I don’t want 5 rows, just one for every 5 year increment).

3 Answers

The sequence of nominal (ie after inflation ) payments is a "geometric sequence" or "geometric progression". The nth term in the sequence:

where a is the initial value, r is the common ratio.

In your example a = 100, r = 1 + 0.03

You want to calculated the sum of the sequence after n years or the "geometric series". The nth item in the series is calculated by:

Below is a minimal example:

You can remove the years 2 - 4 from the spreadheet since the formula in each column is independent of the previous one.

More on geometric sequence and series on Wikipedia

The basic formula is start_amount * (1 + inf_rate)^yearswhere:

• start_amount is the the original dollar amount - eg $1,000,000
• inf_rate is the annual inflation rate as a decimal - eg 0.03
• years is the number of years since the start - eg 0, 5, 10, etc

Here's a minimal example.

Note Use of absolute references for all but the current year means you can copy the formula from one column to the next.

There's a built in function for calculating future value FV that does basically the same thing but also allows for regular payments as in a loan or annuity.

Here is an array formula to get the job done where A3 holds your base amount:

=SERIESSUM(1.03,1,1,INDEX((A3*ROW(1:5))/ROW(1:5),))

Note with array forumlas you must press cntrl+shift+enter after typing or pasting it in or after any changes.

Change the numeral 5 to be whatever amount of years you need.

Here is a way without array formulas but you must enter 100 (or your base amount) for each year you want calculated.

For 5 years:

=SERIESSUM(1.03,1,1,{100,100,100,100,100})

For 10 years:

=SERIESSUM(1.03,1,1,{100,100,100,100,100,100,100,100,100,100})