The PMT function in Google Sheets is used to calculate the payment for a loan based on constant payments and a constant interest rate. This function is very useful in financial analysis, specifically when you need to determine the periodic payments for a loan or an investment.
The syntax of the PMT function is as follows:
PMT(rate, nper, pv, [fv], [type])
rate: The interest rate for the loan. This should be the interest rate per period. For example, if you have an annual interest rate and make monthly payments, you should divide the annual rate by 12.
nper: The total number of payment periods in the loan term. For instance, if you have a 30-year loan and you make monthly payments, this would be 360 (30 years * 12 months per year).
pv: The present value, or the total amount of the loan.
fv (optional): The future value, or a cash balance you want to attain after the last payment is made. If omitted, it is assumed to be 0, which is usually the case for loans.
type (optional): This indicates when payments are due. 0 or omitted means at the end of the period, and 1 means at the beginning of the period.
Follow the steps below to learn how to use the Google Sheets PMT function. We’ll use example figures to give a better illustration of the process.
Begin by opening Google Sheets and creating a new spreadsheet. For this example, we will input basic loan details as follows: In cell A1, type "Annual Interest Rate (%)" and enter 5 in 21. In B2, type "Loan Term (Years)" and enter 20 in B2. Finally, in C1, type "Loan Amount ($)" and input 150000 in C2. These steps set up the essential information for your loan calculation.
Convert the annual interest rate to a monthly interest rate to use in your calculations. Directly beside your loan information, in D1 type "Monthly Interest Rate". Then, in cell D2, enter the formula =B2/12/100. This action divides the annual interest rate by 12 to find the monthly rate and converts the percentage to a decimal format.
Next, calculate how many monthly payments you'll make over the life of the loan. Label cell E2 as "Total Payments (Months)". In cell E2, type in the formula =B2*12. This multiplies the loan term in years by 12 to get the total number of monthly payments.
Now, you'll calculate the actual monthly payment amount. In the row beside your previous entries, label F1 as "Monthly Payment ($)". In cell F2, input the PMT formula as =PMT(D2, E2, -C2). This formula uses D2 for the monthly interest rate, E2 for the total number of payments, and C2 (as a negative value) for the loan amount, indicating cash outflow.
After entering the PMT formula in F2, press Enter. Google Sheets will display the monthly payment amount in cell F2. This value represents how much you need to pay each month to repay the loan under the given terms.
If you're interested in seeing how different terms affect your monthly payment, you can adjust the original values in A2 (interest rate), B2 (loan term), and C2 (loan amount). Google Sheets will automatically recalculate the monthly payment in C2 based on the new input, allowing you to easily compare different loan scenarios.
We hope that you now have a better understanding of what the PMT function in Google Sheets is and how to use it. If you enjoyed this article, you might also like our article on how to do sensitivity analysis in Google Sheets or our article on how to add a secondary axis in Google Sheets.
