To calculate the periodic payment for a loan or annuity investment, you can use a PMT function-based formula in Google Sheets.
Read on to learn more about this one of the must to learn financial functions in Google Sheets.
The payment calculation is based on constant-amount periodic payments and a constant interest rate.
The output of the PMT formula includes principal payment and interest.
To use the PMT function in Google Sheets, other than the loan amount, you must know the annual interest rate and the loan tenure.
In this post, I have included a few formula examples to learn the usage of the PMT function in Google Sheets. Also, you can find a link to my simple EMI calculator in Google Sheets.
You can use my EMI calculator to find the EMI for home/car/personal loan etc. (it’s for educational purposes only).
Here is the screen capture of the said calculator.
At the end of this post, I’ll explain how to make this EMI calculator using a PMT formula in Google Sheets.
I am leaving this for the time being because you must first learn the function usage.
Similar: Create Unit Converter in Google Sheets Using the Convert Function.
Please note that the payment calculated and returned by the PMT function in Sheets includes principal and interest amount.
It doesn’t include taxes, reserve payments, or fees, if any, associated with the loan.
How to Use the PMT Function in Google Sheets
Syntax:
Let us begin with the syntax.
PMT(rate, number_of_periods, present_value, [future_value, end_or_beginning])Now is time to understand/learn the PMT function arguments in Google Sheets.
rate (APR): The annual interest rate.
number_of_periods (NPER): The number of payments to be made in months, i.e., the loan/annuity investment tenure.
present_value (PV): The current value of the annuity. It’s the loan value.
future_value (FV): Optional and 0 by default. The future value, aka FV of a loan, will be 0 at the end of the tenure.
end_or_beginning: Optional and 0 by default. It determines whether payments are due at the end (zero) or beginning (one) of each period.
Out of the five arguments, the first three are must in a PMT formula in Google Sheets.
PMT Formula to Calculate Monthly, Quarterly, and Yearly Payments
Let me detail how to use the function to find the payment for a car loan based on the payment schedule.
Calculate MONTHLY Payment for a Car Loan in Google Sheets
Here is my sample data for the formula.
Annual Interest Rate (APR): 5%
Number of Periods (NPER) in months: 72
Loan Amount (PV): $35,000.00
Assume the above input values are in cells C3, C4, and C5. So here is the PMT formula to calculate the monthly payment for the car loan in Google Sheets.
=pmt(C3/12,C4,C5)Since the APR, which is 5%, in cell C3 is an annual rate, we must divide it by 12 months. That’s what you can see in the above formula.
I have captured the screenshot of this formula example for you. Please refer to that below.
The formula output will be a negative value because loan payment is an outflow of cash.
To get a positive value, you can either multiply the formula output by -1 or use the PMT formula like below.
=-pmt(C3/12,C4,C5)In the above example, the payment is due at the end of each month. If it’s in the beginning, the formula would be like this.
=pmt(C3/12,C4,C5,0,1)Calculate QUARTERLY Payment for a Car Loan in Google Sheets
Here in this PMT function example, there are two changes compared to the earlier PMT formula. What are they?
- I’ve divided the annual rate by 4. In the earlier formula, it was divided by 12.
- The loan tenure is six years (72 months). So in quarterly payment, the number of payments (NPER) will be 24 (6 years * 4 periods). There is no change in the formula, but it is in the input value in cell C4 (please refer to the screenshot below).
Formula:
=pmt(C3/4,C4,C5)
PMT Function in Google Sheets to Calculate the Yearly Payment for a Car Loan
This time the loan payment is yearly. We already have the yearly interest rate. We can use that percentage as it is.
=pmt(C3,C4,C5)
How to Use the Future Value (FV) in PMT Formula in Google Sheets
Follow the below example to understand the future_value (FV) argument in the PMT function in Google Sheets.
Suppose you want to find how much money you must invest ‘periodically’ to get $25,000 at the end of 8 years.
In the following example, I am considering periodic monthly payments at a given annual interest rate of 8.5% for this investment.
See what the PMT formula returns.
It means, if you deposit $182.72 monthly for eight years, you will get $25,000.00.
I hope you could understand how to use the PMT function in Google Sheets.
Now, as I have promised at the beginning of this post, here is a copy of my EMI calculator created in Google Sheets.
How to Make an EMI Calculator in Google Sheets
Here are the formulas in use in this spreadsheet-based EMI calculator in Google Sheets.
Input Values:
Cell C2 (Home Loan Amount): $85,000.00.
Interest Rate (Annual) in Cell C3: 4 (represents 4% but entered as a number).
Cell C4 contains Loan Tenure: 30.
Formula:
=-PMT(UNARY_PERCENT(C3/12),IFS(D4="Year",C4*12,D4="Month",C4),C2)This formula is quite different from the usual PMT formula.
In this, I have used the logical IFS and the Unary_Percent functions.
First of all, I will explain the role of the above two within the PMT function in Google Sheets.
As you can see, the rate in cell C3 is a number value, not a percentage value. The Unary_Percent function interprets that value as a percentage.
I have used the IFS function since there is an option to enter either number of months or year as the period.
If you select the “Year” from the drop-down in cell D4, enter the period as the number of years in cell C4.
If you select “Month,” enter the period as the number of months.
With the help of the IFS function, we can test whether the number_of_periods entered is in months or years and do the calculation accordingly.
That’s all. Thanks for the stay. Enjoy!
