Question

You turned 35 today and have begun to think about saving for retirement. You anticipate that...

You turned 35 today and have begun to think about saving for retirement. You anticipate that you will retire at age 67 (on your birthday), and will need $120,000 a year for 20 years, with the first withdrawal occurring from the retirement account occurring on your 67th birthday, and the last withdrawal occurring on your 86th birthday.

a) If you begin to make monthly payments into your retirement account today (on your 35th birthday), with the last payment into the account the month before your 67th birthday. The expected rate of return on your account, both before and after retirement, is 6% per year, compounded monthly. What is the payment that you must make into the account in order to hit your retirement target?

b) You decide to make annual payments into the retirement account, with the first payment still occurring today and the last payment on your 66th birthday. What is the even annual payment that is required to meet your target?

c) You add one wrinkle to this process. Specifically, you wish to save enough so that, in addition to your withdrawals of $120,000 per year for 20 years, you can leave a bequest to your children in the amount of $500,000. Assuming, as above, that you forecast living only until your 87th birthday, what is the even annual payment that you must make into the account to hit your (new) target?

Homework Answers

Answer #1

a]

First, we calculate the amount required at retirement to enable the yearly withdrawals during retirement. The amount required at retirement is calculated using PV function in Excel :

rate = 6% (rate of return earned)

nper = 20 (number of years in retirement)

pmt = -120000 (yearly withdrawal. This is entered with a negative sign because it is a withdrawal)

fv = 0 (amount remaining after 20 years of withdrawals is zero)

type = 1 (Each withdrawal is made at the start of the year, hence it is an annuity due)

PV is calculated to be $1,458,973.98

Next, we calculate the monthly saving required to accumulate the required amount at retirement. The monthly saving required is calculated using PMT function in Excel :

rate = 6%/12 (Converting annual rate of return into monthly rate of return)

nper = 32*12 (number of months until retirement = number of years until retirement * 12)

pv = 0 (amount currently saved is zero)

fv = 1458973.98 (required amount at retirement)

type = 1 (Each withdrawal is made at the start of the month, hence it is an annuity due)

PMT is calculated to be $1,253.99

Monthly payment to hit retirement target is $1,253.99

b]

We calculate the yearly saving required to accumulate the required amount at retirement. The yearly saving required is calculated using PMT function in Excel :

rate = 6% (annual rate of return)

nper = 32 (number of years until retirement)

pv = 0 (amount currently saved is zero)

fv = 1458973.98 (required amount at retirement)

type = 1 (Each withdrawal is made at the start of the year, hence it is an annuity due)

PMT is calculated to be $15,143.51

Yearly payment to hit retirement target is $15,143.51

c]

First, we calculate the amount required at retirement to enable the yearly withdrawals during retirement. The amount required at retirement is calculated using PV function in Excel :

rate = 6% (rate of return earned)

nper = 20 (number of years in retirement)

pmt = -120000 (yearly withdrawal. This is entered with a negative sign because it is a withdrawal)

fv = 500000 (amount remaining after 20 years of withdrawals)

type = 1 (Each withdrawal is made at the start of the year, hence it is an annuity due)

PV is calculated to be $1,614,876.34

We calculate the yearly saving required to accumulate the required amount at retirement. The yearly saving required is calculated using PMT function in Excel :

rate = 6% (annual rate of return)

nper = 32 (number of years until retirement)

pv = 0 (amount currently saved is zero)

fv = 1614876.34 (required amount at retirement)

type = 1 (Each withdrawal is made at the start of the year, hence it is an annuity due)

PMT is calculated to be $16,761.71

Yearly payment to hit new retirement target is $16,761.71

Know the answer?
Your Answer:

Post as a guest

Your Name:

What's your source?

Earn Coins

Coins can be redeemed for fabulous gifts.

Not the answer you're looking for?
Ask your own homework help question
Similar Questions
Your friend is celebrating her 35th birthday today wants to start saving for her anticipated retirement...
Your friend is celebrating her 35th birthday today wants to start saving for her anticipated retirement at age 65. She wants to be able to withdraw $105,000 from her savings account on each birthday for 20 years following her retirement; the first withdrawal will be on her 66th birthday. Your friend intends to invest her money in the local credit union, which offer 7 percent interest per year. She wants to make equal annual payments on each birthday into the...
Using Excel You have just turned 35, and you intend to start saving for your retirement....
Using Excel You have just turned 35, and you intend to start saving for your retirement. Once you retire in 30 years (when you turn 65), you would like to have an income of $100,000 per year for the next 20 years. Calculate how much you would have to save between now and age 65 in order to finance your retirement income. Make the following assumptions: • All savings draw compound interest of 10% per year. • You make the...
Assume that you are 30 years old today, and that you are planning on retiring at...
Assume that you are 30 years old today, and that you are planning on retiring at age 65. Because your current salary is $45,000 and You expect your salary to increase at a rate of 5% per year as long as you work. To Save for your retirement, you plan on making annual contributions to a retirement account. Your first contribution will be made on your 31st birthday and will be 8% of this year’s salary. Likewise, you expect the...
You just turned 34 years old, and you want to begin saving for retirement at the...
You just turned 34 years old, and you want to begin saving for retirement at the end of the year. Assume that you retire in 30 years at age 65, and would like to have an income of $100,000 per year for 20 years after retirement. How much must you save each year to finance your retirement income? Assume a 10% interest rate, you make the first payment at the end of the year when you turn 35 and the...
Today is your 25th birthday.  You have decided that it is necessary to plan your own retirement...
Today is your 25th birthday.  You have decided that it is necessary to plan your own retirement since you are not confident that government benefits will be available when you retire.  You will make annual payments into an account earning 5% APY starting on your next birthday (26th) a year from now.  Your last payment will occur on your 70th birthday.  You have decided that you need to plan for payments of $500,000 per year and that there will be a total of 20...
You have finished your time at Kelley and need to start thinking about retirement. You plan...
You have finished your time at Kelley and need to start thinking about retirement. You plan on working for 20 more years and then retire. Upon your retirement 20 years from today, you plan to have enough money to withdraw $10,000 per month, with the first payment coming exactly one month after your retirement day. You expect your retirement account to earn a return of 8% APR (stated rate), compounded monthly, on all funds in the retirement account. Assuming you...
Assume that you are 30 years old today, and that you are planning on retirement at...
Assume that you are 30 years old today, and that you are planning on retirement at age 65. Your current salary is $40,000 and you expect your salary to increase at a rate of 4% per year as long as you work. To save for your retirement, you plan on making annual contributions to a retirement account. Your first contribution will be made on your 31st birthday and will be 9% of this year's salary. Likewise, you expect to deposit...
Do the following question on a worksheet entitled ”Loan”. You have just turned 21 and start...
Do the following question on a worksheet entitled ”Loan”. You have just turned 21 and start to make plans for your future retirement (it is never too early). You would like to retire at 55 and enjoy the life. To do so, you understand that you have to plan to start saving ASAP. You want to be able to F2019 Econ 311 Assignment 2 Page 5 withdraw $24,000 annually starting with your 56th birthday and ending with your 75th birthday,...
Can you Solve it in Excel Please Today is your 25th birthday.  You have decided that it...
Can you Solve it in Excel Please Today is your 25th birthday.  You have decided that it is necessary to plan your own retirement since you are not confident that government benefits will be available when you retire.  You will make annual payments into an account earning 5% APY starting on your next birthday (26th) a year from now.  Your last payment will occur on your 70th birthday.  You have decided that you need to plan for payments of $500,000 per year and that...
You decide to open a retirement account at your local bank that pays 9%/year/month (9% per...
You decide to open a retirement account at your local bank that pays 9%/year/month (9% per year compounded monthly). For the next 20 years, you will deposit $600 per month into the account, with all deposits and withdrawals occurring at month’s end. On the day of the last deposit, you will retire. Your expenses during the first year of retirement will be covered by your company’s retirement plan. As such, your first withdrawal from your retirement account will occur on...
ADVERTISEMENT
Need Online Homework Help?

Get Answers For Free
Most questions answered within 1 hours.

Ask a Question
ADVERTISEMENT