Cashflow Modelling the Portfolio
Cashflow Modelling the Portfolio
A portfolio isn't static. You're contributing, earning returns, and facing unexpected changes. Modelling the future tells you whether your plan survives reality.
Key takeaways
- A simple cashflow model projects portfolio value forward 10–30 years by combining contributions, expected returns, and volatility
- The model answers: "At my current savings rate and expected return, will I reach my goals?"
- Models should include best case, base case, and worst case scenarios
- Uncertainty compounds over long periods; 30-year projections have wide ranges but still reveal the sensitivity to your choices
- Update your model annually to confirm you're on track or adjust contributions/allocation
The three components of future portfolio value
Your portfolio 10 years from now depends on:
- Starting value: What you have today (e.g., $500,000)
- Contributions: What you add each month or year (e.g., $2,000/month = $24,000/year)
- Returns: Growth from markets (e.g., 7% annual average)
The formula (simplified) is:
Future Value = (Starting Value × (1 + Return)^Years) + (Annual Contribution × (((1 + Return)^Years - 1) / Return))
In plain English: your starting balance grows at your expected return rate each year, and your contributions also grow at that same rate.
Building a simple model: the spreadsheet approach
Create a spreadsheet with these columns:
| Year | Beginning Balance | Annual Contribution | Total to Invest | Expected Return % | Year-End Balance |
|---|---|---|---|---|---|
| 2025 | $500,000 | $24,000 | $524,000 | 7% | $560,280 |
| 2026 | $560,280 | $24,000 | $584,280 | 7% | $624,980 |
| 2027 | $624,980 | $24,000 | $648,980 | 7% | $693,809 |
| 2028 | $693,809 | $24,000 | $717,809 | 7% | $767,736 |
| 2029 | $767,736 | $24,000 | $791,736 | 7% | $847,017 |
| 2030 | $847,017 | $24,000 | $871,017 | 7% | $931,848 |
The formula for Year-End Balance is:
(Beginning Balance + Annual Contribution) * (1 + Return Rate)
For Year 2025:
($500,000 + $24,000) * 1.07 = $560,280
Drag this down for 10, 20, or 30 years. By 2035 (10 years), you've grown from $500,000 to $1.1 million (rough projection). By 2055 (30 years), you'd reach roughly $3.2 million.
Adding volatility: best case, base case, worst case
Expected returns are averages. Real markets vary. A professional model includes scenarios:
Base Case (7% annual return):
- This is your expectation based on historical averages.
- 55% stock / 20% international / 25% bond portfolio averages 7% long-term.
Best Case (9% annual return):
- Markets perform above historical average.
- Stock allocation to 60/25 or growth is better than expected.
- In 30 years, $500k becomes $5.1 million (vs $3.2M in base case).
Worst Case (5% annual return):
- Markets underperform (below-average decade).
- Or your allocation is more conservative than assumed (higher bond allocation).
- In 30 years, $500k becomes $2.2 million.
The range: $2.2M to $5.1M in 30 years. Wide range, but the base case ($3.2M) is the most likely.
Here's a three-scenario model:
| Year | Base Case (7%) | Best Case (9%) | Worst Case (5%) |
|---|---|---|---|
| 2025 | $560,280 | $566,360 | $552,700 |
| 2030 | $931,848 | $1,047,289 | $830,159 |
| 2035 | $1,532,675 | $1,825,844 | $1,293,607 |
| 2040 | $2,509,685 | $3,157,289 | $2,007,937 |
| 2045 | $4,112,356 | $5,452,482 | $3,109,531 |
| 2050 | $6,746,486 | $9,404,887 | $4,831,013 |
This table shows:
- By 2050 (25 years), you're between $4.8M (worst) and $9.4M (best), with $6.7M as the base.
- The range widens each year because growth compounds.
- Even in the worst case, you grow substantially.
Why this matters: testing your goals
Suppose your goal is to retire in 25 years with $4 million.
Looking at the table:
- Base case (7%): You hit $4.1M by 2045. ✓ Goal achieved.
- Worst case (5%): You hit $3.1M by 2045. ✗ Goal missed.
This tells you: your plan works if markets return 7% (historical average) or better, but it's at risk if returns are consistently below 7%.
How do you de-risk this?
- Increase contributions: If you save $3,000/month instead of $2,000/month, the worst case improves.
- Extend timeline: Retire at 26 years instead of 25, giving more time to compound.
- Reduce goal: You don't need $4 million; $3.5 million would be comfortable, which the worst case reaches.
The model is a planning tool. It shows you the sensitivity of your goals to the variables you control (contributions, timeline) and those you don't (returns).
Building the model in Excel/Sheets
Here's a template you can adapt:
Starting Portfolio Value: $500,000
Annual Contribution: $24,000
Expected Annual Return: 7%
Year | Beginning Balance | Contribution | Investment Total | Return | Ending Balance
1 | 500000 | 24000 | 524000 | 36680 | 560680
2 | 560680 | 24000 | 584680 | 40928 | 625608
...
The "Return" column is: Investment Total × Expected Return Rate.
Or use the Excel formula directly:
=Previous Year Balance * (1 + Return Rate) + Annual Contribution
For 30 years, this quickly shows your trajectory.
Sensitivity analysis: what if X changes?
Once you have a base model, ask "what if" questions:
Question 1: What if I can only save $1,000/month instead of $2,000/month?
- Base case result drops ~15–20% because contributions are half.
- In 30 years: $3.2M becomes $2.4M.
Question 2: What if my expected return is 6% instead of 7%?
- Base case result drops ~20% because each year's growth is lower.
- In 30 years: $3.2M becomes $2.6M.
Question 3: What if I can work 2 more years before retiring?
- Base case result increases by roughly 15% because of the additional contributions and years of compounding.
- In 30 years: $3.2M becomes $3.7M.
These sensitivities show you which levers matter most:
- Increasing contributions by 50% (from $2k to $3k/month) improves the outcome by ~15%.
- Increasing time horizon by 2 years improves the outcome by ~15%.
- Increasing expected return by 1% (from 7% to 8%) improves the outcome by ~20%.
All three levers are roughly equally powerful. But contributions and time horizon are in your control. Return rates are not.
Modelling with Monte Carlo (optional advanced approach)
If you want to be more sophisticated, use Monte Carlo simulation. Instead of assuming 7% every year, you assume returns vary annually with standard deviation.
Example:
- Expected return: 7%
- Standard deviation: 12% (typical for a 55/20/25 portfolio)
- Simulation: Run 1,000 random scenarios where each year's return is drawn from a distribution with 7% mean and 12% volatility.
The result: Instead of "you'll have $3.2M," you get "there's a 75% probability you'll have between $2.5M and $4.2M."
This is more realistic because it acknowledges that volatility matters. But it's also more complex.
For most people, the three-scenario model (best, base, worst) is sufficient and easier to understand.
The modelling decision tree
Real example: the $500k starting portfolio
You have:
- Starting balance: $500,000
- Monthly contribution: $2,000 ($24,000/year)
- Target: $4 million in 25 years for retirement
- Current age: 40, retirement age: 65
Base case (7% return):
- Year 25 (age 65): $4.1 million ✓
Worst case (5% return):
- Year 25: $3.1 million (miss goal by $900k)
Options:
- Increase contribution to $2,700/month → worst case becomes $3.6M (closer to goal)
- Work to age 67 instead of 65 → worst case becomes $3.8M (closer to goal)
- Accept the risk → retire at 65 with ~$3.1M and adjust spending downward
You decide option 2 is best: work 2 more years. Now both base and worst cases hit the $4M goal.
When to remodel
Remodel your portfolio projection:
- Annually: Each January, plug in actual returns from the past year, adjust contribution if your income changed, and see if you're on track.
- After major life changes: Job change, inheritance, major expense, marriage, divorce, health crisis.
- When markets change dramatically: A 30% crash in one year doesn't derail a 30-year plan, but it's worth checking to confirm.
- When you revise goals: If your goal changes from $4M to $5M, obviously you need to remodel.
In most years, you'll find: "I'm on track, no changes needed." That's the goal. The model is a confirmation tool, not a constant recalculation.
Related concepts
Next
Cashflow modelling projects the future based on your current plan. But your plan—your investment policy statement—is a living document. It should evolve only when your life fundamentally changes. In the final article of this chapter, we'll revisit the IPS and understand when to keep it and when to update it.