In this article:

Google Sheets Loan Payment Formula: How to Use It in 2024

Google Sheets Loan Payment Formula

To calculate a loan payment in Google Sheets, you can use the PMT function. The PMT function calculates the payment for a loan based on constant payments and a constant interest rate. Here's the syntax:

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

  • rate: The interest rate for the loan divided by the number of payments per year.
  • nper: The total number of payments for the loan.
  • pv: The present value, or the total amount that a series of future payments is worth now; also known as the principal.
  • [fv] (Optional): The future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (zero), which means the loan is to be paid off fully.
  • [type] (Optional): The number 0 (zero) or 1 and indicates when payments are due. 0 or omitted means at the end of the period. 1 means at the beginning of the period.

How to Use the Loan Payment Formula in Google Sheets

Follow the steps below to use the loan payment formula in Google Sheets. 

1. Label Cells for Loan Details and Input Data

Label cells A1 through A4 for "Interest Rate per Year," "Payments per Year," "Total Number of Payments," and "Loan Amount," respectively. For our example, we’ll use the corresponding data (5%, 12, 48, -10000) in cells B1 through B4.

google sheets loan payment formula

2. Calculate Monthly Interest Rate in a New Cell

In cell B7, calculate the monthly interest rate by dividing the annual interest rate (B1) by the number of payments per year (B2) using the formula =B1/B2.

loan payment formula google sheets

3. Apply the PMT Function to Determine Monthly Payment

Use the PMT function in cell B8 to calculate the monthly payment. Input =PMT(B7, B3, B4) to utilize the monthly interest rate from B7, total number of payments from B3, and the loan amount from B4.

4. Review Monthly Payment Result for Loan Repayment Plan

After entering the PMT formula, check the result in cell B8 to see the monthly payment required to repay the loan under the given terms.

We hope that you now have a better understanding of what the Google Sheets loan payment formula is and how to use the loan payment formula in Google Sheets. If you enjoyed this article, you might also like our article on Google Sheets MINIFs or our article on how to insert special characters 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