# MS Excel: IPMT Function (WS, VBA)

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

## Description

The Microsoft Excel **IPMT function** returns the interest payment for an investment based on an interest rate and a constant payment schedule.

## Syntax

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

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

### Parameters or Arguments

*interest_rate* is the interest rate for the investment.

*period* is the period to calculate the interest rate. It must be a value between 1 and *number_payments*.

*number_payments* is the number of payments for the annuity.

*PV* is the present value of the payments.

*FV* is optional. It is the future value that you'd like the investment to be after all payments have been made. If this parameter is omitted, the **IPMT function** will assume a *FV* 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 IPMT function assumes a *Type* value of 0.

## Applies To

The **IPMT 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 **IPMT 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 **IPMT examples** and explore how to use the **IPMT function** as a worksheet function in Microsoft Excel:

This first example returns the interest payment for a $5,000 investment that earns 7.5% annually for 2 years. The interest payment is calculated for the 8th month and payments are due at the end of each month.

=IPMT(7.5%/12, 8, 2*12, 5000)

This next example returns the interest payment for a $8,000 investment that earns 6% annually for 4 years. The interest payment is calculated for the 30th week and payments are due at the beginning of each week.

=IPMT(6%/52, 30, 4*52, 8000, 0 ,1)

This next example returns the interest payment for a $6,500 investment that earns 5.25% annually for 10 years. The interest payment is calculated for the 4th year and payments are due at the end of each year.

=IPMT(5.25%/1, 4, 10*1, 6500)

## Example (as VBA Function)

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

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

Dim LNumber As Currency LNumber = IPmt(0.0525/1, 4, 10*1, 6500)

In this example, the variable called LNumber would now contain the value of -256.504747.