Documentation/Calc Functions/ISPMT

    From The Document Foundation Wiki
    Other languages:

    Function name:

    ISPMT

    Category:

    Financial Analysis

    Summary:

    Calculates the interest due for a specific period of a fixed-rate investment or loan, assuming that the same amount of capital is repaid at each period. The interest due in any period depends on the outstanding balance at that time and will decline through the term.

    Syntax:

    ISPMT(Rate; Period; Total periods; Investment)

    Returns:

    Returns a real number, which is the value of the interest due at the specified period for the given investment or loan. The returned value is negative for payments and positive for deposits. The number returned is in the same currency unit as the specified investment value.

    Arguments:

    Rate is a real number (expressed as a percentage, such as 2.5%, or a decimal fraction, such as 0.025), or a reference to a cell containing that number, which is the periodic interest rate. It is common for interest rates to be quoted annually and, where necessary, care should be taken to convert such values to be compatible with the length of your payment period.

    Period is a real number, or a reference to a cell containing that number, which is the specific period for which the interest is calculated.

    Total periods is a non-zero real number, or a reference to a cell containing that number, which is the number of payment periods in the term.

    Investment is a real number, or a reference to a cell containing that number, which is the present value of the amount borrowed or invested.

    • If any argument is non-numeric, then ISPMT reports a #VALUE! error.
    • If Total periods is equal to 0.0, then ISPMT returns a #NUM! error.

    Additional details:

    • ISPMT calculates the interest payment for any period using the formula:
    [math]\displaystyle{ \text{ISPMT}~=~Investment ~ \times ~ Rate ~ \times ~ \left( \frac{Period}{Total~periods} ~-~ 1\right) }[/math]
    • A consequence of using the above formula is that care must be taken with the value passed to ISPMT in the Period argument.
    1. If the repayment schedule requires the interest payment for the first period to be based on the full value of the Investment argument (and thus be equal to [math]\displaystyle{ -Investment \times Rate }[/math]), then the Period argument should be set to 0 for the first period. In other words, for this case the expected values of Period lie in the range 0 ≤ PeriodTotal periods - 1.
    2. If a different repayment schedule applies, such that the first capital repayment is made before the interest payment for the first payment is calculated, then the Period argument should be set to 1 for the first period. In other words, for this case the expected values of Period lie in the range 1 ≤ PeriodTotal periods.
    • For an investment of this type, the constant periodic principal payment is [math]\displaystyle{ \frac {Investment}{Total~periods} }[/math] currency units.
    • Note that ISPMT assumes constant periodic principal amounts but variable periodic interest amounts. This is different to the IPMT function, where the total amount due each period (interest plus capital) is constant.

    Examples:

    Worked example for complete repayment schedule

    John decides to borrow $8,000 over a period of 12 months at an annual interest rate of 4%. There is no cash balance remaining at the end of the loan. The loan is structured so that monthly interest payments are calculated based on the capital balance outstanding at the end of the previous month. The following table shows the schedule for his repayments.

    A B C D
    1 Loan amount $8,000
    2 Annual rate 4%
    3
    4 Month Previous
    Balance
    Principal
    Payment
    ISPMT
    5 1 $8,000.00 -$666.67 −26.67
    6 2 $7,333.33 -$666.67 −24.44
    7 3 $6,666.67 -$666.67 −22.22
    8 4 $6,000.00 -$666.67 −20.00
    9 5 $5,333.33 -$666.67 −17.78
    10 6 $4,666.67 -$666.67 −15.56
    11 7 $4,000.00 -$666.67 −13.33
    12 8 $3,333.33 -$666.67 −11.11
    13 9 $2,666.67 -$666.67 −8.89
    14 10 $2,000.00 -$666.67 −6.67
    15 11 $1,333.33 -$666.67 −4.44
    16 12 $666.67 -$666.67 −2.22
    17
    18 SUM -$8,000.00 −173.33

    Cells B5:D16 contain formulas as follows:

    • Cell B5 contains the formula =$B$1.
    • Cell B6 contains the formula =$B5+$C6; cell B7 contains the formula =$B6+$C7; and so on for cells B8:B16.
    • Cells C5:C16 all contain the formula =-$B$1/$A$16.
    • Cell D5 contains the formula =ISPMT($B$2/12; $A5-1; $A$16; $B$1); cell D6 contains the formula =ISPMT($B$2/12; $A6-1; $A$16; $B$1); and so on for cells D7:D16.

    Significant features of the data in the above table include:

    • In all calls to the ISPMT function, the first argument is $B$2/12. The division by 12 converts the annual interest rate to a monthly equivalent to match the period length.
    • In all calls to the ISPMT function, the second argument is passed as the actual period number minus 1. This approach ensures that the interest in the first (and subsequent) periods is based on the capital balance at the end of the previous period.
    • Note that the inclusion of the dollar ($) prefix in cell B1 leads to the values in cells B5:C16 also being displayed as currency values. However, the outputs from the ISPMT function displayed in cells D5:D16 are not displayed as currency values by default.
    • The value −173.33 in cell D18 is calculated as the sum of the values in cells D5:D16 and therefore represents the total interest paid during the loan term.

    Other examples

    Formula Description Returns
    =ISPMT(0.01; 1; 24; 120000)

    Calculate the interest due in the first month for a loan of 120,000 currency units with a two-year term, monthly repayment periods, constant capital repayments each month, and an annual interest rate of 12%. For this example, it is assumed that the first month's interest payment is calculated after the first capital payment ($5,000) has been made. The function returns a value of −1,150 currency units.

    −1150
    =ISPMT(0.01; 18; 24; 120000)

    Calculate the interest due in the eighteenth month for a loan of 120,000 currency units with a two-year term, monthly repayment periods, constant capital repayments each month, and an annual interest rate of 12%. For this example, it is assumed that a month's interest payment is calculated after the capital payment for that month ($5,000) has been made. The function returns a value of −300 currency units.

    −300
    =ISPMT(0.01; 24; 24; 120000)

    Calculate the interest due in the final month for a loan of 120,000 currency units with a two-year term, monthly repayment periods, constant capital repayments each month, and an annual interest rate of 12%. For this example, it is assumed that a month's interest payment is calculated after the capital payment for that month ($5,000) has been made. The function returns a value of 0 currency units, which is expected because the capital repayment in this final month reduces the balance to 0.

    0

    Related LibreOffice functions:

    FV

    NPER

    PMT

    PV

    RATE

    ODF standard:

    Section 6.12.25, part 2

    Related (or similar) Excel functions:

    ISPMT