Need help fidguring out the formula for B$13,$ my current formula is $=$PV$($B$11,$B$6,-$B$7*$B$8)$ and that's now correct. Also need help with B$14,$ Current formula is $=$PMT$($B$11,$B$6,0,-$B$13).$ These are the full instructions for the file. Steps

Begin on the sheet called Retirement Calculator $-$ EOY. Enter your first name, last name, and student ID into cells B$1,$ B$2,$ and B$3,$ respectively.

You may assume that any cash placed into the retirement account or taken out of it happens at the end of each year $($EOY$)$ and that your client wishes to have no money left over or owe anything by the time they expect to die.

Enter the following inputs:

Client age $=25$ years old

Desired retirement age $=65$ years old

Expected retirement duration $=30$ years

Current annual income $=$ $$80,000$

Percent of current income required in retirement $=85\%$

Investment returns in retirement $=3\mathrm{.}00\%$

Using data validation, make cell B$9,$ a dropdown box that allows the user to choose between three different levels of risk tolerance: "Low". "Average", and "High".

A table with these values can be found on the tab called Lookups

Set the level of risk tolerance to "High"

Using the $=$VLOOKUP$()$ function, please create a lookup function in cell B$10$ that uses the dropdown box in cell B$9$ to find the investment return your client can expect to earn based on their level of risk aversion.

The value in cell B$10$ should change as the user changes their input in cell B$9!$

Calculate the amount of retirement savings your client will need by the time they retire, based on the inputs listed above in cell B$13.$

Calculate the amount your client will need to save each year to reach the retirement goal you calculated in step $4$ in cell B$14.$

Below your calculations, there is a table to prove to your client that your calculations in steps $4$ and $5$ will work.

Begin to fill out the table in the "Status" column using the $=$IF$()$ function to take your client's age and determine whether they are working at that age or whether they are retired.

If the client is less than $65$ years old, the status should read "Working"

If the client is greater than or equal to $65$ years of age, their status should read "Retired"

Next, use conditional formatting in the "Status" column, have the cell be colored in green if your client has "Working" status or be colored red if they have "Retired" status.

Fill in the "Beginning Balance", "Deposit$/$Withdrawal$",$ and "Ending Balance" columns. The "Age" column is already filled in for you.

Your first beginning balance should be zero since the problem does not state that your client has any money currently in their account.

The "Deposit$/$Withdrawal$"$ column will switch amounts depending on whether your client is working or retired. Use an $=$IF$()$ function to switch between the cases.

Your ending balance should be your beginning balance$*(1+$ rate of return$)+$ your end$-$of$-$the$-$period deposit$/$withdrawal amount.

Remember that the rate of return your client earns changes based on whether they are working or retired! Use an $=$IF$()$ function to help you switch between cases!

If you have done everything correctly, the ending balance should be zero when your client passes away.

When you are done with the above, go to the tab called Retirement Calculator $-$ BOY.

Repeat all of the steps above, with the exception that you should assume all deposits and withdrawals occur at the beginning of the year $($BOY$).$

Keep in mind, now that deposits$/$withdrawals are happening at the beginning of the year, the ending balance formula will need to change!