Table 1: F uture Value Interest F actor (F. V. IF.) ($1 at r% for n periods). F. V .. Table 3: Future Value of an Annuity Interest Factor (FVIFA) ($1 per period at r%. n \ r. 1%. 2%. 3%. 4%. 5%. 6%. 7%. 8%. 9%. 10%. 11%. 12%. 13%. 14%. 15%. 16%. 17%. 1. This tutorial demonstrates how to create the PVIF, FVIF, PVIFA, and FVIFA tables using Excel. I use conditional formatting, custom number formatting, data.

Author: Goltigal Kazralabar
Country: New Zealand
Language: English (Spanish)
Genre: Video
Published (Last): 21 January 2010
Pages: 266
PDF File Size: 7.46 Mb
ePub File Size: 8.56 Mb
ISBN: 803-3-85183-221-1
Downloads: 30420
Price: Free* [*Free Regsitration Required]
Uploader: Gogar

Rather than creating a large table with the PV function repeated over and over again, we will use Excel’s two-input data table feature. The Table function will display that array in our table area B Here is a small piece of the FVIF table so that you can be sure that yours is correct:.

Additionally, we need to specify the Type argument to the function. Table recalculation can be slow for large tables or complicated formulas, so one of Excel’s calculation options is to Automatic Except for Data Tables.

FVIFA Calculator – Tool Slick

Only the formatting of the result has been changed. To tablr up the rules, select a cell or range and then click the Conditional Formatting button on the Home tab of the ribbon. In fact, it just confuses things.

The key to creating the tables is to understand that they are all based upon the basic time value of money formulas. As noted, these tables provide a great deal of flexibility. For regular annuities this argument is 0, but for annuities due it is 1. AE10 and then call up the dialog box above. You can try it yourself: In A7 enter “Type” for the type of annuity. So, we will apply a custom format to display the text “Period” instead of the result of the formula.


To set the custom number format, select A10 and then right click and choose Format Cells. 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.

It can also add to the functionality. It works by substituting the a value from the top row and left column into the cells specified F1 and F2. You will now see the following dialog box:. For example, we might want to see how the present value changes when both the interest rate and number of periods changes. In B7 we will enter another data validation rule. Click B7 and then the Data Validation button.

FVIFA Calculator

In the format, set the font color to white. That will preserve the data, but it will be invisible because the font color tablw the same as the background color. The complication is because we want the table to handle both regular annuities and annuities due. Then you have to interpolate because 3. For the final touch, we want to make sure that a user cannot enter data that fvifx unexpected in B1: So, the rule will be:.


Since we are building these tables with Excel, we can use its built-in vfifa PV in this case instead of the mathematical formula. The fourth, and final, rule will underline the last visible row, but only in visible columns. That is the same value that we used for the PVIF in the original example problem above. For the text in A9 we need to specify slightly different text depending on the yable of annuity. We need to add 1 to the number of columns because we are including column A, which is not a part of the 30 columns specified.

At this point the PVIF table is fully functional.

Time Value of Money Tables in Excel |

This is the area specifically, F1 and F2 where Excel will substitute the values from the top row and left column to get the numbers to paste into the table. P10 should have the format. AE70 and then use this rule:.

The snippet below shows the formulas that are in the PVIF table from above:. You can see how the rules are created. tabe

Our PVIF table will serve as a template for each of the other three tables.

No Comments

Categories: Literature