Please show excel formula

Instead of asking how much they plan to put into savings, here we ask how much they want to be able to spend. That is, here we calculate the amount that should be saved each period to attain a target retirement income (817) based on current savings, the frequency of savings (B5), the number of years (83 and B4), and the return on the investments (B7 and 89). Note: We will assume here that the person begins saving now instead of delaying. 6. Based on the desired income in retirement (817), estimate how much should be saved each period before retirement. (8 points) a. In cell B19, use the PV function to calculate the how much money is needed at retirement to provide the desired income in B17 b. In cell B20, use the amount in B19 and the PMT function to calculate how much money must be saved each period. Hint: You'll likely need to use the FV function inside the PMT function B11 X G D A fx =PMT(87,84,-B10,0,0) B $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 20 Years you plan to be in retirement 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) Problem 1 Problem 2 Problem 3 Problem 4 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 Problems 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 Amount you can spend each period during your retirement 15 Number of years you will delay before starting to save for retirement 19.828 The interest rate you would need to earn to hit the target amount in 810 if you do delay starting to save Convert that APR to an EAR $5,000.00 How much do you want to be able to spend each period during your retirement? 220 Amount you need in your account at retirement in order to spend this target amount (cell 817) S784.66 Amount you need to save each period before retirement to have enough to meet your goal. Problem 6 Instead of asking how much they plan to put into savings, here we ask how much they want to be able to spend. That is, here we calculate the amount that should be saved each period to attain a target retirement income (817) based on current savings, the frequency of savings (B5), the number of years (83 and B4), and the return on the investments (B7 and 89). Note: We will assume here that the person begins saving now instead of delaying. 6. Based on the desired income in retirement (817), estimate how much should be saved each period before retirement. (8 points) a. In cell B19, use the PV function to calculate the how much money is needed at retirement to provide the desired income in B17 b. In cell B20, use the amount in B19 and the PMT function to calculate how much money must be saved each period. Hint: You'll likely need to use the FV function inside the PMT function B11 X G D A fx =PMT(87,84,-B10,0,0) B $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 20 Years you plan to be in retirement 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) Problem 1 Problem 2 Problem 3 Problem 4 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 Problems 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 Amount you can spend each period during your retirement 15 Number of years you will delay before starting to save for retirement 19.828 The interest rate you would need to earn to hit the target amount in 810 if you do delay starting to save Convert that APR to an EAR $5,000.00 How much do you want to be able to spend each period during your retirement? 220 Amount you need in your account at retirement in order to spend this target amount (cell 817) S784.66 Amount you need to save each period before retirement to have enough to meet your goal. Problem 6