In this article:

PMT Function in Google Sheets: How to Use It in 2024

What is the PMT Function in Google Sheets?

The PMT function in Google Sheets is used to calculate the payment for a loan based on constant payments and a constant interest rate. This function is very useful in financial analysis, specifically when you need to determine the periodic payments for a loan or an investment.

Syntax 

The syntax of the PMT function is as follows:

PMT(rate, nper, pv, [fv], [type])

rate: The interest rate for the loan. This should be the interest rate per period. For example, if you have an annual interest rate and make monthly payments, you should divide the annual rate by 12.

nper: The total number of payment periods in the loan term. For instance, if you have a 30-year loan and you make monthly payments, this would be 360 (30 years * 12 months per year).

pv: The present value, or the total amount of the loan.

fv (optional): The future value, or a cash balance you want to attain after the last payment is made. If omitted, it is assumed to be 0, which is usually the case for loans.

type (optional): This indicates when payments are due. 0 or omitted means at the end of the period, and 1 means at the beginning of the period.

How to Use the PMT Function in Google Sheets

Follow the steps below to learn how to use the Google Sheets PMT function. We’ll use example figures to give a better illustration of the process. 

1. Input Basic Loan Information in Your Spreadsheet

Begin by opening Google Sheets and creating a new spreadsheet. For this example, we will input  basic loan details as follows: In cell A1, type "Annual Interest Rate (%)" and enter 5 in 21. In B2, type "Loan Term (Years)" and enter 20 in B2. Finally, in C1, type "Loan Amount ($)" and input 150000 in C2. These steps set up the essential information for your loan calculation.

pmt function google sheets

2. Calculate the Monthly Interest Rate in Cell D2

Convert the annual interest rate to a monthly interest rate to use in your calculations. Directly beside your loan information, in D1 type "Monthly Interest Rate". Then, in cell D2, enter the formula =B2/12/100. This action divides the annual interest rate by 12 to find the monthly rate and converts the percentage to a decimal format.

google sheets pmt function

3. Determine the Total Number of Monthly Payments in Cell E2

Next, calculate how many monthly payments you'll make over the life of the loan. Label cell E2 as "Total Payments (Months)". In cell E2, type in the formula =B2*12. This multiplies the loan term in years by 12 to get the total number of monthly payments.

pmt function in google sheets

4. Use the PMT Function to Calculate Your Monthly Payment in Cell C2

Now, you'll calculate the actual monthly payment amount. In the row beside your previous entries, label F1 as "Monthly Payment ($)". In cell F2, input the PMT formula as =PMT(D2, E2, -C2). This formula uses D2 for the monthly interest rate, E2 for the total number of payments, and C2 (as a negative value) for the loan amount, indicating cash outflow.

5. Execute the Formula and Review the Calculated Monthly Payment

After entering the PMT formula in F2, press Enter. Google Sheets will display the monthly payment amount in cell F2. This value represents how much you need to pay each month to repay the loan under the given terms.

6. Modify Loan Terms as Needed to Explore Different Payment Scenarios

If you're interested in seeing how different terms affect your monthly payment, you can adjust the original values in A2 (interest rate), B2 (loan term), and C2 (loan amount). Google Sheets will automatically recalculate the monthly payment in C2 based on the new input, allowing you to easily compare different loan scenarios.

We hope that you now have a better understanding of what the PMT function in Google Sheets is and how to use it. If you enjoyed this article, you might also like our article on how to do sensitivity analysis in Google Sheets or our article on how to add a secondary axis in Google Sheets. 

Automate repetitive tasks with Lido

Save hours on repetitive and tedious work. Lido is a new spreadsheet that connects your spreadsheets, forms, PDFs, and email inbox.

Trigger
Action
Select trigger
When a cell value in Google Sheets changes
Check Google Sheets for today's date
When a new row is added to Google Sheets
On new Google Form submission
Send me a daily reminder
On new Typeform submission
When a cell value in database changes
Check database for today's date
When a new row is added in database
When a new HubSpot customer is created
Select action
ADDCALENDAREVENT
Creates a one- or multiple-day calendar event with optional attendees
ADDHUBSPOT
Adds an object to Hubspot
ADDSLACKCHANNEL
Create a Slack channel, and optionally add a topic or members
CALLURL
Makes any HTTP request
CREATEGOOGLEDOC
Replaces [@column_name] values in a Google Doc with the corresponding table row's values
CREATEPDF
Replaces [@column_name] values in a Google Doc with the corresponding table row's values, then export it as a PDF
FETCH
Makes any HTTP request and returns the response
INSERTROWS
Inserts given array below defined values in given worksheet
SENDGMAIL
Sends an email using your Google account
SENDOUTLOOK
Sends an email using your Microsoft Outlook account
SENDSLACK
Sends a Slack message
SENDSMS
Sends an SMS message using your Twilio account
UPDATECELL
Updates cells with given values
UPDATEHUBSPOT
Updates a property of a Hubspot object
Trigger is required
Action is required
Get started