Please use Excel financial functions or algebraic time value of money equations
ID: 2816112 • Letter: P
Question
Please use Excel financial functions or algebraic time value of money equations to answer these questions in the spreadsheet. Give a screenshot of what you did in the excel and give the function.
Prof. Business has a self-managed retirement plan through her University and would like to retire in 10 years and wonders if her current and future planned savings will provide adequate future retirementincome. Here's her information and goals. >Prof. Business wants a 20-year retirement annuity that begins 10 years from todaywith an equal annual payment equal to $100,000 today inflated at 2% annually over 10 years. Her first retirement annuity payment would occur 10 years from today. She realizes her purchasing power will decrease over time during retirement. >Prof. Business currently has $560,000 in her University retirementaccount. she expects these savings and anyfuture deposits into her University and anyother retirement account will earn 7% compounded annually. Also, she expects to earn this same 7% annual return after she retires., Answer the followingquestionsto help Prof. Business finalize her retirement planning. 1. What is Prof. Business' desired annual retirement income? 2.How much will Prof. Business need 10 years from todayto fund her desired retirement annuity? 3. In addition to the $560,000 balance today, Prof. Business will fund her future retirement goal from question 2 by making 10 annual equal deposits at 7% compounded annually into her retirement accounts starting a year from today (the last deposit will be madeExplanation / Answer
1. Desired Annual Income= $100,000*(1+2%)10= $121,899
2. Annuity payment every year(A)= $121,899
Rate(r)=7%
Time period(T)=20 years
Present value of Annuity 10 years from now= (A/r)*(1-1/(1+r)T)=(121,899/0.07)*(1-1/(1+7%)20)
=$1,291,404
Thus, the professor will need $1,291,404 10 years from now to fund the annuity payment plan desired.
3. $560,000 is the initial investment which the professor has which compunded at 7% annually for 10 years with yield 560,000*(1+7%)10= $1,101,605. Thus the difference which the professor needs to cover is $1,291,404-$1,101,605= $189,800.
As mentioned, the last installment will coincide with the retirement date thus that will not yield the annual rate of 7%. Thus the first installment will yield 7% for 9 years, second for 8 years and so on.
Thus, CFi*(1+7%)i-1=$189,800, i from 1 to 10.
Thus, based on the formula above, the annuity payment will be $13,737 which the professor needs to make to cover the difference in the value to fund the retirement to its fullest.
4. Annual contribution to professor in Year 11 from now will be $121,899 which will increase @ 2% per annum.
Present value= Cash flow/(1+7%)T. Cash flow for year post year 11 will be increasing by 2% with T starting from 1 to 20 for each of the cash flows. Thus, adding the individual present values will result in $1,501,808 which is the amount the professor will need to fund the new retirement goals.
5. $560,000 is the initial investment which the professor has which compunded at 7% annually for 10 years with yield 560,000*(1+7%)10= $1,101,605. Thus the difference which the professor needs to cover is $1,501,808-$1,101,605= $400,204.
As mentioned, the last installment will coincide with the retirement date thus that will not yield the annual rate of 7%. Thus the first installment will yield 7% for 9 years, second for 8 years and so on.
Thus, CFi*(1+7%)i-1=$400,204, i from 1 to 10.
Thus, based on the formula above, the annuity payment will be $28,966 which the professor needs to make to cover the difference in the value to fund the retirement to its fullest.
6. (a) Payment of $19,000 will take place at the end of Year 1 which will be compounded for 9 years at 7% resulting in $34,931. Similarly at the end of Year 2, $19,000 will increase by 2% to $19,380 which will be compounded at 7% annually for 8 years resulting in $33,298 and so on. The total value from this compounding will result in $284,300 at the beginning of the retirement. The total money thus the professor will have will be $1,101,605(derived from the $560,000 compounding)+ $284,300= $1,385,904
(b) Using the formula PV=(A/r)*(1-1/(1+r)T)
where PV= $1,385,904, r=7% and T=20 years
A gets computed as $130,820