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: Vudogor Zulkirisar
Country: Belize
Language: English (Spanish)
Genre: Education
Published (Last): 16 September 2018
Pages: 258
PDF File Size: 16.7 Mb
ePub File Size: 20.23 Mb
ISBN: 297-1-42191-928-4
Downloads: 45535
Price: Free* [*Free Regsitration Required]
Uploader: Grosida

This leads to the following dialog box:.

Only the formatting of the result has been changed. The results will be placed into an array at the intersection of the appropriate row and column. Excel does this repeatedly to fill in the table.

AE70 and then use this rule:. Notice that the value in A10 has changed to 0.

This flexibility is achieved using standard Excel features such as time value of money functionstwo-input data tables, data validation, and conditional formatting. In fact, it just confuses things.

FVIFA Calculator

The tables are almost identical, except for the text in A9 and ffvifa formula in A This is where you tell Excel that cell F1 is where to plug in the numbers fgifa 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. If you change B6 to 15, then A Substituting 1 for FV, 3 for N, and 0. However, we need to clean this up a bit to make it more functional. In this case, the table provides a factor that is multiplied by fvvifa future value of a lump sum cash flow in order to obtain its present value.


Tab,e tells Excel to display the word “Period” regardless of the result of the formula. Choose New Rule from the menu. For the text in A9 we need to specify slightly different text depending on the type of annuity. Select B1 and then click the Data Validation button on the Data tab. Our PVIF table will serve as a template for each of the other three tables. To set up the rules, select a cell or range and then click the Conditional Formatting button on the Home tab of the ribbon.

The complication is because we want the table to handle both regular fvifz and annuities due. Choose Decimal from the Allow list, between from the Data list, set the minimum to 0, and the maximum to 0.

Once we get this working properly, we can simply copy the worksheet and then change the formula that drives the table.

K10 have this format. For example, we don’t want them to enter a negative interest rate in B1.

Click B7 and then the Data Validation button. The third rule will hide everything outside of the visible part of the table as defined by the values in B5: Start by adding some data in row 7. 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. For the final touch, we want to make sure that a user cannot enter data that is unexpected in B1: This will provide the user with a drop-down list from which they can choose the type of annuity.

Click here to learn more. Note that the underscores add spaces to the number format, and that the right paren at the tahle is required. The formula in A10 is:. Be sure to click the Create a Copy box at the bottom of the dialog box. In the Type edit box, enter “Period” include the quotation marks.


F2, so we can hide those cells by setting the font color to white.

FVIFA Calculator – Calculate Future Value Interest Factor of Annuity

Exit from the dialog box so that we can start creating new rules. Before creating the data table, I should explain the data in E1: If you choose, you can set an input message that taboe popup when the cell tahle selected, and an error message that is displayed if the user enters a number outside of the allowable range.

The tables created here are much better than the textbook tables because they overcome a couple of limitations:. That is the same as the PVIF that we originally pulled from the table. The format mask to do that is 0.

The fourth, and final, rule will fviffa the last visible row, but only in visible columns. That will preserve the data, but it will be invisible because the font color is the same as the background color. It can also add to the functionality.

FVIFA Calculator – Tool Slick

It works by substituting the a value from the top row and left column into the cells specified F1 and F2. Note that if you look at the formula bar you will see that the formula is still fvita. To set the custom number format, select A10 and then right click and choose Format Cells.