The PMT function in Excel is used to calculate the loan repayment amount (annuity payment). It doesn’t matter whether we are talking about mortgages, consumer loans, or non-bank loans — in most cases, the repayment amount is calculated using the principle of annuity repayment.
Formula for the PMT function
Before we look at an example, let’s see how the function is written in Excel. The PMT function has five arguments, two of which are optional, and it returns the amount of the regular payment that is made periodically over the loan term:
=PMT(rate, nper, pv, [fv], [type])
How to calculate loan payments in Excel using PMT
Let’s take a practical example. Suppose we have a loan with the following parameters:
How would the calculation of the regular payment look?
PMT function arguments
- rate – Interest rate per period. If the loan is paid monthly, divide the annual rate by the number of payments per year, e.g. 3% / 12 = 0.25%
- nper – Total number of payment periods. If the loan is for 30 years with monthly payments, then nper = 30 × 12 = 360 months
- pv – Present value, or principal of the loan. In this case, the loan amount is USD 2,500,000
- [fv] – Future value, or the remaining balance after the last payment. This argument is optional; if omitted, it is assumed to be 0 (the loan is fully repaid).
- [type] – Indicates when payments are due. Use 0 for end-of-period payments (default) or 1 for beginning-of-period payments (payment in advance).
Creating an amortization schedule in Excel
Once we know how to calculate the payment amount, creating a simple amortization schedule in Excel is straightforward. Before we start, we need to understand one basic thing:
Each annuity payment consists of two parts:
- Interest – The portion of the payment that is the cost of borrowing money (it does not reduce the loan principal).
- Principal repayment – The portion of the payment that reduces the outstanding loan balance.
We can now prepare a table with the amortization schedule containing 361 rows (for a 30-year loan with 12 monthly payments per year, plus the initial period) and 5 columns:
- Payment number – from 1 to 360
- Payment amount (USD) – the same for all rows, i.e. the previously calculated value (USD 10,540)
- Interest = Balance × Rate
- Principal = Payment – Interest
- Remaining balance = Previous balance – Principal
The Interest, Principal, and Remaining Balance columns are calculated using formulas, and after filling in all periods, we arrive at the following result:
>> Please download excel with amoratization schedule calculator