How to use PPmt function in VBA?

The PPmt function in VBA is used to calculate the principal part of a loan payment for a given period, assuming a constant interest rate and fixed periodic payments.

Syntax

VBA


PPmt(InterestRate, Per, NPer, PV, [FV], [Type])

Parameters

InterestRate (required): The interest rate for the loan for each period.
Per (required): The period for which you want to find the principal payment and must be in the range 1 to NPer.
NPer (required): Number of total periods in which the loan is to be paid off.
PV (required): The present value or total amount of the loan.
FV (optional): The future value or a cash balance you want to attain after the last payment is made (defaults to 0 if omitted).
Type (optional): When payments are due, Type is 0 or omitted for payments at the end of the period (standard), and 1 for payments at the beginning of the period.

Here’s an example of how you could use the PPmt function in VBA:

VBA


Sub CalculatePrincipalPayment()
    ' Variables declaration
    Dim loanInterestRate As Double
    Dim period As Integer
    Dim numberOfPeriods As Integer
    Dim presentValue As Double
    Dim principalPayment As Double
    
    ' Assign sample values
    loanInterestRate = 0.05 / 12 ' 5% annual interest rate (0.05) converted to monthly (divide by 12)
    period = 1 ' for example, we want to know the principal of the first payment
    numberOfPeriods = 60 ' loan is to be paid off over 5 years with monthly payments (5 * 12 months = 60 periods)
    presentValue = 100000 ' loan amount
   
    ' Using PPmt to calculate the principal for specific period
    principalPayment = PPmt(loanInterestRate, period, numberOfPeriods, presentValue)
    
    ' Print the principal payment to the Immediate window (Debug window in VBE)
    Debug.Print "The principal part of the payment for period " & period & " is " & principalPayment
End Sub

You can substitute your own values into the relevant variables to calculate the principal amount for any loan based on your own terms. Keep in mind that in order for this code to work, you should have macro settings configured to allow VBA to run. If it doesn’t work, check your macro security settings.

facebooklinkedin