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])
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.
Send Email from Google Sheets When a Cell Value Changes
Google Sheets IF Multiple Conditions (Easiest way in 2025)