How to Calculate Interest on Fixed Principal Repayment Loans in Google Sheets Using ISPMT

In financial modeling, loans and capital financing agreements typically follow one of two primary amortization structures: an annuity schedule with constant total periodic payments (where interest decreases while principal repayment increases, calculated via the standard IPMT function), or a fixed principal schedule where the borrower pays an equal amount of principal every period plus interest on the remaining balance. When modeling commercial equipment financing, specialized construction loans, or declining balance corporate credit facilities, using IPMT produces incorrect numbers. Google Sheets includes the specialized ISPMT function specifically designed to calculate the exact interest paid during any specified period of a fixed-principal loan.

Understanding Fixed Principal vs Annuity Loan Amortization

To use financial formulas effectively, understanding the distinction between loan structures is crucial:

• Standard Annuity (Equal Total Payments): The total monthly payment remains constant. Early payments consist mostly of interest, with principal repayment accelerating later in the term. This schedule is modeled using PMT, PPMT, and IPMT.
• Fixed Principal Amortization (Declining Payments): The principal repayment is strictly fixed each period (Total Loan Amount / Number of Periods). Because the outstanding principal balance drops by a constant amount each cycle, the interest payment drops linearly, causing the total payment (principal + interest) to decline with each installment. This schedule is modeled using ISPMT.

The ISPMT function calculates the interest component for any single designated period under this declining payment structure.

Syntax and Parameters of the ISPMT Function

The ISPMT function uses the following standard syntax in Google Sheets:

ISPMT(rate, period, number_of_periods, present_value)

Each argument requires specific input criteria:

• rate: The interest rate per period. For example, if you have an annual interest rate of 6% paid monthly, divide by 12: 6% / 12 or 0.005.
• period: The specific period for which you want to calculate interest. Unlike IPMT (which indexes periods starting at 1), ISPMT uses a zero-based index (where 0 represents the first period, 1 represents the second period, etc.).
• number_of_periods: The total number of payment periods across the loan term (e.g., 5 * 12 = 60 for a 5-year monthly loan).
• present_value: The initial principal loan amount (borrowed capital).

Step-by-Step Example: Modeling a Commercial Equipment Loan

Suppose your business secures a £120,000 commercial equipment loan with a 5-year repayment term (60 monthly payments) at an annual interest rate of 7.2%. The lender mandates equal monthly principal repayments of £2,000 (£120,000 / 60) plus accrued interest.

Set up your spreadsheet variables in cells B1 through B4:

• B1 (Annual Rate): 7.2%
• B2 (Term in Years): 5
• B3 (Total Periods): =B2 * 12 (60)
• B4 (Loan Amount): 120000

To calculate the interest due in the very first month (Period 0):

=ISPMT(B1/12, 0, B3, B4)

The formula returns -£720.00 (interest paid is returned as a negative cash outflow). Because the entire £120,000 was outstanding during the first month, the interest is exactly £120,000 * (7.2% / 12) = £720.00.

To calculate the interest due in the 12th month (Period 11):

=ISPMT(B1/12, 11, B3, B4)

The formula returns -£588.00. By this point, 11 principal payments of £2,000 (£22,000 total) have been amortized, leaving £98,000 outstanding. The interest is £98,000 * 0.006 = £588.00.

To calculate the interest due in the final month (Period 59):

=ISPMT(B1/12, 59, B3, B4)

The formula returns -£12.00, reflecting interest assessed only on the final £2,000 principal tranche.

Building a Complete Fixed-Principal Amortization Schedule

To construct a dynamic multi-row amortization table in Google Sheets:

1. In row 6, create column headers: Period (Month), Beginning Balance, Principal Paid, Interest Paid (ISPMT), Total Payment, and Ending Balance.
2. In column A, list numbers from 0 to 59 representing the periods.
3. In column C (Principal Paid), enter =$B$4 / $B$3 to keep principal constant across all rows.
4. In column D (Interest Paid), enter the formula:

   =-ISPMT($B$1/12, A7, $B$3, $B$4)

   (Adding a leading minus sign converts the cash outflow into a positive accounting figure).
5. In column E (Total Payment), sum columns C and D: =C7 + D7.

Notice that as you drag the formula down the sheet, total payments decline linearly month by month, accurately capturing the true financial impact of equal-principal financing.

Comparing ISPMT vs IPMT to Avoid Costly Calculation Errors

Applying the standard IPMT function to a fixed-principal loan will calculate interest based on an annuity assumption, overstating early principal reductions and misrepresenting interest expenses for tax and cash flow forecasting.

Always verify the loan documentation:

• If the contract specifies “Equal monthly installments”, use IPMT.
• If the contract specifies “Equal principal installments plus interest on unpaid balance”, use ISPMT.

Mastering ISPMT ensures financial analysts and business owners can model commercial loans and debt facilities in Google Sheets with absolute mathematical accuracy.