The VSR spreadsheet, called "Accumulation Worksheet", is now part of the VPW Accumulation And Retirement Worksheet.
INSTRUCTIONS
See this post.
LINKS
The spreadsheet can be used online or downloaded.
The links can be found on the Boglehead wiki VPW page: VPW Accumulation And Retirement Worksheet.
SCREENSHOT

A frequent question, in our forums, is "How much should I save?" A good answer to this is "As much as you can, but not so much as to live like a pauper." But, this is imprecise.
In a recent thread, I proposed an interesting calculation that leads to a variable savings rate which adapts to the saver's situation, retirement horizon, and current portfolio balance. Here's a copy the main parts of my post.
Let's pick a family with two earners making $31,000 each, for a household income of $62,000. That's the approximate US median household income.
In their 20s, they pay down debt, accumulate a down payment, and buy a modest house at the end of their 20s with a 30-year mortgage. They don't start saving and investing before the age of 30. Then, they work, live below their means, save, and invest from age 30 to 64. At age 65, they retire. Each would qualify for a annual $15,500 Social Security pension at age 66, but they decide that one of them will claim at age 65 and get $14,000 and the other will delay until 70 to get $20,000.
We'll assume, for simplicity, that their expenses are relatively level all along. Their mortgage is modest, and once it's paid, an equivalent HELOC is used to spread the cost of maintenance (kitchen remodel, roof, etc.).
How much should they save to retire with dignity? I'll use a similar calculation to what I've shown in the post The Mathematics of Retirement Investing.
One of the spouses likes aggressive investing, but the other is risk averse. They compromise to holding a 50/50 stocks/bonds portfolio (with stocks subdivided between domestic and international, and bonds subdivided between nominal and inflation-indexed) all life long, during both work years and retirement. They know that market returns fluctuate, but, for planning purpose, they use a real 3.5% growth trend for such a balanced portfolio.
To retire with dignity, they'll need to:
- Accumulate ($20,000 X 5) = $100,000 to fill the gap in Social Security payments from age 65 to 69.
- Save and invest enough in a tax-deferred accounts to equalize ($62,000 - savings) with ($14,000 + $20,000 + portfolio withdrawals).
So we want to find the additional savings such that ($62,000 - $1,500 - additional savings) = ($14,000 + $20,000 + 3.2 X additional savings).
Code: Select all
S = additional savings
$62,000 - $1,500 - S = $14,000 + $20,000 + 3.2 S
$60,500 - S = $34,000 + 3.2 S
$60,500 - $34,000 = 3.2 S + S
$26,500 = 4.2 S
S = $26,500 / 4.2 = $6,310
What the calculations above are suggesting is a dynamic savings rate. It projects variable percentage withdrawals back into accumulation years. Similar to VPW, it requires annual calculations without taking the past into account. Let me illustrate this.
At age 30, starting with a $0 portfolio, according to the above calculations, they need to save $7,810, or $300/bi-weekly pay, into a tax-deferred account. That's a 12.6% savings rate.
Every year, they redo the above calculations based on their current portfolio balance and current income, using an updated investment horizon. At age 35, for example, maybe they've been unlucky and their portfolio has only grown to $37,500 (inflation-adjusted) . The household income is still $62,000. Let's do the calculations, keeping the constant real 3.5% portfolio growth trend but for a 30 year investment horizon from age 35 to 64.
In 30 years, $37,500 would grow to $105,255. This would cover the Social Security gap and allow for an additional ($5,255 X 4.8%) = $252 VPW portfolio withdrawal. In 30 years, investing $1000 per year would grow to a total of $51,623 and allow for a ($51,623 X 4.8%) = $2,478 VPW withdrawal.
We want to find the additional savings such that ($62,000 - additional savings) = ($14,000 + $20,000 + $252 + 2.478 X additional savings).
Code: Select all
S = additional savings
$62,000 - S = $14,000 + $20,000 + $252 + 2.478 S
$62,000 - S = $34,252 + 2.478 S
$62,000 - $34,252 = 2.478 S + S
$27,748 = 3.478 S
S = $27,748 / 3.478 = $7,978
See how the savings rate fluctuates according to market returns. Lower market returns lead to a slightly higher savings rate, naturally letting them buy slightly more investment assets when they're down. Higher market returns would lead to a slightly lower savings rate, naturally letting them buy slightly less investment assets when they're up.
Note that there's no need to predict future returns. Instead, I suggest using VPW's growth trend. It's actually important to use a constant growth trend. Saving more when markets are high, and less when they're low (as would happen if one used "good" future return predictions*) wouldn't be a smart thing to do!
* I personally don't believe that anybody is able to make good future return predictions.
Of course, many will consider it generally imprudent to target a retirement age of 65, because one or both spouses could lose their job before that, or they could wish to retire earlier. That's fine. One could easily redo the above calculations with an earlier target retirement age. Note, too, that the above calculations determine a minimal savings rate. One could save more, too, but this would be a choice of targeting a higher standard of living in retirement than when working.