Template · Finance

Retirement savings projection: contributions, growth and withdrawals

Year-by-year balance from saving to retirement and withdrawals, with five return and saving scenarios.

Downloads are included in the $19.85 yearly membership. Sign in

XLSXCSVODSXMLNumbers

	A	B	C	D	E
1	Retirement savings projection
2	Example data — replace the blue input cells with your own.
3
4	BALANCE AT RETIREMENT ($)	BALANCE IN TODAY'S DOLLARS		MONEY LASTS UNTIL AGE	MONTHLY SAVING NEEDED ($)
5	$1,493,037	$711,795		87	$1,455
6	Nominal dollars at the retirement age	Deflated at the inflation rate		Last age with money at the start of the year	Monthly saving from today to reach the target
7
8	Target at retirement
9	Years to retirement	30
10	Years of withdrawals	31
11	Growth factor q = (1 + inflation) / (1 + return)	0.9809
12	PV at retirement of desired income ($)	$2,963,537	Withdrawals at the start of each year, inflated, discounted at the return after retirement.
13	PV at retirement of other income ($)	$1,085,695
14	Target balance at retirement ($)	$1,877,842	Target = PV of desired income less PV of other income, at the retirement age.
15	Target balance in today's dollars ($)	$895,247
16	Monthly return before retirement	0.49%

Showing the first 16 of 54 rows and 6 of 6 columns. Cells with formulas show the formula on hover.

	A	B	C
1	Retirement inputs
2	Blue cells are inputs. Rates are annual; ages are whole years; money is in dollars.
3
4	Ages and balance
5	Current age	35	Whole years.
6	Retirement age	65	Age at which saving stops and withdrawals start.
7	Plan-to age	95	Last age in the projection. The table covers up to 80 years.
8	Current balance ($)	$80,000	Retirement account balance today, before any growth.
9	Current salary ($ per year)	$85,000	Gross salary today. Salary grows at the rate below.
10	Salary growth (% per year)	3.0%	Annual raise used for future contributions.
11
12	Contributions
13	Employee contribution (% of salary)	8.0%	Share of salary saved by the employee.
14	Employer match (% of employee contribution)	50%	Employer adds this share of the employee contribution, up to the cap below.
15	Match cap (% of salary)	6.0%	The match applies to the employee rate up to this share of salary. Example: 50% of the first 6%.
16

Showing the first 16 of 35 rows and 3 of 3 columns. Cells with formulas show the formula on hover.

	A	B	C	D	E	F	G	H	I	J	K	L
1	Year-by-year projection, from current age to plan-to age
2	One row per age. Contributions while saving, withdrawals while retired, growth on the balance at the phase return.
3	Bar scale: largest end balance ($)										$1,493,037
4
5	Age	Phase	Salary ($)	Start balance ($)	Employee contribution ($)	Employer match ($)	Other income ($)	Requested withdrawal ($)	Withdrawal paid ($)	Growth ($)	End balance ($)	End balance, today's $
6	35	Saving	$85,000	$80,000	$6,800	$2,550	$0	$0	$0	$4,800	$94,150	$91,854
7	36	Saving	$87,550	$94,150	$7,004	$2,627	$0	$0	$0	$5,649	$109,430	$104,157
8	37	Saving	$90,177	$109,430	$7,214	$2,705	$0	$0	$0	$6,566	$125,915	$116,924
9	38	Saving	$92,882	$125,915	$7,431	$2,786	$0	$0	$0	$7,555	$143,687	$130,173
10	39	Saving	$95,668	$143,687	$7,653	$2,870	$0	$0	$0	$8,621	$162,831	$143,919
11	40	Saving	$98,538	$162,831	$7,883	$2,956	$0	$0	$0	$9,770	$183,440	$158,180
12	41	Saving	$101,494	$183,440	$8,120	$3,045	$0	$0	$0	$11,006	$205,611	$172,974
13	42	Saving	$104,539	$205,611	$8,363	$3,136	$0	$0	$0	$12,337	$229,447	$188,318
14	43	Saving	$107,675	$229,447	$8,614	$3,230	$0	$0	$0	$13,767	$255,058	$204,232
15	44	Saving	$110,906	$255,058	$8,872	$3,327	$0	$0	$0	$15,303	$282,561	$220,737
16	45	Saving	$114,233	$282,561	$9,139	$3,427	$0	$0	$0	$16,954	$312,081	$237,851

Showing the first 16 of 85 rows and 12 of 15 columns. Cells with formulas show the formula on hover.

	A	B	C	D
1	Return and saving scenarios
2	Balance at retirement and the age the money lasts, for five return shifts and three extra saving rates.
3
4	Balance at retirement by return shift and extra saving (nominal $)
5	Return vs base (points)	Extra saving +0 points	Extra saving +2 points	Extra saving +4 points
6	-2.0%	$1,022,558	$1,161,301	$1,300,044
7	-1.0%	$1,231,518	$1,392,566	$1,553,614
8	0.0%	$1,493,037	$1,680,957	$1,868,876
9	1.0%	$1,820,972	$2,041,335	$2,261,697
10	2.0%	$2,232,831	$2,492,435	$2,752,038
11
12	Money lasts until age, by return shift and extra saving
13	Return vs base (points)	Extra saving +0 points	Extra saving +2 points	Extra saving +4 points
14	-2.0%	77	79	80
15	-1.0%	81	83	86
16	0.0%	87	91	95

Showing the first 16 of 111 rows and 12 of 16 columns. Cells with formulas show the formula on hover.

	A
1	Retirement savings projection
2	Notes: what this workbook does, how to use it and the method behind it.
3
4	What it does
5	Projects a retirement account year by year, from the current age to a plan-to age, with contributions while saving, withdrawals while retired and growth at a phase-specific return.
6	The Projection tab has one row per age (up to 80 rows). The Scenarios tab reruns the balance for returns two points below and above the base and for extra saving of 0, 2 and 4 points of salary.
7	The example saver is fictional: age 35, an 85,000 dollar salary, an 80,000 dollar balance and a desired income of 60,000 dollars a year in today's dollars.
8
9	How to use it
10	1. On Inputs, enter your current age, retirement age, plan-to age, balance and salary, with the salary growth rate.
11	2. Enter your employee contribution, the employer match and the match cap. Enter the return before and after retirement and the inflation rate.
12	3. Enter the retirement income you want in today's dollars, the other income you expect and the age it starts.
13	4. Read the balance at retirement, the age the money lasts until and the monthly saving needed from the Dashboard tiles.
14	5. Check the Scenarios tab for the balance and lasts-until age at each return and extra saving rate.
15
16	Formulas and method

Showing the first 16 of 38 rows and 1 of 1 columns. Cells with formulas show the formula on hover.

What does this template do?

This workbook projects a retirement account from today to a plan-to age. It is for people planning their own savings, couples comparing options, and advisers preparing a first draft. Each year the balance grows at the expected return, employee and employer contributions are added as a share of salary, and in retirement the desired income less other income is withdrawn at the start of the year, inflated each year.

The Projection tab holds one row per age, up to 80 rows. Contributions are salary times the employee rate plus the employer match, where the match applies to the employee rate up to a cap. The Scenarios tab reruns the balance for returns two points below and above the base, with extra saving of 0, 2 and 4 points of salary. The target balance is the present value at retirement of the inflation-adjusted withdrawals, and the monthly saving figure uses PMT.

The example saver is fictional: age 35, an 80,000 dollar balance, an 85,000 dollar salary, and a Social Security style estimate starting at 67. The return figures are assumptions, not forecasts, and returns are not guaranteed.

What’s inside

Year-by-year table from current age to plan-to age, with saving and retired phases
Employer match computed on the employee rate up to a salary cap
Withdrawals equal desired income less other income, inflated each year
Fifteen scenarios: returns from two points below to two points above base, with extra saving of 0, 2 and 4 points
Target balance from the present value of inflation-adjusted withdrawals, and the monthly saving needed to reach it

Which tabs does the workbook have?

Tab	What it holds
Dashboard	Balance at retirement, the lasts-until age, the target and monthly saving, with charts.
Inputs	Ages, balance, salary, contribution rates, returns, inflation and retirement income.
Projection	One row per age: phase, contributions, withdrawals, growth, end balance and bars.
Scenarios	Balance at retirement and lasts-until age for 15 return and saving scenarios.
Notes	What the workbook does, the formulas, assumptions and limits.

What formulas does this template use?

This template holds 2,613 formulas in 2,844 cells across 5 tabs, so 92% of its cells calculate. They use 16 distinct functions; the longest formula is 5,809 characters and 2,080 of them read from another tab.

Function	Uses	What it does
`IF`	4,571	one result when a test is true, another when false
`MAX`	1,624	largest value
`MIN`	1,525	smallest value
`ROUND`	218	rounds to a number of digits
`OR`	192	true when any test is true
`REPT`	178	repeats text
`IFERROR`	51	swaps an error for a fallback value
`INDEX`	46	value at a position in a range
`MATCH`	46	position of a value in a range
`CHOOSE`	40	picks the nth value from a list
`ISNUMBER`	40	true for a number
`ABS`	4	absolute value

The 12 most used of 16 functions. Counted from the workbook itself. Only functions that Excel, LibreOffice Calc, Google Sheets and Apple Numbers evaluate the same way are used, so the formulas survive every download format.

How do you use it?

On Inputs, enter your ages, balance, salary, contribution and match rates, and the return and inflation assumptions.
Enter the retirement income you want in today's dollars, plus any other income and the age it starts.
Read the balance at retirement, the age the money lasts until and the monthly saving needed from the Dashboard tiles.
Check the Scenarios tab to see how the balance and the lasts-until age move with return and extra saving.
Replace the example figures with your own and review the plan with a qualified advisor.

What is it good for?

Estimating a retirement target from a desired income
Comparing the employer match with extra saving
Checking how long a balance lasts in retirement
Preparing questions for a plan review with an advisor

Questions about this sheet

Does the workbook forecast investment returns?

No. Returns, inflation and salary growth are inputs. The Scenarios tab shows how the result moves when the return changes by up to two points.

How is the employer match calculated?

The match is the employer share applied to the employee rate up to the cap, as a share of salary. For example, 50 percent of the first 6 percent of salary.

Why are withdrawals taken at the start of the year?

Each withdrawal comes out before that year's growth. This reduces the balance earlier than end-of-year withdrawals would, so the result is more cautious.

What does the monthly saving figure mean?

It is the level monthly deposit from today that, with the current balance, reaches the target at retirement at the pre-retirement return.