VBA PPMT Function (Syntax + Example)

The VBA PPMT function is listed under the financial category of VBA functions. When you use it in a VBA code, you can calculate the principal amount of payment of a load or an investment. In simple words, every loan and investment has an interest rate and PPMT helps you calculate the only principal amount i.e. without adding the interest (of the amount) part.

Syntax

PPmt(Rate, Per, Nper, Pv, [Fv], [Due])

Arguments

• Rate: The rate of interest for the period.
• Per: The number of the period for which you want to calculate the interest (It must be within 1 to Nper).
• Nper: The number of periods to pay the loan or investment.
• PV: The present value of investment or loan.
• [FV]: The future value of the loan/investment [This is an optional argument and if omitted VBA takes 0 by default].
• [Due]: Defines whether the payment is due at the start or the end of the period, use 0 for the end of the period and 1 for the start of the period [This is an optional argument and if omitted VBA takes the end of the period by default].

Example

To practically understand how to use the VBA PPMT function, you need to go through the below example where we have written a vba code by using it:

Sub example_PPMT()
Range("A9").Value = PPmt(0.08 / 12, 1, 5 * 12, 98000)
End Sub

In the above code, we have used the PPMT to calculate the principal part of the payment for the first month of the investment as we used 1 in the per (period) and in the result, it has returned -1333.75 in the result.

Notes

• If Per is ≤ 0 or > Nper then VBA will return a run-time error of 5.