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/viceversa 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 dualcontribution, 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.
Math/excel help present value, future value, inflation
Math/excel help present value, future value, inflation
When in doubt, http://www.bogleheads.org/forum/viewtopic.php?f=1&t=79939
Re: Math/excel help present value, future value, inflation
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 (inflationadjusted) 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 inflationadjusted 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 doublecheck 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%
If you want $165k (inflationadjusted) 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 inflationadjusted 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 doublecheck 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%
Re: Math/excel help present value, future value, inflation
Ray, you should do all the math in real (inflationadjusted 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...
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...

 Posts: 6
 Joined: Sun May 21, 2017 11:43 am
 Location: Central Florida
Re: Math/excel help present value, future value, inflation
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.
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.
Re: Math/excel help present value, future value, inflation
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.