Question

In: Finance

Please if you could do each problem, and show steps for excel! Thank you! You will...

Please if you could do each problem, and show steps for excel! Thank you!

You will graduate in a few years and start working and it’s never too early to start planning for your retirement and other financial events. Let’s fast forward to the beginning of your career. Here’s some assumptions to help you get started. Your starting annual salary will be $60,000. You plan to work for 43 years before retiring. You expect your salary to grow at an annual average rate of 4% after year 1 on the job. When you retire you want a 22-year retirement annuity that begins 43 years from today with an equal annual payment equal to 80% of your final working year salary. Assume the first retirement annuity payment would occur immediately upon retirement 43 years from today. You realize your purchasing power will decrease over time during retirement. Assume any retirement fund savings will earn an 7.8% compounded annually before and after retirement Answer the following questions to help finalize your retirement planning.

1. What is your expected final year working salary?

2. What is your desired annual retirement income?

3. How much will you need at retirement 43 years after the beginning of your career to fund your desired retirement annuity?

4. How much will you have to deposit annually in the form of an equal annual end of the year deposit over 43 years to fund your desired retirement annuity?

5. Re-work questions 3 and 4 assuming you would like to leave your grandkids $200,000 when you die 22 years after retirement.

6. Now, forget the grandkids and imagine you get $25,000 in graduation gifts from your family that you deposit into your retirement savings account at the beginning of your work career. We-work the previous question (#5) with this new initial deposit. How large does your annual deposit over your working career need to be in addition to the initial $25,000?

7. Looking at your answers in the last two questions, the annual amount, while doable, might be a bit of a financial stretch for you early in your career. Let’s redo the scenario in the last question where you have the $25,000 initial deposit but will deposit $5000 at the end of year for the first 10 years and then another equal annual amount at the end of each year for the remaining working years. How large does this second annual deposit need to be to meet your retirement savings goal from question #3?

Solutions

Expert Solution

Basic data Given:

a) starting annual salary = $ 60,000

b) Working years before retirement = 43 years (i.e.N)

c) expect salary to grow at an annual average rate of 4% after year 1   (i.e. g)

Answer:

1. What is your expected final year working salary?

$60,000 will be expected to grow @ 4 % per annum fro the Next 43 years hence, expected final year salary will be

= First salary x (1+g)N

= $60,000 x (1.04)43

= $60,000 x 5.40

= $ 3,24,000

2. What is your desired annual retirement income?

Desired annual Retirement income = 80% of your final working year salary

= 80% of $ 3,24,000

= $ 2,59,200

3. How much will you need at retirement 43 years after the beginning of your career to fund your desired retirement annuity?

in oreder to find out the amount needed in account at the time of retirement we have to find the present value(at the beggining of 43rd year ) of annual payments.

Year Annual Payment PVIFA @ 7.8% Present value of Payments

1st Payment

(at the beggining of 43rd year)

$2,59,200 1.00 $2,59,200

Remaining 21 payments

(44 th year to 65 th year)

$2,59,200 10.1726* $26,36,726
$28,95,926

Note: PVIFA = [1−(1+r)-n​ ] / r

= [ 1-(1+7.8%)-21] / 7.8%

=10.1726

4. How much will you have to deposit annually in the form of an equal annual end of the year deposit over 43 years to fund your desired retirement annuity?

Future value of an Annuity = P [ (1+r)n-1] / r

Where,Future value of an Annuity = $28,95,926

P = Yearly Annuity Payment

r = rate of return = 7.8%

n = no of years = 43 years

Future value of an Annuity = P [ (1+r)n-1] / r

$28,95,926 = P [ (1+7.8%)43-1] /7.8%

$28,95,926 = P *311.15

Yearly Annuity Payment (P) = $28,95,926 / 311.15

= $ 9,307 per annum

5. Re-work questions 3 and 4 assuming you would like to leave your grandkids $200,000 when you die 22 years after retirement.

Re-work questions 3

Present value (at the time of retirement) of $200,000 required at the end of 22 years after retirement

= $2,00,000 x 1/(1+r)n

= $2,00,000 x 1/(1.078)22

= $2,00,000 x 1/5.2193

= $38,319.27

Hence we have to increase the account balance by $38,319.27 at the end of retirement so that after 22 years there will be a balance of $2,00,000.

therefore , Total amount required @ end of 43 years = $28,95,926 + $38,319.27 = $29,34,245.27

Re-work questions 4 : How much will you have to deposit annually in the form of an equal annual end of the year deposit over 43 years to fund your desired retirement annuity?

Future value of an Annuity = P [ (1+r)n-1] / r

Where,Future value of an Annuity = $29,34,245.27

P = Yearly Annuity Payment

r = rate of return = 7.8%

n = no of years = 43 years

Future value of an Annuity = P [ (1+r)n-1] / r

$29,34,245.27= P [ (1+7.8%)43-1] /7.8%

$29,34,245.27 = P *311.15

Yearly Annuity Payment (P) =$29,34,245.27 / 311.15

= $ 9,430.32 per annum


Related Solutions

Please could you show full steps and no excel please Miss M had R100,000 to invest...
Please could you show full steps and no excel please Miss M had R100,000 to invest in the financial market. She chose to invest R10,000 in Asset A, which returned 12.0%; R40,000 in Asset B which returned 15.0%; R20,000 in Asset C which returned –5.0%, and the remaining in Asset D which returned 5%. The correlation between the assets are ρAB = 0.75, ρAC = 0.35, ρAD = 1, ρBC = –0.5, ρBD = 0.25, and ρCD = -1. The...
(Answers are given but could you please work through the problem and show me the steps?)...
(Answers are given but could you please work through the problem and show me the steps?) Chapter 8 A local health center noted that in a sample of 400 patients, 80 were referred to them by the local hospital. a. Provide a 95% confidence interval for all the patients who are referred to the health center by the hospital. b. What sample size would be required to estimate the proportion of all hospital referrals to the health center with a...
(Answers are given but could you please work through the problem and show me the steps?)...
(Answers are given but could you please work through the problem and show me the steps?) Chapter 9 Understand the difference between type I and type II errors. Establish null and alternative hypotheses The average U.S. daily internet use at home is two hours and twenty minutes. A sample of 64 homes in Soddy-Daisy showed an average usage of two hours and 50 minutes with a standard deviation of 80 minutes. We are interested in determining whether or not the...
Excel format! Please show calculations, it is important! Thank you! PROBLEM # 1 ABC Co. purchased...
Excel format! Please show calculations, it is important! Thank you! PROBLEM # 1 ABC Co. purchased 1,000 shares of XYZ for $23 each this year and classified the investment as a trading security. ABC sold 400 shares of the stock for $24 each. At year end the price per share of the XYZ Company had increased to $26. Instructions Prepare the journal entries for these transactions and any year-end adjustments. PROBLEM # 2 On January 1, 2017, LabTech Inc. purchased...
**Can u show the steps and formula used for each question please and thank you! Super...
**Can u show the steps and formula used for each question please and thank you! Super Sales Company is the exclusive distributor for a high-quality knapsack. The product sells for $120 per unit and has a CM ratio of 25%. The company’s fixed expenses are $405,000 per year. The company plans to sell 14,000 knapsacks this year. 1. What are the variable expenses per unit? 2. Use the equation method for the following: a. What is the break-even point in...
PLEASE COMPUTE THE FOLLOWING IN EXCEL and show the excel sheet, Thank you so much! The...
PLEASE COMPUTE THE FOLLOWING IN EXCEL and show the excel sheet, Thank you so much! The following are the runs scored totals for 9 players for the 2016 New York Yankees: 56,43,63,68,58,80,71,32,19 (a) Find the mean and median (b) Find the standard deviation of this population (c) Considering this as a normal sample of American League players for the 2016 season, find a 99% Confidence Interval for the actual mean of Runs Scored for AL players, 2016 . (d) Considering...
PLEASE SHOW HOW TO SOLVE IN EXCEL SHOW STEPS Refer to the Johnson Filtration problem introduced...
PLEASE SHOW HOW TO SOLVE IN EXCEL SHOW STEPS Refer to the Johnson Filtration problem introduced in this section. Suppose that in addition to information on the number of months since the machine was serviced and whether a mechanical or an electrical repair was necessary, the managers obtained a list showing which repairperson performed the service. The revised data follow. Repair Time in Hours Months Since Last Service Type of Repair Repairperson 2.9 2 Electrical Dave Newton 3 6 Mechanical...
Finance: Debt *Please do not use Excel. If you could show working that would be amazing...
Finance: Debt *Please do not use Excel. If you could show working that would be amazing and appreciated. Thank you very much 1. A bond with a face value of $10,000 that matures in exactly seven years has a price of $10,494.63. The coupon rate is 4.2% p.a. and coupons are paid every six months. Which of the following figures is the closest to the yield to maturity? ( I know the answer is C but I do not know...
PLEASE ANSWER USING EXCEL. please show formulas used in excel. THANK YOU :) Parry Enterprises sells...
PLEASE ANSWER USING EXCEL. please show formulas used in excel. THANK YOU :) Parry Enterprises sells copy paper by the case to office supply stores. Each case of paper costs Parry $15. The operating costs are $30,000 per period. Each period, Parry sells approximately 15,000 cases of copy paper at $35 per case. Texas Office Emporium is requesting an order of 4,000 cases of copy paper in the next period at a price of $25 per case. Since Parry has...
PLEASE ANSWER USING EXCEL. please show formulas used in excel. THANK YOU :) Parry Enterprises sells...
PLEASE ANSWER USING EXCEL. please show formulas used in excel. THANK YOU :) Parry Enterprises sells copy paper by the case to office supply stores. Each case of paper costs Parry $15. The operating costs are $30,000 per period. Each period, Parry sells approximately 15,000 cases of copy paper at $35 per case. Texas Office Emporium is requesting an order of 4,000 cases of copy paper in the next period at a price of $25 per case. Since Parry has...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT