Excel formula for cumulative costs in 5 year increments

Solution 1:

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

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

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.