Google Sheets has a few financial functions that can be used to calculate things like loan payments.
The PMT function is an example of such a function. This can be used to figure out things like loan payments, investment returns, mortgage payments, and more.
The PMT function determines the amount of money that will be paid out on an annuity investment. The payment will be determined using a fixed payment value and a constant or fixed interest rate.
This function can be used to figure out how much a loan, such as a mortgage, will cost.
The PMT function has the following syntax:
=PMT(rate, number_of_periods, present_value, [future_value, end_or_beginning]) here,
rate – the interest rate.
number of periods – The number of payments that will be paid is specified by this parameter.
present_value – the annuity’s current value.
future_value – this is an optional argument. It is the residual future worth after the final payment.
end_or_beginning – this is an optional input that defaults to 0. You can choose whether payments are due at the end of each period (0) or at the beginning (1).
One thing to keep in mind while using this function is that the rate and number of periods arguments must be consistent. For example, if you’re using this function to compute a monthly mortgage payment, divide the mortgage rate by 12 and multiply the number of periods by the years of the loan.
If you were calculating something with a quarterly payment, you would divide the rate by 4 and multiply the number of years of the loan by 4 for the number of periods argument.
This is what I mean when I say that you should maintain these two arguments in your formula consistent.
Using PMT Function
Steps to use PMT function –
- To begin, choose the cell where you wish the formula to be calculated, then input the equals symbol (=), then the minus sign (-), and finally PMT, then press yab on your keyboard. (Because PMT calculates as a negative, we must add a minus sign before the function if we want the result to be a positive number.)
- The first argument, which is the rate, must be entered next. Enter your interest rate (or the interest rate cell) in the appropriate box. Because I’m calculating both annual and monthly payments on a mortgage in this example, I’ve divided this rate by 12 to represent the 12 months in a year. When you’re finished with this step, add a comma.
- The number of periods parameter comes next. Select the cell that holds this value or enter the appropriate number of periods. In my case, the length is 30 years (cell B4), and each year has 12 months, hence this argument is (B4*12) in my formula. When you’re finished with this step, add a comma.
- The present value, which represents the value of the annuity, is the next input we need to pass into the function. You can input the loan amount or the cell that includes the loan amount if you’re computing a loan payment. Place a closing parenthesis around your formula after you’re finished with this step.
- In this example, I am first calculating a yearly payment, so I multiply the entire function by 12 as the next step.
- Your formula will calculate after you press Enter on your keyboard. You will now receive the payment that is paid on a regular basis.
- f you want to calculate monthly payment, copy the same formula and paste it into another cell and remove the end where you multiply it by 12.
- Press Enter on your keyboard and you will now have calculated both the monthly and yearly payments using the PMT function.
The PMT function is a very helpful financial tool for calculating periodical payments on annuities, mortgages, and other types of loans.
If you frequently perform these kind of calculations in your own spreadsheets, this is a function you should learn how to use.
Once you understand the function’s parameters, it’s quite simple to utilise.