## Excel

#### Definitions & Other

Are you a student? Did you know that Amazon is offering 6 months of Amazon Prime - free two-day shipping, free movies, and other benefits - to students?

Virtually every finance textbook has, at the back, a series of tables that contain multipliers that can be used to easily calculate present or future values without the need for a financial calculator. In recent years these tables have slowly given way to financial calculators, but they are still widely used by some professors and on some professional exams.

This tutorial will demonstrate how to create these tables using Excel. The tables created here are much better than the textbook tables because they overcome a couple of limitations:

• Traditional tables only contain a few interest rate/number of period combinations. My tables allow you the flexibility to show almost any number of combinations. This eliminates the need for interpolation.
• Traditional tables have limited accuracy because they typically only display the interest factors to four decimal places. My tables can be reformatted to show up to 15 decimal places (not that you want that many). Thus, they can be more accurate.
• Traditional annuity tables (PVIFA and FVIFA) in most textbooks only work for regular annuities. With my tables you can instantly change the table from regular annuities to annuities due with only a single click.

As noted, these tables provide a great deal of flexibility. This flexibility is achieved using standard Excel features such as time value of money functions, two-input data tables, data validation, and conditional formatting.

You can download a complete copy of the Time Value of Money Interest Factors workbook.

## Using the TVM Tables

Time value of money tables are very easy to use because they provide a "factor" that is multiplied by a present value, future value, or annuity payment to find the answer. So, armed with the appropriate table and a way to multiply (any calculator or even with pencil and paper) you too can easily solve time value of money problems.

The image below shows a snippet of a PVIF (Present Value Interest Factor) table:

In this case, the table provides a factor that is multiplied by a future value of a lump sum cash flow in order to obtain its present value.

Let's look at an example:

Imagine that you need to have $5,000 three years from now and can earn 4% per year in your savings account. How much do you need to deposit today in order to achieve your goal? To solve this problem, we simply multiply the future value ($5,000) by the appropriate PVIF table value:

PV = FV x PVIF

So, look down the first column of the table for the 3 period row, and then across to the 4% column. The PVIF is 0.8890 so the answer is:

PV = 5,000 x 0.8890 = 4,445

Therefore, if you deposit $4,445 today in a saving account that pays 4% interest compounded annually, then you will have$5,000 in three years.

But what happens if the interest rate is 3.5% instead of 3% or 4%? Then you have to interpolate because 3.5% is not in the table. You can approximate the answer by averaging the PVIF table values for 3% and 4% and using that average for the PVIF. The average is 0.90205 so you would get an answer of $4,510.25. The correct answer, though, is$4,509.71 so your answer would be off by about $0.54. Not too bad, but the tables that we create here can easily have the exact interest rate that you need. ## Creating the Interest Factor Tables The key to creating the tables is to understand that they are all based upon the basic time value of money formulas. For example, the PVIF factors from the table above are calculated by using$1 for the FV in the equation for present value:

Substituting 1 for FV, 3 for N, and 0.04 for i we get 0.8890. That is the same as the PVIF that we originally pulled from the table. Since we are building these tables with Excel, we can use its built-in functions (PV() in this case) instead of the mathematical formula.

## Creating the PVIFA Table

The PVIFA (Present Value Interest Factor Annuity) table is only slightly more complicated, but start by creating another copy of the PVIF table. The complication is because we want the table to handle both regular annuities and annuities due.

Start by adding some data in row 7. In A7 enter "Type" (for the type of annuity). In B7 we will enter another data validation rule. Click B7 and then the Data Validation button. This time we want to set the Allow to List and then the Souce to "Regular, Due" (do not type the quotes, but do include the comma). This will provide the user with a drop-down list from which they can choose the type of annuity.

For the text in A9 we need to specify slightly different text depending on the type of annuity. We will use the following IF() statement:

=IF(B7="Regular","Present Value of an Annuity of $1 per Period for N Periods (PVIFA)","Present Value of an Annuity Due of$1 per Period for N Periods (PVIFAd)")

Finally, in A10 we will use the PV() function again, but this time we will set FV to 0 and PMT to 1. Additionally, we need to specify the Type argument to the function. For regular annuities this argument is 0, but for annuities due it is 1. The formula in A10 is:

=IF(B7="Due",PV(F1,F2,-1,0,1),PV(F1,F2,-1,0,0))

Here is a snippet of the table as it appears for regular annuities:

If you change to an annuity due (in B7) then, for reference, you should get 1.000 in B11 and 1.9901 in B12.

## Creating the FVIFA Table

To create the FVIFA (Future Value Interest Factor Annuity) table, start by copying the PVIFA table that we created above. The tables are almost identical, except for the text in A9 and the formula in A10.

For the text in A9 use the following IF() statement:

=IF(B7="Regular","Future Value of an Annuity of $1 per Period at the End of N Periods (FVIFA)","Future Value of an Annuity Due of$1 per Period at the End of N Periods (FVIFAd)")

In A10, we need to change the PV() function to FV() as follows:

=IF(B7="Due",FV(F1,F2,-1,0,1),FV(F1,F2,-1,0,0))

Note that we still need slightly different formulas, depending on the type of annuity as described above. Your FVIFA table should look like the one below:

For reference, if you change B7 to an annuity due you should get 1.0100 in B11 and 2.0301 in B12.

You can download a complete copy of the Time Value of Money Interest Factors workbook.