Question

Jeanette will turn 66 in 2018 (thus she was born after 1942). She is considering taking Social Security retirement benefits at age 62, 66, or 70. Assume her monthly expected benefit is $1,000 at full retirement age, expected inflation is 4%, and her life expectancy is 90. Calculate the present value of her benefits at ages 62, 66, and 70 (A, B, or C).

Choice A – Begin Benefits at age 62.

PMT $750

N 336    (90-62) X 12

I 0.3333    4/12

FV 0

PV@62 $151,450.19

Choice B Begin benefits at 66

PMT $1,000

N 288 (90-66) X 12

I. 0.333 4/12

FV 0

PV@62 $158,094.84

Benefits at age 70

PMT    $1,320

N 240    (90-70) X 12

I    0.333 4/12

FV    0

PV@70    $217,828.85

This example appears in Money Education. I am trying to understand the basics on how to set up excel spreadsheet to come back to the same answers. Can you help with this request.

Answer 1

The formulae provided are for the excel spreadsheet only. Use PV() formula in excel. Set up the spreadsheet as follows:

Choice A:

PMT	750
N	336
I	0.33%
PV	=PV(I, N, -PMT, 0, 0)
PV	1,51,450.19

Choice B:

PMT	-1,000
N	288
I	0.33%
PV	=PV(I, N, PMT, 0, 0)
PV	1,84,948.61

Choice C:

PMT	-1,320
N	240
I	0.33%
PV	=PV(I, N, PMT, 0, 0)
PV	2,17,828.85

Note: Either PMT or PV value will be in negative. Ignore that.