ISPMT
The ISPMT function returns the periodic interest portion for a fixed-rate loan where an equal principal reduction occurs at the beginning of each period and an interest payment on the outstanding balance is made at the end of each period. This function is provided primarily for compatibility with tables imported from other spreadsheet apps. All arguments are number values.
ISPMT(periodic-rate, period, num-periods, present-value)
periodic-rate: The interest rate per period. periodic-rate is entered as a decimal (for example, 0.08) or with a percent sign (for example, 8%). periodic-rate is specified using the same time frame (for example, monthly, quarterly, or annually) as num-periods. For example, if num-periods represents months and the annual interest rate is 8%, periodic-rate is specified as 0.00667 or 0.667% (0.08 divided by 12). periodic-rate can be negative, but the result returned by the function may be difficult to interpret.
period: The payment period for which you want to calculate the amount of interest. period must be greater than 0.
num-periods: The number of periods. num-periods is specified using the same time frame (for example, monthly, quarterly or annually) as periodic-rate. num-periods must be greater than or equal to 0.
present-value: The initial investment, or the amount of the loan or annuity. present-value is often formatted as currency. At time 0, an amount received is a positive amount and an amount invested is a negative amount. For example, it could be an amount borrowed (positive) or the initial payment made on an annuity contract (negative).
Notes
The amortisation of loan principal is the same each period, while interest will decline over time. For example, if the initial principal balance was $5,000 and the loan term was 5 annual periods, the principal payment at the start of each period would be $1,000, including at the start of the first period. The interest paid at the end of each period would decline. This is different from a standard, fully amortising loan where the combined principal and interest payment is constant over time.
The currency shown in this function result depends on your Language and Region settings (in System Preferences in macOS and in Settings in iOS and iPadOS), or on your Time Zone and Region settings in iCloud Settings.
Example |
|---|
A hypothetical fully amortising loan of $200,000 (present-value is positive because it is a cash inflow) has an annual interest rate of 6% payable monthly (periodic-rate is 0.06/12), and a term of 10 years (num-periods is 10*12). =ISPMT(0.06/12, 25, 10*12, 200000) returns -791.666666666667 (negative because it is a cash outflow), the interest payment required at the end of the first month of the third year (payment 25). |