RETIREMENT WORKSHEET TEMPLATE
The blue numbers are variables, but must be changed to match your worksheet figures.
You may also change the number of compounding periods (n) and the interest rate, but
if you change "n" or "r", you will also have to adjust the interest factors accordingly.
First, you have to calculate the future value of your DIFFERENT annuities since you're saving EACH YEAR
under different assumptions.
Age
30-39
40-49
50-59
60-64
Salary
Annual Savings @ 15%
r
n
FVIFA
FVA
7,500
11,250
15,000
18,750
5.00%
5.00%
5.00%
5.00%
10
10
10
5
12.5779
12.5779
12.5779
5.52563
$94,334
$141,501
$188,668
$103,606
50,000
75,000
100,000
125,000
at age 40
at age 50
at age 60
at age 65
The above represents a future value of an annuity calculation because you're saving EVERY year.
Now, you have to take LUMP SUM FUTURE VALUE calculations to bring these figures from age 40, 50, and 60 (respectively)
to your retirement age of 65:
Age
40
50
60
65
Accumulated Savings
94,334
141,501
188,668
103,606
r
n
FVIF
FVA
6.50%
6.50%
6.50%
6.50%
25
15
5
0
4.8277
2.5718
1.3701
1
$455,417
$363,913
$258,495
$103,606
$1,181,430
EXISTING SAVINGS:
Do the same with your existing savings (but n= 35):
5,000
Early Savings
6.50%
35
9.0622
TOTAL IN SAVINGS ACCOUNT @ AGE 65
$45,311
$1,226,741
Assuming a 20 year retirement, how much can you withdraw each year so that at the end of 20 years, the balance will be $0?
YOU CAN SOLVE FOR YOUR ANNUITY WITHDRAWAL AMOUNT IN ONE OF THREE WAYS:
1. Using a sinking fund interest factor where you apply that interest factor to the future value.
1st: Do a lump sum FV calculation to carry the total savings in the account (at age 65) 20 years into the future (from age 65-85):
n=20, r=5%
PV =
$1,226,741
FVIF = 2.6533
$3,254,913
Assuming you didn't spend ANY of your retirement, this is the amount accumulated in your retirement fund at the age of 85.
But, you want to draw down that account balance so that in 20 years, there's a $0 balance at age 85. It's a sinking fund problem.
2nd: Now do a sinking fund calculation:
Future Value: $3,254,913
Sinking fund table factor:
0.0302 when n= 20, r = 5%
2. Solve for the annuity by the following formula:
3. On a financial calculator, solve for PMT:
PV/PVIFA = A
PV $1,226,741
FV
$0
I/Y (r)
5
n
20
$1,226,741
÷ 12.462
$98,298
$98,439
CPT PMT
COMPUTE
Based on the assumptions above, this is
the amount you will be able to withdraw
per year from your retirement account.
*Your answers may differ slightly for each different method (though not statistically significant) because of the rounding involved when using the interest factor tables.
Purchase answer to see full
attachment