Spreadsheets: Absolute cell references

Most Americans are allowed to create their own retirement funds using an Individual Retirement Account. Under the law, everybody can put aside up to $4000 (possibly more, depending on your age) into such a fund each year until they retire. The amount placed into such an account (and all of the interest earned on it) is exempt from taxation until it is withdrawn at retirement time. Although retirement probably seems very far away for most college students, you should begin saving for retirement as early as possible. To see why, create an IRA worksheet. The beginnings of the spreadsheet are in the file IRA.xls. Load the file and complete it using the following instructions. Save the file on your account using the file name IRA-xxx.xls where the "xxx" is your user name.

The user should be able to enter the annual savings amount and the annual interest rate (formatted as a percent). EVERYTHING ELSE ON THE WORKSHEET SHOULD DEPEND ON THOSE TWO NUMBERS.

· [1] Set the font for the entire worksheet to 10-point Verdana.

· [1] Right-align the entire worksheet.

· [1] Set the width of columns B, C, D, and E to 10.

· [1] Set the width of column A to 13.

· [1] Center the title (cell A1) over columns A through E (merge cells) and make it 16-point blue, bold, and italic.

· [1] Set the background of the two user input values (B3 and B4) to light yellow (to emphasize that these are the values to be input by the user). Put a "thick box border" around them. Set the text to bold.

· [1] Set cells A3 and A4 to bold.

· [1] Set the column headings (row 6) to bold, right-align them, and place a "thick bottom border" at the bottom of each cell.

· [1] Turn on the "wrap text" formatting property for the headings.

· [1] Place the numbers from 25 to 65 in column A below the word "Year" with 0 decimal places.

· [1] The beginning balance in year 25 (row 7) should be 0.

· [2] The amount saved each year should be the same as the amount given at the top of the worksheet (B4), and if the user changes the value at the top, the numbers in column C should change automatically.

· [2] The interest earned in any year (column D) should be that year's beginning balance (column B) plus the amount saved (column C), all multiplied times the interest rate given at the top of the worksheet.

· [2] The ending balance in any year (column E) should be the sum of the beginning balance (column B), the amount saved (column C), and the interest (column D).

· [2] The beginning balance (column B) for each year after the first year should be the previous year's ending balance (column E of the previous row).

· [1] Format all currency values with a dollar sign and 0 decimal places.

· [1] Format all percent values with a percent sign and 2 decimal places.

· [1] Add your name to the right part of a header.

· [1] Make sure the printout fits on a single page in portrait orientation.

· [1] Make all columns wide enough to display all of the data in that column.

· Make sure you follow all spreadsheet design guidelines.

Set the values to $4000 and 10% and get a printout. Email the file to tom.kleen@briarcilff.edu.

FYI: To see how the interest rate affects your IRA, see how much a person would have if he saved $4000 per year from age 25 to 65 (40 years) at the following rates: 2.5%, 5%, 7.5%, 10%, 15%. Now for the interesting part. To see how important it is to start saving early for retirement, look at the amounts of interest earned in the last few years. Remember that every year you delay starting to save for retirement, you are removing a year from the bottom of this table. Saving $4000 when you are 25 can cost you almost $200,000 when you reach retirement age.

Note that this worksheet assumes that you start saving at the age of 25. If you started at age 21, there would be four more rows at the bottom of the worksheet. You might want to add 4 more rows (don't bother to change the age column) and see what a difference starting 4 years earlier makes.

Updated 2005.12.03