The VBA Pmt Function
Description
The VBA Pmt function calculates the constant periodic payments required to pay a loan or investment that has a fixed interest rate.
The syntax of the function is:
Pmt( Rate, Nper,
Pv, [Fv],
[Due] )
Where the function arguments are:
| Rate | - | The interest rate, per period. |
| 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 Pmt Function Example
In the following VBA code, the VBA Pmt function is used to calculate the monthly payments on a loan of $50,000 which is to be paid off in full after 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 monthly payments to a loan that is to be paid in full over 5 years.
Dim mthlyPmt As Double' Interest is 5% per year and payments are made at the end of the month. mthlyPmt = Pmt( 0.05/12, 60, 50000 ) ' mthlyPmt is calculated to be -943.56168220055. |
The above VBA code calculates that a monthly payment of $943.56 is required, to pay off the loan in full over 5 years.
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 during the 5 years of the annuity is supplied as 60 months.
- In the example, the future value of the annuity is 0, and so the [Fv] argument can be omitted from the Pmt function.
- In the example, the payments are due at the end of each period. Therefore, the [Due] argument can also be omitted from the Pmt function.
- The result from the Pmt function is a negative number, indicating that the calculated payments are outgoing.
VBA Pmt Function Error
If the supplied Nper is equal to 0, the VBA Pmt function produces the error:
Run-time error '5': Invalid procedure call or argument
