In this article:

May 8, 2024

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.

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

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.

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.

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.

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.

Get Google Sheets productivity and automation tips delivered straight to your inbox

We'll email you 1-3 times a week — and never share your information.

Use Lido to connect your spreadsheets to email, Slack, calendars, and more to automate data transfers and eliminate manual copying and pasting. View all use cases ->