Build Your Own Retirement-Projection Spreadsheet

Building a custom retirement projection spreadsheet is far simpler than most people assume. With a basic understanding of the FV (Future Value) function, a few critical assumptions, and a willingness to experiment, you can create a powerful tool that projects your net worth decade by decade, tests "what-if" scenarios, and shows precisely when you can afford to stop working. This article walks you through the entire process—from setting up your initial worksheet to stress-testing your assumptions.

Quick definition: A retirement projection spreadsheet forecasts your net worth and income needs from today through retirement using compound growth rates, annual contributions, inflation, and spending assumptions.

Key Takeaways

• A retirement projection starts with five core assumptions: starting balance, annual contribution, investment return, inflation, and retirement spending target.
• The FV formula forms the mathematical backbone: you'll apply it year by year to compound your assets.
• Building year-by-year (rather than jumping straight to year 30) lets you apply variable rates, changing contributions, and realistic life events.
• A working spreadsheet includes three main tabs: Inputs (assumptions), Projections (year-by-year detail), and Summary (key milestones).
• Sensitivity analysis—testing how changes in return or spending alter your retirement date—reveals which assumptions matter most.

Core Assumptions: The Inputs Tab

Every retirement projection rests on five pillars of assumptions. Create an "Inputs" tab (or section) where you define these clearly. Users should be able to change one number and watch the entire projection shift.

1. Starting Portfolio Balance

What's your net worth today? Include stocks, bonds, savings, real estate equity, and business value. Exclude debt (count that separately in your "needs at retirement" calculation). Use a single cell:

Starting Balance: $250,000

2. Annual Contribution

How much will you invest each year until retirement? Be realistic. This might be:

• 15% of gross salary
• A fixed $20,000 per year
• Increasing contributions as you get raises (adjust annually in the projection)

Use a cell or range:

Annual Contribution: $30,000

If contributions vary by year (increasing over time), you can instead list them explicitly in your projection tab, year by year.

3. Expected Annual Return

This is your long-term investment return before inflation. Historical S&P 500 returns average around 10% nominal, 7% real (after inflation). Conservative projections use 6–7%; aggressive ones use 8–10%. For a balanced portfolio, 7% is reasonable.

Expected Annual Return: 7.0%

4. Inflation Rate

Inflation erodes purchasing power. Historical U.S. inflation averages 3%. The Federal Reserve (https://www.federalreserve.gov) targets 2%. Use 3% unless you have specific expectations.

Inflation Rate: 3.0%

5. Annual Retirement Spending

How much (in today's dollars) will you need per year in retirement? Common rules of thumb: 70–80% of pre-retirement income, or calculate from your current lifestyle. Use a realistic figure:

Annual Retirement Spending (Today's Dollars): $80,000

This spending will grow with inflation each year.

Setting Up the Projections Tab: Year-by-Year Detail

Flowchart

Now create a "Projections" tab with columns for each year from today through age 95 (or whenever you're comfortable assuming life ends). Here's the structure:

Year	Age	Starting Balance	Annual Contribution	Investment Return	Spending (if retired)	Ending Balance	Status
2025	45	$250,000	$30,000	$17,500	$0	$297,500	Working
2026	46	$297,500	$30,000	$22,825	$0	$350,325	Working
...	...	...	...	...	...	...	...
2040	60	$1,250,000	$30,000	$89,900	$0	$1,369,900	Working
2041	61	$1,369,900	$0	$95,893	$82,474	$1,383,319	Retired

Let's build this in Excel or Google Sheets:

Column A (Year): 2025, 2026, 2027, etc.

Column B (Age): Calculate as =starting_age + (year - start_year). Or if you're 45 in 2025, use =45 + (A2 - 2025).

Column C (Starting Balance): The prior year's ending balance. In row 2, reference your Inputs tab: =Inputs!C2 (your starting balance). In row 3, use =D2 (prior row's ending balance).

Column D (Annual Contribution): Reference your Inputs tab, but allow for changes. If contributions stop at retirement, use:

=IF(Status="Retired", 0, Inputs!C3)

Or if retirement is based on age:

=IF(B2 >= retirement_age, 0, Inputs!C3)

Column E (Investment Return): Calculate as =C2 * Inputs!C4, where C4 is your return rate. This assumes you're earning the full-year return on the starting balance. (For more precision, you could average starting and ending balance, but this simplification is standard.)

Column F (Spending): If retired, use:

=IF(B2 >= retirement_age, Inputs!C5 * (1 + Inputs!C5)^(B2 - retirement_age), 0)

This grows your spending with inflation each year of retirement. The (1 + inflation)^years factor compounds inflation.

Column G (Ending Balance):

=C2 + D2 + E2 - F2

Starting balance + contribution + investment return - spending.

Column H (Status):

=IF(B2 >= retirement_age, "Retired", "Working")

Finding Your Retirement Date

Once you've set up the year-by-year projections, add a simple calculation in your Summary tab:

Retirement Date: (lookup the first year where Status = "Retired")

Or use a formula to find it:

=INDEX(A:A, MATCH("Retired", H:H, 0))

This gives you the year you can retire. You can also add:

Current Age: 45
Retirement Age: =Retirement Year - Start Year
Years Until Retirement: =Retirement Age - Current Age

Handling Variable Contributions and Life Changes

The real world isn't constant. You'll get raises, switch jobs, have kids, care for parents. Your spreadsheet should reflect this:

Option 1 (Simple): List contributions explicitly in column D for each year. Don't reference a formula; type in the expected contribution.

Year | Contribution
2025 | $30,000
2026 | $32,000 (raise)
2027 | $32,500 (small raise)
2028 | $35,000 (promotion)
...

Option 2 (Dynamic): Use a % increase. If you expect 2% annual raises:

=D1 * 1.02

Option 3 (Mixed): Allow manual overrides. In each cell:

=IF(D2 <> "", D2, D1 * 1.02)

If you manually enter a contribution, it uses that. Otherwise, it applies a 2% raise formula.

Similarly, handle major life events:

• Year you have a child: Add childcare costs to retirement spending.
• Year you pay off a mortgage: Reduce retirement spending.
• Year you receive an inheritance: Add a one-time contribution.

Testing Sensitivity: The "What If" Section

The true power of a spreadsheet emerges when you test assumptions. Add a "Sensitivity" section in your Summary tab:

Base case:

Retirement Year (7% return, $80k spending): 2041

Downside case (pessimistic):

Return drops to 5%: Retirement Year = 2045
Spending rises to $100k: Retirement Year = 2044
Return = 5% AND Spending = $100k: Retirement Year = 2049

Upside case (optimistic):

Return achieves 9%: Retirement Year = 2039
Spending drops to $60k: Retirement Year = 2038
Return = 9% AND Spending = $60k: Retirement Year = 2035

Create separate columns or tabs for each scenario. Then ask: How sensitive is my retirement date to each assumption? If a 1% change in return shifts your retirement by 5 years, that assumption is critical—scrutinize it. If a $5k change in spending barely moves the needle, it's less important.

Handling Taxes and Fees

Your spreadsheet so far assumes gross returns. Reality includes taxes and investment fees:

Investment fees: Most index funds charge 0.03–0.10% annually. Actively managed funds charge 0.50–2.00%. Subtract this from your return assumption.

Example: If you expect 7% gross returns and pay 0.20% in fees, your net return is 6.80%.

Taxes: This is complex. In a traditional 401(k) or IRA, you don't pay taxes until withdrawal. In a taxable brokerage account, you owe taxes annually on dividends and capital gains.

A simplified approach:

• Assume your return projection is already net of taxes (historical averages often reflect this).
• If you're saving in a 401(k) or Roth IRA, minimal taxes apply during the accumulation phase.
• In retirement, taxes will reduce your spending power, so increase your retirement spending estimate by 20–30% to account for federal and state income taxes.

More precisely, use the IRS website (https://www.irs.gov) to model your specific tax situation, but for a rough projection, conservative estimates are safer than overly optimistic ones.

Validating Your Spreadsheet: Sanity Checks

Before trusting your projection, run these tests:

Test 1: Confirm the math. Pick a year mid-projection. Manually calculate: starting balance × (1 + return) + contribution - spending. Does it match your spreadsheet's ending balance? If not, you have a formula error.

Test 2: Test an extreme scenario. Set contributions to $0 and return to 0%. Your balance should stay flat (if you're retired and spending, it should decline). Set spending to $0 and contribution to $100,000, and your balance should grow by the annual return on a large base. Obvious? Yes. But errors hide in corner cases.

Test 3: Compare to a published calculator. Run your assumptions through FIRECalc (https://www.firecalc.com) or cFIREsim. If your result is dramatically different, debug. Small differences (±1–2 years) are normal; large ones suggest an error.

Test 4: Sense-check the retirement spending. If you're projecting $80,000/year in retirement, is that realistic for your desired lifestyle? Will you have paid off your house by then? Do you have a pension? Are healthcare costs included? Adjust if necessary.

Real-World Example: Sarah's Path to Financial Independence

Sarah is 35, earns $100,000/year, and has $150,000 in investments. She expects to earn 7% annually, contribute $20,000 per year (20% of gross), and wants to retire on $70,000/year (today's dollars).

Year 1 (Age 35):

• Starting: $150,000
• Contribution: $20,000
• Return: $150,000 × 0.07 = $10,500
• Spending: $0 (still working)
• Ending: $180,500

Year 2 (Age 36):

• Starting: $180,500
• Contribution: $20,000
• Return: $180,500 × 0.07 = $12,635
• Spending: $0
• Ending: $213,135

Her spreadsheet continues this pattern. By year 15 (age 50), her balance has grown to approximately $730,000. At year 22 (age 57), she crosses $1.2 million, and her investment returns alone ($84,000) exceed her retirement spending ($70,000). She can retire at 57.

Testing sensitivity: If returns drop to 5%, she retires at 60. If she increases contributions to $25,000/year, she retires at 55. If she saves for only 20 years (raising her retirement age from 57 to 59), she still retires before 60.

Common Mistakes to Avoid

Mistake 1: Using nominal return without adjusting for inflation. If you assume 7% nominal return, your retirement spending grows with inflation. The math works, but be consistent. Don't assume 7% "real" (inflation-adjusted) return; that's too optimistic.

Mistake 2: Forgetting about healthcare. Medicare begins at 65, but early retirees need coverage until then. Budget $200–$500/month for health insurance if retiring before 65.

Mistake 3: Underestimating retirement spending. People often spend more in early retirement (travel, hobbies) and less later (less active). Use a conservative estimate.

Mistake 4: Ignoring sequence-of-returns risk. Your spreadsheet assumes consistent 7% annual returns. Reality is lumpy: some years up 20%, some years down 15%. A market crash early in retirement is far worse than one late in your working years. Stress-test downside scenarios.

Mistake 5: Setting retirement spending in today's dollars but forgetting to inflate it. Use a formula that grows spending by inflation each year, as shown above.

Advanced: Monte Carlo Simulation

For those ready to level up, Monte Carlo simulation tests thousands of random market return sequences to find the probability of success. This is beyond a basic spreadsheet but doable in Google Sheets with RANDBETWEEN and iteration, or in Excel with Data Table simulation. We'll explore this in the next article.

FAQ

Q: Should I include real estate equity in my retirement projection? A: Yes, but carefully. Include it as part of your starting balance. In retirement, you might downsize, generating a lump sum, or take a reverse mortgage. Model whichever applies to your plan.

Q: What if I have a pension? A: Reduce your retirement spending estimate. If your pension is $40,000/year and you need $80,000, you only need portfolio withdrawals of $40,000. Adjust your spending column accordingly.

Q: How do I handle inflation in the spending column? A: Use the formula shown above: multiply retirement spending by (1 + inflation rate)^years. This grows your annual need to account for rising prices.

Q: What's a reasonable withdrawal rate in retirement? A: The 4% rule suggests you can withdraw 4% of your portfolio in year one of retirement, then adjust for inflation annually, with a high probability of not running out of money over 30 years. Your spending of $80,000 on a $2 million portfolio is 4%; that's safe historically.

Q: Should I use different returns for stocks and bonds? A: If you have a 60/40 portfolio (60% stocks, 40% bonds), use a blended return. Historically, stocks return ~10%, bonds ~5%; a 60/40 mix yields 8%. Refine this based on your actual asset allocation.

Q: How often should I update my projection? A: Annually, especially after a major market move or life change. Every few years, validate against published calculators or consult a professional to ensure you're on track.

Related Concepts

Understanding the complete retirement-planning ecosystem:

• Safe withdrawal rate: The percentage of your portfolio you can withdraw annually in retirement without running out of money.
• Sequence of returns risk: The risk that poor market returns early in retirement deplete your portfolio faster than expected.
• Tax-efficient withdrawal strategy: Choosing which accounts (pre-tax, Roth, taxable) to draw from first to minimize taxes.
• Longevity risk: The risk of living longer than you plan, exhausting savings.
• Inflation protection: Using TIPS (Treasury Inflation-Protected Securities) or stocks to hedge inflation risk.

Summary

A retirement projection spreadsheet transforms abstract financial goals into concrete, testable predictions. By combining the FV formula, year-by-year detail, variable contributions, and tax/fee adjustments, you create a dynamic model that reveals precisely when you can retire and how sensitive that date is to your core assumptions. The beauty of building it yourself is not just cost (free), but control: you understand every line, can modify it instantly, and can run scenarios that generic calculators cannot.

Whether you're 25 and want to know when you can quit your job, or 55 and want to confirm you can afford to retire next year, this spreadsheet is your personalized, always-current financial roadmap.

Next

→ Monte Carlo Simulation in a Spreadsheet