If you’ve ever thought about getting a mortgage or any other loan, knowing how to use the PMT function in Excel can give you some idea of ??what your payments will look like.
PMT stands for Payment. After you enter all the necessary data into the function, it will return you the periodic payment that you need to make.
Understanding how the PMT function in Excel works can help you measure how long it will take to pay off a loan if you pay a certain amount, or based on a change in the interest rate.
How does the PMT function work in Excel
The PMT function is much simpler than other Excel functions like Index or Vlookup But that doesn’t make it any less useful.
To receive recurring loan payments, you need to provide the following input to the PMT function.
- rate: the interest rate on the loan.
- nper: The total number of payments made during the entire loan period.
- pv: initial loan balance (present value).
- fv: cash remaining after loan disbursement (future value). This is optional and defaults to 0.
- type: whether payments should be made at the beginning of each billing period (1) or at the end (0). This is also optional.
An easy way to understand how you can use this feature is to start with a simple example.
– /
Let’s say you are considering taking a personal loan of $ 10,000 from your bank. You know that you want to repay it in 4 years (48 months), but you don’t know what interest rate you will receive when the bank repays your loan.
To estimate the size of your payment at different interest rates, you can use the PMT function in Excel.
Set up a spreadsheet with known fixed values ??at the top. In this case, it is the loan amount and the number of payments. Create one column for all possible interest rates and an empty column for payment amounts.
Now in the first cell “Payments” enter the PMT function.
= PMT (B5; B2; B1)
Where B5 is the cell with the interest rate, B2 is the cell with the number of payments, and B1 is the cell with the loan amount (present value). Use the “$” symbol for B1 and B2 as shown below to keep these cells constant as you populate the column in the next step.
Press Enter and you will see the amount paid at that interest rate.
While holding down the Shift key, place the cursor in the lower right corner of the first cell with the payment amount until the cursor changes to two horizontal lines. Double-click and the rest of the column will be populated with payments at different interest rates.
These results show how much payment you can expect on this loan, depending on what interest rate the bank is offering.
What’s useful about using Excel for this is that you can also change the cells with the total loan amount or the number of payments and watch how this changes the amount of the recurring loan payments.
Other examples of PMT function in Excel
Let’s take a look at some more complex examples.
Imagine that you have won a major award, and the organization that will present you with the award has given you a choice: accept it as a lump sum or annuity. You can get $ 1,000,000 in 5% annuity over 10 years or $ 750,000 in a lump sum today. Which is the best option in the long run?
The PMT function in Excel can help. In the case of an annuity, you need to know what the annual payment is.
To do this, use the same approach as in the previous example, but this time the known values ??are:
- future value: $ 1,000,000
- rate: 5%
- number of payments: 10 (one annual payment for ten years)
Enter function:
= PMT (B2; B3,0; B1,0)
A zero at the end means that payments will be made at the end of each period (year).
Press Enter and you will see that the annual payment is $ 79,504.57.
Then let’s see how much money you’ll have in 10 years if you take $ 750,000 today and put it into an investment that yields a modest interest rate of just 3%.
Determining the future value of the lump sum requires another Excel formula called FV (Future Value).
This formula requires:
- Interest rate: 3%
- Number of payments: 10 (years)
- Payments: 0 (amount not withdrawn)
- Present value: – $ 750,000 (deposited amount).
This formula: = FV (B2, B3, B4, B1)
Press Enter and you will see that if you invested all $ 750,000 today and earned only 3%, then in 10 years you would have made $ 7,937.28 more.
This means taking a lump sum today and investing it yourself will be smarter, because you can probably earn a lot more than just 3% if you invest smartly.
The PMT function in Excel is useful
Whether you are applying for a home loan, car loan, or considering lending money to a family member, Excel’s PMT feature can help you figure out the right loan terms for your situation.
So the next time you’re thinking about going to the bank or car dealership, sit down with Excel first and do your homework. Understanding the impact of interest rates and payments will give you far more benefits than taking a cold walk and having to take someone else’s word for it.
If you use Excel a lot, you should check out our Excel Tips and Tricks. And if you know of other cool use cases for the PMT feature, share them in the comments section below.
–