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 10 months 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?

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.

You are 25 years old and decide to start saving for your retirement. You plan to...

You are 25 years old and decide to start saving for your retirement. You plan to save $3000 at the end of each year (so the first deposit will be one year from now), and will make the last deposit when you retire at age 65. Suppose you earn 10% per year on your retirement savings. How much will you have saved for retirement? How much will you have saved if you wait until age 35 to start saving (again,...

Subjects