To make the best use of the PPMT function in Google Sheets, it’s helpful to learn about two other financial functions: PMT and IPMT.

For example, you can calculate the Equated Monthly Installment (EMI) of a loan using the PMT function. Within that EMI, you can extract the interest component using the IPMT function. The PPMT function is used to determine the principal portion of the EMI.

In other words, you can bifurcate the principal and interest components of a monthly payment using the IPMT and PPMT functions, respectively, in Google Sheets. This method applies to quarterly and yearly equal periodic payments as well.

It’s important to note that the principal portion will not remain the same across all periods. This rule applies to the interest portion as well.

The advantage of using these two financial functions is that they allow you to find the principal and interest components for any payment term during the loan tenure.

PPMT Function – Purpose and Syntax

The ‘P’ in PPMT stands for principal, indicating that this function is designed to calculate the principal payment.

The purpose of the PPMT function in Google Sheets is to return the payment on the principal of an investment based on periodic, constant payments and a constant interest rate.

PPMT(rate, period, number_of_periods, present_value, [future_value], [end_or_beginning])

• Rate: The annual interest rate.
• Period: The specific period (1 to Nper) for which you want to calculate the principal payment.
• Number of periods (Nper): The total number of payments to be made (terms in months).
• Present value (Pv): The current value of the annuity (loan amount).
• Future value (Fv): (Optional) The future value remaining after the final payment has been made.
• End or beginning: (Optional) Indicates whether payments are due at the end (0) or beginning (1) of each period (0 by default).

Now, let’s learn how to use the PPMT function in Google Sheets.

Formula Examples for the PPMT Function in Google Sheets

Example 1: Monthly Payments

Suppose you wish to buy a car costing $20,000. Since you don’t have that amount readily available, you decide to take out a car loan with a tenure of 5 years (60 months).

Assume the annual interest rate is 3.29%. Your estimated monthly payment (EMI) will be $362. You can calculate this EMI using the PMT function in Google Sheets as follows:

=ROUND(PMT(3.29%/12, 60, 20000))

I used the ROUND function to round the returned amount, but this is optional.

Since the annual interest is 3.29%, I divided it by 12 to convert it to a monthly rate.

This payment includes both principal and interest components.

Using the PPMT Function

To extract the principal payment from the EMI, see the example below.

In cell D9, you can find the EMI calculated using the PMT function:

=PMT(D4/12, D3, D2)

This returns $361.96, where:

• D2 contains the total loan amount,
• D3 contains the term in months,
• D4 contains the annual interest rate.

In cell D7, I used the PPMT function to calculate the principal payment for the first period:

=PPMT(D4/12, 1, D3, D2)

Result: $307.12

Compared to the PMT formula, the only additional parameter in the PPMT formula is the period.

Needless to say, the PMT amount will remain constant throughout the tenure, as it involves periodic payments. However, the principal payment (PPMT) and interest payment (IPMT) will vary in each payment period.

Using the PPMT and IPMT functions, we can determine the interest and principal amounts for different periods.

For the IPMT function, simply use the same PPMT formula and change ‘P’ to ‘I’:

=IPMT(D4/12, 1, D3, D2)

Result: $54.83

In summary:

PMT = IPMT + PPMT

To calculate the interest or principal component in subsequent periods, simply adjust the period in cell D5 accordingly.

Example 2: PPMT Function for Quarterly Payments

In the case of quarterly repayment of a loan, the calculation of PPMT will be as follows:

1. Convert the annual interest rate of 3.29% to a quarterly rate: =3.29%/4.
2. Convert the number of periods from months to quarters: =60/12*4.

Formula:

=PPMT(D4/4, D5, D3/12*4, D2)

Example 3: PPMT Function for Yearly Payments

For yearly repayment of a loan, the calculation of PPMT will be as follows:

1. Use the annual interest rate of 3.29% as it is.
2. Convert the number of periods from months to years: =60/12.

Formula:

=PPMT(D4, D5, D3/12, D2)

Optional Parameters in the PPMT Function

Before concluding this tutorial, note that I omitted two optional parameters in my examples:

Future Value (Fv): The future value or balance of a loan after the final payment will typically be 0. You can omit this in formulas unless you want to specify a different value.

End or Beginning: This parameter indicates whether payments are due at the end (0) or beginning (1) of each period (0 by default).