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):||16 October 2004|
|PDF File Size:||17.19 Mb|
|ePub File Size:||3.46 Mb|
|Price:||Free* [*Free Regsitration Required]|
This tells Excel to display the word “Period” regardless of the result of the formula.
But what happens if the interest rate is 3. So, armed with the appropriate table and a way to multiply any calculator or even with pencil and paper you fvfa can easily solve time value of money problems. Also, we don’t need to see the number in A Let’s take care of a couple of simple items first.
Select the entire table A So, we will apply a custom format to display the text “Period” instead of the result of the formula.
Are you a student? Rather than creating a large table with the PV function repeated over and over again, we will use Excel’s ffvifa data table feature. Our PVIF table will serve as a template for each of the other three tables.
The key to creating the tables is to understand that they are all based upon the basic time value of money formulas. This flexibility is achieved using standard Excel features such as time value of money functionstwo-input data tables, data validation, and conditional formatting. Be sure to click the Create a Copy box at the bottom fvica the dialog box. Apply a border to the bottom using the Format button.
So, essentially what happens in the data table is that Excel will plug numbers into F1 and F2 and then recalculate the formula in A The PVIF is 0.
Time Value of Money Tables in Excel |
Again, this is a two-input data table. Apply a format with a border on the right edge only, and set the font to bold. The results will be placed into an array at the intersection of the appropriate row and column.
Note that the PV function is only used in the upper-left fable of the table. Click OK to apply the formatting rule.
The snippet below shows the formulas that are in the PVIF table from above:. We can do this by applying some data validation rules to those cells. We want the period numbers to have two decimal places and to be roughly centered in column A.
In this section we will see how to apply several different kinds of formatting and data validation rules to make the TVM tables more flexible and functional. In the Type edit box, enter “Period” include the quotation marks. Did you know that Amazon is offering 6 months of Amazon Prime – free two-day shipping, free movies, and other benefits – to students? Conditional formatting changes the look of a cell or range when certain conditions are met.
So we will simply copy the PVIF worksheet.
For example, we don’t want them to enter a negative interest rate in B1. Start by adding some data in row 7. This will launch tzble following dialog box:. Apply a format by clicking the Format button and apply some borders, background shading, and a bold font. Go to the Number tab and choose the Custom category. How much do you need to deposit today in order to achieve your goal?
So, the rule will be:. Virtually every finance textbook has, at the back, a series of tables that tzble multipliers that can be used to easily calculate present or future values without the need for a financial calculator. Here is a small piece of the FVIF table so that you can be sure that yours is correct:. The formula in A10 is:. This time we want to set the Allow to List and then the Souce to “Regular, Due” do not type the quotes, but do include the comma.
Additionally, we need to specify the Type argument to the function.
If you change to an annuity due in B7 then, for reference, you should get 1. The average is 0.