Help needed with retirement numbers

Non-investing personal finance issues including insurance, credit, real estate, taxes, employment and legal issues such as trusts and wills
Post Reply
Cheego
Posts: 54
Joined: Mon Jul 18, 2016 11:57 am
Location: Greenville, SC

Help needed with retirement numbers

Post by Cheego » Tue Nov 13, 2018 5:49 pm

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?

Cheego
Posts: 54
Joined: Mon Jul 18, 2016 11:57 am
Location: Greenville, SC

Re: Help needed with retirement numbers

Post by Cheego » Thu Nov 15, 2018 4:02 pm

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

ved
Posts: 699
Joined: Sat Jan 18, 2014 6:56 pm

Re: Help needed with retirement numbers

Post by ved » Thu Nov 15, 2018 4:46 pm

Cheego wrote:
Tue Nov 13, 2018 5:49 pm
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?

Shouldn't the bolded be:

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

birdec
Posts: 39
Joined: Fri Jul 11, 2014 10:44 am

Re: Help needed with retirement numbers

Post by birdec » Thu Nov 15, 2018 4:57 pm

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.

Cheego
Posts: 54
Joined: Mon Jul 18, 2016 11:57 am
Location: Greenville, SC

Re: Help needed with retirement numbers

Post by Cheego » Thu Nov 15, 2018 8:16 pm

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.

Cheego
Posts: 54
Joined: Mon Jul 18, 2016 11:57 am
Location: Greenville, SC

Re: Help needed with retirement numbers

Post by Cheego » Fri Nov 16, 2018 10:40 am

Just got this reply from Karl at DinkyTown...

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. :confused

Post Reply