Some individuals may not intend to save money for the first few years. Cell B13 allows...

  

Transcribed Image Text:

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) a. Use the RATE function in cell 814 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. Use the EFFECT function in cell B15 to convert this APR into an EAR. b. c. 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 83). 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. B11 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 <> A Problem 1 Problem 2 Problem 31 Problem 4 Problem 5 Problem 6 X fx =PMT(B7,84,-B10,0,0) B C D $11,000.00 How much money do you currently have in savings? $750.00 How much money do you plan to save each period? 30 Years until your retirement E F 20 Years you plan to be in retirement 4 How frequently do you save money each year? Annually (1), quarterly (4), or monthly (12 times each 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) 7.77% Expected annual return on savings before retirement (convert to an APR) $460,570.90 Amount you'll have in your account at retirement based on the savings amount in cell B2 5467 Amount you can spend each period during your retirement 15 Number of years you will delay before starting to save for retirement 19.82% The interest rate you would need to earn to hit the target amount in 810 if you do delay starting to save. 21 34% Convert that APR to an EAR. $5,000.00 How much do you want to be able to spend each period during your retirement? 5232.03862 Amount you need in your account at retirement in order to spend this target amount (cell 817) $734,66 Amount you need to save each period before retirement to have enough to meet your goal. 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) a. Use the RATE function in cell 814 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. Use the EFFECT function in cell B15 to convert this APR into an EAR. b. c. 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 83). 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. B11 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 <> A Problem 1 Problem 2 Problem 31 Problem 4 Problem 5 Problem 6 X fx =PMT(B7,84,-B10,0,0) B C D $11,000.00 How much money do you currently have in savings? $750.00 How much money do you plan to save each period? 30 Years until your retirement E F 20 Years you plan to be in retirement 4 How frequently do you save money each year? Annually (1), quarterly (4), or monthly (12 times each 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) 7.77% Expected annual return on savings before retirement (convert to an APR) $460,570.90 Amount you'll have in your account at retirement based on the savings amount in cell B2 5467 Amount you can spend each period during your retirement 15 Number of years you will delay before starting to save for retirement 19.82% The interest rate you would need to earn to hit the target amount in 810 if you do delay starting to save. 21 34% Convert that APR to an EAR. $5,000.00 How much do you want to be able to spend each period during your retirement? 5232.03862 Amount you need in your account at retirement in order to spend this target amount (cell 817) $734,66 Amount you need to save each period before retirement to have enough to meet your goal.

Answer rating: 100% (QA)

a Use the RATE function in cell 814 to calculate what you would need the return to be in order to have the same amount of money at retirement as in Pr... View the full answer