ISPMT Function in Google Sheet

/ Resources / By /
ISPMT Function
 
ISPMT Function is coming under the head  Financial  Functions used in the Google sheet. This google sheet function is also similar to the ISPMT finance function in MS Excel.
 
The ISPMT function returns the interest payment for an investment for a given rate and a constant payment schedule.

Syntax of ISPMT Function

ISPMT(rate, period, number_of_periods, present_value)

 
 
This function uses the following parameters
 
  1. Rate – It is the interest rate
  2. Period – It is the period for which we want to calculate the interest. It should be a number between ‘1’ and ‘number_of_periods’.
  3. number_of_periods: The number of payments to be made.
  4. present_value: Current value of the annuity. 
 
ISPMT Finance Function useful for forecasting revenues and expenses in financial analysis. If the business is taken a long-term loan, then the finance team can calculate the interest payable for a particular period. 
 
ISPMT finance function can be used in budget preparation, future cash projection, financial statement project etc.
 
We need to ensure that consistent units are used for the rate, period, and number of periods.
 
Suppose a firm has a long-term loan of $ 50000. The interest is 7.5% per year. Loan period is 2 years. 
 
From this example, How can we calculate the interest payment for the 10th month?
 
The formula is 
 
 
ISPMT(rate, period, number_of_periods, present_value)
 
 
Let us see how to solve the problem with ISPMT function in google sheet.
 

Find the parameters

 
Rate: 7.5 % per year. We need to change the yearly interest rate to Monthly Rate ( 7.5% / 12 ).
 
Period: As per the question, we need to find the Interest for the 10th month.
 
Number_of_periods: As per the question, it is 2 years. We need to change the yearly value to monthly value ( 2 * 12 = 24)
 
present_value: $ 50000.
 
 
=ISPMT(7.5%/12, 10, 2*12, 50000)
 
This will return  -182.29
 

Points to Remember

 
The situation may ask to calculate interest in weekly, monthly or quarterly. In that case, we need to convert the interest rate and number of periods to week or month or quarter as shown below
 
Months = Years * 12
Monthly Rate = Annual Rate /12
 
Quarter = Years * 4
Quarterly Rate = Annual Rate / 4
 
Weeks = Years * 52
Weekly Rate =Annual Rate /52
 
Hope this article may help you to understand the concept of ISPMT Finance Function in google sheet. 
 
If you feel to express your opinion, please use the comment box.

Related Posts