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: Milabar Mikanos
Country: Cayman Islands
Language: English (Spanish)
Genre: Science
Published (Last): 8 May 2012
Pages: 391
PDF File Size: 7.38 Mb
ePub File Size: 4.89 Mb
ISBN: 216-3-77903-659-7
Downloads: 65789
Price: Free* [*Free Regsitration Required]
Uploader: Yorisar

Start by adding some data in row 7. This will provide the user with a drop-down list from which they can choose the type of annuity.

But what happens if the interest rate is 3. Your worksheet should tabpe look like the one below, except for the shading in row We don’t need to use that setting here, but you should be aware that it exists.

To create the data table we need to select A Exit rvifa the dialog box so that we can start creating new rules. The fourth, and final, rule will underline the last visible row, tabel only in visible columns. This tutorial will demonstrate how to create these tables using Excel. So, we will apply a custom format to display the text “Period” instead of the result of the formula. Be sure to click the Create a Copy box at the bottom of the dialog box.


FVIFA Calculator – Tool Slick

Here is a small piece of the FVIF table so that you can be sure that yours is correct:. Let’s take care of a couple of simple items first. Note that this does not change the formula or the result, only what appears in the cell. We can do this by applying some data fvida rules to those cells.

The rest of the table is filled in automatically when we use the Data Table command. In the Type edit box, enter “Period” dvifa the quotation marks.

This will launch the following dialog box:. So, the rule will be:.

FVIFA Calculator

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. 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.

Click here to learn more. Choose Fvifq Rule from the menu.

FVIFA Calculator – Calculate Future Value Interest Factor of Annuity

In B7 we will enter another data validation rule. This is where you tell Excel that cell F1 is where to plug in the numbers from the top row of the table the interest rates and that F2 is where to plug in the numbers from the left column the period numbers.


As noted, these tables provide a great deal of flexibility. Apply a format with a border on the right edge only, and set the font to bold.

This flexibility is achieved using standard Excel features such as time value of money functionstwo-input data tables, data validation, and conditional formatting. To test it, change B6 to, say, 10 and make sure that only A Note that we still need slightly different formulas, depending on the type of annuity as described above.

For example, we might want to see how the present value changes when both the interest rate and number of periods fvjfa. Go to the Number tab and choose the Custom category. Apply a format by clicking the Format button and apply some borders, background shading, and a bold font.

The snippet below shows the formulas that are in the PVIF table from above:. Choose Decimal from the Allow list, between from the Data list, set the minimum to 0, and the maximum to 0. The formula in A10 is:.

We will use the following IF statement:.