Math/excel help -present value, future value, inflation

Non-investing personal finance issues including insurance, credit, real estate, taxes, employment and legal issues such as trusts and wills
Post Reply
User avatar
Topic Author
ray.james
Posts: 1166
Joined: Tue Jul 19, 2011 4:08 am

Math/excel help -present value, future value, inflation

Post by ray.james » Wed Nov 13, 2019 5:37 pm

I am doing a spread sheet for myself but I couldn't figure out the math and inflation. I hope math/excel gurus can help me determine the correct formulas/additional columns if needed. I am not sure how useful it is to others. Let me know if you would like to see the results/sheet.

What I am trying to do:
Assuming at today's tax brackets, 22% marginal rate ends at 165k for MFJ. If one is expecting 165k IRA withdrawals in retirement for 30 years at 4% growth, the starting figure is ~2,968,000(call this target_number). Now to achieve that during working years, I am using 25 years of accumulation at 7% yielding 43850(call this contribution_needed). Assuming 3% match and both spouses can contribute, the figure for contribution per year ends up at $21,272.

Whats its use for me:
The goal of this exercise is to draw the accumulation curve using $21,272 or $43,850 to see whether one is ahead/vice-versa on the curve of contributions. Second a rough number if mind to not worry about IRA_balances for different marginal tax brackets. Third, account for social security/rental income of future to be subtracted out of 165k figure and arriving at the marginal numbers. A few personal factors like dual-contribution, job losses, 401k availability can be baked in to number of contribution years(25) which helps as it is configurable.

Questions
1) Is there a calculator already for this.( of course excel will make everything configurable and future proof)
2) How to calculate target_number in excel.
3) How to calculate contribution_needed.
4) The inflation in tax brackets and contributions increases cancel out inflation factors?(of course, not exactly but more or less)- or is it double accounted since both inflate.
5) If 4 is correct, can one use full returns of 7%(70/30 fund ) during accumulation or whether it need to be reduced for inflation(5%).

Thank you.
When in doubt, http://www.bogleheads.org/forum/viewtopic.php?f=1&t=79939

fyre4ce
Posts: 368
Joined: Sun Aug 06, 2017 11:29 am

Re: Math/excel help -present value, future value, inflation

Post by fyre4ce » Wed Nov 13, 2019 6:32 pm

In general, the best way to account for inflation is to just subtract the predicted inflation rate from the nominal investment return, to get a real return. Doing that will adjust investment performance, tax bracket creep, and contribution size all for inflation; your analysis will be entirely in real dollars.

If you want $165k (inflation-adjusted) of retirement income, you need to assume a withdrawal rate. If you assume a 4% withdrawal rate, you will need a $4.125M (= $165k / 4%) IRA, not $2.968M as you indicate.

To get to a $4.125M inflation-adjusted balance, you can use the =PMT function in Excel. If you assume a 7% nominal return and 3% inflation, your real return will be 4%. You also need to tell the function how much you have saved already; I'll assume $0 since you don't indicate otherwise. Your required annual contribution will be:

PMT(4%, 25, $0, $4125000) = -$99,049.35 [negative sign means you're paying out]

That figure ($99,049) would include both your contribution and any matching.

If you want to double-check your answer, you can use the =FV function:

FV(4%, 25, -$99049.35) = $4,125,000

Hope that helps.

Edit: corrected answer for 4% real return; first figure was for 5%

User avatar
siamond
Posts: 5027
Joined: Mon May 28, 2012 5:50 am

Re: Math/excel help -present value, future value, inflation

Post by siamond » Wed Nov 13, 2019 11:26 pm

Ray, you should do all the math in real (inflation-adjusted terms) by using rates of return (and discount rates, same thing) which are real quantities. This makes things MUCH simpler and MUCH more intuitive.

The following spreadsheet (which I maintain) was developed for another thread and provides a fairly general purpose Present Value (aka Time Value of Money) computation. It is mostly aimed at early retirees (pre and post retirement), but its applicability is broader and should address (some) needs of accumulators:
https://docs.google.com/spreadsheets/d/ ... sp=sharing

Please make a copy for your own needs. I didn't try to think hard if this will solve your specific problem though. It should at least be a starting point...

Camp Shore
Posts: 6
Joined: Sun May 21, 2017 11:43 am
Location: Central Florida

Re: Math/excel help -present value, future value, inflation

Post by Camp Shore » Fri Nov 15, 2019 12:44 pm

The numbers you want could be done with a financial calculator (such as Texas Instruments), or with Excel. If you want to understand the math, YouTube is a good resource for math lessons. The basic TVM equation is very useful. The financial functions in Excel are based on that equation.

However you choose to do your math, I recommend you play with some simple problems to be sure you understand what you're doing. You need to understand PV, FV, r , n, and when to use a negative or positive number. You can't get useful answers unless you understand those inputs.

dbr
Posts: 30799
Joined: Sun Mar 04, 2007 9:50 am

Re: Math/excel help -present value, future value, inflation

Post by dbr » Fri Nov 15, 2019 1:05 pm

Generally good. The one trick with inflation is factors that do not index with inflation. Many tax points are inflation indexed but some are not. If anything in your calculation is defined in fixed dollars, such as a fixed pension or a mortgage payment, the inflation will have to be explicitly included somewhere. Your case does not seem to have those complexities, mostly.

Post Reply