Table of Contents
The PMT function in Google Sheets is an in-built financial function that can be used to calculate the payment for a loan based on constant payments and a fixed interest rate. It takes three arguments; the interest rate per period, the number of periods and the present value. This can be useful for calculating loan payments, or amortization schedules. Examples of its usage include calculating the loan payment for a car, a mortgage or other types of loans.
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 .