Question

In: Finance

Please use Excel financial functions or algebraic time value of money equations. Prof. Business has a...

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?

Solutions

Expert Solution

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.


Related Solutions

Solve the following financial problems using the time value of money functions in Excel (PV, FV,...
Solve the following financial problems using the time value of money functions in Excel (PV, FV, PMT, NPER, RATE, EFFECT) OR using your financial calculator. Assume you deposit $3,000 today, and $300 at the end of each year, in an account earning 4% per year for 20 years. What is the future value? General Electric has an unfunded pension liability of $300 million that must be paid in 15 years. The CFO deposits $10 million in account today to help...
Solve the following financial problems using the time value of money functions in Excel (PV, FV,...
Solve the following financial problems using the time value of money functions in Excel (PV, FV, PMT, NPER, RATE, EFFECT) OR using your financial calculator. Assume you deposit $3,000 today, and $300 at the end of each year, in an account earning 4% per year for 20 years. What is the future value? General Electric has an unfunded pension liability of $300 million that must be paid in 15 years. The CFO deposits $10 million in account today to help...
Use the Excel time value of money functions to complete the following problems. Highlight your answers....
Use the Excel time value of money functions to complete the following problems. Highlight your answers. Upload your solution on blackboard. 1) Manny’s grandparents gave him $1,800 for his birthday. He opened a savings account that pays 4% annually. How much money will he have in 7 years if he does not make any withdrawals? N= I= PV= PMT= FV= 2) Your parents will retire in 25 years. They currently have $100,000 in savings. They think they will need $1,000,000...
Can you use Excel for time value of money computations such as NPV or IRR? If...
Can you use Excel for time value of money computations such as NPV or IRR? If so, what are the functions? Please no handwritten answers
Needed into a excel page format We need to use the basic time value of money...
Needed into a excel page format We need to use the basic time value of money function to calculate the answer to this question. According to th basic TVM function: PV = FV / (1 + r)n where FV is future value, PV is present value, r is the discount rate and n is the number of periods. PV = \frac{500}{(1 + 0.15)^1} + \frac{1000}{(1 + 0.15)^5} PV = 434.7826 + 497.1767 = $931.96 b. This is an ordinary annuity,...
Problema 1 (Please Solve with Excel) The Time Value of Money Jackson L. Brown Valuation of...
Problema 1 (Please Solve with Excel) The Time Value of Money Jackson L. Brown Valuation of Expected Earnings Dr. Michael Niesvwiadomy (Legal Consultant and an Assistant Professor at the University of North Texas). Jackson L. Brown was born on January 15, 1960 in Austin, Texas. He was raised in Plano, Texas, where his family moved a few years after his birth. There he attended and graduated from Plano High School. Jackson had planned to go to college but due to...
What is the Time Value of Money and how do financial managers use it to make...
What is the Time Value of Money and how do financial managers use it to make business decisions?
Time value of money is a financial concept that illustrates how the value of money grows...
Time value of money is a financial concept that illustrates how the value of money grows over time. This takes into consideration that the money can be invested at a specified interest rate, that grows. One financial concept is present value (PV) and another financial concept is future value (FV). Discuss and show one example of how the present value formula is a good method to determine how much is needed to save monthly, in order to have a specified...
Time Value Money Ice Breaker Discussion Please take a moment to click on "Time Value money"...
Time Value Money Ice Breaker Discussion Please take a moment to click on "Time Value money" topic and and share your understanding to that concept with the class.
How do healthcare entities use the “time value of money” to increase financial performance?
How do healthcare entities use the “time value of money” to increase financial performance?
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT