PLEASE ANSWER WITH THE EXCEL FUNCTIONS FOR EACH PROBLEM! Thank you

Problem $1$

$0\mathrm{.}1$

$0\mathrm{.}05$

$35$

$25$

Problem $2$

Problem $3$

Problem $4$

Problem $5$

$$50,000\mathrm{.}00$

Problem $6$

Problem $7$

Problem $8$

Problem $9$

How much money do you plan to save each period?

Expected return on your savings before retirement $($this is an EAR$)$

Expected return on your savings during retirement $($this is an EAR$)$

Years until your retirement

Years you plan to be in retirement $($how long your money needs to last$)$

How frequently do you save money each year? Annually $(1),$ quarterly $(4),$ or monthly $(12$ times each year$)?$

Expected return on your savings during retirement $($this is an APR$)$

Expected return on your savings before retirement $($this is an APR$)$

Amount you'll have in your account at retirement based on the amount you save each period $($answer should be about $$1,699,396).$

Amount you could spend each period during your retirement

$$ 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 $($answer should be about $$17,948).$

$$ 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 C$20$ 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?

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. 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$

#$3$

Use the NOMINAL function to convert the effective annual return in cell C$4$ to an APR. Be sure your formula links to cell C$7$ since the compounding frequency must match the savings frequency. Use the NOMINAL function to convert the effective annual return in cell C$3$ to an APR. Be sure your formula links to cell C$7$ 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 C$7).$

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 C$16.$

#$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.

#$10$ Format cells with percentages using the Percentages format, and cells with dollar amounts using the Currency format. In both cases, display two decimals.