What is ISPMT in Google Sheets?
The ISPMT function in Google Sheets calculates the interest payment for a specific period of a loan or investment. We use the function when there is a loan with equal principal payments and use it to determine the interest portion for a given period. Summarizing the above, ISPMT in Google Sheets enables you to determine the interest you are paying over a specific period, based on the principal amount, interest rate, and the number of periods.
In a simple example, the formula is used below to calculate the interest payment for the first month of a 36-month loan with a 10% annual interest rate and a present value of $10,000.
=ISPMT(0.10/12, 1, 36, 10000)
Key Takeaways
- The ISPMT in Google Sheets is a financial function that calculates the interest paid during a specific period of an investment or loan.
- It is a very helpful function that simplifies the process of determining interest payments, making it accessible for users.
- It is very helpful in budgeting and understanding the cost of borrowing over time.
- The function is useful for calculation of interest for loan management, investment analysis, and any scenario where interest calculations are necessary.
Syntax
The syntax of the function is as follows:
=ISPMT(rate,per,nper,pv)
The function has the following arguments:
- Rate (required) – This is the interest rate on the investment or loan.
- Per(required) – This is the period for which we want to calculate the interest. It is an integer between 1 and nper.
- Nper (required) – The number of periods over which the loan or investment must be paid back.
- Pv (required) – The present value of the loan/investment. It is also the total value of payments made to date.
Whenever we use the formula, the outgoing payments are represented by negative numbers and the incoming payments are given as positive numbers.
How To Use ISPMT Function in Google Sheets?
The ISPMT is an oft-used function for financial calculations. One can enter it in two ways in Google Sheets.
Manually Entering the ISPMT Function
You can enter the ISPMT function manually in Google Sheets. Let us look at how to do so with a simple example. A person has a loan amount of $10,000 for a period of 4 year. It is at an annual interest rate of 6%. Find the interest paid in the second year using simple interest.
Step 1: Let us enter all the details in a Google Sheet. Here, there is a loan amount of $10,000, which is an outflow. Hence, it is represented as a negative number.
Step 2: Next, we must find the interest for the second year. Enter the following function in cell B5.
=ISPMT(B1, B3, B2, B4).
We have the interest rate in cell B1, total years in cell B2, the specific year in cell B3, and the loan amount in cell B4.
We write the order of the arguments based on the syntax:
=ISPMT(rate, period, number_of_periods, present_value).
Step 3: Press Enter to get the interest value in cell B5. The interest is calculated as $300.00 for the 2nd year on a simple interest basis.
As it is an outflow, it is shown as a negative value.
From the Google Sheets Menu
You can insert the ISPMT function from the Google Sheets menu in a simple way.
- First, go to the “Insert” menu option.
- Then, go to “Functions” and choose “Financial.”
- Choose the ISPMT function and enter the required arguments.
Examples
Let us look at some simple examples of how to use the ISPMT function in different ways in Google Sheets.
Example #1 – Calculate Interest Paid on a Loan
In this example, a businessman has taken a loan of $20,000 for a period of 5 years at an annual interest rate of 6%. He wants to calculate the interest paid during the second year using simple interest. We use the ISPMT function for the same.
Step 1: Enter all the details in a Google Sheet. Since the interest is calculated annually using simple interest, we use the ISPMT function to calculate the interest paid in a specific year.
- 6% – Annual interest rate
- 5 – Total number of years (loan tenure)
- 2 – Period for which you want to calculate interest (2nd year)
- -20000 – Loan amount (entered as a negative value since it’s an outflow)
Step 2: Apply the following formula in cell B5:
=ISPMT(B1, B3, B2, B4). Press Enter.
As seen above, the interest paid in the 2nd year is $150, shown as -150.00 to indicate an outflow. This method helps break down the loan into yearly interest components using simple interest.
Example #2 – Forecast Returns On An Investment
We consider another example where a person invests $12,000 for a period of 6 years at an annual interest rate of 5%. He wishes to forecast the interest earned in the 4th year using simple interest.
Let us look at the steps to do the same. It is very simple and is similar to how the calculation is done for a loan.
Step 1: We must enter all the details in a new spreadsheet. As it is an investment, and we must calculate the simple interest earned per year, let us use the ISPMT function to forecast the return in a specific year.
- 5% – Annual interest rate
- 6 – Total number of years (investment period)
- 4 – The year for which to calculate interest (4th year)
- -12000 – Investment amount (entered as a negative value to represent an outflow)
Step 2: Now, apply the following formula in cell B5:
=ISPMT(B1, B3, B2, B4).
Press Enter.
As seen above, the interest earned in the 4th year is $200, shown as -200.00 to indicate it connection to the original investment, which is an outflow.
This approach is useful for projecting year-by-year interest earnings on investments.
ISPMT vs. IPMT in Google Sheets
We use both the ISPMT and IPMT functions in Google Sheets to calculate the interest portion of a loan payment. However, they differ in the way they handle the loan period. ISPMT calculates interest for a specific period in a loan where principal payments are equal across all periods whereas IPMT calculates the interest for a specific period in a loan where the total payment is fixed, but the principal and interest portions vary during each period.
Let us look at a comparative table summarizing the two functions.
| Feature | ISPMT | IPMT |
|---|---|---|
| Principal Payment | Here, there are equal principal payments | There is a fixed total payment with the principal reducing over time with each payment |
| Loan Type | Loans with equal principal payments. | Loans with fixed periodic payments |
| Payment Type | Interest for a specific period; begins with zero. | Interest portion of regular payments |
| Syntax | ISPMT(rate, period, number_of_periods, present_value) | IPMT(rate, period, number_of_periods, present_value) |
| Commonly used in | Forecast interest earnings, fixed-income planning. | Loan amortization schedules, mortgage analysis |
Important Things to Note
- ISPMT in Google Sheets is useful to estimate how much of the periodic payment goes towards interest versus principal.
- It is particularly helpful when dealing with loans or investments where the principal is paid down evenly over the entire term.
- It is often used in conjunction with other financial functions like PMT to calculate total payment and with PPMT to calculate principal payment.
Frequently Asked Questions (FAQs)
When using ISPMT, ensure that you use consistent units for the rate, period, and number of periods. If a person is availing a car loan for 36 months, which he should pay monthly, we should divide the annual percentage rate by 12, and the number of payments is 36. Similarly, one can pay a different type of loan of the same length, quarterly, in which case we divide the annual percentage rate by 4, and the number of payments would be 12.
Some of the related functions are as follows:
PPMT: It calculates the payment on the principal of an investment on the assumption of periodic payments of constant amounts at a constant interest rate.
PMT: The PMT in Google Sheets calculates the periodic payment for an annuity investment. It is based on constant-amount periodic payments and a constant interest rate.
NPER: It calculates the number of payment periods for an investment based on constant-amount periodic payments and a constant interest rate.
A negative value when using the ISPMT function means the interest is an outflow. For example, money we pay such as a loan.
Not just loans, we can use ISPMT to forecast returns from simple interest investments. It calculates the interest earned in any period of the investment term.
Download Template
This article must help understand ISPMT in Google Sheets with its formulas and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What is ISPMT in Google Sheets. We learn its syntax & how to use it to find the interest payment for an investment with examples. You can learn more from the following articles. –
Leave a Reply