This is a real world exercise to determine the amount of savings you should have put away for your retirement. This is a dynamic and powerful assignment to demonstrate the power of the time value of money. Set your own financial goals and use this worksheet to understand how much you will need to have saved by retirement age to live the lifestyle that you have planned for yourself.

First, complete page one of the worksheet first by closely following the directions of each question. Much of the assignment is subjective based on your own financial goals, but the math is objective - and you must show your work to demonstrate your understanding of the assignment.

Next, complete #9 of the worksheet using your best estimate of you future earnings. If the time period set forth isn't appropriate due to your age, feel free to adjust the timeframes as needed.

Input the values from #9 on the worksheet into the Retirement Excel Template (Type directly over my values. They are there for illustrative purposes). It is important the values from the spreadsheet match the values you input into the Excel template. Also, don't forget to include the amount of savings you estimate to have accumulated. The worksheet is designed to be flexible to meet your own individual goals. If you are an older student, then change the variables accordingly or you may create a completely fictional scenario. Just be sure that if you change time (n) or rate (r) then you also change the associated interest factors.

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.

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. This exercise is designed for you to understand the power of compounding and the importance of time in that process (start early, save a lot). I hope you can walk away with this knowledge. While the Connect exercises make sure you know how to crunch the numbers, this template is something you can hopefully take with you long after this class ends.

A Retirement Solution Template [Protected View] - Exc File Home Insert Page Layout Formulas Data Review View Help Search PROTECTED VIEW Be careful--files from the Internet can contain viruses. Unless you need to edit it's safer to stay in Protected View Enable A29 ELF G H B 1 RETIREMENT WORKSHEET TEMPLATE K MIN The blue numbers are variables, but must be changed to match your worksheet figures. You may also change the number of compounding periods in and the interest rate, but 5 you change"n" or you will also have to adjust the interest factors accordingly 6. First, you have to calculate the future value of your DIFFERENT annuities since you're saving EACH YEAR 7 under different assumptions 8 9 Ago Salary Annual Savings FVFA FV. 10 30-39 50.000 7.500 5.00% 10 12.5779 $94.334 at age 40 11 40-49 75,000 11.250 5.00% 10 12.5779 $141,501 at age 50 12 50-59 100,000 15,000 5.00% 10 12.5779 $168.668 at age 60 13 50-64 125.000 18.750 5.00% 5 5.52563 $103.606 at age 65 74 15 The above represents a future value of an annuity calculation because youre saving EVERY year 15 Now, you have to take LUMP SUMFUTURE VALUE calculations to bring these ligures from age 40.50 and 60 (respectively) 17 to your retirement age of 65 18 19 Age Accumulated Savings FVE FVA 20 40 94,334 6.50% 25 4.8277 $455,417 21 50 141.501 6.50% 15. 25718 $363.913 22 60 188,888 6.50% 5 13701 1258,495 23 65 103,608 6.50% 0 1 $103.606 24 $1,181,430 25 EXISTING SAVINGS 26 Do the same with your existing savings burne 352 5,000 6.50 35 90622 145.311 29 TOTAL IN SAVINGS ACCOUNT AGE 65 ...... 29 30 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? 31 32 YOU CAN SOLVE FORYOUR ANNUITY WITHDRAWAL AMOUNT IN ONE OF THREE WAYS 33 1 Using a sinking fund interest factor where you apply that interest factor to the future value. 34 Do slump sum FV Nouation to carry the total savings in the account at age 65120 years into the future homage 65-851 Sheet1 Sheets 27 Ely Savings DO Retirement Solution Template (Protected View] - Excel File Home Insert Page Layout Formulas Data Review View Help Search A PROTECTED VIEW Be careful--files from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View. Enable Edit M N B D E F G H 27 Early Savings 5,000 6.50% 35 90522 $45.311 28 TOTAL IN SAVINGS ACCOUNTAGE 65 29 30 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? 31 32 YOU CAN SOLVE FOR YOUR ANNUITY WITHORAWAL AMOUNT IN ONE OF THEEWAYS: 33 1. Using a sinking fund interest factor where you apply that interest factor to the future value. 34 1st: Do a lurp sum FV calculation to carry the total savings in the account at age 65) 20 years into the future from age 85-85). 35 n-20, -5% PL $1226,741 FV, 2.6533 ....... 36 Assuming you didn't spend ANY of your retirement, this is the amount accumulated in your retirement lund at the age of 85 37 But you want to draw down that account balance so that in 20 years, there's 10 balance at age 65. it's a sinkinglund problem 38 2nd Now do a sininglund calculator 39 Future Value: $3.254.913 Sinking tundtableta 0.0302 when n-20, -5% ###### 40 41 2. Solve for the annuity by the following for PVIPVFA-A $1.226,74 12.462 ***** 42 43 3. On a financial calculator, solve for PMT: PU ****** OPT PMT COMPUTE FV 10 Radandhe annuation about 45 WY) 5 We amount ob advero wala 46 20 Darvour acco 47 Thuraanveer may are as for readiverent mented itengah meaninay sendicant/Newermeneuropa.neted arter weiggereverthanas ration 48 49 50 51 52 55 55 56 57 S 00 Shoot1 Sheets Ready File Retirement Worksheet (1) Protected View) - Word Home Insert Design Layout References Mailing Review Help Search PROTECTED VIEW Be carefulfiles from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View Enable Editing Sign in 1. After my education is complete, my career plans include: Retirement Worksheet 2. I am currently working responsibilities include hrs./week at where my primary job 3. I believe / don't believe this job will assist mein achieving my long-term career goals. 4. YOUR ESTIMATE/BEST GUESS I intend to withdraw $ at the END of each year from my retirement account to support my lifestyle. Input how much you think you need to live comfortably in your retirement years. To help you with your estimate, experts estimate you should plan to live on approximately 70% of your ending salary in your retirement years in order to maintain your standard of living (Assume no pension or Social Security benefits 5. When retire, my goal is to have saved in a retirement account. I believe these funds will be sufficient to maintain my desired lifestyle through my retirement years. One approach is to take your figure from 4 and multiply it by 20 years, assuminga 20 year retirement. There are no wrong answers here. Input this figure BEFORE you calculate anything on the Excel template. Just take a guess. What do you think is a reasonable amount to have as your nest egg on the day you retire (age 65 in this example that would support the annuity withdrawal from the previous question. from my retirement fund each year over the 6. Based on my total retirement savings from question #5, assuming those funds are invested at 5% compounded annually, I am able to withdraws next 20 years. Show all inputs below. Compute with the financial calculator (solve for PMT). INPUTSN= 1Y= FV PV PMT 7. In order to meet your retirement goals (withdrawing an annuity stream for 20 years from question #4 how much would you need to have in your retirement account at age 88? mother words, based on the amount of the annuity from question #4 the total retirement savings account must have an actual balance of in the account on the day of retirement at age 65 assuming rate of de Thiraimentimet ICOT DA Ichin.. 718 word O Sign in Retirement Worksheet (1) (Protected View) - Word Home Insert Design Layout References Mailings Review View Held Search ROTECTED VIEW Be careful--files from the Internet can contain viruses. Unless you need to edit it's safer to stay in Protected View. Enable Editing INPUTSN 1/4 = FV= PY PMT= 7. In orderto meet your retirement goals (withdrawing an annuity stream for 20 years) from question #4, how much would you need to have in your retirement account at age 65? In other words, based on the amount of the annuity from question 4, the total retirement savings account must have an actual balance of compounded anually. This is a present value of annuity calculation (CPTPV). (Show all inputs below.) in the account on the day of retirement at age 65 assuming a rate of 5% INPUTS NE 1/4 = FV- PV = PMT = 8. Review your answers from questions #4-7. This is just the "off the cuff" approach to retirement planning. How close were you to "reality"? What conclusions can you draw based on your estimates and how they compare to your calculations in #6 & #77 Now let's take a more analytical approach to retirement planning 9 INPUTINTO TEMPLATE: It is imperative that the figures below match your BLUE figures on the Exceltemplate, including the EARLY SAVINGS. I hope to have (EARLY SAVINGS ON TEMPLATE) of retirement savings in the bank by age 30 i hope to earns per year when I'm 30. O A Retirement Solution Template [Protected View] - Exc File Home Insert Page Layout Formulas Data Review View Help Search PROTECTED VIEW Be careful--files from the Internet can contain viruses. Unless you need to edit it's safer to stay in Protected View Enable A29 ELF G H B 1 RETIREMENT WORKSHEET TEMPLATE K MIN The blue numbers are variables, but must be changed to match your worksheet figures. You may also change the number of compounding periods in and the interest rate, but 5 you change"n" or you will also have to adjust the interest factors accordingly 6. First, you have to calculate the future value of your DIFFERENT annuities since you're saving EACH YEAR 7 under different assumptions 8 9 Ago Salary Annual Savings FVFA FV. 10 30-39 50.000 7.500 5.00% 10 12.5779 $94.334 at age 40 11 40-49 75,000 11.250 5.00% 10 12.5779 $141,501 at age 50 12 50-59 100,000 15,000 5.00% 10 12.5779 $168.668 at age 60 13 50-64 125.000 18.750 5.00% 5 5.52563 $103.606 at age 65 74 15 The above represents a future value of an annuity calculation because youre saving EVERY year 15 Now, you have to take LUMP SUMFUTURE VALUE calculations to bring these ligures from age 40.50 and 60 (respectively) 17 to your retirement age of 65 18 19 Age Accumulated Savings FVE FVA 20 40 94,334 6.50% 25 4.8277 $455,417 21 50 141.501 6.50% 15. 25718 $363.913 22 60 188,888 6.50% 5 13701 1258,495 23 65 103,608 6.50% 0 1 $103.606 24 $1,181,430 25 EXISTING SAVINGS 26 Do the same with your existing savings burne 352 5,000 6.50 35 90622 145.311 29 TOTAL IN SAVINGS ACCOUNT AGE 65 ...... 29 30 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? 31 32 YOU CAN SOLVE FORYOUR ANNUITY WITHDRAWAL AMOUNT IN ONE OF THREE WAYS 33 1 Using a sinking fund interest factor where you apply that interest factor to the future value. 34 Do slump sum FV Nouation to carry the total savings in the account at age 65120 years into the future homage 65-851 Sheet1 Sheets 27 Ely Savings DO Retirement Solution Template (Protected View] - Excel File Home Insert Page Layout Formulas Data Review View Help Search A PROTECTED VIEW Be careful--files from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View. Enable Edit M N B D E F G H 27 Early Savings 5,000 6.50% 35 90522 $45.311 28 TOTAL IN SAVINGS ACCOUNTAGE 65 29 30 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? 31 32 YOU CAN SOLVE FOR YOUR ANNUITY WITHORAWAL AMOUNT IN ONE OF THEEWAYS: 33 1. Using a sinking fund interest factor where you apply that interest factor to the future value. 34 1st: Do a lurp sum FV calculation to carry the total savings in the account at age 65) 20 years into the future from age 85-85). 35 n-20, -5% PL $1226,741 FV, 2.6533 ....... 36 Assuming you didn't spend ANY of your retirement, this is the amount accumulated in your retirement lund at the age of 85 37 But you want to draw down that account balance so that in 20 years, there's 10 balance at age 65. it's a sinkinglund problem 38 2nd Now do a sininglund calculator 39 Future Value: $3.254.913 Sinking tundtableta 0.0302 when n-20, -5% ###### 40 41 2. Solve for the annuity by the following for PVIPVFA-A $1.226,74 12.462 ***** 42 43 3. On a financial calculator, solve for PMT: PU ****** OPT PMT COMPUTE FV 10 Radandhe annuation about 45 WY) 5 We amount ob advero wala 46 20 Darvour acco 47 Thuraanveer may are as for readiverent mented itengah meaninay sendicant/Newermeneuropa.neted arter weiggereverthanas ration 48 49 50 51 52 55 55 56 57 S 00 Shoot1 Sheets Ready File Retirement Worksheet (1) Protected View) - Word Home Insert Design Layout References Mailing Review Help Search PROTECTED VIEW Be carefulfiles from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View Enable Editing Sign in 1. After my education is complete, my career plans include: Retirement Worksheet 2. I am currently working responsibilities include hrs./week at where my primary job 3. I believe / don't believe this job will assist mein achieving my long-term career goals. 4. YOUR ESTIMATE/BEST GUESS I intend to withdraw $ at the END of each year from my retirement account to support my lifestyle. Input how much you think you need to live comfortably in your retirement years. To help you with your estimate, experts estimate you should plan to live on approximately 70% of your ending salary in your retirement years in order to maintain your standard of living (Assume no pension or Social Security benefits 5. When retire, my goal is to have saved in a retirement account. I believe these funds will be sufficient to maintain my desired lifestyle through my retirement years. One approach is to take your figure from 4 and multiply it by 20 years, assuminga 20 year retirement. There are no wrong answers here. Input this figure BEFORE you calculate anything on the Excel template. Just take a guess. What do you think is a reasonable amount to have as your nest egg on the day you retire (age 65 in this example that would support the annuity withdrawal from the previous question. from my retirement fund each year over the 6. Based on my total retirement savings from question #5, assuming those funds are invested at 5% compounded annually, I am able to withdraws next 20 years. Show all inputs below. Compute with the financial calculator (solve for PMT). INPUTSN= 1Y= FV PV PMT 7. In order to meet your retirement goals (withdrawing an annuity stream for 20 years from question #4 how much would you need to have in your retirement account at age 88? mother words, based on the amount of the annuity from question #4 the total retirement savings account must have an actual balance of in the account on the day of retirement at age 65 assuming rate of de Thiraimentimet ICOT DA Ichin.. 718 word O Sign in Retirement Worksheet (1) (Protected View) - Word Home Insert Design Layout References Mailings Review View Held Search ROTECTED VIEW Be careful--files from the Internet can contain viruses. Unless you need to edit it's safer to stay in Protected View. Enable Editing INPUTSN 1/4 = FV= PY PMT= 7. In orderto meet your retirement goals (withdrawing an annuity stream for 20 years) from question #4, how much would you need to have in your retirement account at age 65? In other words, based on the amount of the annuity from question 4, the total retirement savings account must have an actual balance of compounded anually. This is a present value of annuity calculation (CPTPV). (Show all inputs below.) in the account on the day of retirement at age 65 assuming a rate of 5% INPUTS NE 1/4 = FV- PV = PMT = 8. Review your answers from questions #4-7. This is just the "off the cuff" approach to retirement planning. How close were you to "reality"? What conclusions can you draw based on your estimates and how they compare to your calculations in #6 & #77 Now let's take a more analytical approach to retirement planning 9 INPUTINTO TEMPLATE: It is imperative that the figures below match your BLUE figures on the Exceltemplate, including the EARLY SAVINGS. I hope to have (EARLY SAVINGS ON TEMPLATE) of retirement savings in the bank by age 30 i hope to earns per year when I'm 30. O