Contents
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.
Unlock Your Potential
Excel
Access
Power BI
