Financial Time Value of Money calculations
 

Hello,

I'm trying to create a retirement planning spreadsheet but I need help with one of the calculations please.

Here is an example:

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..

(This is not a school problem, I'm creating this problem because it is easier than trying to explain what I want alone)

I have figured out the following:
Years until retirement: 29
Years income needed: 30

$35,000 today inflates to $62,154.56 in 29 years.

Total cost of retirement: This is where I'm stuck.

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.

I can't just subtract the inflation can I? I don't think the numbers will work correctly. Is there a way to do this WITHOUT populating a table. I would like to just use a formula to do it.

Financial Time Value of Money calculations
 

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)

Financial Time Value of Money calculations
 

PS.... I wrote:
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)
[....]
=PMT(A2,A3,0,-NPV(A2,FV(A1,ROW(INDIRECT(A3&":"&A4-1)),0,-A5))*(1+A2),1)
[....]
=PMT(A2,A3,0,-NPV(A2,FV(A1,ROW(INDEX($A:$A,A3):INDEX($A:$A,A4-1)),0,-A5))*(1+A2),1)

I think the use of NPV and FV makes the formula's intent clear.

But generally, I avoid array-entered formulas because they are error-prone:
we forget ctrl+shift and just press Enter unconsciously.

You might consider the following normally-entered alternatives (just press
Enter as usual):

=PMT(5.5%,29,0,-35000*SUMPRODUCT((1+2%)^ROW($29:$94)
/(1+5.5%)^(ROW($29:$94)-29)),1)

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

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

If you understand the mathematical formulas for NPV and FV, the SUMPRODUCT
expression might be clear.

Financial Time Value of Money calculations
 

[Apparently eternal-september.org is having some problems. Eventually,
the following might appear to be a duplicate posting. Sorry about
that.]

Subject: Financial Time Value of Money calculations
Date: Sat, 30 Mar 2013 14:18:39 -0700
Message-ID:

PS.... I wrote:
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)
[....]
=PMT(A2,A3,0,
-NPV(A2,FV(A1,ROW(INDIRECT(A3&":"&A4-1)),0,-A5))*(1+A2),1)
[....]
=PMT(A2,A3,0,-NPV(A2,FV(A1,ROW(INDEX($A:$A,A3):INDEX($A:$A,A4-1)),
0,-A5))*(1+A2),1)

I think the use of NPV and FV makes the formula's intent clear.

But generally, I avoid array-entered formulas because they are
error-prone: we forget ctrl+shift and just press Enter unconsciously.

You might consider the following normally-entered alternatives (just
press Enter as usual):

=PMT(5.5%,29,0,-35000*SUMPRODUCT((1+2%)^ROW($29:$94)
/(1+5.5%)^(ROW($29:$94)-29)),1)

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

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

If you understand the mathematical formulas for NPV and FV, the
SUMPRODUCT expression might be clear.

Financial Time Value of Money calculations
 

On Thursday, March 28, 2013 10:20:24 AM UTC-5, Jim Thornton wrote:
Hello,



I'm trying to create a retirement planning spreadsheet but I need help with one of the calculations please.



Here is an example:



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.

Financial Time Value of Money calculations
 

Just to make a correction in the last two function calls when interest is to be compounded monthly

The present value is found as it was listed in my last reply

=tadPV ( 5.5%, 2%, 0%, 30, -62154.56, 0, 1, 0, 1/12 )

But the payment will now take this new present value as the sinking fund so I will put in XXXXX as a replacement for the future value in the tadPMT formula

=tadPMT ( 5.5%, 2%, 0%, 29, 0, XXXXX , 1, 0, 1/12 )