Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

See attached excel file. Please fill in the shaded boxes in the worksheet. Answer the following questions on the next Excel worksheet labeled Capital Needs.Rely

image text in transcribed

See attached excel file. Please fill in the shaded boxes in the worksheet.

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 if identified in Question 1.You will have 3 separate solutions here as well.

image text in transcribed 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 ifdentified in Question 1. You will have 3 separate solutions here as well. Refer to the discussion in the text on pages 724 through 729 for assistance on this. To check your final answer you can actually substitute the data above with the data in the example on page 724 of the text and you should get exactly the results that you see in the first two rows of Exhibit 15.21 on page 729. If you check your answer this way, make sure you re-enter the data as above before submitting for grading! ment s have nt age of to help y on the ed cells. ital this ulate the ll. e example o rows of he data as Given Information: Current Age: Retirement Age: Mortality Age: Inflation: Investment ROR: Current Salary: Wage Replacement: Social Security Estimate: Calculations: Total Needs in Today's Dollars: Less Social Secuity in Today's Dollars: Annual Amount Needed in Today's Dollars: IARR: Pure Annuity Models CP PPP 1st year need at retirement: Capital needed at retirement: Monthly savings to meet Capital Need: >>>>>>>>>>>>>>>>>>>Do Not Alter Anything Below This Row

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Foundations Of Financial Management

Authors: Stanley Block, Geoffrey Hirt, Bartley Danielsen, Doug Short, Michael Perretta

11th Canadian Edition

1259024970, 978-1259265921

More Books

Students also viewed these Finance questions

Question

Were the participants sensitized by taking a pretest?

Answered: 1 week ago