ISPMT Function
Useful for financial analysts as it helps forecast interest payments while preparing revenue forecasts, annual budgets, etc.
What is the ISPMT Function?
Excel is widely used by everyone in every field. In finance, we generally use Excel to calculate investments, financial models, and so on. So, here we discuss the “ISPMT” Function of Excel.
The ISPMT function is grouped in Excel Financial Functions. This function will calculate the interest paid during a specific investment period. ISPMT is useful for financial analysts as it helps forecast interest payments while preparing revenue forecasts, annual budgets, etc.
For instance, if a company wants to take out a loan, they need to know how much interest will be paid over a definite period. By using the ISPMT function, we can know the interest due in the future.
Ensure that the units used to specify Rate and Nper are consistent. If you pay 12% monthly on a four-year loan, use 12/12 for the Rate and 4 * 12 for Nper. If you make annual payments on the same loan, use an interest rate of 12% and a Nper of 4.
For all parameters, the cash you pay, such as deposits transferred to savings or other withdrawals, is represented by a negative number; cash you receive, such as dividend checks and other deposits, is represented by a positive number.
Understanding the ISPMT Function
ISPMT counts each cycle from zero instead of one. Most loans use a repayment plan with regular payments. This function returns the interest payments for this type of loan for a given period.
Some loans use a repayment plan with equal principal repayments. The ISPMT function returns the interest payments for this type of loan for a given period.
The amortization schedule below uses an equal principal repayment schedule for illustrative purposes. The interest charge for each period equals the interest rate multiplied by the outstanding balance from the previous period.
Each installment's repayment amount equals the principal plus interest of the current installment.
Features of the function:
- To compute the interest paid over a specific period invested.
- It contains the loan amount, the interest rate, the number of payments, and the payment frequency.
- The data set is evenly distributed.
- Low degree of correlation among the data.
How to use the ISPMT function
In Excel, every function has its specialty to calculate. We need interest rates, the number of periods, and interest values to calculate the result using this function. The syntax for the function is:
Where
- Rate (mandatory parameter) - It is the interest rate for the investment or loan.
- Per (mandatory parameter) - This is the period for which we want to calculate the interest. It must be an integer between 1 and nper.
- Nper (mandatory parameter) – Number of periods over which the loan or investment must be repaid.
- Pv (mandatory parameter) - This is the current value of the loan/investment. This may be the total value paid so far.
Example 1
With the help of the example, we can easily understand the concept.
Let’s have a company take out a 50 million-dollar loan. It must be repaid in 10 years, and the interest is charged at 8% per year. The interest needs to be paid monthly. What is the amount to be paid in 12 months?
Payments are made monthly, so we need to convert the annual interest rate of 7% to a monthly interest rate (= 8%/12) and the number of periods from years to months (=10*12).
So the next step is how to calculate the amount. Firstly, we need to convert the interest rate and period in years into months.
In the second step, write the formula of ISPMT, i.e.,
=ISPMT(rate,per,nper,pv)
In the third step, put it according to the formula syntax, and select a monthly rate of 0.7%.
Then select the number of months according to the question. We need to calculate the 12-month amount, so write 12. Then select years in months, that is, 120.
After then, select the loan amount that is 5,000,000. The installment is 30,000. It is shown in a negative sign due to cash outflow.
The above image illustration clearly explains the ispmt function in Excel.
Example 2
Various annual interest rates are given with the same loan amount and period.
| Annual Interest Rate | Period (years) | Invested months | Loan value |
|---|---|---|---|
| 8% | 5 | 48 | 50000 |
| 9% | 5 | 60 | 50000 |
| 10% | 5 | 70 | 50000 |
| 11% | 5 | 80 | 50000 |
To compute the installment, we need to put the ispmt formula to evaluate it quickly. Let’s show the formula in the next image.
We also need to be cautious of the common errors while using the function. If you get an error from the Excel Ispmt function, it's probably #VALUE! Error: General Error #VALUE! - Occurs if any of the supplied arguments are non-numeric.
Some users have also experienced the following issues:
- Excel function's results are much higher or lower than expected. Possible cause Many users forget to convert the interest rate or period to months or quarters when calculating monthly or quarterly payments.
- Fix this by ensuring the rate and nper parameters are in the correct units. That is: months = 12 * years, quarters = 4 * years, monthly rate = annual rate / 12,quarterly rate = annual rate / 4.
Advantages & Disadvantages of ISPMT Function
Every Excel function has pros and cons. Since every coin has two sides, the financial Excel functions have various advantages and disadvantages, as given below.
Some of the advantages are
1. Save time:
It can help you save time by directly computing the installments using the formula. For example, if you want to compute the 3 installments, you directly apply the formula with the given interest, then the month number 3 after that number of years, and then the loan amount.
2. Save money:
It can help save money by computing the interest paid on an investment over a certain period.
3. Estimation:
It can help people to estimate the number of upcoming installments.
4. Time period:
To calculate the interest paid during a definite investment time.
5. Forecasting:
It helps forecast interest payments while preparing revenue forecasts, annual budgets, etc. If a company wants to take out a loan, we need to know how much interest would be paid in a specific period.
6. Financial analysts:
It is widely used by financial analysts to forecast future payments.
On the other hand, the disadvantages are
1. Inaccurate:
It may not be accurate if the interest rate or the number of periods changes over time.
2. Fluctuation in interest:
In this case, the payment amount is never constant. This is why payment equals the sum of principal and interest, where interest is not constant for each period. Instead, it fluctuates over a period of time.
3. Assumptions:
It assumes that payments are being made at last.
4. Missing values:
When missing values are in the dataset, do not use the ISPMT function.
Similar functions of ISPMT
There are so many Excel formulas that are similar to determine the values.
ISPMT is an excel financial formula that calculates the number of payment periods for a loan or investment. It is similar to the PMT function but can also calculate payments on profitable investments.
The PMT function can only calculate payments on loans that have been amortized. Here are some formulas similar to ISPMT
- IPMT: This function calculates the interest payment on an amortized loan.
- PPMT: This function calculates the principal repayment of an amortized loan.
- PMT: This function returns a loan's payment amount given an interest rate and a fixed payment schedule.
IPMT
IPMT is so much similar to the ISPMT function. IPMT returns interest paid over a period of time for investments based on periodic, fixed, and fixed-rate payments.
Where
- Rate - It is the interest rate per period, which is required.
- Per - It is the interval for which you want to find the interest and must be in the range 1 to nper.
- Nper - It is the total number of payment periods in an annuity.
- Pv - The present value or the lump-sum money is a series of mandatory future payments.
- Fv - It is the future value or cash balance you want to achieve after your last payment. If the future value is omitted, it is assumed to be 0 (for example, a loan has a future value of 0).
PPMT
Suppose you want to know exactly how much of your monthly payment is principal; you can use the PPMT function in Excel. The function will calculate the principal repayment for any payment from the first to the last loan.
Where
- Rate: the interest rate per period.
- Per: the payment period you want to focus on, such as month two.
- Nper: the total number of payments.
- Pv: the present value for a loan is how much you originally borrowed.
- Fv: the future value you want to remain after the last payment. It defaults to zero. It is an optional value.
- Type: indicates if payments are due at each period's start (1) or end (0). It defaults to zero. It is an optional value.
PMT
In Excel, the PMT function returns a loan's payment amount given an interest rate and a fixed payment schedule.
Where
- Rate: the interest rate per period.
- Per: the payment period you want to focus on, such as month two.
- Nper: the total number of payments.
- Pv: the present value for a loan is how much you originally borrowed.
- Fv: the future value you want to remain after the last payment. It defaults to zero. It is an optional value.
- Type: indicates if payments are due at each period's start (1) or end (0). It defaults to zero. It is an optional value
or Want to Sign up with your social account?