How to use the PPMT Function (WS,VBA)

Description

The Microsoft Excel PPMT function returns the payment on the principal for a particular payment based on an interest rate and a constant payment schedule.

Syntax

The syntax for the PPMT function in Microsoft Excel is:

PPMT( interest_rate, period, number_payments, PV, [FV], [Type] )

Parameters or Arguments

interest_rate

The interest rate for the loan.

period

The period used to determine how much principal has been repaid. Period must be a value between 1 and number_payments.

number_payments

The number of payments for the loan.

PV

The present value or principal of the loan.

FV

Optional. It is the future value or the loan amount outstanding after all payments have been made. If this parameter is omitted, it assumes a FV value of 0.

Type

Optional. It indicates when the payments are due. If the Type parameter is omitted, it assumes a Type value of 0. 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.

Applies To

• Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Type of Function

• Worksheet function (WS)
• VBA function (VBA)

Example (as Worksheet Function)

Let's look at some PPMT examples and explore how to use the PPMT function as a worksheet function in Microsoft Excel:

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)
Result: -$197.40

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)
Result: -$34.81

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)
Result: -$595.53

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)
Result: -$161.37

Example (as VBA Function)

The PPMT function can also be used in VBA code in Microsoft Excel.

Let's look at some Excel PPMT function examples and explore how to use the PPMT function in Excel VBA code:

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).