How to Use the PMT Function in Google Sheets (3 Examples)


The PMT function in Google Sheets can be used to find the periodic payment for a loan.

This function uses the following basic syntax:

PMT(rate, number_of_periods, present_value)

where:

  • rate: The annual interest rate
  • number_of_periods: Number of payments to be made
  • present_value: The total amount of the loan

The following examples show how to use this function in different scenarios.

Example 1: Calculate Loan Payments for Mortgage

Suppose a family takes out a mortgage loan for a house with the following details:

  • Mortgage Amount: $200,000
  • Number of Months: 360
  • Annual Interest Rate: 4%

The following screenshot shows how to use the PMT function in Google Sheets to calculate the necessary monthly loan payment:

PMT function in Google Sheets

The monthly loan payment is $954.83. This is how much the family must pay each month in order to pay off the $200,000 loan in 360 months.

Note: When using the PMT function, we divided the annual interest rate by 12 (since we’re paying monthly) and we placed a negative sign in front of the mortgage amount since the family technically started with a value of -$200,000 and are trying to get back to zero.

Example 2: Calculate Loan Payments for Car Loan

Suppose an individual takes out a loan for a car with the following details:

  • Loan Amount: $20,000
  • Number of Months: 60
  • Annual Interest Rate: 3%

The following screenshot shows how to use the PMT function in Google Sheets to calculate the necessary monthly loan payment:

The monthly loan payment is $359.37. This is how much the individual must pay each month in order to pay off the $20,000 loan in 60 months.

Example 3: Calculate Loan Payments for Student Loan

Suppose a student takes out a loan for university with the following details:

  • Loan Amount: $40,000
  • Number of Months: 120
  • Annual Interest Rate: 5.2%

The following screenshot shows how to use the PMT function in Google Sheets to calculate the necessary monthly loan payment:

The monthly loan payment is $428.18. This is how much the individual must pay each month in order to pay off the $40,000 loan in 120 months.

Note: You can find the complete online documentation for the PMT function here.

Additional Resources

The following tutorials explain how to perform other common tasks in Google Sheets:

How to Calculate Compound Interest in Google Sheets
How to Combine Columns in Google Sheets
How to Compare Two Columns in Google Sheets

Leave a Reply

Your email address will not be published. Required fields are marked *