How to Calculate Loan Payments with Excel PMT Function

🔵 See the steps to calculate a loan payment in Excel, by using the PMT function

✅ Get the sample Excel file to follow along
► contextures.com/excelpmtfunction.html

🔶 Instructor: Debra Dalgleish, Contextures Inc.
💡 Get Debra's monthly Excel tips ► myctx.link/CtxNews
Subscribe to Contextures YouTube: youtube.com/user/contextures?sub_confirmation=1
#ContexturesExcelTips

'----------------------
Transcript - Calculate Loan Payments with Excel PMT Function

In Excel, to calculate monthly payments, you can use the PMT function.

In this example, we're going to enter the annual rate, and then the number of payments we have to make, the amount that we'll be borrowing, and then we'll use the PMT function in this cell to calculate the monthly payment.

The annual rate that we're going to pay is 5%.

In this cell, we're going to borrow the amount over four years. There are 12 months per year, so 48 months. I'll be making 48 payments, and the amount that we're going to borrow is $10,000.

Those are the three numbers that we need in order to calculate the monthly payment.

Here is the syntax for the PMT function. We'll start by typing equals, and then PMT, open bracket, and now I'm going to click on the cell where I entered the rate.

I'll click here where it says 5%, but it's not 5% per month. That's the annual rate. I'm going to click after that cell reference, type a slash for division. then I'll type 12, because we're paying that rate over 12 months.

Then I'll type a comma, and the next argument is the number of periods. I'll click on the Number of Payments cell, and type another comma.

The present value, or the amount of the loan, is 10,000, so I'll click on that cell.

The other two arguments are optional, so I'm not going to use them.

The fv is the future value, so that's what you want left at the end of all your payments, and if you don't enter it, we assume zero. We want to pay everything off, so I don't have to enter anything there.

The type is also optional. If I omit it, we assume that it's zero, and you're going to be making your payments at the end of the period. If you type a one, then you'd be paying at the beginning of the period.

I'll close the bracket and press Enter, and there's the monthly payment.

It's in this cell as a negative amount because it's a payment that I owe, but if you wanted to show it as a positive number, just click after the equals sign, right before the PMT function name, and type a minus sign, and press Enter. Now that shows as a positive amount in the payment cell.

For more Excel tips and tutorials, and to download the sample file for this video, please visit my Contextures website at www.contextures.com.