ISMPT Excel Formula

Author Zaheer    Category Formulas     Tags ,

As an Excel Financial Function, ISPMT function allows you to compute the amount of interest that has been paid for a given time for a loan or an investment.

For this function, the syntax is ISPMT( rate, per, nper, pv ). The argument rate in the syntax is the interest fee for every period. The per in the syntax is the argument is the time for the interest will be calculated. This argument must be an integer that could be one up to the nper. The number of years or the time that the investment or loan will be paid is represented by the nper argument. Moreover, the value of the investments or the loans is represented by the pv argument.

Remember that in a general cash flow the payments which are outgoing are negative numbers. For the positive numbers this will be represented by positive numbers.

As an example, you want to compute the interest of a loan for $50,000 for its first and second month which will be fully paid within five years.  The interest of the loaned money is 5% annually.

In cell A2 input the data Mth 1: which means that this is for the first month. On the cell beside which is cell B2 input the formula =ISPMT(5%/12, 1, 60, 50000) and the result will be $204.86. For the second month input the data in cell A3 as Mth 2: and on the cell beside it input the formula = ISPMT(5%/12, 2, 60, 50000) the result will be $201.39. the results are the interest of the loaned amount of $50,000 for the first two months.

The most common error that occurs is the #VALUE! error. This error will occur if the given arguments are not numbers or recognized values by Excel. There will also be instance that the expected value is higher or lower that the value given by the Excel function. This could be caused by not converting the interest fees or the given time to months or quarters.

To solve this error, make sure that the nper argument and the rate argument is expressed in the right unit. The conversion of unit to months is through multiplying the number of years to 12. To convert the unit to quarter, multiply the number of years to 12. For monthly rates, divide the annual rate to 12 and for the quarterly rate divide the annual rate to 4.

Post comment