Since
release of the latest version of the ABC last month, we have decided that
eventually we will probably develop separate ABC spreadsheets for pre-retirees
and retirees as the current version is somewhat “clunky.” For the time-being, however, pre-retirees can
use this somewhat more clunky version.

While
most retirees know approximately how much their Social Security benefit is or
will be in the future, some younger retirees and many pre-retirees may not have
a good idea of how much they should input in our spreadsheets for their
benefit. This post will describe a
process that can be used for this purpose.

**Betsy’s Financial Goals**

- Betsy wants to retire from her current job at age 65 with no part-time employment thereafter
- Betsy would like her real dollar first year post-retirement spending to be no less than 75% of her real spending in her final year of employment
- Betsy has no other financial goals, such as paying for her son’s college expenses
- Betsy doesn’t want to over-save or under-save for her retirement. In other words, she would like to maintain a reasonable balance between her pre- and post-retirement lifestyles.
- Betsy doesn’t want to become a burden on her son, but doesn’t feel a need to leave him a large estate.

**Betsy’s Data**

- Betsy is age 50 and is divorced
- She is employed and her current gross pay is $70,000 a year
- Her employer matches her 401(k) contributions $.50 for each dollar up to 6% of pay
- The current value of her home is $250,000

**Betsy’s Assets:**

- Accumulated savings, 401(k) and personal assets, of $100,000
- The present value of her future employment income
- The present value of her estimated future Social Security benefit of $46,900 per year commencing in 20 years at age 70. Betsy develops her estimated age 70 Social Security benefit of $46,900 using the following process:

Step
1: Betsy goes to the Social Security Quick Calculator on the Social
Security website.

Step
2: She enters her date of birth, gross
pay in the current year and her desired future benefit commencement date. She also indicates that she wants her benefit
estimate to be in current dollars and submits her request.

Step
3: She multiplies the resulting monthly
benefit estimate by 12 and divides the result by her annual gross pay. This gives Betsy a replacement ratio.

Step
4: She multiplies the replacement ratio
developed in Step 3 by her estimated pay in the year preceding her retirement
(and if this date precedes her Social Security benefit commencement, by the
anticipated increase in inflation for the bridge period). Her estimated pay in the year preceding her
retirement is based on her assumption for future pay increases as discussed in
the assumptions section below.

Betsy
follows the process above and develops a replacement percentage of 46%, which
she multiplies by her gross pay of $70,000 and 19 years of 2% per annum
increases (a factor of 1.457) to produce an estimated annual age 70 benefit of
about $46,900.

- The present value of future employer matching contributions to the 401(k) plan
- The present value of proceeds from future home sales.

**Betsy’s Assumptions:**

For present value
calculations, Betsy, with assistance from her financial advisor, has selected
these assumptions:

- Annual discount rate of 4%
- Annual rate of inflation of 2%
- She expects her employment will continue until she retires, and her gross pay will increase annually at the rate of inflation (2%)
- She expects to contribute, into her employer’s 401(k) plan, at least the minimum to receive the maximum matching contribution ($2,100 in the current year increasing by 2% per year). She uses the Present Value Calculator in this website to estimate the present value of the matching contributions to be about $29,000.
- She expects her existing home mortgage will be paid off by the time she retires. She expects to sell her home when she enters an assisted living facility and she expects that the value of her home will earn 4% per annum.
- She expects to live until age 95
- She expects her future essential expenses (excluding health related expenses) will increase with inflation, that her future essential health related expenses will increase with inflation +2% and her future non-essential expenses will remain constant in nominal dollars. Based on her expected distribution of such expenses, she believes her total annual recurring expenses in retirement will increase by inflation minus 0.5% each year after retirement.
- She expects that she will have to live the last three years of her life in an assisted living facility. She estimates the current cost of a three-year stay in her geographic area at $170,000 and she believes this cost will increase in the future by inflation plus 2% each year. Based on the approximation technique outlined in our post of January 12, 2016 to reflect the reduction in other recurring expenses, she estimates a present value of her long-term care costs at $102,000 (60% of $170,000).
- She estimates the present value of her future unexpected expenses to be $25,000
- She estimates that $200,000 (in nominal dollars) will be sufficient to cover her funeral expenses, with any remainder to be left to her son.

**Entries in Column B of the Input Tab**

Row Entry

7 $100,000 (Betsy’s accumulated savings)

9 $46,900 (Betsy’s projected Social Security benefit
as developed above)

11 20 (the number of years before expected commencement
of her

benefit)

25 $250,000 (the present value of her home sale
proceeds)

27 4% (Betsy’s expected rate of return on her
investments/discount rate)

29 45 (Betsy’s lifetime planning period—95 minus her
age)

33 $200,000 (The amount desired to be left at the end of
Betsy’s lifetime

planning period)

35 2% (the expected annual rate of future inflation)

The result in Row 41 with
these input items is $743,813 (the present value of Betsy’s retirement spending
budgets based on the input items above).
This amount is carried forward to the new pre-retirement spending and
savings tab as the beginning value.

**Entries in Column B of Pre-Retirement Spending & Savings Tab**

Betsy then goes to the

**Pre-Retirement Spending & Saving**tab and makes the following entries:
Row Entry

6 $70,000 (Betsy’s current gross pay)

7 15 (the number of years until her desired
retirement age of 65)

8 2% (Betsy’s estimate of the annual future
percentage increase in her

gross pay)

10 $29,000 (Betsy’s
estimate of the PV of future matching contributions to

the 401(k) plan)

13 $102,000 (60% of Betsy’s estimate of 3 years of
assisted living cost of

$170,000)

15 $25,000 (Betsy’s reserve for unexpected expenses)

20 12% (the percentage of her gross pay she intends to
save)

23 1.5% (the desired annual increase in Betsy’s recurring
retirement

spending budget)

The result shown in E 26 of
the new tab with these input items is that if all of Betsy’s assumptions are
realized in the future, her expected real dollar spending budget in her first
year of retirement (age 65) would be 76.22% of her age 64 real dollar spending
budget. If she only wanted to save 8%
of her pay each year rather than 12%, she could still reach her 75% real
spending goal by working approximately another two years. Betsy understands that because she has
included the value of her home as an asset for budget purposes, she may have
cash-flow problems later in her retirement that might require downsizing her
home or taking out a reverse mortgage earlier than she might want.

Betsy also understands that
(i) she may not be able to continue in her current employment until her desired
retirement age, (ii) her estimated Social Security benefit may be reduced as a
result of impending Social Security reform and (iii) some of her other
assumptions may also turn out to be optimistic.
For this reason, she decides that she will try to save at least 15% of
her pay each year just to be a little safer.
Of course, she will monitor her actual savings and spending each year
and revisit this process every year to make sure she remains
on track to meet her financial goals.