Problem 1 Problem 2 Problem 3 Problem 4 Problem 5 Problem 6 Problem 7 Problem 8...

Transcribed Image Text:

Problem 1 Problem 2 Problem 3 Problem 4 Problem 5 Problem 6 Problem 7 Problem 8 Problem 9 1000.00 How much money do you plan to save each period? 30 Years until your retirement 20 Years you plan to be in 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)? Expected return on your savings before retirement (this is an APR) Expected return on your savings during retirement (this is an APR) 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 35000 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 2000000 How much money would you like to have in savings when you retire? 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) 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 provided in the 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. #1 Use data validation in cell C2 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 CS 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 C6 to an APR. Be sure your formula links to cell C7 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 in a year as specified in cell C7). #6 Calculate the amount she will have at retirement (cell C11) if she already has $35,000 in her retirement account. Hint: this is easiest to do by using the optional [pv] argument in the FV function. #7 Use the PMT function to calculate the amount she can spend each period in retirement given the amount you calculate cell C16. #8 Dalia would like to have $2,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 C7) 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 using the Percentages format, and cells with dollar amounts using the Currency format. In both cases, display two decimals. Problem 1 Problem 2 Problem 3 Problem 4 Problem 5 Problem 6 Problem 7 Problem 8 Problem 9 1000.00 How much money do you plan to save each period? 30 Years until your retirement 20 Years you plan to be in 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)? Expected return on your savings before retirement (this is an APR) Expected return on your savings during retirement (this is an APR) 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 35000 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 2000000 How much money would you like to have in savings when you retire? 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) 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 provided in the 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. #1 Use data validation in cell C2 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 CS 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 C6 to an APR. Be sure your formula links to cell C7 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 in a year as specified in cell C7). #6 Calculate the amount she will have at retirement (cell C11) if she already has $35,000 in her retirement account. Hint: this is easiest to do by using the optional [pv] argument in the FV function. #7 Use the PMT function to calculate the amount she can spend each period in retirement given the amount you calculate cell C16. #8 Dalia would like to have $2,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 C7) 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 using the Percentages format, and cells with dollar amounts using the Currency format. In both cases, display two decimals.