Part 1: Troy & Tammy Taylor - Retirement Savings (15 points)

Troy and Tammy are starting to take their retirement planning seriously. They are both 46 and plan to retire in 20 years at the age of 66. They expect to live 15 years in retirement (a life expectancy of 81). Between their 401k and IRA accounts they currently have $87,432 in retirement savings.

They currently have a combined income of $95,000 per year and expect to be able to live comfortably in retirement 20 years from now with 80% of their current purchasing power. They expect inflation to be 2.5% per year for the rest of their lives. They also expect to earn 11.0% per year (the average return on Blue Chip stocks) on their investments, both now and in retirement.

Conduct an analysis of their retirement planning needs and provide them with a professionally written letter. In the letter and attached schedules, provide information that answers the following questions. Please include a description of the relevant assumptions and any explanatory comments that make the results easier to understand.

What amount of annual income will they need (after adjusting for inflation) in each of the fifteen years of retirement to have the purchasing power of 80% of their current income?

Assuming they will continue to earn 11.0% on their investments, how much money will they need to have in their retirement accounts when they retire so that it will provide the fifteen years of income? (Remember to consider that they will continue to earn interest on their investments during retirement).

Taking into account what they currently have in savings, how much will they have to save each month to meet their retirement needs?

Sensitivity analysis: Redo the analysis assuming they only earn 9% per year on their investments, instead of 11.0%. Determine the needed amounts so they have the money they need in retirement.

Note: Assume that all payments will be made at the end of the period (ordinary annuity).

When submitting the letter, please include your Excel file.

Part 2:The Maxwell Family - College Savings (15 points)

Matt and Martha Maxwell live in an upscale neighborhood in Orem, Utah. Matt is a partner in the family owned automotive painting business. Martha stays home with their child, Melanie, who is age 5. After visiting with their financial planner, the couple became concerned that they were spending too much and not putting enough funds aside for Melanie's future educational needs. Matt earns $105,000 per year, but with the rising costs of education, they are concerned.

Matt is an alumnus of Ohio State University, with tuition and book expenses of approximately $13,600 per year. Martha graduated from Utah Valley University. The expense for tuition and books there is estimated at about $7,500 per year. When Melanie turns 18, the couple wishes to send her to one of these two exceptional universities. They have a slight preference for Utah Valley University. The problem, however, is that with the rate at which tuition is increasing the Maxwell's are not sure they can save enough money and they have decided they do not want to borrow to pay for Melanie's education.

Assume the tuition at both universities will increase at an annual rate of 6% per year for the foreseeable future. Living expenses are currently estimated at $8,000 per year at both schools. This expense is expected to grow at only 3% per year. Further, assume that Maxwell's can deposit their money into a growth oriented mutual fund at the Salt Lake City based mutual fund company, Wasatch Advisors which has historically earned 12% per annum.

The couple wishes to save by having a pre-determined amount automatically withdrawn form their bank account at the end each month. They plan to contribute from now until Melanie starts college. When Melanie starts college, at the beginning of his freshman year, they will stop making contributions. They want to have enough in their account to cover all four years of college expenses when Melanie starts college. They will make annual withdrawals from the account to cover both tuition and living expenses for Melanie at the beginning of his freshman, sophomore, junior, and senior years. When the withdrawal for the senior year is made the account balance will be zero. Remember, that the funds will continue to earn interest while Melanie is in college.

Complete a thorough analysis and write a professional letter to the Maxwell's (who don't understand finance) explaining the analysis you performed, why you performed it, the results and conclusions. In the letter and attached schedules provide information that answers the following questions.

What will be the tuition expense, living expense, and total expense for each of the four years that Melanie will attend college? Provide the information for each University.

What amount will be needed in the account when Melanie starts his freshman year if he attends Ohio State? What amount if he attends UVU?

How much money will Matt and Debra have to deposit at the end of each month to allow Melanie to attend Ohio State? How much money will have to be deposited per month to allow Melanie to attend Utah Valley University? Assume that Matt and Debra stop making deposits when Melanie starts college.

The Pearson's are concerned that given the current market performance the mutual fund will only earn 10% per year. If the return is only 10% how much will be needed in the account when Melanie starts college and how much will have to be deposited per month for Melanie to have sufficient funds to attend each school?

Note: Assume that Melanie will pay his tuition and living expenses at the beginning of the year.

Include your Excel file with your submission.