I'm looking to create a formula that will calculate an amount that represents future purchasing power in today's dollars in light of interest, taxes, and inflation. I know there are several online calculators but I wanted something to work within the spreadsheets I use to watch our spending and saving.

There is no need for anything super-complicated so I've tried to come up with a simple solution. Assume that all retirement savings are tax deferred.

Interest times (1 minus our effective tax rate in retirement) = net interest return

Then I subtract a theoretical inflation number to get what I consider our net (after-tax and after-inflation) purchasing power.

Interest: 4%

Effective tax rate: 22%

Inflation: 2.5%

.04 * (1 - .22) = .0312

then

.0312 - .0250 = .0062

Let's call the answer to this calculation "Rate". So in a spreadsheet formula, I use it along with NPER (years) and PV (starting balance).

PMT (rate, nper, -pv)

Rate = .0062

NPER = 30

PV = 1,000,000

PMT (.0062, 30, -1000000) = 36,362.31

Is this even remotely close to being right? That we would have $36,362 of today's purchasing power each year for 30 years?

## Help needed with retirement numbers

### Re: Help needed with retirement numbers

Just a bump to see if anyone can help me out with this.

### Re: Help needed with retirement numbers

Cheego wrote: ↑Tue Nov 13, 2018 5:49 pmI'm looking to create a formula that will calculate an amount that represents future purchasing power in today's dollars in light of interest, taxes, and inflation. I know there are several online calculators but I wanted something to work within the spreadsheets I use to watch our spending and saving.

There is no need for anything super-complicated so I've tried to come up with a simple solution. Assume that all retirement savings are tax deferred.

Interest times (1 minus our effective tax rate in retirement) = net interest return

Then I subtract a theoretical inflation number to get what I consider our net (after-tax and after-inflation) purchasing power.

Interest: 4%

Effective tax rate: 22%

Inflation: 2.5%

.04 * (1 - .22) = .0312

then

.0312 - .0250 = .0062

Let's call the answer to this calculation "Rate". So in a spreadsheet formula, I use it along with NPER (years) and PV (starting balance).

PMT (rate, nper, -pv)

Rate = .0062

NPER = 30

PV = 1,000,000

PMT (.0062, 30, -1000000) = 36,362.31

Is this even remotely close to being right? That we would have $36,362 of today's purchasing power each year for 30 years?

Shouldn't the bolded be:

.0312 - (1-.0250) = .03042

### Re: Help needed with retirement numbers

I was taught to use ((1 + after tax return) / (1 + inflation) -1) *100

So, 1.0312/1.025 -1 * 100 = 0.605%

PV = -1,000,000

n = 30

i = .605%

PMT = 36,550

So basically what you got. Looks right to me.

So, 1.0312/1.025 -1 * 100 = 0.605%

PV = -1,000,000

n = 30

i = .605%

PMT = 36,550

So basically what you got. Looks right to me.

### Re: Help needed with retirement numbers

Thanks birdec. That is an interesting way to figure it out. I would have never thought of that.

Sure could use a bit of feedback from #Cruncher.

Funny how the DinkyTown.com calculator says it should be $36,329. I just cant figure out their math though. I sent the question to the owner of DinkyTown. He has answered other questions about his calculators. Seems like a really nice guy so may be I will get an answer. If so, I will update this thread.

Sure could use a bit of feedback from #Cruncher.

Funny how the DinkyTown.com calculator says it should be $36,329. I just cant figure out their math though. I sent the question to the owner of DinkyTown. He has answered other questions about his calculators. Seems like a really nice guy so may be I will get an answer. If so, I will update this thread.

### Re: Help needed with retirement numbers

Just got this reply from Karl at DinkyTown...

I do understand what he is saying here but have no idea how to put that into a calculation.

*There is the thing, we calculate interest per period not annually. I believe the issue you are having is tied to that aspect.To get an periodic rate from an annual rate you can’t just divided by the number of periods or the annual resulting $ amount is too high. You need to figure the periodic rate (using the NPV formula) that will produce exactly the same annual return. This periodic rate is just a little lower than dividing the annual by the number of periods, to compensate for the periodic vs. annual compounding.*I do understand what he is saying here but have no idea how to put that into a calculation.