Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Please answer everything one by one clearly. Here you will build a model for spending the amount you have accumulated in your retirement account. The

Please answer everything one by one clearly.

Here you will build a model for spending the amount you have accumulated in your retirement account. The model will be based on the the following assumptions: You will retire on your 65th birthday with a retirement account balance equal to $1,000,000. Each year after retirement: You plan to withdraw $60,000 at the beginning of each year from your account to support yourself, except when the amount in your account falls below $60,000. There will be no more contributions coming into your account. You will continue to earn some return on the amount in your account, the rate of of return will be 2%. (If this rate of return seems low, it is because you will be changing your investments to reduce risk, and low-risk investments tend to earn a low rate of return on average.) You will have to pay a tax on the amount you withdraw, equal to 12% of the amount withdrawn. The bank where you maintain your retirement account charges a fee at the beginning of each year equal to 0.25% of your previous year's account balance. The worksheet "My Retirement-Tables" contains tables created to help you perform the calculations involved in spending your retirement nest egg.

In the green table, type the numbers reflecting the assumptions given above. To calculate the "withdrawal amount available to you for spending after paying taxes," insert a formula subtracting the tax amount from the withdrawal amount.

In the blue table,insert appropriate formulas to calculate the cells of each column. Insert references to the green table cells whereever appropriate and possible. A few tips-- The year- end account balance each will be equal to previous year's account balance minus fees, minus withdrawal amount, plus the return earned. The formula will have to be slightly different for the first year, where the "previous year's account balance" is simply the account balance you start retirement with: $1,000,000. Your return will be calculated and credited to your account at the end of each year. It will be equal to your rate of return multiplied by the previous-year's account balance. Again, the formula will have to be slightly different for the first year, where the "previous year's account balance" is simply the account balance you start retirement with: $1,000,000. You cannot overdraw your account, i.e. withdraw more than the amount that remains your account. So when your account balance falls below the yearly amount you have been withdrawing since the beginning of retirement, you will make a final withdrawal equal to the amount that remains in your account. At that point, your acccount balance will fall to zero, and your account will be closed. You will not owe any fees that final year. You will have to adjust the formulas in the final year to reflect this.

image text in transcribed

Q1 How many years will it take you to spend down your entire retirement account?

Q2 What age will you be that year?

Q3 What will be the amount of your final withdrawal?

Q4 What is the total amount you will have paid in fees?

Q5 What is the total amount you will have earned in returns?

Q6 What is the total amount you will have withdrawn?

Q7 Suppose you want your yearly withdrawal figure to be such that you will have $60,000 available for spending after taxes. (Tip: Make a copy of the "My retirement- Tables" worksheet, and rename the copy "Q7". In the green table, use goalseek to find the withdrawal amount that will leave you $60,000 after paying taxes) How much will you need to withdraw every year? Now at what age will you deplete your account?

Q8 Go back to assuming $60,000 yearly withdrawal. Suppose you have created a will saying that any amount left in the account at your death will be passed on to your heirs. If you die exactly 15 years after you retire, how much will your heirs receive from the account?

Q9 Suppose you want to make sure that in case you live to be 90, you don't run out of money in your retirement account before that. What fixed yearly withdrawal amount will allow you to stretch out the money in your account till your 90th birthday? (Tip: Make a copy of the my retirement-Tables" worksheet. Rename the copy "Q9." Make sure that all the rows until age 100 contain appropriate formulas. Then use Excel's "goalseek" feature to find the fixed amount that will target your account balance at age 90 to $0.)

Q10 Suppose on the day you retire, a financial company offers you the following deal: if you give them the $1,000,000 that is in your account, they will send you a check for $50,000 every year for as long as you live. Your heirs will get nothing. Assume the financial company is totally legit and realiable, and their payments to you will also be taxed at 12% rate. Would you accept the deal? (Tip: There is no right answer for this) Explain all factors that will affect whether you accept the deal or not.

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image_2

Step: 3

blur-text-image_3

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Venture Capital And The Finance Of Innovation

Authors: Andrew Metrick, Ayako Yasuda

3rd Edition

1119490111, 978-1119490111

More Books

Students also viewed these Finance questions