Annuitize a sum and build in increases for inflation
July 23, 2018 12:31 PM Subscribe
I'm trying to figure out what I can budget/spend in retirement. For now, Social Security has regular increases for the cost of living. I want a formula to annuitize my retirement savings with an annual increase. I have a google sheet and have used an increase for interest on the savings, and have a similar adjustment for the amount I can take out needing to increase. Is there a useful formula? I have Excel and am very proficient with it, but I am not a Financial Planner, there are a ton of formula options in Excel.
Feel free to suggest a better spread sheet plan; I copied this and made it a bit less specific/ accurate.
Feel free to suggest a better spread sheet plan; I copied this and made it a bit less specific/ accurate.
I'm having trouble understanding, too. Are you saying you'd like to calculate the present value of each year's income (column H), in order to get a sense of whether you'll be able to meet expenses? That's a relatively straightforward formula, since you've picked an inflation rate, but I don't want to confuse you if that's not what you're after.
Also, you seem to be ignoring tax implications altogether, though, since you have four investment accounts, it seems very likely that one or more of them are tax-advantaged. Finally, have you confirmed you'll be taking minimum distributions from your tax-deferred accounts once you hit 70.5?
(I think it should be $K$9 for column C, too.)
posted by praemunire at 1:23 PM on July 23, 2018
Also, you seem to be ignoring tax implications altogether, though, since you have four investment accounts, it seems very likely that one or more of them are tax-advantaged. Finally, have you confirmed you'll be taking minimum distributions from your tax-deferred accounts once you hit 70.5?
(I think it should be $K$9 for column C, too.)
posted by praemunire at 1:23 PM on July 23, 2018
Response by poster: thank you, fixed that error.
Is there a formula baked in to Excel that will do this more elegantly? FV does not have the added parameters of ROI & COLA.
Is the information useful? For 2018, 5% ROI and 3% COLA look okay, but is there a component I'm missing?
posted by theora55 at 2:25 PM on July 23, 2018
Is there a formula baked in to Excel that will do this more elegantly? FV does not have the added parameters of ROI & COLA.
Is the information useful? For 2018, 5% ROI and 3% COLA look okay, but is there a component I'm missing?
posted by theora55 at 2:25 PM on July 23, 2018
Response by poster: Taxes - those are 401K and IRA accounts, and the withdrawals are greater than required. The increase in the standard deduction should make the taxes pretty manageable. I never rolled them over when I changed jobs, and despite the conventional wisdom, this paid off when 1 company moved to a new plan and my vesting went from 60% to 100%.
posted by theora55 at 2:49 PM on July 23, 2018
posted by theora55 at 2:49 PM on July 23, 2018
I am not an accountant, so please don't hinge your retirement on this (a very necessary warning, I'm sure). "Rate" will be your "ROI" in an FV function. It's defined as "interest rate" because it's primarily used to calculate, e.g., the value of an interest-paying bond, but there's no reason you can't use an assumed market return as that number. It's just that you go from a (reasonably) certain calculation to a fairly speculative one. Very importantly, there is zero formal means by which your investments will increase according to inflation (which I assume you're using the SSA's COLA as a proxy for). Thus, there is no explicit inflation/COLA input into that equation (as there is when you're calculating your SS payment). Now, as a general matter, your rate of investment return will be affected by inflation--that's how it's ultimately reflected in the calculation--but that's obviously not guaranteed.
I would expect your rate of market return to diminish over the years as you shift your investment mix to be more and more conservative. Worth thinking about.
posted by praemunire at 3:06 PM on July 23, 2018
I would expect your rate of market return to diminish over the years as you shift your investment mix to be more and more conservative. Worth thinking about.
posted by praemunire at 3:06 PM on July 23, 2018
I started to build a sample spreadsheet for you, but then realized I was thinking about it backwards from how your spreadsheet does -- I was trying to see how long my funds would allow me to spend a set level of expenses. But it looks like you are trying to determine how much you can draw from your retirement portfolio to last you for the rest of your life? Is that your intention, or just a function of how you built the spreadsheet?
Re-reading the question -- when you say "annuitize", you are trying to withdraw the same amount from your portfolio every year, increasing every year by COL? Or are you trying to provide your self with a steady "salary" every year (including SS), and that "salary" is indexed to inflation/COL?
I know you asked a pretty narrow Excel question, so apologies if this is retreading ground you have already considered, but in terms of getting at the underlying methodology for this spreadsheet, I must ask if you have read up on the 4% withdrawal "rule"? (which is allowed to be inflation-indexed). The 4% rule is challenging to model in a spreadsheet, because it is based on historical market data that is not a nice steady 5% climb, but it is a philosophy that has been somewhat stress-tested (and criticized) in real-world data.
posted by misterbrandt at 5:11 PM on July 23, 2018
Re-reading the question -- when you say "annuitize", you are trying to withdraw the same amount from your portfolio every year, increasing every year by COL? Or are you trying to provide your self with a steady "salary" every year (including SS), and that "salary" is indexed to inflation/COL?
I know you asked a pretty narrow Excel question, so apologies if this is retreading ground you have already considered, but in terms of getting at the underlying methodology for this spreadsheet, I must ask if you have read up on the 4% withdrawal "rule"? (which is allowed to be inflation-indexed). The 4% rule is challenging to model in a spreadsheet, because it is based on historical market data that is not a nice steady 5% climb, but it is a philosophy that has been somewhat stress-tested (and criticized) in real-world data.
posted by misterbrandt at 5:11 PM on July 23, 2018
You can convert your nominal rate of return into a real rate of return by subtracting inflation. So if your estimated rate of return on investments is 5% and inflation is 2%, use 3% as your estimated rate of return, and your cash flows in your time value of money calculations become real dollars.
In general, we see retirees spend more money in the early years of retirement. As they age, spending declines modestly. Then, towards the end of life, health costs can increase spending. (There is high variance to this last stage, as some people die suddenly, and others live for a years requiring expensive care.)
posted by thenormshow at 5:11 PM on July 23, 2018
In general, we see retirees spend more money in the early years of retirement. As they age, spending declines modestly. Then, towards the end of life, health costs can increase spending. (There is high variance to this last stage, as some people die suddenly, and others live for a years requiring expensive care.)
posted by thenormshow at 5:11 PM on July 23, 2018
Response by poster: I'm not an accountant so I may have used inaccurate terms. I have read a fair number of consumer-grade articles and found them lacking. A 4% rule might work this year, but as a geezer, I've experienced inflation and recession and that rule of thumb does not inspire confidence. Thanks, thenormshow, I can use return-inflation.
posted by theora55 at 10:37 PM on July 23, 2018
posted by theora55 at 10:37 PM on July 23, 2018
I'm sleepy this morning, so I may be off--but since the social security figure in the spreadsheet is going to have the COLA applied, why would you want to use the real return on the investments to calculate total available income? Seems like you'd be mixing apples and oranges. Either you want the nominal figure for both (and you can then discount the total for inflation), or you want to use the real figure for both.
posted by praemunire at 8:27 AM on July 24, 2018
posted by praemunire at 8:27 AM on July 24, 2018
This thread is closed to new comments.
When you are doing year-by-year calculations, I think a simple percent increase year-over-year is fine (with the caveat that this ignores how the market really works. But assuming you understand that...). If you want to calculate out an investment balance many years in the future (without calculating the intervening values), FV is the function to use.
What about the current spreadsheet is lacking or inaccurate for your purposes?
posted by misterbrandt at 1:11 PM on July 23, 2018 [1 favorite]