Martha and Duane Johnson are worried about having enough annual income when they retire. After talking with them, you document the following:

-They are both 30 years old.

-They both expect to retire in 40 years (at approximately age 70).

-Together they currently make $ 125,000 per year.

-They agree with the common assumption that, with the expected Social Security payments, they can live quite well after retirement on 80% of their combined salary in their last year of work.

-They have no dependents and do not expect to have any.

-They currently have no retirement savings account.

Prepare an analysis of their retirement by answering the following questions for the Johnsons. Copy the needed excel calculators from your template to a new file to show your calculations.

1: What is their expected combined salary in their last year of work if inflation averages 2.5% each year? Assume 40 years for the number of periods. What is 80% of that amount?

2: How much would they need in their retirement savings account to provide this target annual income starting at age 70 if they can earn a 6% APR and expect to live 25 years (approximately age 95)? Assume 25 years for the number of periods, ordinary annuity.

3: How much would they need to save annually to reach this amount in their retirement savings account if they can earn 8% APR? What percentage is this annual payment of their current annual income? Assume 40 years for the number of periods, ordinary annuity.

4: They just purchased a lottery ticket. They want to know (if they win) how much they would need to deposit today to reach the retirement savings amount needed at age 70 if they can earn 8% APR? Assume 40 years for the number of periods.

5: If stocks return 10% APR and bonds return 5% APR and they rebalance their portfolio every year, what percentage of their portfolio should be in a well-diversified stock portfolio/fund and what percentage should be in a well-diversified bond portfolio/fund to earn an average return of 8%? Show the formula and each step toward the solution.