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:
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