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 seem 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 previou 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 ow. any fees that final year. You will have to adjust the formulas in the final year to reflect this. 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 retirementTables" 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 90 th 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 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
