The VBA PPmt Function
Description
For a loan or investment that is paid by constant periodic payments and has a constant interest rate, the VBA Ppmt function calculates the principal part of the payment, during a specific period.
The syntax of the function is:
PPmt( Rate, Per,
Nper, Pv,
[Fv], [Due] )
Where the function arguments are:
| Rate | - | The interest rate, per period. |
| Per | - | The period number for which you want to calculate the principal payment (must be an integer between 1 and Nper). |
| Nper | - | The number of periods over which the loan or investment is to be paid. |
| Pv | - | The present value of the loan / investment. |
| [Fv] | - |
An optional argument that specifies the future value of the loan / investment. If omitted, [Fv] uses the default value 0. |
| [Due] | - |
An optional argument that defines whether the payment is due at the start or the end of the period. The [Due] argument can have the value 0 or 1, meaning:
0 - the payment is due at the end of the period; If the [Due] argument is omitted, it uses the default value 0 (denoting payments due at the end of the period). |
Cash Flow Sign Convention:
Note that, in line with the general cash flow sign convention, cash outflows are represented by negative numbers and cash inflows are represented by positive numbers.This is seen in the example below.
VBA PPmt Function Example
In the following VBA code, the VBA PPmt function is used to calculate the principal parts of the payments during months 1 and 2 of a loan of $50,000 which is to be paid off in full over 5 years. Interest is charged at a rate of 5% per year and the loan payments are made at the end of each month.
|
' Calculate the principal payments during months 1 & 2, for a loan that is to be paid in full
' over 5 years. Interest is 5% per year and payments are made at the end of the month. Dim ppMth1 As Double Dim ppMth2 As Double
' Principal payment during month 1:
' Principal payment during month 2:ppMth1 = PPmt( 0.05/12, 1, 60, 50000 ) ' ppMth1 is calculated to be -735.228348867216. ppMth2 = PPmt( 0.05/12, 2, 60, 50000 ) ' ppMth2 is calculated to be -738.29180032083. |
The above VBA code calculates that:
- The principal payment during month 1 = $735.23;
- The principal payment during month 2 = $738.29.
Note that:
- As the payments are monthly, the annual interest rate of 5% is divided by 12 to calculate the monthly interest rate. Also, the number of periods is supplied as 60 months.
- The [Fv] argument is omitted from the PPmt function and so is set to the default value 0.
- The [Due] argument is also be omitted from the function and so is set to the default value 0 (denoting that payments are made at the end of each period).
- The results from the PPmt function are negative numbers, indicating that the calculated principal payments are outgoing.
VBA PPmt Function Error
If the VBA PPmt function is supplied with a Per that is ≤ 0 or > Nper, you will get the error:
Run-time error '5': Invalid procedure call or argument
