# 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).

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

• David. Thank you very much. That worked! – devhead Jan 10 at 17:46

The basic formula is `start_amount * (1 + inf_rate)^years` where:

• `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.

• Hi David. After reading your answer, I realized my questions wasn't very clear. I've updated it to make it more clear about the need for cumulative running total. – devhead Jan 8 at 17:18
• Yes @devhead that is quite a different question now after your edits. I'll add a new answer rather than edit this one. – David Furphy Jan 9 at 23:19

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})
``````
• I'm sorry. Maybe I'm just not clear. The number over 5 years should be at least 5x the origal number, as it's cumulative - that is, the running amount of the entire annual amount. So in other words, I want to see how much the company paid over the course of 5 years. – devhead Jan 8 at 21:34
• Still not what I'm looking for. Hopefully ths image is worth a thousand words imgur.com/a/sdOjwGW – devhead Jan 9 at 5:37
• @devhead okay first year does have interest, I stripped the +100 off the end. It looks like every fourth year has \$4 instead of \$3. Is the actual interest rate 3.3%? – Brian Jan 9 at 16:45
• Looks much better. However, when I do the basic formula that lists every year, then I add it up, its different than your formula. So with yours (I'm using your exact formula), on 5th year, it's \$545; 10th year it's \$1,165. On mine, on 5th year, it's \$547; 10th year it's \$1,181. – devhead Jan 9 at 17:06
• @devhead Yes on your chart why does every 4th year add \$4 instead of \$3? And when I total your 10 amounts it comes to 1180 instead or 1181. – Brian Jan 9 at 17:18