A to Z of Excel Functions: The ISPMT Function

Welcome back to our regular A to Z of Excel Functions blog. Today we look at the ISPMT function.

The ISPMT function

This function calculates the interest paid (or received) for the specified period of a loan (or an investment) for a constant instant rate with equal principal repayments.  In reality, this is quite an easy financial instrument to calculate using basic formulae, but the ISPMT function makes it slightly simpler than computing from first principles.

The ISPMT function employs the following syntax to operate:

The ISPMT function has the following arguments:

• rate: this is required and represents the constant interest rate for the loan or investment
• per: this is required, and specified the period to be considered, between periods 1 and nper
• nper: this is also required and denotes the total number of payments for the loan or investment
• pv: also necessary, this is the present value, or the total amount that a series of future payments is worth now, also known as the principal (i.e. what you are borrowing).

It should be further noted that:

• the payment returned by PPMT relates to the principal but considers no taxes, reserve payments or other fees sometimes associated with loans
• make sure that you are consistent about the units you use for specifying rate and nper. If you make monthly payments on a four-year loan at an annual interest rate of 12%, use 12%/12 for rate and 4*12 for nper. If you make annual payments on the same loan, use 12% for the rate and 4 for nper
• ISPMT counts each period beginning with zero (0), not with one (1)
• most loans use a repayment schedule with even periodic payments. The IPMT function returns the interest payment for a given period for this type of loan
• some loans use a repayment schedule with even principal payments. The ISPMT function returns the interest payment for a given period for this type of loan
• this is one of Excel’s financial functions which distinguishes between cash inflows (positive) and outflows (negative).

Please see my example below:

We’ll continue our A to Z of Excel Functions soon. Keep checking back – there’s a new blog post every other business day.

A full page of the function articles can be found here.