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.
|Published (Last):||9 February 2007|
|PDF File Size:||11.24 Mb|
|ePub File Size:||10.33 Mb|
|Price:||Free* [*Free Regsitration Required]|
To set the custom number format, select A10 and then right click and choose Format Cells. K10 have this format.
For the interest rate we want to allow any decimal number between 0 and 0. The key to creating the tables is to understand that they are all based upon the basic time value of money formulas.
Your worksheet should now look like the one below, except for the shading in row 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 format mask to do that is 0. Let’s set one more custom number format, this time in A Are you a student? You can also create a one-input data table by specifying only the row or column input cell, but that fviga suit the purpose here.
Again, this is a two-input data table. We want the period numbers to have two decimal places and to be roughly centered in column A. It works by substituting the a value from the top row and left column into the cells specified F1 and F2.
Note that the underscores add spaces to the number format, and that the right paren at the end is required. The snippet below shows the formulas that are in the PVIF table from above:. To set up the rules, select a cell or range and then click the Conditional Formatting button on the Home tab of the ribbon.
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. They tble be formulas that will evaluate to either True or False. The Table function will display that array in our table area B But what happens if the interest rate is 3.
This will ttable the following dialog box:. Here is a small piece of the FVIF table so that you can be sure that yours is correct:. AE70 and then use this rule:. Additionally, we need to specify the Type argument to the function.
For reference, if you change B7 to an annuity due you should get 1. As noted, these tables provide a great deal of flexibility. For the final touch, we want to make sure that a user cannot enter data that is unexpected in B1: Conditional formatting changes the look of a cell or range when certain conditions are met.
Notice that the value in A10 has changed to 0. So, we will apply a custom format to display the text “Period” instead of the result of the formula. This flexibility is achieved using standard Excel features such as time value of money functionstwo-input data tables, data validation, and conditional formatting.
AE10 and then call up the dialog box above. The complication is because we want the table to handle both regular annuities and annuities due. However, we need to clean this up a bit to make it more functional.
Our PVIF table will serve as a template for each of the ffifa three tables. Not too bad, but the tables that we create here can easily have the exact interest rate that you need. Only the formatting of the result has been changed.
Click the OK button to apply the custom number format. So we will simply copy the PVIF worksheet. A70 and then create this formatting rule:. This will provide the user with a drop-down list from which they can choose the type of annuity. The first rule will create the shading and borders for the top row of our fvifaa. Excel does this repeatedly to fill in the table. In the Type edit box, enter “Period” include the quotation marks.
This rule checks to see that it is in column A and that the row number is in the visible range. Then you tabls to interpolate because 3. That is the same as the PVIF that we originally pulled from the table. Note that if you look at the formula bar you will see that the formula is still there.