A Simple Long-Horizon Projection Template
The best financial plan is one you build and maintain yourself. A spreadsheet that you created, understand, and can modify is infinitely more useful than a black-box calculator or a financial plan gathering dust on your shelf. This article provides a step-by-step template for building a simple 30-year wealth projection model. You can build it in Google Sheets, Excel, or any spreadsheet tool. The logic is portable; the format matters less than the discipline of building and maintaining it.
Quick definition
A long-horizon projection template is a spreadsheet model that projects your wealth forward decade by decade, accounting for contributions, investment returns, withdrawals, taxes, and inflation. It answers: How much will I have at age 50? At 60? At 70? Will my money run out? What happens if returns are 2% lower than expected? It is not a prediction (markets are unpredictable), but a coherent stress-tested assumption set that clarifies what future looks like if your plan holds.
Key takeaways
- Build your own model: Understanding how a model works (because you built it) is more valuable than trusting someone else's output.
- Simplicity scales: A two-sheet model (assumptions + projection) is superior to a 50-tab complexity that no one will update.
- Scenarios are the insight: The base case is useful; comparing it to optimistic and pessimistic cases is where real clarity emerges.
- Input controls output: Spending 80% of your effort validating assumptions and 20% running the model (not vice versa) produces better decisions.
- Monthly updates are low-friction: If you update your actual balance monthly and one or two assumptions quarterly, the model stays current with minimal effort.
The Two-Sheet Structure
Sheet 1: Assumptions All model inputs live here: starting balance, annual contributions, investment returns, withdrawal amounts, inflation, tax rates, life expectancy. By isolating assumptions from calculations, you can swap scenarios (optimistic vs. pessimistic) with a single cell change.
Sheet 2: Projection Year-by-year calculations that answer: given the assumptions, what does wealth look like at each future point? This sheet is built on formulas that reference Sheet 1, so changes to assumptions flow through automatically.
Sheet 1: Assumptions (The Foundation)
Create a clear structure for all inputs:
PERSONAL ASSUMPTIONS Starting age: 35 Life expectancy (planning to): 95 Spouse (Y/N): N Children: 0
BALANCE ASSUMPTIONS Current investments: $250,000 Current cash reserve: $30,000 Current home equity: $150,000 (Adjust based on asset class)
INCOME ASSUMPTIONS Current salary: $100,000 Annual salary growth: 2.5% Other annual income: $0 Years until retirement: 30
CONTRIBUTION ASSUMPTIONS Annual contribution (working): $20,000 Annual contribution (retired): $0 Increase contributions with: Salary growth (2.5%)
WITHDRAWAL ASSUMPTIONS Annual retirement withdrawal: $0 (Not retired yet) Withdrawal amount (retirement): $60,000 (planning figure) Increase withdrawals with: Inflation (2.5%)
INVESTMENT ASSUMPTIONS Expected annual return: 7.0% Standard deviation (volatility): 12% Expected inflation: 2.5%
TAX ASSUMPTIONS Current marginal tax rate: 24% Retirement marginal tax rate: 22% Long-term capital gains rate: 15% Tax-loss harvesting (annual): 2% of gains
### Building the Assumptions Sheet
Keep formatting simple and scannable. Group related assumptions. Use clear labels. Most importantly, separate assumptions (cell references) from calculations. Never hard-code a number into a formula that could change—always reference an assumption cell.
**Example:** If your projection calculates annual salary, it should reference the "Annual salary growth" cell, not hard-code 2.5%. When you revisit your plan next year and decide salary growth should be 3%, you change one cell, and the entire projection updates automatically.
## Sheet 2: Projection (The Engine)
The projection sheet is structured as a table with years down the rows and key metrics across the columns:
| Year | Age | Salary | Contribution | Beginning Balance | Investment Return | Withdrawals | Ending Balance | Inflation Factor |
|------|-----|--------|---------------|-------------------|-------------------|-------------|-----------------|-----------------|
| 1 | 35 | 100000 | 20000 | 280000 | 19600 | 0 | 319600 | 1.000 |
| 2 | 36 | 102500 | 20500 | 319600 | 22372 | 0 | 362472 | 1.025 |
| 3 | 37 | 105063 | 21025 | 362472 | 25373 | 0 | 408870 | 1.051 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 30 | 65 | 181860 | 0 | 1420000 | 49400 | 60000 | 1409400 | 1.809 |
Let's break down each column:
### Year and Age
Simply increment from your starting point. Year 1 is age 35, Year 2 is age 36, etc.
### Salary
Formula: `=Previous Year Salary * (1 + Annual Salary Growth Rate)`
- Year 1: Hard-code current salary (e.g., $100,000)
- Year 2: `=D2 * (1 + $salary_growth_rate)`
- Year 3+: Same formula, copy down
### Contribution
Formula: `=IF(Age <= Retirement Age, Salary * Contribution Rate, Retirement Contribution)`
- While working: contribution grows with salary
- After retirement: contribution becomes $0 (or a fixed amount if you're adding inherited money)
### Beginning Balance
The portfolio balance at the start of the year.
- Year 1: Reference your starting balance assumption
- Year 2+: `=Previous Year Ending Balance`
### Investment Return
Formula: `=Beginning Balance * Expected Return Rate`
- Year 1: `=$Beginning_Balance * $Expected_Return`
- Year 2+: `=Beginning Balance in Year 2 * Expected Return`
This is your simplification for base-case modeling. In reality, returns vary year-to-year, but for a baseline projection, using the expected average return is reasonable. (For tail-risk scenarios, you'd modify this cell to return lower values.)
### Withdrawals
Formula: `=IF(Age >= Retirement Age, Prior Year Withdrawal * (1 + Inflation Rate), 0)`
- Before retirement: $0
- After retirement: withdrawals grow with inflation
### Ending Balance
Formula: `=Beginning Balance + Contribution + Investment Return - Withdrawals - Taxes`
Taxes are important but complex. For simplicity, you can:
1. Ignore them initially (conservative, since taxes reduce ending balance)
2. Use an effective tax rate and subtract it from returns
3. Separate taxable and tax-advantaged accounts
### Inflation Factor
Formula: `=(1 + Inflation Rate) ^ Year`
This shows how purchasing power erodes. In Year 30 at 2.5% inflation, $1 = $0.55 in today's dollars. Use this to convert future dollar amounts back to today's money for clarity.
## Building the Projection: Step by Step
**Step 1: Create the header row**
Label columns: Year, Age, Salary, Contribution, Beginning Balance, Investment Return, Withdrawals, Ending Balance, Inflation Factor.
**Step 2: Create 30 rows (or however many years you're projecting)**
Years 1–30, ages 35–65, etc.
**Step 3: Fill in Year and Age**
Simple incrementing.
**Step 4: Build the Salary column**
- Year 1: `=Assumptions!Salary`
- Year 2: `=Year1_Salary * (1 + Assumptions!Salary_Growth)`
- Copy down 30 times
**Step 5: Build Contribution**
- Year 1–30: `=IF(Age <= Retirement_Age, Salary * Contribution_Rate, Assumptions!Retirement_Contribution)`
- Copy down
**Step 6: Build Beginning Balance**
- Year 1: `=Assumptions!Starting_Balance`
- Year 2–30: `=Prior_Year_Ending_Balance`
- Copy down
**Step 7: Build Investment Return**
- All years: `=Beginning_Balance * Assumptions!Expected_Return`
- Copy down
**Step 8: Build Withdrawals**
- Years 1–30: `=IF(Age < Retirement_Age, 0, Prior_Year_Withdrawal * (1 + Assumptions!Inflation))`
- Copy down
**Step 9: Build Ending Balance**
- All years: `=Beginning_Balance + Contribution + Investment_Return - Withdrawals`
- Copy down
**Step 10: Build Inflation Factor**
- All years: `=(1 + Assumptions!Inflation) ^ Year`
- Copy down
## Adding Scenario Columns
The power of a spreadsheet is comparing scenarios. Add three versions of the projection:
**Scenario A: Base Case** (7% returns, 2.5% inflation, contributions as planned)
**Scenario B: Optimistic** (9% returns, 2% inflation, 25% higher contributions)
**Scenario C: Pessimistic** (4% returns, 4% inflation, 10% lower contributions)
Build each scenario using the same formulas, but with different assumption cells. Compare the results:
- Base case ends with $1.4M at age 65
- Optimistic case ends with $1.8M at age 65
- Pessimistic case ends with $1.1M at age 65
This comparison reveals which assumptions matter most. If the base case lands comfortably above your retirement goal ($1M), you're in good shape. If the pessimistic case puts you below your goal, you need either a higher savings rate, a later retirement date, or lower expectations.
## Real-World Example: A 35-Year-Old Building a 30-Year Plan
**Assumptions:**
- Current age: 35, planning to age 65
- Current portfolio: $250K
- Current salary: $100K, growing 2.5% annually
- Annual contributions: 20% of salary ($20K initially)
- Expected return: 7%
- Inflation: 2.5%
- Retirement withdrawal needed: $60K annually
**Projection (5-year snapshot):**
| Year | Age | Salary | Contribution | Beg. Balance | Return | Withdrawals | End Balance |
|------|-----|--------|---------------|--------------|--------|-------------|-------------|
| 1 | 35 | 100000 | 20000 | 250000 | 17500 | 0 | 287500 |
| 2 | 36 | 102500 | 20500 | 287500 | 20125 | 0 | 328125 |
| 3 | 37 | 105063 | 21025 | 328125 | 22969 | 0 | 372119 |
| 4 | 38 | 107689 | 21538 | 372119 | 26048 | 0 | 419705 |
| 5 | 39 | 110381 | 22076 | 419705 | 29379 | 0 | 471160 |
By Year 30 (age 65):
- Projected portfolio: $1.42M
- Retirement needs: $60K, growing to ~$104K in nominal dollars (purchasing power constant in today's dollars)
- Safe withdrawal rate: 4% of $1.42M = $56,800 (slightly tight)
**Decision:** The plan works, but barely. Options:
1. Increase contributions by $3,000/year (from $20K to $23K)
2. Extend working years to 67 (add $200K+ to portfolio)
3. Accept a slightly tighter withdrawal rate (3.8% = $53,960)
4. Aim for higher returns via improved investment strategy (unlikely, best to not count on)
This is the kind of clarity a projection template provides. Without modeling, you'd be guessing.
## Maintaining Your Model (Monthly and Quarterly)
Once built, maintenance is straightforward:
**Monthly (5 minutes):**
- Update "Current Balance" assumption with your actual portfolio balance
- Check that the projection looks reasonable
- Note any significant market moves
**Quarterly (15 minutes):**
- Update salary assumption if you received a raise
- Verify contributions are on track
- Note any life changes (health, family, career moves)
**Annually (2 hours):**
- Full review (as described in the prior chapter)
- Update all assumptions based on past year's data
- Add a new row (Year 31) extending your projection
- Compare projections to actual results (did you earn 7%?)
- Adjust assumptions if needed
## Building Tail-Risk Scenarios
Once your base case is built, add stress-test scenarios:
**Bear Market Scenario (Years 1–5):**
Change expected return to -1% for years 1–5, then 8% for years 6–30.
Does your plan survive?
**Inflation Scenario:**
Change inflation to 4%, expected return to 4% (slower growth in real terms).
Can you still meet goals?
**Income Loss Scenario:**
Change salary growth to 0% in year 3–4 (simulate job loss/career disruption).
Does the plan hold?
These scenarios reveal vulnerabilities before they occur. If your plan fails a stress test, you have time to adjust (higher savings, later retirement, lower expectations).
## A Projection Template Visualization
```mermaid
graph TD
A["Projection Template"] --> B["Sheet 1: Assumptions"]
A --> C["Sheet 2: Projection"]
B --> B1["Personal Data<br/>Age, life expectancy"]
B --> B2["Income & Contribution<br/>Salary, growth rate"]
B --> B3["Investment Returns<br/>Expected %, volatility"]
B --> B4["Withdrawals<br/>Retirement amount, inflation"]
B --> B5["Tax Rates<br/>Marginal rate, LTCG rate"]
C --> C1["Year-by-Year<br/>Calculation"]
C --> C2["Ending Balance<br/>at Each Milestone"]
C --> C3["Base Case<br/>Projection"]
C --> C4["Optimistic<br/>Case"]
C --> C5["Pessimistic<br/>Case"]
C3 --> D["Goal Achieved?<br/>Decision Point"]
C4 --> D
C5 --> D
D --> E["If yes: maintain plan"]
D --> F["If no: adjust savings<br/>or timeline"]
Common Mistakes
Mistake 1: Over-complexity A 50-tab spreadsheet with sophisticated tax calculations is less useful than a 2-tab model you understand and update. Start simple. Add complexity only when it materially changes decisions.
Mistake 2: Hard-coding numbers instead of referencing assumptions Never embed 7.0% return directly in a formula. Always reference an "Expected Return" cell. This makes scenario testing trivial (change one cell) instead of tedious (update 30 cells).
Mistake 3: Ignoring taxes Taxes reduce returns by 20–40% depending on your situation. Even a rough estimate (subtract 20% of gains annually) is better than ignoring taxes. Your real portfolio returns net of taxes, not gross returns.
Mistake 4: Not updating the model A plan built in 2024 but never updated is worthless by 2025 (new income data, market performance, life changes). Commit to quarterly reviews and annual updates. The time investment is minimal; the value is immense.
Mistake 5: Treating the projection as a prediction This model is not a prediction. Markets are too unpredictable. It's a stress-tested assumption set that clarifies: given what I believe, what does the future look like? When reality diverges (returns are 3% instead of 7%, or you inherit money, or you lose a job), you update assumptions and re-project. The model adapts; reality doesn't wait for the model.
FAQ
What if I don't know what my expected return should be?
Use historical averages: 10% for a 100% stock portfolio, 5% for a 100% bond portfolio, 7% for a 60/40 portfolio. These are long-term (30+ year) averages; short-term results vary widely. If you want to be conservative, use 6% for a 60/40 portfolio.
Should I include my home equity in the projection?
Optionally. If your home is a long-term residence (not for sale), home equity is not liquid and shouldn't affect retirement planning. If you plan to downsize (sell the home, buy cheaper, invest the difference), then yes, include it. Keep it separate from your investment portfolio for clarity.
What if I have a pension or Social Security?
Treat them as income. In your retirement withdrawal calculations, use: Needed_Withdrawal = Retirement_Lifestyle_Cost - Pension - Social_Security - Investment_Income. If you expect $60K in annual spending, and you'll have $30K from Social Security, you only need $30K from your portfolio.
What if my contributions change (bonuses, raises, career changes)?
Update the assumption. If your salary grows 2.5% but you receive a $10K annual bonus, split it: 2.5% growth + $10K added each year. Your formula becomes =Salary * (1 + Growth) + Bonus.
How often should I rebuild the model?
Never. Update assumptions; don't rebuild. A model you've used and understood for 10 years is more valuable than a new model built from scratch. Continuity matters.
Should I model different scenarios for each person in a couple?
Yes, model the primary earner's scenario (main income driver) and then check if combined income and assets achieve shared goals. If both incomes are important, consider modeling the higher-earner scenario, then adding the lower-earner's income and assets as additional safety margin.
What if market returns are negative (bear market)?
Change the expected return to negative for those years. Model: Year 1 (market down 20%, return = -20%), Year 2 (market recovery, return = 15%), Year 3+ (normal 7%). Run the projection. If you survive the bear market scenario, you're resilient.
Related Concepts
- Long-horizon investing principles: The foundation of the assumptions you feed into the model.
- Tail risk and stress testing: Models without tail-risk scenarios are incomplete; scenarios without models are too abstract.
- Annual plan reviews: The model is reviewed and updated during annual reviews; it's the tool that makes reviews concrete.
- Asset allocation and expected returns: Your assumed expected return depends on your asset allocation; mismatches here cause model failures.
Summary
A long-horizon projection template is not a prediction tool—it's a coherence test. Does your plan actually achieve your goals, given reasonable assumptions? Does it survive tail-risk scenarios? What assumptions matter most, and which are irrelevant? Where are the vulnerabilities?
Building the model yourself (versus using someone else's) forces you to examine every assumption. Do you really expect 7% returns? Can you tolerate the sequence of returns implicit in early retirement? Will your income growth assumptions hold? Will your expenses actually stay constant in retirement?
These are hard questions. The spreadsheet forces you to answer them explicitly, not hide behind vague plans. Most plans fail not because the assumptions are wrong, but because the assumptions were never examined in the first place. Your template is the examination tool.
Start simple. Two sheets, 30 years, basic formulas. Update it once a year. You'll know your financial future better than most people, and you'll adapt faster when reality diverges from expectations.
Next
The 40-to-50-year investor case — Why the compounding effects intensify as your time horizon lengthens, and how to think about ultra-long-term investing.
Authority links: