Question

In: Math

John is 37 years old and would like to establish a retirement plan. Develop a spreadsheet...

John is 37 years old and would like to establish a retirement plan. Develop a spreadsheet model that could be used to assist John with retirement planning. Your model should include the following input parameters:

John’s current age = 37 years,

Johns current total retirement savings = $259,000,

Annual rate of return on retirement savings = 4 percent,

Johns current annual salary = $145,000,

Johns expected annual percentage increase in salary = 2 percent,

Johns percentage of annual salary contributed to retirement = 6 percent,

Johns expected age of retirement = 65,

Johns expected annual expenses after retirement (current dollars) = $90,000,

Rate of return on retirement savings after retirement = 3 percent,

Income tax rate postretirement = 15 percent

Assume that John’s employer contributes 6% John’s salary to his retirement fund. John can make an additional annual contribution to his retirement fund before taxes (tax free) up to a contribution of $16,000. Assume he contributes $6,000 per year. Also, assume an inflation rate of 2%.

Managerial Report Your spreadsheet model should provide the accumulated savings at the onset of retirement as well as the age at which funds will be depleted (given assumptions on the input parameters).

Outline the factors that will have the greatest impact on his retirement.

Show all work in excel.

Solutions

Expert Solution

milcah answered 1 year ago
Next > < Previous

Related Solutions

You are 30 years old today and are planning for retirement. You would like to work...
You are 30 years old today and are planning for retirement. You would like to work for 30 more years and retire when you are 60. You currently earn $100,000 per year. You expect inflation will average 3% per year over the next 30 years. To simplify this problem, assume your income is paid annually and you receive a salary of $100,000 today. You will make your first retirement withdrawal in 31 years at the age of 61. To simplify...
Your client is 37 years old. She wants to begin saving for retirement, with the first...
Your client is 37 years old. She wants to begin saving for retirement, with the first payment to come one year from now. She can save $13,000 per year, and you advise her to invest it in the stock market, which you expect to provide an average return of 12% in the future. If she follows your advice, how much money will she have at 65? Do not round intermediate calculations. Round your answer to the nearest cent. $   How...
Your client is 37 years old. She wants to begin saving for retirement, with the first...
Your client is 37 years old. She wants to begin saving for retirement, with the first payment to come one year from now. She can save $5,000 per year, and you advise her to invest it in the stock market, which you expect to provide an average return of 11% in the future. If she follows your advice, how much money will she have at 65? Do not round intermediate calculations. Round your answer to the nearest cent. $ How...
Your client is 37 years old. She wants to begin saving for retirement, with the first...
Your client is 37 years old. She wants to begin saving for retirement, with the first payment to come one year from now. She can save $6,000 per year, and you advise her to invest it in the stock market, which you expect to provide an average return of 6% in the future. If she follows your advice, how much money will she have at 65? Do not round intermediate calculations. Round your answer to the nearest cent. $   How...
Jeremy would like to retire in 25 years. He would like his retirement income to be...
Jeremy would like to retire in 25 years. He would like his retirement income to be $250,000, and this figure should grow at the same rate as inflation, expected to be 2 percent annually. He expects to live 30 years after he retires, and plans to leave $3 million to TYU after he dies. Jeremy currently has $1,000,000 in his retirement fund. The fund is expected to earn 6 percent annually. Assuming that Jeremy increases his annual retirement savings by...
John is 37 years old this year. He and his wife have a daughter, Sara, who...
John is 37 years old this year. He and his wife have a daughter, Sara, who is 12 years old. John wants to provide for the family for the next 10 years until Sara is 22 years old and has completed her university education. He estimates that the income needed for the family is $70,000 and Sara’s university education is $42,000. He also wants to provide a total amount of $180,000 as retirement income for his wife and he estimates...
Can you establish a KEOGH retirement plan even if you are covered by a corporate retirement...
Can you establish a KEOGH retirement plan even if you are covered by a corporate retirement in your primary job? If the owner of a Keogh plan dies, what happens to the funds in the Keogh? Can you have a Keogh and an IRA plan or even a Roth IRA?
Shenli would like to plan for retirement. With the help of afinancial planner, she estimates...
Shenli would like to plan for retirement. With the help of a financial planner, she estimates that she will need $3,000,000 when she retires 30 years from now. Assume her investments produce returns of 7% per year. How much would Shenli need to save each year if she makes equal end of year deposits for the next 30 years that she works?
Mrs.McNamara just turned 44 and is beginning to plan for her retirement. She would like to...
Mrs.McNamara just turned 44 and is beginning to plan for her retirement. She would like to make annual contributions to a retirement fund beginning with X on her 45th birthday and increasing by $500 each year until her last contribution on her 64th birthday. These contributions should fund annual retirement checks beginning with $50,000 on her 65th birthday and increasing by 5% each year until her last retirement check issued on her 84th birthday. The fund will earn interest at...
Michael is 30 years old. He comes up with a plan to save for his retirement...
Michael is 30 years old. He comes up with a plan to save for his retirement at 65 years. Currently, he has saved $40,000 in a balanced superannuation account earning 5.5% annually. He has set himself a retirement target of $2,000,000. How much must be deposited in his superannuation account starting next year each year to reach his target? Assume his contributions are to be invested at 5.5% annually. Please, round your answer to two decimal places.
ADVERTISEMENT
Subjects
ADVERTISEMENT
Latest Questions
ADVERTISEMENT