How to use the ISPMT function
What is the ISPMT function?
The ISPMT function calculates the interest paid during a specific period of an investment.
Table of Contents
1. Introduction
What is the difference between the IPMT function and the ISPMT function?
The IPMT function lets you also specify a future value [fv] and when the payments are due [type] which is not the case with the ISPMT function.
ISPMT(rate, per, nper, pv)
IPMT(rate, per, nper, pv, [fv], [type])
IPMT function is commonly used for loans where the repayment schedule involves even periodic payments.
The ISPMT function calculates the interest payment for a given period in an investment or loan with even principal payments.
What is the difference between straight line amortization loan (linear loan) and an annuity loan?
A linear loan has equal principal payments, the interest decreases over time as the remaining principal decreases. In other words, the payment made at regular intervals varies over time based on how much is left on the loan.
An annuity loan has equal payments made at regular intervals. The principal and the interest varies over time, however the payment is fixed across the life of the loan.
The key difference lies in how they allocate those payments between interest and principal. The ISPMT function calculates the interest based on a linear loan, whereas the IPMT function calculates the interest based on an annuity loan.
What is interest payment?
An interest payment is the amount of interest owed or earned on a loan or investment for a specific payment period. The ISPMT function calculates the interest portion of a periodic payment on an investment or loan. Interest payments decline over time as the principal is paid off.
What is an investment?
An investment is the purchase of an asset with the expectation of generating income or appreciation in the future. For example, stocks, bonds, real estate, commodities, art etc.
What are periodic constant payments?
Periodic constant payments are payments that are made at regular intervals such as monthly, quarterly, or yearly and have the same amount each time.
What is a constant interest rate?
A fixed interest rate is an interest rate that remains the same throughout the term of a loan or an investment.
What is the principal?
The principal is the amount you have to pay back and interest is the charges you pay for borrowing the money.
2. Syntax
ISPMT(rate, per, nper, pv)
| rate | Required. The interest rate. 10% = 0.1 |
| per | Required. The period for which you want to find the interest, and must be between 1 and nper. |
| nper | Required. The total number of payment periods. |
| pv | Required. The present value. For a loan, pv is the loan amount. |
Make sure you use the same unit across all arguments.
Negative numbers is cash you pay and positive numbers is cash you receive.
3. Example 1
A business takes out a $100,000 linear loan. It needs to be repaid in 5 years, and the interest is 5% per year. What is the interest payment for the 2nd year of the loan?
The arguments for the ISPMT function are :
- Rate: This is the interest rate per period. In the example, it is set to 5%.
- Per: This is the period for which the interest payment is calculated. In the example, it is set to 1 which corresponds to the 2nd year. 0 (zero) represents the first year.
- Nper: This is the total number of periods for the loan or investment. In the example, it is set to 5 years.
- Pv: This is the present value or the initial loan amount. In the example, it is set to $100,000
Formula in cell B3:
With these inputs, the ISPMT function calculates the interest payment for the 2nd period as $4,000.00 The 1st period is 0 (zero), that is why the second period is number 1.
The chart plots the ISPMT values (interest payment) as well as the remaining loan balance over these 5 periods. The chart shows the initial loan balance of $100,000. In the first period, the interest payment (ISPMT) is $5,000, and the remaining balance is $80,000 after the $25,000 payment is made. The principal is 100,000/5 = 20,000. The interest portion decreases over time as the loan balance reduces with each payment period.
The table below, in the image above, describes the payment amounts, interest portions (ISPMT), principal portions, and remaining balance for each of the 5 periods. It illustrates how the $100,000 loan is paid off over these periods with the interest amount gradually declining as the principal is paid down.
4. Example 2
You are investing 50,000 in a project with quarterly interest payments. The annual interest rate is 8% and the loan needs to be repaid in 3 years. Calculate the interest payment for the second quarter using the ISPMT function?
The arguments for the ISPMT function are :
- Rate: This is the interest rate per period. In the example, it is set to 8%. There are 4 quarters in a year. We need to calculate the ratio between 8% and 4 quarters per year which is 0.02 (2%).
- Per: This is the period for which the interest payment is calculated. In the example, it is set to 1 which corresponds to the 2nd quarter. 0 (zero) represents the first quarter.
- Nper: This is the total number of periods for the loan or investment. In the example, it is set to 3 years. There are 3 * 4 = 12 quarters in this example.
- Pv: This is the present value or the initial loan amount. In the example, it is set to $50,000
Formula in cell B3:
With these inputs, the ISPMT function calculates the interest payment for the 2nd period as $916.67. The second period is argument Per based on value 1, it starts with 0 (0) which is the first period.
The chart displays a blue line representing the ISPMT values (interest payment) and a red line representing the remaining loan balance over 12 payment periods. The chart shows that the initial loan balance is $50,000 (red line). In the first period, the interest payment (ISPMT) is $1,000 (blue line), and the remaining balance is $45,833.33 after the payment is made. The principal is the same over time, in this example, 50,000/12 = 4,166.67 The interest portion keeps decreasing as the loan balance decreases over the subsequent periods.
Functions in 'Financial' category
The ISPMT function function is one of 27 functions in the 'Financial' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form