Financial Time Value of Money calculations

  • Thread starter Thread starter jthornton.private
  • Start date Start date
J

jthornton.private

Hello,

I'm trying to create a retirement planning spreadsheet but I need help withone 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.
 
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)
 
PS.... I said:
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.
 
[Apparently eternal-september.org is having some problems. Eventually,
the following might appear to be a duplicate posting. Sorry about
that.]

Subject: Re: Financial Time Value of Money calculations
Date: Sat, 30 Mar 2013 14:18:39 -0700
Message-ID: <[email protected]>

PS.... I said:
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.
 
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. Iwould like to just use a formula to do it.

You would have to do the calculations in two parts. At first you would needto calculate the amount at time period 29 that is needed for an annual withdrawal of $62,154.56 (the $35,000 at time period 29) for 30 years before all the funds are depleted.

That amount which is the present value of annual payments in amount of $62,154.56 for 30 years will become your sinking fund required to calculate theannual deposits starting now until retirement. A sinking fund is amount ofmoney you wish to have at a future date by making annual deposits that earn interest.

So this is a two step procedure

1) Find the present value of annual withdrawals at retirement age
2) Use the amount from step 1 to calculate the annual deposits required till retirement age

What makes your problem more interesting is the fact that you are considering the effect of inflation in this case 2% per annum. Your rate of return on the investment is 5.5% at which your deposits earn interest.

Joeu provided you with one way of calculating the annual payment you require, but in my opinion the Excel TVM functions of RATE, NPER, PMT, PV and FV are very limited in what can be done with them without resorting to extra mutations of formulas

Here I will illustrate the calculations required for your problem by makinguse of TVM functions that are part of TADXL Excel add-in http://tadxl.com

The TVM functions in TADXL add more functionality to cater for annuity payments that may grow, shrink, increase and decrease along with the traditional annuity payments in constant uniform series of amounts. As your problem states that the annuity payment grows with a geometric gradient, thus TVM functions in TADXL are built to address these sort of calculations.

Besides the 5 TVM functions of tadRATE, tadNPER, tadPMT, tadPV and tadFV there is a new TVM function in TADXL called GRADIENT which represents either a growth rate by which an annuity payment grows or shrinks. Or the gradientrepresents a constant money amount by which the annuity payment increases or decreases. The former growth type is called a geometric gradient and thelatter growth type is called the scalar gradient.

The following input will be required for the calculation of present value in step 1

RATE: 5.5%
GRADIENT: 2%
TAXRATE: 0%
NPER: 30
PMT: 62154.56
FV: 0
TYPE: 1
GTYPE: 0

We will assume that interest earned is compounded annually thus the following formula will give us the present value of annual withdrawals of $62154.56 starting at retirement till age 95.

We will now use the Excel PV function http://tadxl.com/excel_pv_function.html with the following values as input

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

The answer you get is $1,192,607.57 which is the amount you must have at age 65 to be able to withdraw an annual amount of $62154.56 for 30 years. Of course this amount $1,192,607.57 will be reinvested at age 65 at 5.5% interest rate while inflating at 2% per annum

This leads us to Step 2 in finding the annual deposit amount required at present till retirement to have an amount of $1,192,607.57 at age 65. The annual deposits will inflate at 2% while earning interest at 5.5%.

We will now use the Excel PMT function http://tadxl.com/excel_pmt_function.html with the following values as input. Once again we are assuming interest is compounded annually

RATE: 5.5%
GRADIENT: 2%
TAXRATE: 0%
NPER: 29
PV: 0
FV: $1,192,607.57
TYPE: 1
GTYPE: 0

=tadPMT ( 5.5%, 2%, 0%, 29, 0, 1192607.57, 1, 0 )

This gives us the answer to your problem with an amount of -$13,419.75 a negative sign indicates outgoing cash flow.

So you would need to make an annual deposit in amount of $13,419.75 for 29 years starting now to be able to withdraw an annual amount of $62,154.56 for 30 years starting at age 65

If the interest on your deposits is compounded quarterly, monthly, weekly or daily then we would add a new value to the list of arguments in each of the tadPV and tadPMT functions to represent this

This value is called COMPOUNDING and we can use the following values in it to represent different sort of interest compounding

1 - Annual
6/12 - Semi-Annual
3/12 - Quarterly
1/12 - Monthly
1/52 - Weekly
1/365 - Daily
0 - Continuous or infinite

So if the interest for your deposits was compounded monthly then the following two function calls will be used instead

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

=tadPMT ( 5.5%, 2%, 0%, 29, 0, 1192607.57, 1, 0, 1/12 )
 
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 )
 
Back
Top