Part 1. Retirement Analysis Martha and George Smith are worried about having enough annual income when they retire. After talking with them, you document the following: - They are both 25 years old. - They both expect to retire in 45 years (at approximately age 70 ). - Together they currently make $130,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. Using the provided excel template, prepare an analysis of their retirement by answering the following questions for the Smiths. Copy the needed excel calculators from your Ch 4 TVM Quick Calculators to the Case 2 excel template file to show your calculations. Place your copied calculators vertically in sheet two of the excel file. 1. What is their expected combined salary in their last year of work if inflation averages 2.5% each year and their salaries increase by inflation each year? Assume 45 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 5\% APR and expect to live 25 years (approximately to 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 45 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 45 years for the number of periods. 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 45 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 equations and each step toward the solution. Just type as text in the excel file. Hint: Need two equations. Substitute the 2nd equation into the first equation solve for one of the % Weights. Then use the 2nd equation to solve for the second weight. 8%=((%WeightofStocks)10%)+((%WeightofBonds)5%)%WeightofStocks=1%WeightofBonds Present to the class. Present to the class your analysis using your excel file