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

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

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

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

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

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

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

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

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)

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.

(scroll to see more)

- ABS (WS, VBA)
- ACCRINT (WS)
- ACCRINTM (WS)
- ACOS (WS)
- ACOSH (WS)
- ADDRESS (WS)
- AGGREGATE (WS)
- AMORDEGRC (WS)
- AMORLINC (WS)
- AND (WS)
- AND (VBA)
- AREAS (WS)
- ASC (VBA)
- ASIN (WS)
- ASINH (WS)
- ATAN (WS)
- ATAN2 (WS)
- ATANH (WS)
- ATN (VBA)
- AVEDEV (WS)
- AVERAGE (WS)
- AVERAGEA (WS)
- AVERAGEIF (WS)
- AVERAGEIFS (WS)
- BETA.DIST (WS)
- BETA.INV (WS)
- BETADIST (WS)
- BETAINV (WS)
- BIN2DEC (WS)
- BIN2HEX (WS)
- BIN2OCT (WS)
- BINOM.DIST (WS)
- BINOM.INV (WS)
- BINOMDIST (WS)
- CASE (VBA)
- CBOOL (VBA)
- CBYTE (VBA)
- CCUR (VBA)
- CDATE (VBA)
- CDBL (VBA)
- CDEC (VBA)
- CEILING (WS)
- CEILING.PRECISE (WS)
- CELL (WS)
- CHAR (WS)
- CHDIR (VBA)
- CHDRIVE (VBA)
- CHIDIST (WS)
- CHIINV (WS)
- CHITEST (WS)
- CHOOSE (WS, VBA)
- CHR (VBA)
- CINT (VBA)
- CLEAN (WS)
- CLNG (VBA)
- CODE (WS)
- COLUMN (WS)
- COLUMNS (WS)
- COMBIN (WS)
- COMBINA (WS)
- COMPLEX (WS)
- Concat with & (WS, VBA)
- CONCATENATE (WS)
- CONVERT (WS)
- COS (WS, VBA)
- COSH (WS)
- COUNT (WS)
- COUNTA (WS)
- COUNTBLANK (WS)
- COUNTIF (WS)
- COUNTIFS (WS)
- COVAR (WS)
- CSNG (VBA)
- CSTR (VBA)
- CURDIR (VBA)
- CVAR (VBA)
- DATE (VBA)
- DATE (WS)
- DATEADD (VBA)
- DATEDIF (WS)
- DATEDIFF (VBA)
- DATEPART (VBA)
- DATESERIAL (VBA)
- DATEVALUE (WS, VBA)
- DAVERAGE (WS)
- DAY (WS, VBA)
- DAYS360 (WS)
- DB (WS)
- DCOUNT (WS)
- DCOUNTA (WS)
- DDB (WS, VBA)
- DEGREES (WS)
- DGET (WS)
- DIR (VBA)
- DMAX (WS)
- DMIN (WS)
- DOLLAR (WS)
- DPRODUCT (WS)
- DSTDEV (WS)
- DSTDEVP (WS)
- DSUM (WS)
- DVAR (WS)
- DVARP (WS)
- ERROR.TYPE (WS)
- EVEN (WS)
- EXACT (WS)
- EXP (WS, VBA)
- FACT (WS)
- FALSE (WS)
- FILEDATETIME (VBA)
- FILELEN (VBA)
- FIND (WS)
- FIX (VBA)
- FIXED (WS)
- FLOOR (WS)
- FORECAST (WS)
- FORMAT Dates (VBA)
- FORMAT Numbers (VBA)
- FORMAT Strings (VBA)
- FREQUENCY (WS)
- FV (WS, VBA)
- GETATTR (VBA)
- GROWTH (WS)
- HLOOKUP (WS)
- HOUR (WS, VBA)
- HYPERLINK (WS)
- IF (WS)
- IF-THEN-ELSE (VBA)
- IFs (more than 7) (WS)
- IFs (up to 7) (WS)
- INDEX (WS)
- INDIRECT (WS)
- INFO (WS)
- INSTR (VBA)
- INSTRREV (VBA)
- INT (WS, VBA)
- INTERCEPT (WS)
- IPMT (WS, VBA)
- IRR (WS, VBA)
- ISBLANK (WS)
- ISDATE (VBA)
- ISERR (WS)
- ISERROR (WS, VBA)
- ISLOGICAL (WS)
- ISNA (WS)
- ISNONTEXT (WS)
- ISNULL (VBA)
- ISNUMBER (WS)
- ISNUMERIC (VBA)
- ISPMT (WS)
- ISREF (WS)
- ISTEXT (WS)
- LARGE (WS)
- LCASE (VBA)
- LEFT (WS, VBA)
- LEN (WS, VBA)
- LINEST (WS)
- LN (WS)
- LOG (WS, VBA)
- LOG10 (WS)
- LOOKUP (WS)
- LOWER (WS)
- LTRIM (VBA)
- MATCH (WS)
- MAX (WS)
- MAXA (WS)
- MDETERM (WS)
- MEDIAN (WS)
- MID (WS, VBA)
- MIN (WS)
- MINA (WS)
- MINUTE (WS, VBA)
- MINVERSE (WS)
- MIRR (WS, VBA)
- MKDIR (VBA)
- MMULT (WS)
- MOD (WS)
- MONTH (WS, VBA)
- MONTHNAME (VBA)
- N (WS)
- NA (WS)
- NETWORKDAYS (WS)
- NETWORKDAYS.INTL (WS)
- NOT (WS)
- NOW (WS, VBA)
- NPER (WS, VBA)
- NPV (WS, VBA)
- ODD (WS)
- OFFSET (WS)
- OR (WS)
- OR (VBA)
- PERCENTILE (WS)
- PERCENTRANK (WS)
- PERMUT (WS)
- PI (WS)
- PMT (WS, VBA)
- POWER (WS)
- PPMT (WS, VBA)
- PRODUCT (WS)
- PROPER (WS)
- PV (WS, VBA)
- QUARTILE (WS)
- RADIANS (WS)
- RAND (WS)
- RANDBETWEEN (WS)
- RANK (WS)
- RATE (WS, VBA)
- REPLACE (WS)
- REPLACE (VBA)
- REPT (WS)
- RIGHT (WS, VBA)
- RND (VBA)
- ROMAN (WS)
- ROUND (WS)
- ROUND (VBA)
- ROUNDDOWN (WS)
- ROUNDUP (WS)
- ROW (WS)
- ROWS (WS)
- RTRIM (VBA)
- SEARCH (WS)
- SECOND (WS)
- SETATTR (VBA)
- SGN (VBA)
- SIGN (WS)
- SIN (WS, VBA)
- SINH (WS)
- SLN (WS, VBA)
- SLOPE (WS)
- SMALL (WS)
- SPACE (VBA)
- SQRT (WS)
- STDEV (WS)
- STDEVA (WS)
- STDEVP (WS)
- STDEVPA (WS)
- STR (VBA)
- STRCOMP (VBA)
- STRCONV (VBA)
- SUBSTITUTE (WS)
- SUBTOTAL (WS)
- SUM (WS)
- SUMIF (WS)
- SUMIFS (WS)
- SUMPRODUCT (WS)
- SUMSQ (WS)
- SUMX2MY2 (WS)
- SUMX2PY2 (WS)
- SUMXMY2 (WS)
- SWITCH (VBA)
- SYD (WS, VBA)
- T (WS)
- TAN (WS, VBA)
- TANH (WS)
- TEXT (WS)
- TIME (WS)
- TIMESERIAL (VBA)
- TIMEVALUE (WS, VBA)
- TODAY (WS)
- TRANSPOSE (WS)
- TRIM (WS, VBA)
- TRUE (WS)
- TRUNC (WS)
- TYPE (WS)
- UCASE (VBA)
- UPPER (WS)
- VAL (VBA)
- VALUE (WS)
- VAR (WS)
- VARA (WS)
- VARP (WS)
- VARPA (WS)
- VDB (WS)
- VLOOKUP (WS)
- WEEKDAY (WS, VBA)
- WEEKDAYNAME (VBA)
- YEAR (WS, VBA)
- --- end ---

While using this site, you agree to have read and accepted our Terms of Service and Privacy Policy.

Copyright © 2003-2014 TechOnTheNet.com. All rights reserved.