FIN 3100 Principles of Finance

Case Study

Barry and Samantha Harris Retirement Savings

!!!! Prepare a formal advisory investing letter for Harris Family using the scenario below!!!!

(Hint: you can start from filling the Excel template. Once you have all values calculated in Excel, then you can work on the letter, and attach or insert your excel tables within the letter for submission.)

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 15 years in retirement (a life expectancy of 81). Between their 401k and IRA accounts they currently have $66,000 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. They expect inflation to be 2% per year for the rest of their lives. They also expect to earn 10.5% per year (the average return on Blue Chip stocks) on their investments, both now and in retirement.

Conduct an analysis of their retirement planning needs and provide them with a professionally written 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 fifteen years of retirement to have the purchasing power of 80% of their current income?

Assuming they will continue to earn 10.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 fifteen 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 10.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 period (ordinary annuity).

Barry and Samantha Harris - Retirement Calculations

Step 1:

Step 2:

Step 3:

What annual income will they need in eacch year during retirement?

How much will they need in their account when they start retirement?

Recalculate using 9%

They will need the net present value of the fifteen years of income.

Annual Income

85,000.00

Calculate the net present value by discounting the fifteen years of income at the rate of return.

How much will they need when they start retirement?

80%

68,000.00

The amount will be different depending on whether they withdraw the money at the beginning or end of the year.

Adjusted for inflation

Initial amount (PV) @ age 46

(68,000.00)

Calculated using Excel

Calculated using Excel

Inflation rate

2.0%

End of year

End of year

Years

20

Rate of Return

?

Rate of Return

?

Future Value @ age 66

Net Present Value

?

Net Present Value

?

Annual inflation adjusted income in retirement

Calculated with the calculator

Calculated with the calculator

Age

Relative Year

Annual Income needed

Relative Year

End of year

Relative Year

End of year

67

1

?

1

?

1

?

68

2

?

2

?

2

?

69

3

?

3

?

3

?

70

4

?

4

?

4

?

71

5

?

5

?

5

?

72

6

?

6

?

6

?

73

7

?

7

?

7

?

74

8

?

8

?

8

?

75

9

?

9

?

9

?

76

10

?

10

?

10

?

77

11

?

11

?

11

?

78

12

?

12

?

12

?

79

13

?

13

?

13

?

80

14

?

14

?

14

?

81

15

?

15

?

15

?

Sum =

?

Sum =

?

How much will the need to save each month to have what they need when they retire?

How much will the need to save each month to have what they need when they retire?

Withdrawal timing

End of Year

Current Savings

Present Value

?

Withdrawal timing

End of Year

Interest Rate

I/Y

?

Current Savings

Present Value

?

Months to Retirement

# of periods N

?

Interest Rate

?

Amount Needed

Future value

?

Months to Retirement

?

Future value

?

Solve for the payment amount

PMT

?

Solve for the payment amount

?