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
Retirement Solution Template Protected view) - Excel 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 Editing 79 fo D G H M N RETIREMENT WORKSHEET TEMPLATE 0 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 you change"n" or "you will also have to adjust the interest factors socordingly. First, you have to calculate the future value of your DIFFERENT annuities since you're saving EACH YEAR 7 under diferent assume Age Salary Annual Savings FV. FV. 10 30-39 50.000 7,500 5.00% 10 12.5779 11 40-49 94,334 at age 40 75,000 11250 5.004 10 12.5779 $141,501 at age 50 2 50.59 100,000 15,000 5.00% 10 12.5779 $188,669 at age 60 1360-64 125.000 18,750 5.00% 5.52563 $103.606 age 65 14 15 The above represents a future value of an annuity calculation because you're saving EVERY year 16 Now, you have to take LUMP SUM FUTURE VALUE calculations to bring these figures from age 40,50, and cofrespectivel) 17 to your retirement age of 65 18 19 Age Accumulated Savings FV. FV. 20 40 94,334 6.50% 25 4.8277 $455,417 21 50 141,501 6.50% 25718 $363.913 22 60 188.668 6.50% 5 1.3701 $258 495 23 65 103.606 6505 1 $103.SOE 24 $1181430 25 EXISTING SAVINGS 26 Do the same with your existing savings (but n: 351 27 Early Savings 5.000 6.50% 35 9.0622 28 TOTAL IN SAVINGS ACCOUNT AGE 65 F = 26599 30 Assuming a 2 gear 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 VITHDRAWAL 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 st Do a lump sum Fy calculation to carry the total savings in the account at age 65 20 years into the future fromage 65-85): 35 -20.5% P11226,741 36 Assuming you didn't spend ANY of your retirement, this is the amount accumulated in your retirement fund at the age of 5 37 Dur, you want to draw down that account balance so that in 20 years, there's a to balance at age 85. 's a sinking fund problem 38 Ind. How do a sinking fund calculation 39 Future Value: $3254,913 Sinking fund table fa 0.0302 when n. 20.5% 40 41 2. Solve for the annuity by the following PVPV = A $1,226,741 +12462 Sheet1 Sheets Ready X File Home Retirement Solution Template (Protected View - Excel Insert Page Layout Formulas Data Review View 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 Help Search 429 Enable Editing f N D $363,913 B 20 40 94334 G H 8.50% 25 21 50 M 141501 4,8277 $455417 6.504 15 22 60 25718 188,668 6.50% 5 2365 13701 $259,495 103.606 6.50% 1 24 $103.06 25 EXISTING SAVINGS $1,181,430 26 Do the same with your existing savings (but : 35) 27 Early Savings 5,000 6.50% 35 9.0622 45.311 TOTAL IN SAVINGS ACCOUNT AGE 65 23 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? 32 YOU CAN SOLVE FOR YOUR ANNUITY WITHDRAWAL AMOUNT IN ONE OF THREE VAYS: 33 1. Using a sinking fund interest Factor where you apply that interest factor to the future value 34 ist: 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.95 35 20,1-5% P $1226.741 F. 26533 #t 36 Assuming you didn't spend ANY of your retirement, this is the amount accumulated in your retirement fund at the age of 85. 37 But you want to draw down that account balance so that in 20 years, there's a $0 balance at age 05. It's a sinking fund problem 38 2nd: Now do a sinking fund calculation 39 Future Value: $3,254,913 Sinking fund tableta 0.0302 when = 20.1 = 5% 40 41 $1226,741 + 12.462 2. Solve for the annuity by the following PWIPVA 42 43 3. On a financial calculator, solve for PI PY ###### CPT PMT OMPUTE FY Nimmeansumptions alove. mora 44 IVYO 5 e amount with 45 20 Wamente 45 47 we may alter sig for at alleen motto/Motor can become a long to where for 48 49 50 51 52 53 54 55 57 59 60 61 Sheet1 Sheet3 Ready e File Home insert Design Layout References Mailings Review View 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 1. After my education is complete, my career plans include: Enable Editing Retirement Worksheet 2 I am currently working responsibilities include_ hrs/week at _____ where my primaryjob 3. I believe/don't believe this job will assist mein achieving my long-term career goals, 4 YOUR ESTIMATE/BEST GUESS: 1 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 endingsalary now retirement years in order to maintain Dur standard of living. (Assume no pension or Social Security benefits.) 5. When I retire, my goal is to have saved in a retirement account. I believe thesefunds will be sufficient to maintain my desired lifestyle through my retirementy One approach is to take your figure from #4 and multiply it by 20 years, assuming a 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 6 from my retirement fundescherte Based on my total retirement savings from question #5, assuming those funds are invested at 5% compounded annually, I am able to withdraw $__ next 20 years. Show all inputs below. Compute with the financial calculator (solve for PMT). INPUTSN= 1/Y 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 657 Inother words, 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 minute communded Thiricant nuntion Coroin Ishowallinnontohohin 10 words Retirement Worksheet 3) Protected view) - Word Design Layout References Mailings Review Help O Search O PROTECTED VIEW Be carefulfils from the latet can contain viruses. Unless you need to edit, it's safe to stay in Protected View Enable Editing son Input how much you think you need to live comfortably in your retirement years. To help you with your estimate, experts estimate you should plantoive on approximately 70% of your ending salary in your retirement years in orderto maintain your standard of living (Assume no pension or Social Security benefits.) 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 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 6s in this example that would support the annuity withdrawal from the previous question. fram my retirement fund each year overthe 6. Based on my total retirement savings from Question 25, assuming those funds are invested at 5% compounded annually, I am able to withdraws next 20 years. Show all inputs below. Compute with thefinancial calculator (solve for PMT). INPUTSNE 1/4 = FV= PV PMT= 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 657 in other words, based on the amount of the annuity from question #4, the total retirement savings account must have an actual balance of 5 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: 1/Y= FV PV = PMT= NE 8. Review your answers from questions 4-27. This is just the off the cuft approach to retirement planning, How close were youto "reality"? What conclusions can you draw based on your estimates and how they compare to your calculations in #G & #7? RA132 PM Retirement Worksheet (3) Protected View) - Word File Home Insert Design Layout References Mailings Review View Help Search O 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 Editing 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 I hope to earn $ per year when i'm 40 I hopeto earns per year when I'm 50. I hope to earn $ per year when I'm 60. 10. I PROMISE that I WILL SAVE 15% of that salary each year, and I expect to retire at age 65. 11. If my life expectancy is age es, my retirement years will total 12. SHOW WORK IN THE TEMPLATE Assume invest 15% of my salary annually based upon the above salaries at a savings rate of 6.5% compounded annually. At retirement eneste inding the retirement fundsi had saved by age 30) would total: (from Excel template) 13 SHOW WORK IN THE TEMPLATE: Based on the amount of funds in your retirement account (question #12), how much can you withdraw each year during retirement in the word what your annum annuity that you will be able to live on to support your lifestyle? 718 e Retirement Worksheet (3) (Protected Vie) - Word Home Insert Design Layout References Mailings Review View Help Search PROTECTED VIEN Be careful-files from the Internet can contain viruses. Unless you need to edit, it's afer to stay in Protected View Enable Editing I hope to earn $ per year when I'm 30. I hope to earns per year when I'm 40 I hope to eam per year wheni'm 50. I hope to earn $ per year when I'm 60. 10. I PROMISE that I WILL SAVE 15% of that salary each year, and I expect to retire at age 65 11. If my life expectancy is age 85, my retirement years will total 12. SHOW WORK IN THE TEMPLATE: Assumelinvest 15% of my salary annually based upon the above salaries at a savings rate of 6.5% compounded annually. At retirement age, my nestees (including the retirement tundsi had saved by age 30) would total: (from Excel template) 13. SHOWWORK IN THE TEMPLATE: Based on the amount of funds in your retirement account (question #12), how much can you withdraw each year during retirement? In other words, what is your annual annuity that you will be able to live on to support your lifestyle? (transfer the value from the COMPUTE box on the Excel template here which you are to solve using your financialcalculator. It should bevery similar to the other based computed using the interest factor and formula) 14. With a disciplined savings plan i knowi can meet my long-term financial goals: TRUE/FALSE 15. Please comment on this exercise. What are your thoughts/conclusions? words Retirement Solution Template Protected view) - Excel 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 Editing 79 fo D G H M N RETIREMENT WORKSHEET TEMPLATE 0 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 you change"n" or "you will also have to adjust the interest factors socordingly. First, you have to calculate the future value of your DIFFERENT annuities since you're saving EACH YEAR 7 under diferent assume Age Salary Annual Savings FV. FV. 10 30-39 50.000 7,500 5.00% 10 12.5779 11 40-49 94,334 at age 40 75,000 11250 5.004 10 12.5779 $141,501 at age 50 2 50.59 100,000 15,000 5.00% 10 12.5779 $188,669 at age 60 1360-64 125.000 18,750 5.00% 5.52563 $103.606 age 65 14 15 The above represents a future value of an annuity calculation because you're saving EVERY year 16 Now, you have to take LUMP SUM FUTURE VALUE calculations to bring these figures from age 40,50, and cofrespectivel) 17 to your retirement age of 65 18 19 Age Accumulated Savings FV. FV. 20 40 94,334 6.50% 25 4.8277 $455,417 21 50 141,501 6.50% 25718 $363.913 22 60 188.668 6.50% 5 1.3701 $258 495 23 65 103.606 6505 1 $103.SOE 24 $1181430 25 EXISTING SAVINGS 26 Do the same with your existing savings (but n: 351 27 Early Savings 5.000 6.50% 35 9.0622 28 TOTAL IN SAVINGS ACCOUNT AGE 65 F = 26599 30 Assuming a 2 gear 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 VITHDRAWAL 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 st Do a lump sum Fy calculation to carry the total savings in the account at age 65 20 years into the future fromage 65-85): 35 -20.5% P11226,741 36 Assuming you didn't spend ANY of your retirement, this is the amount accumulated in your retirement fund at the age of 5 37 Dur, you want to draw down that account balance so that in 20 years, there's a to balance at age 85. 's a sinking fund problem 38 Ind. How do a sinking fund calculation 39 Future Value: $3254,913 Sinking fund table fa 0.0302 when n. 20.5% 40 41 2. Solve for the annuity by the following PVPV = A $1,226,741 +12462 Sheet1 Sheets Ready X File Home Retirement Solution Template (Protected View - Excel Insert Page Layout Formulas Data Review View 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 Help Search 429 Enable Editing f N D $363,913 B 20 40 94334 G H 8.50% 25 21 50 M 141501 4,8277 $455417 6.504 15 22 60 25718 188,668 6.50% 5 2365 13701 $259,495 103.606 6.50% 1 24 $103.06 25 EXISTING SAVINGS $1,181,430 26 Do the same with your existing savings (but : 35) 27 Early Savings 5,000 6.50% 35 9.0622 45.311 TOTAL IN SAVINGS ACCOUNT AGE 65 23 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? 32 YOU CAN SOLVE FOR YOUR ANNUITY WITHDRAWAL AMOUNT IN ONE OF THREE VAYS: 33 1. Using a sinking fund interest Factor where you apply that interest factor to the future value 34 ist: 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.95 35 20,1-5% P $1226.741 F. 26533 #t 36 Assuming you didn't spend ANY of your retirement, this is the amount accumulated in your retirement fund at the age of 85. 37 But you want to draw down that account balance so that in 20 years, there's a $0 balance at age 05. It's a sinking fund problem 38 2nd: Now do a sinking fund calculation 39 Future Value: $3,254,913 Sinking fund tableta 0.0302 when = 20.1 = 5% 40 41 $1226,741 + 12.462 2. Solve for the annuity by the following PWIPVA 42 43 3. On a financial calculator, solve for PI PY ###### CPT PMT OMPUTE FY Nimmeansumptions alove. mora 44 IVYO 5 e amount with 45 20 Wamente 45 47 we may alter sig for at alleen motto/Motor can become a long to where for 48 49 50 51 52 53 54 55 57 59 60 61 Sheet1 Sheet3 Ready e File Home insert Design Layout References Mailings Review View 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 1. After my education is complete, my career plans include: Enable Editing Retirement Worksheet 2 I am currently working responsibilities include_ hrs/week at _____ where my primaryjob 3. I believe/don't believe this job will assist mein achieving my long-term career goals, 4 YOUR ESTIMATE/BEST GUESS: 1 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 endingsalary now retirement years in order to maintain Dur standard of living. (Assume no pension or Social Security benefits.) 5. When I retire, my goal is to have saved in a retirement account. I believe thesefunds will be sufficient to maintain my desired lifestyle through my retirementy One approach is to take your figure from #4 and multiply it by 20 years, assuming a 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 6 from my retirement fundescherte Based on my total retirement savings from question #5, assuming those funds are invested at 5% compounded annually, I am able to withdraw $__ next 20 years. Show all inputs below. Compute with the financial calculator (solve for PMT). INPUTSN= 1/Y 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 657 Inother words, 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 minute communded Thiricant nuntion Coroin Ishowallinnontohohin 10 words Retirement Worksheet 3) Protected view) - Word Design Layout References Mailings Review Help O Search O PROTECTED VIEW Be carefulfils from the latet can contain viruses. Unless you need to edit, it's safe to stay in Protected View Enable Editing son Input how much you think you need to live comfortably in your retirement years. To help you with your estimate, experts estimate you should plantoive on approximately 70% of your ending salary in your retirement years in orderto maintain your standard of living (Assume no pension or Social Security benefits.) 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 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 6s in this example that would support the annuity withdrawal from the previous question. fram my retirement fund each year overthe 6. Based on my total retirement savings from Question 25, assuming those funds are invested at 5% compounded annually, I am able to withdraws next 20 years. Show all inputs below. Compute with thefinancial calculator (solve for PMT). INPUTSNE 1/4 = FV= PV PMT= 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 657 in other words, based on the amount of the annuity from question #4, the total retirement savings account must have an actual balance of 5 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: 1/Y= FV PV = PMT= NE 8. Review your answers from questions 4-27. This is just the off the cuft approach to retirement planning, How close were youto "reality"? What conclusions can you draw based on your estimates and how they compare to your calculations in #G & #7? RA132 PM Retirement Worksheet (3) Protected View) - Word File Home Insert Design Layout References Mailings Review View Help Search O 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 Editing 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 I hope to earn $ per year when i'm 40 I hopeto earns per year when I'm 50. I hope to earn $ per year when I'm 60. 10. I PROMISE that I WILL SAVE 15% of that salary each year, and I expect to retire at age 65. 11. If my life expectancy is age es, my retirement years will total 12. SHOW WORK IN THE TEMPLATE Assume invest 15% of my salary annually based upon the above salaries at a savings rate of 6.5% compounded annually. At retirement eneste inding the retirement fundsi had saved by age 30) would total: (from Excel template) 13 SHOW WORK IN THE TEMPLATE: Based on the amount of funds in your retirement account (question #12), how much can you withdraw each year during retirement in the word what your annum annuity that you will be able to live on to support your lifestyle? 718 e Retirement Worksheet (3) (Protected Vie) - Word Home Insert Design Layout References Mailings Review View Help Search PROTECTED VIEN Be careful-files from the Internet can contain viruses. Unless you need to edit, it's afer to stay in Protected View Enable Editing I hope to earn $ per year when I'm 30. I hope to earns per year when I'm 40 I hope to eam per year wheni'm 50. I hope to earn $ per year when I'm 60. 10. I PROMISE that I WILL SAVE 15% of that salary each year, and I expect to retire at age 65 11. If my life expectancy is age 85, my retirement years will total 12. SHOW WORK IN THE TEMPLATE: Assumelinvest 15% of my salary annually based upon the above salaries at a savings rate of 6.5% compounded annually. At retirement age, my nestees (including the retirement tundsi had saved by age 30) would total: (from Excel template) 13. SHOWWORK IN THE TEMPLATE: Based on the amount of funds in your retirement account (question #12), how much can you withdraw each year during retirement? In other words, what is your annual annuity that you will be able to live on to support your lifestyle? (transfer the value from the COMPUTE box on the Excel template here which you are to solve using your financialcalculator. It should bevery similar to the other based computed using the interest factor and formula) 14. With a disciplined savings plan i knowi can meet my long-term financial goals: TRUE/FALSE 15. Please comment on this exercise. What are your thoughts/conclusions? words
