A B C Problem 1 Problem 2 Problem 3 Problem 4 Problem 5 D E F G 500.00 How much money do you plan to save each period? Cell Styles H I J 0.1 Expected return on your savings before retirement (this is an EAR) 0.05 Expected return on your savings during retirement (this is an EAR) L Editing Sensitivity Add-ins Analy Format v Dat 35 Years until your retirement 25 Years you plan to be in retirement (how long your money needs to last) 12 How frequently do you save money each year? Annually (1), quarterly (4), or monthly (12 times each year)? 18 4.89% Expected return on your savings during retirement (this is an APR) 9.57% Expected return on your savings before retirement (this is an APR) $1,699,395.51 Amount you'll have in your account at retirement based on the amount you save each period (answer should be about $1,699,396). $9,824.85 Amount you could spend each period during your retirement 50000 How much money do you currently have in savings? Problem 6 Problem 7 $3,104,517.35 Amount you'll have in your account at retirement based on the amount you save each period plus the amount already in savings $17,948.40 Amount you could spend each period during your retirement (answer should be about $17,948). 15000 How much money would you like to receive in each period in retirement (e.g., if periods are monthly, what monthly income do you want in retirement)? How much money will you need to have at retirement based on the amount above in cell C20 and the years you plan to be in retirement? (answer should be about $2,594,535). Given your current savings, how much money would you need to save, starting today, to hit that target? Problem 8 1. $2,594,535.41 Time Value of Money Review 50 Points -+- Use the information provided in the cells to construct a spreadsheet to help someone see what to expect for retirement. Format all cells with dollar amounts or percentages as Currency or Percentage, respectively. Start by restricting the numbers that can be used as inputs. #1 Use Data Validation in cells C2 to C4 to restrict the value to be a decimal larger than zero. When a negative number is entered, the cell should return the error message "Do not enter negative numbers" Use Data Validation in cells C5 to C7 to restrict the value to be a whole number larger than zero. When a negative number is entered, the cell should return the error message "Do not enter negative numbers" We have been give EARS, but it is more convenient to work with APRS when the annuity payments are not annual. Calculate the APRS. #2 Use the NOMINAL function to convert the effective annual return in cell C4 to an APR. Be sure your formula links to cell C7 since the compounding frequency must match the savings frequency. #3 Use the NOMINAL function to convert the effective annual return in cell C3 to an APR. Be sure your formula links to cell C7 since the compounding frequency must match the savings frequency. Based on the information given, calculate the amount that will be in savings at retirement and what annuity payment that could purchase. #4 Use the FV function to calculate how much will be in savings at retirement. #5 Use the PMT function to calculate how much can be spent each period while in retirement (use the same number of periods in a year as specified in cell C7). Many people already have money in a retirement account. We will adjust our calculations to include any current savings. #6 Re-calculate the amount that will be in savings at retirement (cell C11) if there is already $50,000 in the retirement account. Use the optional [pv] argument in the FV function in problem #4. #7 Use the PMT function to calculate the amount that can be spent each period in retirement given the amount you calculate as the total retirement savings in cell C16. Now we want the user to enter the amount they would like to spend each period in retirement, e.g. $15,000. Given the previous assumptions, including the current savings of $50,000, calculate the following: #8 Calculate how much must be in the retirement account for this desired annuity in retirement. Use the PV function. #9 Calculate how much money must be saved each period, starting today, to hit this target amount given your current savings. Use the PMT function and include current savings. Change the formatting to make the spreadsheet easier to read.