Question

In: Finance

***PLEASE USE EXCEL AND SHOW FORMULAS**** Barry and Samantha are starting to take their retirement planning...

***PLEASE USE EXCEL AND SHOW FORMULAS****

Barry and Samantha 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 20 years in retirement (a life expectancy of 86). Between their 401k and IRA accounts they currently have $84,295 in retirement savings. They currently have a combined income of $80,000 per year and expect to be able to live comfortably in retirement with 85% 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.5% per year on their investments, both now and in retirement.

Conduct an analysis of their retirement planning needs and provide them with a professionally written letter. Use the rubric provided when preparing the 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 twenty years of retirement to have the purchasing power of 85% of their current income?

Assuming they will continue to earn 11.5% on their investments, how much money will they need to have in their retirement accounts when they retire so that it will provide the twenty years of income?

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 that they only earn 9% on their investments, instead of 11.5%. 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 year (ordinary annuity).

Solutions

Expert Solution

Part A)

Annual expenses equivalent to the 85% of the current purchasing power can be calculated as follows:

Current Income = $80,000

85% of the current purchasing power = 85% * $80,000

85% of the current purchasing power = $68,000

Annual expenses will increase at the rate of inflation i.e, 2.5% pa. So the annual expenses in the age of 67 = 85% of the current purchasing power * (1+Rate of Inflation)Number of years

Annual expenses in the age of 67 = $68,000 * (1+2.5%)21

Annual expenses in the age of 67 = $114,211.57

Similarly, we'll calculate the expenses for the remaining years adjusted for inflation:

Part B)

For calculating the amount of retirement corpus we can make use of Goal seek function in excel. Excel > Data > What If Analysis > Goal Seek.

Here we have assumed opening balance of the retirement corpus to be $1, the annual expenses are taken as derived in the previous part, and closing balance is the difference of Opening Balance and the annual expenses.

Since we'll continue to earn 11.5% on the retirement corpus, hence the opening balance for the following year will be Closing balance of the previous year * (1+Rate of return).

Calculations are as follows:

The results of Goal Seek functions are as follows:

Hence the amount of retirement corpus at the age of 66 should be $1,152,095.67.

Part C)

Their current savings are = $84,295

The value of current savings at the age of 66 = Current Savings * (1+Rate of return)Number of years

Rate of return = 11.5%

Number of years = 20

Value of current savings at the age of 66 = $84,295 * (1+11.5%)20

Value of current savings at the age of 66 = $743,531.14

Retirement Corpus requirement = $1,152,095.67

Deficit = Retirement Corpus - Value of savings

Deficit = $1,152,095.67 -  $743,531.14 = $408,564.53

So, Barry and Samantha needs to save $408,564.53 more by the end of age 66 to have sufficient amount in their retirement corpus.

Value of annual savings can be calculated using Future value of annuity equation:

Future Value of Annuity(FVA) = Periodic payments * [(1+Rate of return)Number of years - 1]/Rate of return

FVA = $408,564.53

Rate of return = 11.5%

Number of years = 20

$408,564.53 = Periodic payments * [(1+11.5%)20 - 1] / 11.5%

Periodic payments = $6,007.85

Hence, Barry and Samantha needs to save $6,007.85 annually to fund their retirement corpus.

Part D)

Calculation based on 9% rate of return instead of 11.5%

New retirement corpus amount = $1,160,054.49

Their current savings are = $84,295

The value of current savings at the age of 66 = Current Savings * (1+Rate of return)Number of years

Rate of return = 9%

Number of years = 20

Value of current savings at the age of 66 = $472,423.80

Deficit = Retirement Corpus - Value of savings

Deficit = $1,160,054.49 -  $472,423.80 = $687,630.68

So, Barry and Samantha needs to save $687,630.68 more by the end of age 66 to have sufficient amount in their retirement corpus.

Value of annual savings can be calculated using Future value of annuity equation:

Future Value of Annuity(FVA) = Periodic payments * [(1+Rate of return)Number of years - 1]/Rate of return

FVA = $687,630.68

Rate of return = 9%

Number of years = 20

$687,630.68 = Periodic payments * [(1+9%)20 - 1] / 9%

Periodic payments = $13,440.76

Hence, Barry and Samantha needs to save $13,440.76 annually to fund their retirement corpus.


Related Solutions

Barry and Samantha are starting to take their retirement planning seriously. They are both 51 and...
Barry and Samantha are starting to take their retirement planning seriously. They are both 51 and plan to retire in 15 years at the age of 66. They expect to live 20 years in retirement (a life expectancy of 86). Between their 401k and IRA accounts they currently have $132,400 in retirement savings. They currently have a combined income of $85,000 per year and expect to be able to live comfortably in retirement with 80% of their current purchasing power....
Barry and Samantha are starting to take their retirement planning seriously. They are both 51 and...
Barry and Samantha are starting to take their retirement planning seriously. They are both 51 and plan to retire in 15 years at the age of 66. They expect to live 20 years in retirement (a life expectancy of 86). Between their 401k and IRA accounts they currently have $132,400 in retirement savings. They currently have a combined income of $85,000 per year and expect to be able to live comfortably in retirement with 80% of their current purchasing power....
Barry and Samantha are starting to take their retirement planning seriously. They are both 51 and...
Barry and Samantha are starting to take their retirement planning seriously. They are both 51 and plan to retire in 15 years at the age of 66. They expect to live 20 years in retirement (a life expectancy of 86). Between their 401k and IRA accounts they currently have $132,400 in retirement savings. They currently have a combined income of $85,000 per year and expect to be able to live comfortably in retirement with 80% of their current purchasing power....
Barry and Samantha are starting to take their retirement planning seriously. They are both 51 and...
Barry and Samantha are starting to take their retirement planning seriously. They are both 51 and plan to retire in 15 years at the age of 66. They expect to live 20 years in retirement (a life expectancy of 86). Between their 401k and IRA accounts, they currently have $132,400 in retirement savings. They currently have a combined income of $85,000 per year and expect to be able to live comfortably in retirement with 80% of their current purchasing power....
Please use excel and show formulas You are planning to invest $10,000 per year to save...
Please use excel and show formulas You are planning to invest $10,000 per year to save for retirement. Assume you'll get an annual rate of return of 7%, and that you'll make the yearly investments at the beginning of each period. If you plan on retiring at 65, compare the difference between starting these yearly investments at 30 years old versus starting at 40 years old.
Barry and Samantha Harris – Retirement Savings You must work on your own spreadsheet with Excel...
Barry and Samantha Harris – Retirement Savings You must work on your own spreadsheet with Excel skills or concepts. Barry and Samantha are starting to take their retirement planning seriously. They are both 45 and plan to retire in 20 years at the age of 65. They expect to live 15 years in retirement (a life expectancy of 80). Between their 401k accounts they currently have $66,000 in retirement savings. They currently have a combined income of $80,000 per year...
***Excel is required to solve this problem. Please use excel and show all formulas used in...
***Excel is required to solve this problem. Please use excel and show all formulas used in each cell I would really appreciate the work*** Three-Stage FCFE Model: Biomet Inc., designs, manufactures and markets reconstructive and trauma devices, and reported earnings per share of $0.56 in 1993, on which it paid no dividends. (It had revenues per share in 1993 of $2.91). It had capital expenditures of $0.13 per share in 1993 and depreciation in the same year of $0.08 per...
PLEASE SHOW ALL WORK IN EXCEL!!!!!!! SHOW CALCULATIONS / FORMULAS IN EXCEL!!!!!!!! Voters arrive at a...
PLEASE SHOW ALL WORK IN EXCEL!!!!!!! SHOW CALCULATIONS / FORMULAS IN EXCEL!!!!!!!! Voters arrive at a polling station at an average of 4 per minute (Poisson) where the service rate is 50 per hour (Poisson). a. What is the average number of voters in the system with 5 voter booths? (10 pts) b. What is the minimum number of voting booths needed to keep the average time in the system under three minutes? (10 pts) PLEASE SHOW ALL WORK IN...
Troy and Tammy are starting to take their retirement planning seriously. They are both 46 and...
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...
Tom and Debbie are starting to take their retirement planning seriously. They are both 46 and...
Tom and Debbie 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 $67,966 in retirement savings. They currently have a combined income of $88,000 per year and expect to be able to live comfortably in retirement with 80% of their current purchasing power....
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT