I have a question about an excel file I'm trying to create to capture inflation and future costs.

To kind of simplify it and hopefully make it more easily relatable, we can use a retirement calculation scenario.

I want to capture my total retirement costs if I live to be 90, including an estimated inflation. My table will essentially have rows going from age 40 to 90, so each row total retirement cost should be less than the previous row.

I currently am capturing inflation with a FV formula, but it's locking in the cost portion at the inflation rate of that time, if that makes sense. So, say in 10 years I'll be 50 and my current yearly costs are $100k, my formula is FV(3%,10,0,-$100K,0)*(90-50).

Which is saying that my $100K costs would be $134K in 10 years so multiply that by 40 years of retirement, I'll need $5.4mm to retire at 50.

My current formula is failing to capture that the costs would continue rising via inflation in those 40 years of retirement....

TIA

You have to change your formula in each cell to reflect the timeframe. So instead of 10, the age 51 cell will be 11, 52 will be 12, 53 will be 13, etc. You can build a formula to calculate that so you don't have to manually type it in each cell.

Edit to be more clear: You don't need the 90-50 factor in this calculation and you will have an amount in each row for 40 years. Then sum up those 40 calculations. There may be a formula that will do 40 years correctly with one cell, but I don't know what it is if there is one.

I think I'm understanding what you're saying and it's what I ended up doing. I had a table on the side with the cost w/ inflation for that year. I then had a formula in my main table that summed up those cells.

I was hoping there was a more automated way to do it. I'm sure there is, but I probably know 1/100 of excels capabilities haha

A little simpler formula that will calculate in one cell. I assumed starting at 50 in your example and going through the year you are 90, which is actually 41 years. The total was $10,571,689.40 when I summed up those 41 individual calculations.

The easier formula for future value of an annuity, what this essentially is, is FV = P * [(1+r)^n-1]/r. However, in this situation, you are not starting until year 11, so you have to subtract the first 10 years from the full annuity.
To get the value in this situation, it is:

[100,000*[(1+.03)^51-1]/.03]-[100,000*[(1+.03)^10-1]/.03] = 10,571,689.40

That worked! Thanks.

Comparing the formula to my results by manually adding the chart of per year costs I had to +1 year instead of -1 year. But that's exactly what I'm looking for. Thanks again.

ETA: Simplified the formula by not subtracting the years from each other in the exponent and it reflected your formula

Try this request in ChatGPT and see if it gives you the correct answer. I'm really curious as to when and how to use it, but this seems like a great use case...

htxag09 said:

---

Which is saying that my $100K costs would be $134K in 10 years so multiply that by 40 years of retirement, I'll need $5.4mm to retire at 50.

My current formula is failing to capture that the costs would continue rising via inflation in those 40 years of retirement....

---

A little curious why you are trying to capture total costs across retirement as a lump sum at the beginning of retirement time? Sorry if that's more B&I question than Nerdery.

Effectively what you are doing in your math above by looking at 40 years time is looking at a 2.5% withdrawal rate on the lump sum (rate = 1/40 = 2.5%). Generally this is how you look at retirement savings rather than a lump sum of future value spending that you assume will need to sit in cash and diminish over time due to inflation.

Your assumed withdrawal rate + expected inflation = returns needed to never reduce your principle. As a rule of thumb somewhere between 3-4% is probably a safe(ish) assumed draw down rate. If you stick to your 2.5% you should be good to weather any storms that would come.