In-Class Exercise – Excel Financial Functions

 

The second Excel exercise covers formatting, formulas, and financial functions in Excel. Retirement is years away, but it is never too soon to start planning. Most corporations include some type of retirement contribution in their benefits package and/or you can supplement that money through an individual retirement account (IRA). The Future Value function enables you to calculate the amount of money you will have at retirement, based on a series of uniform contributions during your working years. Once you reach retirement, you do not withdraw all of the money immediately, but withdraw it periodically as a monthly pension. The PMT function permits you to calculate the value of your monthly pension.

 

Create a worksheet similar to the one in Figure 1. The annual salary in the illustration is relatively modest by design to show you the power of compound interest. You can use any salary you like for your worksheet.

 

Calculating Your Retirement
Accrual Phase			Pension Phase
Annual Salary	$35,000		The size of your "nest egg"	$1,124,305
Employee contribution	6.20%		Interest rate	6%
Employer contribution	6.20%		Years in retirement	25
Total contribution	$4,340		Monthly Pension	$7,244
Interest Rate	8%
Years contributing	40		Your Name Goes Here
Future Value	$1,124,305		Click here for Social Security Web Site

 

 

Note the following:

1.      The "total contribution" is a formula based on a percentage of your annual salary, plus a matching contribution from your employer. The 6.2% in the above figure corresponds to the percentages that are currently in effect for Social Security. (In actuality, the government currently deducts 7.65% from your paycheck, and allocates 6.2% for Social Security and the remaining 1.45% for Medicare.)

2.      The “accrual phase” uses the FV function to determine the amount of money you will accumulate. The FV function in the cell to the right of "Future Value" has three arguments – the interest rate per period, the number of periods, and the contribution per period.

3.      The pension phase takes the amount of money you have accumulated and uses the PMT function to determine the payments you will receive in retirement.  The formula in the cell to the right of "the size of your nest egg" is a simple reference to the amount accumulated in the future value cell, whereas the formula in the cell to the right of "Monthly Pension" uses the PMT function to compute your monthly pension. Note that the accrual phase uses an annual contribution in its calculations, whereas the pension phase determines a monthly pension.

4.      Add a hyperlink in your worksheet that points to the retirement calculators on the Social Security Web site (http:www.ssa.gov/planners/calculators.htm). f you are interested, you can use the calculator to estimate your benefit from Social Security.

5.      Add your name to the worksheet, print the worksheet both ways to show displayed values and cell formulas, and submit the assignment.