Hi Chip!
This sounds like an assignment question! And I would hope that it
contains an overall rider that you should make sensible assumptions
where there is missing data.
You have missing data! What is the life expectation of "you". Is "you"
male or female? Does "you" smoke or not? Does "you" have any
disabilities etc. that would cause life insurance companies to load
the premium? What country is "you" in? (Life tables vary between
countries). What happens on death? If death occurs before 65 then
what? If death occurs after 65, then what?
And what's this rate of return? Is it annual nominal compounded
monthly? Or is it Annual effective?
Are these payments now and the annuity payments at 65 made at the end
of each month?
Let's assume that you get back accumulated value of premiums up to age
65 and that after 65 you get the right to receive 3000 per month in
perpetuity with the first payment being received 1 month after
retirement. Let's also assume that the rate is 10% per annum nominal
compounded monthly.
Amount required at 65:
=3000*1/(10%/12)
Returns: 360000
Monthly investment to secure 360000 in 25 years:
=PMT(10%/12,25*12,0,360000,0)
-271.322683944221
Now the checks:
=FV(10%/12,25*12,-271.32,0,0)
Returns: 359996.438853156
(Error due to rounding of payment)
=359996.43*(10%/12)
Returns: 2999.97025
Now if this is an assignment, be careful to state all the assumptions,
make sure that it is written up. Add value, perhaps by quoting the
formulas that are behind the calculations and (unless you want to be
hit for plagiarism) make sure that you acknowledge assistance in a
form that will allow precise location of source.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
Chip said:
5. You are planning your retirement (you are now 40). You want to
know how much you need to invest monthly to get $3000 a month back at
age 65. You have been assured that a 10% is rate of return.
