a. Now calculate what someone will be able to spend in retirement given how much they are saving (B2), how often they're saving (B5), how long they save (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. (12 points) In cell B9, use the function NOMINAL to convert the expected return on your savings prior to retirement into an APR. Note that if you pick monthly payments (12 payments per year), then the APR needs to be based on monthly compounding, so you'll need to link to cells B5 and 36. b. In cell B10, use the function FV to calculate the 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. 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 of each period during retirement given savings at retirement). 5. Assume that this individual intends to save nothing for the first few years, which is common for young workers. (12 points) 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. b. Use the EFFECT function in cell B15 to convert this APR into an EAR. Use the IFERROR function in cell 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). In the same cells, B14 and B15, change the cells' alignment formatting so the text shrinks to fit in the cell when/if the error message is displayed. C. a. c. What am I doing wrong for the NPV and PMT (problem 4)? The "Amount you can spend each period during your retirement" should be $8,884.69. Please show your work. My work is shown below. B D E F G H J 1 2 $ 3 4 5 750.00 How much money do you save each period? 30 Years until your retirement 15 Years in you plan to be in retirement 4.00 How many periods in a year? 8.00% Expected return on your savings before retirement (this is an EAR) 6.00% Expected return on savings during retirement (this is an APR) Problem 1 Problem 2 Problem 3 6 7 8 9 Problem 4 10 11 7.77% Expected annual return on savings before retirement (convert to an APR) $3,649.95 Amount you'll have in your account at retirement based on the savings amount in cell B2 -810 Amount you can spend each period during your retirement 12 13 Problem 5 14 5 Number of years you delay before starting to save for retirement If you do delay starting to save, what would the interest rate need to be for you to hit the target amount in B10? (APR) Convert that APR to an EAR. 15 16 $5,000.00 How much do you want to be able to spend each period during your retirement? 17 18 19 Problem 6 Amount you need in your account at retirement in order to spend this amount (cell B17) Amount you need to save each period before retirement to have enough to meet your goal. 20 21 A B 1 2 750 30 15 4 How much money do you save each period? Years until your retirement Years in you plan to be in retirement How many periods in a year? Expected return on your savings before retirement (this is an EAR) Expected return on savings during retirement (this is an APR) 5 4 6 Problem 1 Problem 2 Problem 3 0.08 0.06 7 8 9 Problem 4 10 11 12 =NOMINAL(B6,B5) =FV(B6,B5,-B2,0,1) =PMT(B6,B5,0,B10) 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 Problem 5 13 14 =RATE((B3-B13) *B5,-B2,0,B10) *B5 Number of years you delay before starting to save for retirement If you do delay starting to save, what would the interest rate need to be for you to hit the target amount in B10? (APR) Convert that APR to an EAR. 15 16 17 5000 How much do you want to be able to spend each period during your retirement? 18 19 Problem 6 Amount you need in your account at retirement in order to spend this amount (cell B17) Amount you need to save each period before retirement to have enough to meet your goal. 20 21 a. Now calculate what someone will be able to spend in retirement given how much they are saving (B2), how often they're saving (B5), how long they save (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. (12 points) In cell B9, use the function NOMINAL to convert the expected return on your savings prior to retirement into an APR. Note that if you pick monthly payments (12 payments per year), then the APR needs to be based on monthly compounding, so you'll need to link to cells B5 and 36. b. In cell B10, use the function FV to calculate the 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. 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 of each period during retirement given savings at retirement). 5. Assume that this individual intends to save nothing for the first few years, which is common for young workers. (12 points) 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. b. Use the EFFECT function in cell B15 to convert this APR into an EAR. Use the IFERROR function in cell 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). In the same cells, B14 and B15, change the cells' alignment formatting so the text shrinks to fit in the cell when/if the error message is displayed. C. a. c. What am I doing wrong for the NPV and PMT (problem 4)? The "Amount you can spend each period during your retirement" should be $8,884.69. Please show your work. My work is shown below. B D E F G H J 1 2 $ 3 4 5 750.00 How much money do you save each period? 30 Years until your retirement 15 Years in you plan to be in retirement 4.00 How many periods in a year? 8.00% Expected return on your savings before retirement (this is an EAR) 6.00% Expected return on savings during retirement (this is an APR) Problem 1 Problem 2 Problem 3 6 7 8 9 Problem 4 10 11 7.77% Expected annual return on savings before retirement (convert to an APR) $3,649.95 Amount you'll have in your account at retirement based on the savings amount in cell B2 -810 Amount you can spend each period during your retirement 12 13 Problem 5 14 5 Number of years you delay before starting to save for retirement If you do delay starting to save, what would the interest rate need to be for you to hit the target amount in B10? (APR) Convert that APR to an EAR. 15 16 $5,000.00 How much do you want to be able to spend each period during your retirement? 17 18 19 Problem 6 Amount you need in your account at retirement in order to spend this amount (cell B17) Amount you need to save each period before retirement to have enough to meet your goal. 20 21 A B 1 2 750 30 15 4 How much money do you save each period? Years until your retirement Years in you plan to be in retirement How many periods in a year? Expected return on your savings before retirement (this is an EAR) Expected return on savings during retirement (this is an APR) 5 4 6 Problem 1 Problem 2 Problem 3 0.08 0.06 7 8 9 Problem 4 10 11 12 =NOMINAL(B6,B5) =FV(B6,B5,-B2,0,1) =PMT(B6,B5,0,B10) 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 Problem 5 13 14 =RATE((B3-B13) *B5,-B2,0,B10) *B5 Number of years you delay before starting to save for retirement If you do delay starting to save, what would the interest rate need to be for you to hit the target amount in B10? (APR) Convert that APR to an EAR. 15 16 17 5000 How much do you want to be able to spend each period during your retirement? 18 19 Problem 6 Amount you need in your account at retirement in order to spend this amount (cell B17) Amount you need to save each period before retirement to have enough to meet your goal. 20 21
