Organize income estimates for the transition into retirement with this free retirement income tax analysis template for Excel. Compare the current year with three retirement years and record the assumptions you want to review before preparing a tax estimate.
What it actually calculates: The workbook totals entered income and expense categories and shows the current-year composition percentages. It does not calculate taxable income, federal or state income tax, withholding, or after-tax retirement income. Treat it as a planning worksheet rather than a tax calculator.
File format: Microsoft Excel (.xlsx), supplied in a ZIP archive.
Included: Current-year and three retirement-year columns, income categories, an expense section, subtotal formulas, and current-year percentage calculations.
Preview of the income and expense comparison worksheet. Select the image to view it at full size.
Extract the ZIP file, open the workbook in Excel, and save a working copy. Separate Word and PDF versions are not included.
Review before use: The original section titled “Tax Deductible Expenses” contains legacy categories. A printed category does not establish that a payment is deductible. Relabel this section for review and verify the applicable tax treatment before using any amount in a tax calculation.
What the Spreadsheet Includes
Layout of the supplied Excel worksheet
Section
Included fields
Year columns
Current Year in D; Retirement Year 1, Year 2, and Year 3 in F, G, and H.
Current-year percentages
Column E shows each entry’s share of its section total.
Income
Gross wages or salary, bonus, retirement plans and deferred compensation, capital gains or losses, Social Security, and other income.
Expense review
Legacy labels for moving, unreimbursed employee expenses, charitable contributions, alimony, medical costs, real property taxes, interest, and miscellaneous deductions.
Totals
Income totals in row 16 and expense-section totals in row 27.
No tax brackets, deduction limits, filing-status selection, Social Security benefit estimator, required minimum distribution calculation, or automatic growth forecast is included. Retirement-year amounts must be entered by the user.
How to Prepare the Retirement Income Comparison
1. Label the years and define the basis
Replace the general year headings with specific calendar years. Record the planning date, currency, and whether figures relate to an individual or household. State whether future estimates are nominal amounts for each year or amounts expressed in today’s purchasing power; avoid mixing the two.
Use a consistent basis for the income section, such as gross amounts received or expected before withholding. Keep a separate schedule for taxable portions, withholding, and cash received after withholding.
2. Gather the current-year figures
Use payroll records, retirement statements, benefit statements, and investment records to support the current-year amounts in column D. Separate actual amounts already received from estimates for the rest of the year in your supporting notes.
A year-end tax planning worksheet can help organize a separate annual review. It is not a substitute for verifying current tax rules.
3. Estimate the three retirement years
Enter each year’s expected income in columns F through H. Consider the timing of employment ending, pension payments starting, benefit elections, and planned withdrawals. Document the source and date of each estimate instead of assuming that the workbook supplies those projections.
Use the retirement plan contributions analysis worksheet for a separate review of contribution assumptions. Contributions and retirement withdrawals serve different purposes; do not transfer one into the other without explanation.
4. Avoid duplicate capital-gain entries
The income total sums rows 8 through 15, including row 11, which is labeled “Capital gains (losses):”, and rows 12 and 13 for long-term and short-term amounts. If you enter the two components, keep row 11 free of a duplicate subtotal. Otherwise, the total counts the same gains twice.
Record gains or losses on your defined basis, not automatically the entire proceeds from an investment sale. Keep supporting cost and transaction records separately.
5. Review the expense section
Rename “Tax Deductible Expenses” to Expenses for Tax Review until you have verified eligibility. Record actual or estimated payments separately from any amount ultimately allowed as a deduction. Eligibility, limits, and treatment depend on the tax year, jurisdiction, and circumstances.
This section is not a complete retirement spending budget. Use our living cost analysis template to organize broader household expenses without implying that those expenses are tax deductions.
Understand the Percentage and Total Formulas
The current-year income total in D16 uses =IF(SUM(D8:D15),SUM(D8:D15),""). The corresponding retirement-year totals sum the same rows in columns F, G, and H.
For example, E8 divides current-year wages by the current-year income total. The expense percentages use the current-year expense-section total instead. These percentages describe the composition of each section; they are not tax rates, year-over-year changes, or the proportion of income spent.
The original total formulas display a blank when a section sums to zero. A blank may therefore represent a zero result rather than missing data. If you need visible zeros, an optional replacement is =IF(COUNT(D8:D15)=0,"",SUM(D8:D15)) in D16 and =IF(COUNT(D19:D26)=0,"",SUM(D19:D26)) in D27. Adapt the column references for other years and test the results after editing.
Retirement Income Comparison Example
This hypothetical example uses one currency and gross planning amounts. It does not determine their taxable portions.
Illustrative income estimates
Income source
Current year
Retirement Year 1
Wages or salary
60,000
10,000
Retirement plans and deferred compensation
0
24,000
Long-term capital gains
2,000
2,000
Social Security
0
18,000
Other income
3,000
3,000
Total entered income
65,000
57,000
All other income entries are zero, and the capital-gains heading row is left empty to avoid duplication. Current-year wages represent 60,000 ÷ 65,000 = 92.31% of entered income. The worksheet totals retirement-year income at 57,000; it does not calculate how much tax is due on that amount.
Separate Income Planning from Tax Calculation
For US planning, the IRS explains that pension and annuity payments can be fully or partly taxable, depending on the circumstances. See IRS Topic 410: Pensions and Annuities. A withdrawal’s gross amount is not always its taxable amount.
Qualified Roth IRA distributions are tax-free, subject to the applicable conditions; see the IRS Roth IRA guidance. Social Security has its own tax treatment, explained in IRS Topic 423. The workbook does not apply these rules.
Prepare any tax estimate separately using the applicable year’s rules, filing status, taxable income categories, deductions, credits, and state or local treatment. Keep withholding and estimated payments distinct from total tax liability. The worksheet’s expense total cannot simply be subtracted from its income total to produce a verified taxable-income figure.
Frequently Asked Questions
Does this template calculate retirement income tax?
No. Despite its original title, it is an income and expense comparison worksheet with totals and current-year percentages. Tax calculations require a separate model.
How many retirement years are included?
Three, alongside the current year. Add more columns if needed, then check formulas and print settings.
Are all listed expenses deductible?
No. The inherited labels do not confirm eligibility. Verify current rules and distinguish payments made from deductions allowed.
Does it estimate Social Security or retirement withdrawals?
No. It provides input rows for those amounts but does not calculate benefits or determine withdrawal requirements.
Can I use the worksheet outside the United States?
You can adapt the comparison structure, but its labels are US-oriented. Replace them and apply the relevant local tax treatment separately.
Can I use Google Sheets?
You can try importing the .xlsx workbook. Check formulas, percentage formats, column widths, and print layout after import.
