Creating Time Value of Money Tables in Excel

Saturday, July 26, 2008

If you have ever taken a finance or accounting course, then you may remember using tables from the back of the book to solve time value of money problems. These tables (called PVIF, PVIFA, FVIF, and FVIFA) are falling out of use due to the use of financial calculators and spreadsheets, but they are still being used in some places. I know, because I get requests occasionally for them and I often see people searching my site for them. So, since my site is all about the time value of money, I figured it was about time that I put together a tutorial on how to create these TVM tables using Excel.

If you don’t know what I’m talking about, I have included a picture of a part of the PVIF table below:

Using the tables is quite easy. For example, suppose that you want to know the present value of $1,000 to be received in 2 periods at an interest rate of 3% per period. (You know you do!) To find the answer you simply multiply $1,000 (the FV) by the appropriate value from the table:

PV = FV x Table Value = $1,000 × 0.9426 = $942.60

The other tables provide multipliers that can be used to solve for the present value of an annuity (PVIFA), and the future value of a lump sum (FVIF) or annuity (FVIFA).

The tables covered in my tutorial are much more flexible than the fixed tables in textbooks. For example, my tables can contain up to 30 columns and 60 rows (you could make them bigger with minimal effort), and you can set the starting interest rate as well as the increase in the rate. Similarly, you can specify the starting period and the amount by which the period increases. The tables are constructed using two-input data tables, conditional formatting, data validation, custom number formats, and Excel’s built-in PV() and FV() functions. Even if you aren’t interested in the TVM tables you may still enjoy reading about some of those techniques.

If you aren’t familiar with using Excel’s built in time value of money functions (e.g., PV(), FV(), Pmt(), etc) then you might be interested in my tutorial on using Microsoft Excel as a Financial Calculator.

Problems with Tables

I have never been a fan of the time value of money tables. My primary problem with them is pedagogical. I have always believed that students don’t really learn the principles of the time value of money if they use tables. If you don’t understand time value of money, then you don’t understand finance. That is why I spend a lot of time in my classes explaining the concepts and using the TVM formulas.

Other problems with the tables include the need to interpolate, rounding errors, and the lack of large interest rates and number of periods. My tables can overcome each of these problems:

  • There is no need to interpolate because you can make the table display any interest rate. Do you need to use 20.35% as as your discount rate? No problem with my tables, but you won’t find that rate in most tables.
  • Rounding errors result because most printed tables only display 4 decimal places. With my tables you can display as many as you desire.
  • The lack of large interest rates and periods is solved by my tables because you choose the rates and number of periods that you want to see. Most printed tables only show rates up to about 15%, and only about 20 to 30 periods.
  • Most textbooks do not include tables for annuities due, but my annuity tables make that a simple choice with a drop-down list.

Obviously, tables are an anachronism – especially if you are using Excel. However, people still use them and want to know how to use Excel to create them. So, I’m trying to fill a need. Note that these tables are not meant to be used in Excel calculations (you certainly could, but that would be pointless), rather they are meant to be printed and used when required.

You can see the tutorial, or just download the Excel 2007 TVM Tables worksheet.

Posted by .(JavaScript must be enabled to view this email address) on 07/26 at 04:56 PM

Permalink Tell-a-Friend


Commenting is not available in this channel entry.