totn Excel Functions

MS Excel: How to use the PPMT Function (WS, VBA)

This Excel tutorial explains how to use the Excel PPMT function with syntax and examples.

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.

The PPMT function is a built-in function in Excel that is categorized as a Financial Function. It can be used as a worksheet function (WS) and a VBA function (VBA) in Excel. As a worksheet function, the PPMT function can be entered as part of a formula in a cell of a worksheet. As a VBA function, you can use this function in macro code that is entered through the Microsoft Visual Basic Editor.

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.

Returns

The PPMT function returns a numeric value.

Applies To

  • Excel for Office 365, Excel 2019, 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).