The Retirement Income Planning Worksheet
Most people build these spreadsheets by throwing every income source into one column and then hoping the bottom number looks okay. That approach misses the actual problem, which is sequence risk and the mismatch between when expenses actually spike and when your portfolio might be underwater. A real worksheet forces you to model those years explicitly instead of relying on a single withdrawal rate that assumes flat returns and steady spending. Retirement Income Planning Worksheet — that's what I call it in conversations with clients even though the file is usually named something like income_model_v7_final.xlsx. The structure matters more than the name. Here's how I actually build one.
Building the skeleton
Start with columns for each year of retirement. Not months at first. Years are enough for the high-level picture and it keeps the spreadsheet from turning into an unmanageable mess. Row headers should be: portfolio balance at start of year, contribution or rollover income if any, Social Security, pension, annuity payments, bond fund dividends, Treasury bill yields, interest from cash reserves, rental income, part-time work, withdrawals for essential expenses, withdrawals for discretionary spending, taxes owed, inflation adjustment applied to fixed expenses, and portfolio balance at end of year. The end-of-year balance is the formula that connects everything. It's start balance plus all income minus all withdrawals minus taxes. If you get that right once, the rest of the sheet runs itself. I usually set up the first three years manually with real numbers to verify the formulas are pulling correctly before expanding to forty or fifty years. One thing beginners consistently mess up is the tax row. They calculate taxes on total withdrawals without considering the order of account types. That's wrong. Traditional IRA and 401k withdrawals are fully taxable at ordinary income rates. Roth withdrawals are generally tax-free. Brokerage account withdrawals trigger capital gains tax on the appreciation portion. Your worksheet needs separate rows for each account type so the tax calculation reflects the actual sequencing decision you're making.
Income sources and what they actually look like
Social Security numbers should come from your actual estimate at full retirement age, not the maximum possible benefit. Most people overstate this by two to three thousand dollars a year. Pull your statement from ssa.gov and use the actual projected amount. If you're considering early filing or delayed credits, model both scenarios in adjacent columns so you can see the tradeoff clearly. Pension income is straightforward if you have one. Annuities less so because many people confuse the stated payout rate with the real after-tax yield. A $50,000 annual annuity payment might only be $30,000 in taxable income if you contributed $20,000 of after-tax money. Factor that distinction in because it changes your tax bracket calculations significantly. Portfolio withdrawals are where the whole exercise gets meaningful. The common 4% rule assumes a thirty-year horizon and historical US market returns. That's a reasonable starting point but it's not a law. Your actual safe withdrawal rate depends on your expense structure, your asset allocation, and whether you're willing to trim spending in bad years. I usually model a flexible withdrawal approach where discretionary spending drops by a percentage equal to the portfolio's worst annual return below a set threshold.
Get the Full Details
The edge case I keep running into
Here's a specific problem that came up recently with a client. She had a traditional IRA worth about $900,000, a pension that covered roughly sixty percent of her essential expenses, and Social Security deferred until age 70. The standard worksheet approach showed her portfolio lasting past age 95 with comfortable margins. The problem was that her required minimum distributions at age 72 would push her into a higher tax bracket and cause her Social Security benefits to become partially taxable. That created a spike in taxable income she hadn't anticipated, which then affected her Medicare Part B and D premiums through income-related monthly adjustment amounts. The workaround was to run a Roth conversion scenario in the years between 65 and 72 when her income was lower. I added a row that simulated converting twenty thousand dollars annually from traditional IRA to Roth, which filled up her standard deduction space each year at zero additional tax cost. This reduced the RMD baseline and kept her Medicare IRMAA brackets manageable. The spreadsheet flagged the difference immediately once I linked the Roth conversion row to the RMD calculation row and the IRMAA impact row. Without that linkage, the analysis would have looked fine on the surface and failed in practice.
Advanced nuance most guides skip
Sequence of returns risk isn't just about bad years happening early. It's about bad years happening while you're simultaneously withdrawing from a declining portfolio. The math is brutal. A twelve percent loss in year one followed by a twelve percent loss in year two with steady withdrawals can destroy a portfolio faster than a single twenty-four percent crash because you're selling shares at depressed prices to fund withdrawals, which means fewer shares participate in the eventual recovery. To capture this properly in your worksheet, you need to model different return sequences across your retirement timeline. Don't just use one average annual return. Create a Monte Carlo simulation or at minimum run three scenarios: optimistic, median, and pessimistic. The pessimistic scenario should include a period of low returns coinciding with high withdrawals in the first ten years. If your portfolio survives that scenario, you have a reasonably robust plan. If it fails, you need to adjust something before you retire. Another thing people overlook is healthcare cost volatility in the pre-Medicare years. A forty-eight-year-old retiring at sixty-two faces a decade and a half of healthcare costs that aren't covered by any public program. Employer subsidies often disappear upon retirement. Private marketplace plans can cost fifteen to thirty thousand dollars annually depending on your state, age, and health status. Including a row for healthcare premiums and out-of-pocket costs starting at your actual retirement age rather than age 65 will change your withdrawal strategy significantly.
What this tool actually can't do
I need to be blunt about the limitations because worksheets get sold as crystal balls. A Retirement Income Planning Worksheet cannot predict black swan events. It cannot account for long-term care costs unless you explicitly build in that row, and even then you're guessing at timing and magnitude. It cannot adapt to policy changes. If Congress alters Social Security, Medicare, or tax law five years from now, your spreadsheet is now wrong. It gives you a structured way to think about decisions, not a guarantee of outcomes. For people with simple profiles — one account type, Social Security as the primary income floor, modest portfolio, no pension — a basic annual worksheet is sufficient. For anyone with multiple account types, significant non-retirement income like rental properties or business proceeds, or complex tax situations, the spreadsheet becomes so large that manual management turns into a part-time job. In those cases, professional planning software or a fee-only fiduciary who uses proper Monte Carlo tools like NewRetirement or MoneyGuidePro is more efficient. You'll spend less time maintaining the model and more time making decisions. The real value of building this worksheet yourself is that you learn the mechanics of your own financial situation. The numbers you confront in the cells tend to stick with you in a way that a PDF summary from a financial advisor never does. You notice the gaps. You see where your assumptions are optimistic. You understand the tradeoffs between spending now and preserving capital for later. That awareness is what actually changes behavior, not the spreadsheet itself.
