0

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

0

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

enter image description here

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:

enter image description here

Below is a minimal example:

enter image description here

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
2

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. simple spreadsheet calculating future value in multi year increments

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
0

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

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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