1 2 Problem 1 3 4 5 6 7 8 9 Problem 2 10 Problem 3 11 Problem 4 12 Problem 5 13 14 15 16 Problem 6 17 Problem 7 18 19 20 Problem 8 21 Problem 9 22 23 24 25 26 27 28 29 30 31 32 A 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 B D E F Expected return on your savings before retirement (this is an APR) Expected return on your savings during retirement (this is an APR) H $1,000.00 How much money do you plan to save each period? 30 Years until your retirement 15 Years you plan to be retirement (how long your money needs to last) 0.08 Expected return on your savings before retirement (this is an EAR) 0.05 Expected return on your savings during retirement (this is an EAR) 12 How frequently do you save money each year? Annually (1), quarterly (4), or monthly (12 times each year)? I Amount you'll have in your account at retirement based on the amount you save each period Amount you could spend each period during your retirement 1000000 How much money would you like to have in savings when you retire? J Interest rate (rate of return) you would need per period to hit this target amount. That periodic rate converted into an effective annual return (EAR) K 25000 How much money do you currently have in savings? Amount you'll have in your account at retirement based on the amount you save each period plus the amount already in savings Amount you could spend each period during your retirement L M N Time Value of Money Review 50 Points Your friend Dalia needs your help in planning for retirement. She has told you how much she is saving and how frequently, the number of years until her retirement, and how long she needs the savings to last in retirement. She has also told you what she believes she can earn on her investment prior to retirement, but she knows the interest earned during retirement will be lower as she will invest in safer assets. Use the information in dark green cells to construct a spreadsheet to help Dalia see what she can expect for retirement. Format all cells with dollar amounts or percentages as Currency or Percentage, respectively. P 0 Use data validation in cell B2 to restrict the number 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" # 2 Use the NOMINAL function to convert the effective annual return in cell B5 to an APR. Be sure your formula links to cell B7 since the compounding frequency must match the savings frequency. #3 Use the NOMINAL function to convert the effective annual return in cell B6 to an APR. Be sure your formula links to cell B7 since the compounding frequency must match the savings frequency. #4 Use the FV function to calculate how much Dalia will have in savings when she gets to retirement. #5 Use the PMT function to calculate how much Dalia can spend each period while in retirement (use the same number of periods as specified in cell B7 and remember the interest rate during retirement is less than during retirement). #6 Calculate the amount she will have at retirement (cell B11) if she already has $25,000 in her retirement account. Hint: this is easiest to do by using the optional [pv] argument in the FV function. R S T U #7 Use the PMT function to calculate the amount she can spend each period in retirement given the amount you calculate cell B16. #8 Dalia would like to have $1,000,000 in her account at retirement. Assuming she currently has no savings, use the RATE function to calculate what rate of return she would need each period (defined in cell B7) to reach that goal. #9 Use that periodic rate of return to calculate the effective annual return (EAR) that would allow Dalia to hit her target. Use the POWER function in your formula (note that the symbol ^ is also used for exponents in Excel equations, but I want you to use the POWER function). # 10 Format cells with percentages as using the Percentages format, and cells with dollar amounts using the Currency format. In both cases, display two decimals. V W X