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.