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 )
