Excel Assignment:

Create a spreadsheet to help you calculate retirement benefits:

Input questions should include: Current age, age at retirement and age at death? Other relevant data will also need to be input.

The spreadsheet should be easily readable, and usable. This implies good setup with an input section, and an output section. It also needs to be user friendly so anyone could use it$.$

For the full $10$ points, you must make your spreadsheet interesting, and special. Fulfilling the minimum requirements will NOT earn a $10$ on this assignment.

To help with grading, please input the following data in your spreadsheet model

Step $1$:

Age: $25$

Retirement: $67$

Death: $90$

Money set aside for retirement now: $1,000$

Money set aside each month: $500$

Interest rate earned before retirement: $10\%$

Interest rate earned during retirement: $6\%$

Cost of travel to be taken at the beginning of retirement: $150,000$

Death benefit for posterity, or me if I outlive my plan: $2$ million

Step $2$:

Calculate the amount you will get to live on EACH MONTH during retirement based on your savings plan. You will use the $=$ fv$()$ and $=$pmt$()$ functions. Remember that for interest rates, use the decimal divided by $12,$ so $8\%=\mathrm{.}08/12$

Step $3$:

Create a $2$ variable table with RETIREMENT AGE as the row ranging from $60$ to $70$ in $1$ year increments. The column should include monthly retirement contribution ranging from $250$ to $1,000$ in $50$ dollar increments. The center of the table should show monthly payment outcomes given the age and payment options chosen.

You may work on this project in groups of $2,$ or individually. If you work as a team, please place both names on the assignment. Also, along with a sheet showing your answers, please print out a sheet of your formulas so I can see how you got there.

$$

Instructions on making a $2$ variable data table

example

Answer cell $60616263$

$250$

$300$

$350$

By answer cell, I imply you should put a cell reference to the monthly payment during retirement

Then highlight the entire rectangle and select data$/$what if$/$table

Input row $($click on the location you would like the retirement age to go$)$

Input column $($click on the location you would like the pre$-$retirement payment to go$)$

Note:

Use cell references in all of your calculations. In order to get the correct answer, your years during retirement must be calculated. The table changes the age at retirement, so the pre$-$retirement N and the post retirement N will change automatically to reflect the new retirement age.