# MS Excel: PMT Function (WS, VBA)

Learn how to use the Excel **PMT function** with syntax and examples.

## Description

The Microsoft Excel **PMT function** returns the payment amount for a loan based on an interest rate and a constant payment schedule.

## Syntax

The syntax for the Microsoft Excel **PMT function** is:

PMT( interest_rate, number_payments, PV, [FV], [Type] )

### Parameters or Arguments

*interest_rate* is the interest rate for the loan.

*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 PMT 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 PMT function assumes a *Type* value of 0.

## Applies To

The **PMT function** can be used in the following versions of Microsoft Excel:

- Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

## Type of Excel Function

The **PMT function** can be used in Microsoft Excel as the following type of function:

- Worksheet function (WS)
- VBA function (VBA)

## Example (as Worksheet Function)

Let's look at an example to how you would use the **PMT examples** and explore how you would use the **PMT function** as a worksheet function in Microsoft Excel:

This first example returns the monthly payment on a $5,000 loan at an annual rate of 7.5%. The loan is paid off in 2 years (ie: 2 x 12). All payments are made at the beginning of the period.

=PMT(7.5%/12, 2*12, 5000, 0, 1)

This next example returns the weekly payment on a $8,000 loan at an annual rate of 6%. The loan is paid off in 4 years (ie: 4 x 52). All payments are made at the end of the period.

=PMT(6%/52, 4*52, 8000, 0, 0)

This next example returns the annual payment on a $6,500 loan at an annual rate of 5.25%. The loan is paid off in 10 years (ie: 10 x 1). All payments are made at the end of the period.

=PMT(5.25%/1, 10*1, 6500, 0, 0)

This final example returns the monthly payment on a $5,000 loan at an annual rate of 8%. The loan is paid on for 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.

=PMT(8%/12, 3*12, 5000, -1000, 0)

## Example (as VBA Function)

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

Let's look at some Excel PMT function examples and explore how you would use the **PMT function** in Excel VBA code:

Dim LValue As Currency LValue = Pmt(0.08/12, 3*12, 5000, -1000, 0)

In this example, the variable called LValue would now contain the value of ($132.01)