Show for all calculations :

Total Needs in Todays dollars

Less Social Security in Todays dollars

Annual amount needed in Todays Dollar:

IARR:

Show all formulas for Models Table .

Do not use the other answer on Chegg it is wrong

Hanford MacDwaddy is 47 years old today and makes $78,000 per year. His wage replacement ratio has been determined to be 72%. He expects inflation will average 3.5%/ year over his lifetime. He expects to earn 8% on his investments and he plans to retire at age 67 . You have helped him determine his estimated social security retirement benefit at his full retirement age of 67 to be $18,000 per year (in today's dollars). Due to longevity in his family he wants you to help him determine his capital needs for retirement assuming he lives to age 97. Answer the following questions on the next Excel worksheet labeled "Capital Needs". Rely on the use of the TVM functions we have learned so far and keep your answers within the shaded cells. Questions: 1. Calculate Hanford's capital needed at retirement at age 67 using the Pure Annuity, Capital Preservation and PPP models. You will have 3 separate solutions here. Remember we do this calculation in BEGIN mode since we need the money each year at the start of each year. 2. Calculate the monthly savings Hanford must make at the end of each month to accumulate the capital needed as identified in Question 1. You will have 3 separate solutions here as well. All the steps needed to complete this assignment are discussed thoroughly on pages 628 to 632 in the 10th edition textbook (pages 722 to 727 in 9th edition) To check your final answer you can actually substitute the data above with the data in the example on page 629 (722 in 9th edition) of the text and you should get exactly the results that you see in the first two rows of Exhibit 15.18 on page 632 (727 in 9th edition). If you check your answer this way, make sure you re- Hanford MacDwaddy is 47 years old today and makes $78,000 per year. His wage replacement ratio has been determined to be 72%. He expects inflation will average 3.5%/ year over his lifetime. He expects to earn 8% on his investments and he plans to retire at age 67 . You have helped him determine his estimated social security retirement benefit at his full retirement age of 67 to be $18,000 per year (in today's dollars). Due to longevity in his family he wants you to help him determine his capital needs for retirement assuming he lives to age 97. Answer the following questions on the next Excel worksheet labeled "Capital Needs". Rely on the use of the TVM functions we have learned so far and keep your answers within the shaded cells. Questions: 1. Calculate Hanford's capital needed at retirement at age 67 using the Pure Annuity, Capital Preservation and PPP models. You will have 3 separate solutions here. Remember we do this calculation in BEGIN mode since we need the money each year at the start of each year. 2. Calculate the monthly savings Hanford must make at the end of each month to accumulate the capital needed as identified in Question 1. You will have 3 separate solutions here as well. All the steps needed to complete this assignment are discussed thoroughly on pages 628 to 632 in the 10th edition textbook (pages 722 to 727 in 9th edition) To check your final answer you can actually substitute the data above with the data in the example on page 629 (722 in 9th edition) of the text and you should get exactly the results that you see in the first two rows of Exhibit 15.18 on page 632 (727 in 9th edition). If you check your answer this way, make sure you re