Finance Homework question

Nunya2010 used Ask the Experts™
Any thoughts I’ve been working on this on for 4 days. I need to use an excel function to answer this question

Ellen is planning her retirement and has an annuity with a present worth of $500,000.  Actuarial tables show that people who live to be 65 (her age) will on the average live 30 additional years.  If the annuity will earn 6% interest compounded monthly, how much per month (equal amounts) can she withdraw over her 30 years of retirement.  If she unexpectedly only lives 15 years, how much will be remaining in the annuity for her heirs?
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Is this a hypothetical question?

I would have to highly dispute the fact that any gender, who reaches age 65, will on average, live 30 more years.
I'm seeing data from 2006 (and it says it was updated in April 2010)
that shows a 65 year old female only living 19.72 more years.
The only ones who make it to age 95 are those who make it to age 91.
Check it;

In finance problems like this, you're usually solving for 1 of 5 possible variables.
If you know the other 4, you can usually solve for the fifth.

In excel, they are:
Nper - Number of periods
PV - Present Value
FV - Future Value
rate - Rate of return; and
pmt, or payment.

For this problem, you know the following:

NPER: 360 (12 mos * 30 yrs)
PV: -500,000 (Cash value invested)
FV: 0.00 (she wants to run out of cash on death)
RATE: 6.00%/12 (monthly rate of return)
PMT: ??? - The amount she can spend / month

You can (and probably should) check this in an amort. schedule.
The am schedule will give you the answer to your second problem; but
its a messy way to solve.

What you should do instead, is turn the second problem into an FV problem,
solving with the same variables; and your PMT amount from problem 1; and 180
as the NPER in the equation.

for a hint (maybe I did this wrong); if she had a rate of return of 3.00% instead of your
6.00%; she could withdrawal 2,180.02 / month, and in 15 years be down to 305,252.85

did you get your problem solved?

the facts in the question do not seem real, so the scenario must have been "hypothetical"

the stats show, people who are age 65 will not be expected to live 30 more years, at least from the stats I was able to locate.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial