1. Time Value of Money Math and Excel

Instructions: Using EXCEL, build Factor Tables for the following 3 calculations. Each table should be on a separate worksheet in your Excel book.

- PVF Present Value of 1

- FVF Future Value of 1

- PVF-OA Present Value of an Ordinary Annuity of 1

Each table must comply with the following:

- Create each of these tables using only interest rates from 1% to 6% (incrementing by 1%). In other words, there should be 6 columns.

- Use only periods 1 to 10. So there should only be 10 rows.

- The cells in your spreadsheet should be formulas. The formulas should include an absolute cell reference to the interest rate (top row) and an absolute reference to the period (left hand column). The following discusses the differences between a relative reference and an absolute reference.

EXCEL Relative and Absolute cell references

By default, a cell reference is a relative reference, which means that the reference is relative to the location of the cell. If, for example, you refer to cell A2 from cell C2, you are actually referring to a cell that is two columns to the left (C minus A)in the same row (2). When you copy a formula that contains a relative cell reference, that reference in the formula will change.

As an example, if you copy the formula =B4*C4 from cell D4 to D5, the formula in D5 adjusts to the right by one column and becomes =B5*C5. If you want to maintain the original cell reference in this example when you copy it, you make the cell reference absolute by preceding the columns (B and C) and row (2) with a dollar sign ($). Then, when you copy the formula =$B$4*$C$4 from D4 to D5, the formula stays exactly the same.