The ISPMT function calculates the interest portion of a loan payment for a specific period based on a fixed interest rate.


=ISPMT(Rate, Per, Nper, PV)

Rate The fixed interest rate per period.
Per The period for which you want to calculate the interest payment.
Nper The total number of payment periods in the loan.
PV The present value or the loan amount.
FV (Optional) The future value or cash balance of the loan after the last payment. If omitted it is assumed to be 0.
Type (Optional) Specifies whether payments are due at the beginning or end of the period. 0 for end of the period (default) 1 for beginning of the period.


When dealing with loans and seeking to discern the specific interest component of a payment for a given period, turn to ISPMT in Excel. This function proves invaluable in financial assessments, aiding in the calculation of interest payments on loans with fixed interest rates over multiple periods. It finds widespread utility in scenarios where precise interest tracking or forecasting is paramount, such as in loan repayment schedules and financial planning endeavors. The ISPMT function operates effortlessly to deliver calculated interest figures with ease and accuracy, facilitating informed decision-making and financial analyses. Its intuitive structure allows for seamless integration within Excel workbooks, serving as a dependable resource for navigating the realms of loan interest computation.


Suppose you have taken out a loan of $50,000 with an annual interest rate of 6%. You wish to determine the interest payment for the 6th month of the loan. The ISPMT formula would be: =ISPMT(0.06/12, 6, 12, 50000) This will provide you with the interest component of the 6th month's loan payment.

Imagine you have borrowed $30,000 with a monthly interest rate of 1.5%, and you want to compute the interest payment for the 4th month in a 36-month loan term. The ISPMT formula to achieve this calculation would be: =ISPMT(0.015, 4, 36, 30000) This calculation will furnish you with the interest amount due in the 4th month of your loan tenure.


What does the ISPMT function primarily calculate?

The primary purpose of the ISPMT function is to compute the interest portion of a loan payment for a specific period based on a fixed interest rate.

Can the ISPMT function account for different payment timing scenarios?

Yes, the ISPMT function offers flexibility by allowing you to specify the payment due timing using the optional Type argument. By adjusting this parameter, you can calculate interest payments for scenarios where payments are due either at the beginning or end of the period.

What precaution should be taken when using the ISPMT function?

It's essential to ensure that the interest rate provided is in line with the payment period frequency and that the period and the total number of payment periods correspond to the loan payment schedule. Moreover, consider any optional arguments, like future value, to tailor the calculation accurately to the loan's specific terms.

Related functions