PVIFA Excel Function
Written by:Abraham A.What is PVIFA
Assume you have borrowed an amount of $X from a bank that charges compound interest of i% for n periods. You have to repay a periodic payment of $1 for n periods at an interest rate of i%. PVIFA will be the amount of money you borrowed for which you must make periodic payment of $1 for n periods at an interest rate of i%.
Compound Interest
As stated earlier compound interest is the interest chraged on a loan plus interest on interest. Compounding of interest may be either discrete or it may be continuous compouding. When interest is calculated per period such as per year, per quarter, per month or per day then it is referred to as periodic or discrete compounding of interest. Yet modern banking permits calculation of interest on continuous scale, this is referred to as continuous compounding of interest. Excel functions only provide results when interest is compounded discretely.
PVIFA MS Excel Example
This article http://support.microsoft.com/kb/141695 from Official Microsoft Excel Support states that "Microsoft Excel does not include a function for determining compound interest".
The Solution
tadPVIFA Excel function calculates loan principal for which one has to repay a periodic amount of $1 for n periods using either discrete or continuous compounding of interest. No VBA programming required. tadPVIFA Excel function is part of the TADXL Excel add-in for finance, that provides numerous financial functions that are not currently available in Excel. tadPVIFA accepts the following four values
- RATE
refers to the periodic interest rate - NPER
refers to the number of periods - TYPE
refers to either 0 for end of period deposits or to 1 for start of period deposits - Compounding
refers to either 0 for discrete compounding of interest or to 1 for continuous compounding of interest
| A | B | C | |
| 1 | RATE | 4% | 4% |
| 2 | NPER | 10 | 10 |
| 3 | TYPE | 0 | 0 |
| 3 | Compounding | 0 | 1 |
| 4 | TADXL | =tadPVIFA(B1, B2, B3, B4) | =tadPVIFA(C1, C2, C3, C4) |
| 5 | PVIFA | 8.11090 | 8.07826 |
You can try out the sample PVIFA calculation in Excel as shown in the previous example. If Excel reports the #NAME error when you enter the =tadPVIFA(B1, B2, B3, B4) function in cell B5, then you must install TADXL Add-in for finance to see the results of loan principal calculation using tadPVIFA function. TADXL is an Excel Add-in for finance, that contains many financial functions which are not provided by Excel. Go ahead and download TADXL now.
Related Interest factors Excel functions
Following is a list of related Excel functions that cover other interest factors used in financial management:PVIFA Calculator
You can opt to use this PVIFA Calculator that calculates not only PVIFA but also the interest rate and number of periods from present value annuity factor. The PVIFA calculator permits you to select from discrete and continuous compounding of interest.
PVIFA Table
Get yourself a PVIFA PDF download file that tabulates present value annuity factor of a $1 at interest rates ranging from 1% to 50% for number of periods ranging from 1 to 50. The PVIFA table comes in an easy to read format where alternating rows are highlighted in contrasting colors. A must have for any financial analyst, business student, and other accounting professionals.
PVIFA on ba ii plus
TI BA II plus is a popular financial calculator manufactured by Texas Instruments. PVIFA on BA II plus may be found using the time value of money function keys. This tutorial guides you on how to find PVIFA on BA II plus.