Copy of Retirement Templates

	A	B	C	D	E	F	G
1	ANNUITY METHOD	LISA KNOPE	LISA KNOPE	Joey Tribbiani	Audrey Johnson	Shuri	Shannon
2		Scenario 1	Scenario 2	Scenario 2	Scenario 2	Scenario 2
3	Current age	35	25	35	21	24	30
4	Retirement age	80	60	62	67	65	62
5	Expected last year of life	100	90	85	90	85
6	WLE	45	10	27	46	41
7	RLE	20	25	23	23	20
8	Current wages	40,000.00	40,000.00	55,000.00	65,000.00	220,000.00
9	WRR	80.00%	100.00%	90.00%	80.00%	80.00%
10	Retirement current assets	0.00			7,700.00	0.00
11	Inflation rate	2.50%	3.00%	2.00%	3.00%	3.00%
12	Working life investment return	8.00%	7.00%	9.00%	8.00%	7.50%
13	Retirement investment return	5.00%	7.00%	6.00%	5.00%	5.00%	6%
14	Retirement wages	10,000.00				0.00	1531.4
15	AIME			5,500		6,400
16
17	STEP 1
18	Determine retirement living expense in today's dollars
19	Income today	40,000.00	40,000.00	55,000.00	65,000.00	220,000.00
20	WRR	80.00%	100.00%	90.00%	80.00%	80.00%
21	Needs	32,000.00	40,000.00	49,500.00	52,000.00	176,000.00
22	Less Retirement wages	-10,000.00
23	Less Annual Social Security benefit			-27,139.08		-28,759.08
24	Amount to be funded	22,000.00	75,000.00	22,360.92	52,000.00	147,240.92
25
26	STEP 2
27	Inflate the needs from step 1 to beginning of retirement
28	PV	22,000.00	75,000.00	22,360.92	52,000.00	147,240.92
29	NPER	45	10	27	46	41
30	RATE	2.50%	3.00%	2.00%	3.00%	3.00%
31	PMT	0	0	0	0	0
32	FV	-$66,833.87	-$100,794	-$38,167.55	-$202,542.27	-$494,714.61
33
34	STEP 3
35	Determine the funding need at retirement age
36	PMT	-$66,833.87	-$100,794	-$38,168	-$202,542	-$494,715
37	NPER	20	25	23	23	20
38	RATE	2.439%	3.883%	3.922%	1.942%	1.942%
39	FV	0	0	0	0	0
40	PV	$1,073,469.17	$1,656,088	$593,889	$3,801,007	$8,292,912
41
42	STEP 4
43	PV (how much savings today?)	0.00	0.00	0.00	-7,700.00	0.00
44	FV (retirement need from step 3)	$1,073,469.17	$1,656,088	$593,889	$3,801,007	$8,292,912
45	RATE (working life investment return) /12	0.67%	0.58%	0.75%	0.67%	0.63%	69.28
46	PMT (monthly savings amount)	-$203.52	-$9,512.58	-$434.28	-$611.28	-$2,535.29
47	N (WLE) *12	540	120	324	552	492
48
49	Savings rate	-6%	-285%	-9%	-11%	-14%
50
51	LTC Analysis
52	Age LTC benefits begin					70	80
53	How many months LTC?					180	24
54	Daily cost of LTC					250	200
55	LTC inflation rate					4.00%	3.00%
56
57	Assumptions
58	Step 1
59	PV					7500	6000
60	NPER					552	600
61	Rate					0.003333333333	0.0025
62	PMT					0	0
63	FV					-$47,079.76	-$26,839.85
64
65	Step 2
66	PMT					-$47,079.76	-$26,839.85
67	NPER					180	24
68	Rate					0.08%	0.25%
69	FV					0	0
70	PV					$7,874,820.06	$626,016.46
71
72
73	Step 3
74	FV					$7,874,820.06	$626,016.46
75	NPER					60	216
76	Rate					0.42%	0.50%
77	PMT					0	0
78	PV					-$6,136,102.24	-$213,165.25
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100