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.

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.

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