That is, a sum of money today is worth more than the same sum will be in the future, because money has the potential to grow in value over a given period of time. Provided money can earn interest, any amount of money is worth more the sooner it is received. Present value impact factors are often used in analyzing annuities. The present value interest factor of an annuity PVIFA is useful when deciding whether to take a lump-sum payment now or accept an annuity payment in future periods.
|Published (Last):||26 December 2006|
|PDF File Size:||14.53 Mb|
|ePub File Size:||9.60 Mb|
|Price:||Free* [*Free Regsitration Required]|
Did you know that Amazon is offering 6 months of Amazon Prime - free two-day shipping, free movies, and other benefits - to students?
Click here to learn more 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. 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. This tutorial will demonstrate how to create these tables using Excel.
My tables allow you the flexibility to show almost any number of combinations. This eliminates the need for interpolation. Traditional tables have limited accuracy because they typically only display the interest factors to four decimal places. My tables can be reformatted to show up to 15 decimal places not that you want that many. Thus, they can be more accurate.
With my tables you can instantly change the table from regular annuities to annuities due with only a single click. As noted, these tables provide a great deal of flexibility. This flexibility is achieved using standard Excel features such as time value of money functions , two-input data tables, data validation, and conditional formatting. Using the TVM Tables 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.
So, armed with the appropriate table and a way to multiply any calculator or even with pencil and paper you too can easily solve time value of money problems. The image below shows a snippet of a PVIF Present Value Interest Factor table: In this case, the table provides a factor that is multiplied by a future value of a lump sum cash flow in order to obtain its present value. How much do you need to deposit today in order to achieve your goal? The PVIF is 0. But what happens if the interest rate is 3.
Then you have to interpolate because 3. The average is 0. Not too bad, but the tables that we create here can easily have the exact interest rate that you need. Creating the Interest Factor Tables The key to creating the tables is to understand that they are all based upon the basic time value of money formulas.
That is the same as the PVIF that we originally pulled from the table. Since we are building these tables with Excel, we can use its built-in functions PV in this case instead of the mathematical formula. This allows us to enter a formula once, and then it will automatically populate the table based on values in the left column and top row of the table.
This feature is typically used for sensitivity analysis. For example, we might want to see how the present value changes when both the interest rate and number of periods changes. The snippet below shows the formulas that are in the PVIF table from above: Note that the PV function is only used in the upper-left corner of the table.
The rest of the table is filled in automatically when we use the Data Table command. It works by substituting the a value from the top row and left column into the cells specified F1 and F2. Excel does this repeatedly to fill in the table. We will see how to create the data table in section below. Once we get this working properly, we can simply copy the worksheet and then change the formula that drives the table. This is the formula that will drive our data table.
The 0. This will "step up" the interest rate. Copy this formula across to AE10 that is 30 columns of interest rates. Copy this formula down through A This will "step up" the period number by the number of units specified in B4.
Do not add the shading in row We will do that with Conditional Formatting later on. Before creating the data table, I should explain the data in E1:F2. 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. Notice that the value in A10 has changed to 0. That is the same value that we used for the PVIF in the original example problem above.
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 results will be placed into an array at the intersection of the appropriate row and column. The Table function will display that array in our table area BAE You will now see the following dialog box: 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.
Please note that the actual numbers in F1 and F2 do not matter at all because Excel is going to replace them to create the table. Again, this is a two-input data table.
Your worksheet should now look like the one below, except for the shading in row At this point the PVIF table is fully functional. If you change the value in B1, for example, then the interest rates in the table will change, and the interest factors will be recalculated as well. However, we need to clean this up a bit to make it more functional. It can also add to the functionality. 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 fact, it just confuses things. So, we will apply a custom format to display the text "Period" instead of the result of the formula. Note that this does not change the formula or the result, only what appears in the cell. To set the custom number format, select A10 and then right click and choose Format Cells. Go to the Number tab and choose the Custom category.
In the Type edit box, enter "Period" include the quotation marks. This tells Excel to display the word "Period" regardless of the result of the formula.
Click the OK button to apply the custom number format. Note that if you look at the formula bar you will see that the formula is still there. Only the formatting of the result has been changed. We want the period numbers to have two decimal places and to be roughly centered in column A. The format mask to do that is 0. Note that the underscores add spaces to the number format, and that the right paren at the end is required.
Applying Conditional Formatting Rules Conditional formatting changes the look of a cell or range when certain conditions are met.
To set up the rules, select a cell or range and then click the Conditional Formatting button on the Home tab of the ribbon. Choose New Rule from the menu. We want to create rules that are based on formulas, so choose the last item in the Rule Type list Use a formula to determine which cells to format. This leads to the following dialog box: You can see how the rules are created. They must be formulas that will evaluate to either True or False. Exit from the dialog box so that we can start creating new rules.
The first rule will create the shading and borders for the top row of our table. Select AAE10 and then call up the dialog box above.
We only want to apply the format to the cells if they are in the "visible" part of the table that is, the column is within the range specified by the number of columns in B6. Apply a format by clicking the Format button and apply some borders, background shading, and a bold font.
Click OK to apply the formatting rule. To test it, change B6 to, say, 10 and make sure that only AK10 have this format. If you change B6 to 15, then AP10 should have the format. For the second rule we want to apply a border to the right edge of column A, but only those rows that are supposed to be visible in the table.
Apply a format with a border on the right edge only, and set the font to bold. The third rule will hide everything outside of the visible part of the table as defined by the values in B5:B6. That will preserve the data, but it will be invisible because the font color is the same as the background color.
The fourth, and final, rule will underline the last visible row, but only in visible columns. Applying Data Validation Rules For the final touch, we want to make sure that a user cannot enter data that is unexpected in B1:B6. We can do this by applying some data validation rules to those cells.
Select B1 and then click the Data Validation button on the Data tab. This will launch the following dialog box: For the interest rate we want to allow any decimal number between 0 and 0.
Choose Decimal from the Allow list, between from the Data list, set the minimum to 0, and the maximum to 0. If you choose, you can set an input message that will popup when the cell is selected, and an error message that is displayed if the user enters a number outside of the allowable range. Set up similar rules for B2:B6 as follows: B2 - Decimal between 0 and 0. The others are almost done as well! So we will simply copy the PVIF worksheet.
Be sure to click the Create a Copy box at the bottom of the dialog box.
(k)ارزش فعلى و جدول مربوط به آن
Pvif & Fvif Table