Retirement Solution Temp

User Generated

oehghf2014

Business Finance

Description

  1. The spreadsheet will then calculate much of the work for you.  But please calculate the very last box at the bottom of the Excel template using your financial calculator.  The variables are there for you.  This assignment isn't designed to have you crunch a lot of numbers.  You're doing enough of that type of practice inside of Connect.  Rather, this exercise is the practical application of what you have learned about the time value of money from Chapter 9.  
  2. Go back and complete the remainder of the worksheet based on the values computed in the Excel template.  

The Excel template must be submitted with the completed worksheet and the figures in both the worksheet and template must be consistent with one another.  Discrepancies will be a cause for point deductions.  Also, proofread carefully because I will also deduct points for spelling, punctuation, and grammatical errors. Your submission must include both a completed retirement worksheet as well as the Excel template and be sure the numbers between the two documents match accordingly.

Retirement Solution Template (2).xls 

Unformatted Attachment Preview

RETIREMENT WORKSHEET TEMPLATE The blue numbers are variables, but must be changed to match your worksheet figures. You may also change the number of compounding periods (n) and the interest rate, but if you change "n" or "r", you will also have to adjust the interest factors accordingly. First, you have to calculate the future value of your DIFFERENT annuities since you're saving EACH YEAR under different assumptions. Age 30-39 40-49 50-59 60-64 Salary Annual Savings @ 15% r n FVIFA FVA 7,500 11,250 15,000 18,750 5.00% 5.00% 5.00% 5.00% 10 10 10 5 12.5779 12.5779 12.5779 5.52563 $94,334 $141,501 $188,668 $103,606 50,000 75,000 100,000 125,000 at age 40 at age 50 at age 60 at age 65 The above represents a future value of an annuity calculation because you're saving EVERY year. Now, you have to take LUMP SUM FUTURE VALUE calculations to bring these figures from age 40, 50, and 60 (respectively) to your retirement age of 65: Age 40 50 60 65 Accumulated Savings 94,334 141,501 188,668 103,606 r n FVIF FVA 6.50% 6.50% 6.50% 6.50% 25 15 5 0 4.8277 2.5718 1.3701 1 $455,417 $363,913 $258,495 $103,606 $1,181,430 EXISTING SAVINGS: Do the same with your existing savings (but n= 35): 5,000 Early Savings 6.50% 35 9.0622 TOTAL IN SAVINGS ACCOUNT @ AGE 65 $45,311 $1,226,741 Assuming a 20 year retirement, how much can you withdraw each year so that at the end of 20 years, the balance will be $0? YOU CAN SOLVE FOR YOUR ANNUITY WITHDRAWAL AMOUNT IN ONE OF THREE WAYS: 1. Using a sinking fund interest factor where you apply that interest factor to the future value. 1st: Do a lump sum FV calculation to carry the total savings in the account (at age 65) 20 years into the future (from age 65-85): n=20, r=5% PV = $1,226,741 FVIF = 2.6533 $3,254,913 Assuming you didn't spend ANY of your retirement, this is the amount accumulated in your retirement fund at the age of 85. But, you want to draw down that account balance so that in 20 years, there's a $0 balance at age 85. It's a sinking fund problem. 2nd: Now do a sinking fund calculation: Future Value: $3,254,913 Sinking fund table factor: 0.0302 when n= 20, r = 5% 2. Solve for the annuity by the following formula: 3. On a financial calculator, solve for PMT: PV/PVIFA = A PV $1,226,741 FV $0 I/Y (r) 5 n 20 $1,226,741 ÷ 12.462 $98,298 $98,439 CPT PMT COMPUTE Based on the assumptions above, this is the amount you will be able to withdraw per year from your retirement account. *Your answers may differ slightly for each different method (though not statistically significant) because of the rounding involved when using the interest factor tables.
Purchase answer to see full attachment
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer


Anonymous
Just what I needed. Studypool is a lifesaver!

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Similar Content

Related Tags