wrote:
Bob is currently 36 years old and wants to retire at 65.
He expects to live to 95 and would like to know based on
his current savings how much the shortfall is for his
goals and what he needs to save from now until retirement.
He would like to have $35,000 (today's dollars) per year
for retirement.
[....]
I have figured out the following:
Years until retirement: 29
Years income needed: 30
[....]
I'm trying to figure out what the total cost of retirement
will be while inflating the income needed by 2% per year
and investing the captial at a rate of 5.5% per year.


Errata: "years income needed" is 66: 95 - 29.

The "total cost of retirement" is not the same as how much "to save from now
until retirement". And I do not believe that knowledge of the first leads
to the second directly.

The "total cost of retirement" is:

=FV(2%,66,-FV(2%,29,0,-35000))

where FV(2%,29,0,-35000) is the withdrawal (income) in the 30th year:
$35,000 inflated 29 years.

However, in order to determine how much "to save from now until retirement",
I believe we need to know the net present value of the cash flows
(income/withdrawals) during retirement.

The amount "to save from now until retirement" can be calculated with the
following array-entered formula (press ctrl+shift+Enter instead of just
Enter):

=PMT(5.5%,29,0,-NPV(5.5%,FV(2%,ROW($29:$94),0,-35000))*(1+5.5%),1)

where NPV(5.5%,FV(2%,ROW($29:$94),0,-35000))*(1+5.5%) is the net present
value at the end of the 29th year of the withdrawals in year 30 through 95.

It is the NPV expression that must be array-entered. If you calculate that
formula separately in A6 for example, the PMT formula is the following
normally-entered formula (just press Enter as usual):

=PMT(5.5%,29,0,-A6,1)

To parameterize, assume that A1 is the inflation rate (2%), A2 is the
investment rate of return (5.5%), A3 is the years until retirement (29), A4
is the life expectancy (95), and A5 is annual withdrawal during retirement
in current dollars ($35,000).

Then the amount "to save from now until retirement" is the following
array-entered formula (press ctrl+shift+Enter instead of just Enter):

=PMT(A2,A3,0,-NPV(A2,FV(A1,ROW(INDIRECT(A3&":"&A4-1)),0,-A5))*(1+A2),1)

However, INDIRECT is a "volatile" function. That means the formula is
recalculated every time any cell in any worksheet in the workbook is edited.

That might be okay if you do not have too many such formulas.

Alternatively, to avoid "volatile" recalculations, use the following
array-entered formula (press ctrl+shift+Enter instead of just Enter):

=PMT(A2,A3,0,-NPV(A2,FV(A1,ROW(INDEX($A:$A,A3):INDEX($A:$A,A4-1)),0,-A5))*(1+A2),1)