In: Finance
Please use Excel financial functions or algebraic time value of money equations.
Prof. Business has a self-managed retirement plan through her University and would like to retire in 8years and wonders if her current and future planned savings will provide adequate future retirement income. Here’s her information and goals.
Prof. Business wants a 20-year retirement annuity that begins 8 years from today with an equal annual payment equal to $110,000 today inflated at 2% annually over 8 years. Her first retirement annuity payment would occur 8 years from today. She realizes her purchasing power will decrease over time during retirement.
Prof. Business currently has $640,000 in her University retirement account. She expects these savings and any future deposits into her University and any other retirement account will earn 7.5% compounded annually. Also, she expects to earn this same 7.5% annual return after she retires.
Answer the following questions to help Prof. Business finalize her retirement planning.
1.What is Prof. Business’ desired annual retirement income?
2.How much will Prof. Business need 8 years from today to fund her desired retirement annuity?
3.In addition to the $640,000 balance today, Prof. Business will fund her future retirement goal from question 2 by making 8 annual equal deposits at 7.5% compounded annually into her retirement accounts starting a year from today (the last deposit will be made when Prof. Business retires). How large does this annual deposit need to be in addition to the initial $640,000 invested in Prof. Business’ retirement fund?
4.This annual figure from #3 is morethan the Prof.’s current annual contribution, which makes her feel a little anxious about her future planned retirement. Also, Prof. Business’ annual retirement account contribution is based on a percentage of her salary and will increase as her salary increases. So, let’s re-plan her retirement income. Let’s account for the fact that her and the University’s contributions to Prof. Business’ University retirement plan are based on a certain percentage of her salary and will increase as her salary increases. Based on this formula, her first upcoming end of the year deposit will be $20,200 and let’s assume that her annual deposit and salary will grow at a 2% annual rate over the remaining 7 years (8 total deposits) to Prof. Business’ retirement. These deposits are in addition to the $640,000 she currently has today in the University retirement plan. Answer the following based on these assumptions. a)How much money will Prof. Business have in her retirement account immediately after her last deposit 8 years from today? b)What would be the equal annual payment from her 20-year retirement annuity whose first payment occurs exactly 8 years from today?
Retirement planning for Prof. Business
Information given:
Retirement begins after 8 years. Therefore N (Period of
investment) = 8
Post retirement period = 20 Years. Therefore N (Period of
consumption) = 20
Inflation = 2%
Current savings -
University retirement account = $640,000
Expected return on investment: 7.50% p.a.
Return compounded: Annually
Annual cash flow required today: $110,000
This will grow at the rate of inflation (2%)
Q1 Solution:
Desired annual retirement income calculated using time value of money:
Present value of cash flow: $110,000
N (period) = 8 years
R (inflation rate) = 2%
FV (first cash flow value at retirement) = $128,882.53
In other words, current annual cash flow requirement will grow at 2% inflation annually to $128,882.53 in 8 years.
Below is the table giving details of annual cash flow requirement throughout 20 year retirement period (Rounded off).
End of year |
Amount |
8 |
128,883 |
9 |
131,460 |
10 |
134,089 |
11 |
136,771 |
12 |
139,507 |
13 |
142,297 |
14 |
145,143 |
15 |
148,046 |
16 |
151,006 |
17 |
154,027 |
18 |
157,107 |
19 |
160,249 |
20 |
163,454 |
21 |
166,723 |
22 |
170,058 |
23 |
173,459 |
24 |
176,928 |
25 |
180,467 |
26 |
184,076 |
27 |
187,758 |
Note:
1) This is just reflecting the annual cash flow requirement and not the PV required to meet the requirement cash flow.
2) Formula for calculating Future Value is :
where PV = Present value of the cash flow
r = rate at which the cash flow needs to be compounded. Ordinarily
this is desired rate of return but in this example it is
inflation.
n = period
I hope this was helpful. Please share your feedback. Thanks in advance.