In this problem, we want to create a spreadsheet to help someone estimate their income in retirement. To do this, we will have them enter several numbers: the amount of money currently in savings, the amount being saved each period, the number of years they plan to save, and the number of years they plan to be in retirement. Your spreadsheet should allow the user to change these numbers, but the calculations should be protected (so the user doesn’t accidentally delete or change the formulas).

These retirement calculations require the user to provide information.

1. For cell B5, use the Data Validation tool found in the Data tab to

   1. Display the message “Please input 1 for annual payments, 4 for quarterly payments, and 12 for monthly payments” when the cell is selected. Note: message wording/punctuation must be exact.

   2. In the data validation formula box, enter the following formula: =OR(B5=1,B5=4,B5=12)

   3. Display the error message “You must choose annual, quarterly or monthly payments” if a value is entered that returns the value FALSE (that is, if anything other than 1, 4, or 12 is entered).

2. For cell B6, use the Data Validation tool found in the Data tab to: (4 points)

   1. Display the message “Please input the expected annual return on your savings while you’re working and saving for retirement” when the cell is selected.

   2. Restrict the values that can be entered into this cell to be a decimal between 0.05 and 0.25 (5% and 25%). (Look under Settings). Format this cell B6 to display as a percent (%).

   3. Display the error message “The return must be between 5% and 25% (0.05 and 0.25)” if a value is entered that is outside this range.

3. For cell B7, use the Data Validation tool found in the Data tab to 

   1. Display the message “Please input the expected annual return on your savings during retirement” when the cell is selected.

   2. Restrict the values that may be selected to the list I’ve provided in Column Q (Look under Settings. In the Allow dropdown box, select List. Use Source to select those cells in Column Q). Format this cell B7 to display as a percent (%).

   3. Now hide Column Q (right-click on the column heading, Q).

      Now calculate what someone will be able to spend in retirement given how much they have in saving (B1), how much they are saving (B2), how often they’re saving (B5), and how much longer they will be saving (B3), how long they’ll be in retirement (B4), and the return on their investments both prior to and during retirement (B6/B9 and B7).

   4. Based on the amount being saved each period, estimate how much can be spent each period in retirement. 

   6. 1. In cell B9, use the function NOMINAL to convert the expected return on your savings prior to retirement into an APR. Note that if the user picks monthly payments (12 payments per year), then the APR needs to be based on monthly compounding.

      2. In cell B10, use the function FV to calculate how much money will be in the account at retirement. As with all the calculations, this should be linked to other cells; don’t input the values.

      3. In cell B11, use the PMT function to calculate the retirement annuity based on the savings at retirement in B10, (how much money you can live off each period during retirement given savings at retirement).

   7. Some individuals may not intend to save money for the first few years. Cell B13 allows the user to specify when they plan to start saving. (12 points)

      1. Use the RATE function in cell B14 to calculate what you would need the return to be in order to have the same amount of money at retirement as in Problem 4 (cell B10) given the delay entered in cell B13. Report this rate as an APR.

      2. Use the EFFECT function in cell B15 to convert this APR into an EAR.

      3. Use the IFERROR function in cells B14 and B15 to return the message "You never save any money!” if the worker delays

         saving (cell B13) for more years than there are years until retirement (cell B3). Instead of asking how much they plan to put into savings, here we ask how much they want to be able to spend. That is, here we calculate the amount that should be saved each period to attain a target retirement income (B17) based on current savings, the frequency of savings (B5), the number of years (B3 and B4), and the return on the investments (B7 and B9). Note: We will assume here that the person begins saving now instead of delaying.

   8. Based on the desired income in retirement (B17), estimate how much should be saved each period before retirement. (10 points)

      1. In cell B19, use the PV function to calculate how much money is needed at retirement to provide the desired income

         in B17.

      2. In cell B20, use the amount in B19 and the PMT function to calculate how much money must be saved each period. Hint:

         You’ll likely need to use the FV function inside the PMT function.

   9. To give a sense of how interest rates (B6) and the years of savings (B3) impact the funds available in retirement (B11), create a two-way data table and allow the interest rate to vary from 2% to 12% in increments of 1%, and the years of savings from 10 years to 50 years in increments of 5. Link cell B23 to cell B11. The start of the table will be in cell B23 and end in cell M32 (if done correctly when you put your cursor in any cell between cells C24 to M32 you will see {=TABLE(B6,B3)} in the function bar. (5 points)

   10. To check your answers/calculations, place the following values into the spreadsheet:

       Cell B1 --->	$ 10,000	How much money do you currently have in savings?
       Cell B2 --->	$ 750	How much money do you plan to save each period?
       Cell B3 --->	30	Years until your retirement
       Cell B4 --->	15	Years you plan to be in retirement
       Problem 1	4	How frequently do you save money each year? Annually (1), quarterly (4), or monthly (12 times each year)?
       Problem 2	8.00%	Expected return on your savings before retirement (this is an EAR)
       Problem 3	6.00%	Expected return on savings during retirement (this is an APR)
       Problem 5	15	Number of years you will delay before starting to save for retirement
       	$ 5,000	How much do you want to be able to spend each period during your retirement?

       These are the values you should get out (cells B11 and B19)

       Answer to Problem 4	$11,439.95	The amount you can spend each period during your retirement
       Answer to Problem 6	$196,901.34	The amount you need in your account at retirement in order to spend this target amount (cell B17)

   11.  

   12. 
       

Transcribed Image Text:

Problem 1 Problem 2 Problem 3 Problem 4 Problem 5 Problem 6 0 10 15 20 25 30 35 40 45 50 How much money do you currently have in savings? How much money do you plan to save each period? Years until your retirement Years you plan to be in retirement How frequently do you save money each year? Annually (1), quarterly (4), or monthly (12 times each year)? Expected return on your savings before retirement (this is an EAR) Expected return on savings during retirement (this is an APR) Expected annual return on savings before retirement (convert to an APR) Amount you'll have in your account at retirement based on the savings amount in cell B2 Amount you can spend each period during your retirement Number of years you will delay before starting to save for retirement The interest rate you would need to earn to hit the target amount in B10 if you do delay starting to save. Convert that APR to an EAR. How much do you want to be able to spend each period (cell B5) during your retirement? Amount you need in your account at retirement in order to spend this target amount (cell B17) ▬▬▬▬▬ Amount you need to save each period (cell B5) before retirement to have enough to meet your goal. 2% 3% 4% 5% 6% 7% 8% 9% 10% 11% r 12% Problem 1 Problem 2 Problem 3 Problem 4 Problem 5 Problem 6 0 10 15 20 25 30 35 40 45 50 How much money do you currently have in savings? How much money do you plan to save each period? Years until your retirement Years you plan to be in retirement How frequently do you save money each year? Annually (1), quarterly (4), or monthly (12 times each year)? Expected return on your savings before retirement (this is an EAR) Expected return on savings during retirement (this is an APR) Expected annual return on savings before retirement (convert to an APR) Amount you'll have in your account at retirement based on the savings amount in cell B2 Amount you can spend each period during your retirement Number of years you will delay before starting to save for retirement The interest rate you would need to earn to hit the target amount in B10 if you do delay starting to save. Convert that APR to an EAR. How much do you want to be able to spend each period (cell B5) during your retirement? Amount you need in your account at retirement in order to spend this target amount (cell B17) ▬▬▬▬▬ Amount you need to save each period (cell B5) before retirement to have enough to meet your goal. 2% 3% 4% 5% 6% 7% 8% 9% 10% 11% r 12%

Answer rating: 100% (QA)

Retirement Income Spreadsheet This spreadsheet helps estimate your retirement income based on various factors Heres a breakdown of the requested funct... View the full answer