Excel: PPmt Function
In Excel, the PPmt function returns the payment on the principal for a particular payment based on an interest rate and a constant payment schedule.
The syntax for the PPmt function is:
PPmt( interest_rate, period, number_payments, PV, FV, Type )
interest_rate is the interest rate for the loan.
period is the period used to determine how much principal has been repaid. Period must be a value between 1 and number_payments.
number_payments is the number of payments for the loan.
PV is the present value or principal of the loan.
FV is optional. It is the future value or the loan amount outstanding after all payments have been made. If this parameter is omitted, the PPmt function assumes a FV value of 0.
Type is optional. It indicates when the payments are due. Type can be one of the following values:
Value Explanation 0 Payments are due at the end of the period. (default) 1 Payments are due at the beginning of the period.
If the Type parameter is omitted, the PPmt function assumes a Type value of 0.
Applies To:
- Excel 2007, Excel 2003, Excel XP, Excel 2000
For example:
Let's take a look at a few examples:
This first example returns the amount of principal paid off by the payment made in the 5th month of a $5,000 loan with monthly payments at an annual interest rate of 7.5%. The loan is to be paid off in 2 years (ie: 2 x 12). All payments are made at the beginning of the period.
=PPmt(7.5%/12, 5, 2*12, 5000, 0, 1)
This next example returns the amount of principal paid off by the payment made in the 20th week of a $8,000 loan with weekly payments at an annual interest rate of 6%. The loan is to be paid off in 4 years (ie: 4 x 52). All payments are made at the end of the period.
=PPmt(6%/52, 20, 4*52, 8000, 0, 0)
This next example returns the amount of principal paid off by the payment made in the 4th year of a $6,500 loan with annual payments at an annual interest rate of 5.25%. The loan is to be paid off in 10 years (ie: 10 x 1). All payments are made at the end of the period.
=PPmt(5.25%/1, 4, 10*1, 6500, 0, 0)
This final example returns the amount of principal paid off by the payment made in the 14th month of a $5,000 loan with annual payments at an annual interest rate of 8%. The loan is to be paid off in 3 years (ie: 3 x 12) with a remaining balance on the loan of $1,000 after the 3 years. All payments are made at the end of the period.
=PPmt(8%/12, 14, 3*12, 5000, 1000, 0)
VBA Code
The PPmt function can also be used in VBA code. For example:
Dim LValue As Currency
LValue = PPmt(0.08/12, 14, 3*12, 5000, 1000, 0)
In this example, the variable called LValue would now contain the value of ($161.37).