How to Use Excel PMT Function | Calculate Monthly Loan Payment Amount

In this video, we will cover how you can use the PMT function in Excel.

The PMT function helps you calculate the payments that you, as the borrower, would have to make against a loan.

Let’s say you’re getting financing for Two Hundred Thousand dollars with an annual rate of Five Percent, no down payment and tenure of Twenty years. At the end, you have a balloon payment of Fifty Thousand dollars.

The balloon payment here is negative because in cashflow terms you would be paying out this amount at the end, therefore this will be deducted from your account. Similarly, the borrowed amount here is positive, because you will be getting this amount once the loan is approved.

So, let’s calculate the monthly payment using the PMT function.

First, let’s select the cell here next to monthly payments and click on the function icon. In the popup window, search “PMT” and select it from the results here.

Over here we have 5 arguments that will help us calculate our monthly payments.

First is the rate, which will be the interest rate for the loan. For this calculation to work, you need to maintain a standard unit. Meaning, if you include the annual rate here, then the subsequent period should be annual as well.

Since we want to calculate monthly payments, let’s select the Monthly Rate, which is simply dividing the annual rate by 12.

Next, lets move to NPER, which is the total number of payments that are made. In this case, it will be one payment for each month. Therefore, let’s select the cell with total months here.

Present value is the total amount being financed. So let’s select the loan amount here.

Next is the future value section, which helps account for the cash balance, or balloon payment, that we would be paying out at the end of our financing tenure. So, let’s select this cell over here. If you don’t have a balloon payment in your calculation, you can leave it blank.

Last of all you have the type. There are cases where payments are required to be made at the beginning of each month, for example when calculating leases etc. In that case, you can enter “one” over here. Loan payments are usually at the end of the period, so we can leave it blank.

Now simply click on OK and the function will automatically calculate the monthly payments.

And that’s it!

❓💬 Which Excel function would you like to know more about? Let us know in the comments below.

#HowTech #Excel
--------------------------------------------------------------------------------------------------------------
✅All our announcements are available here t.me/howtechtv
✅ Commercial questions [email protected]
✅ Instagram instagram.com/howtechprojects
✅ Twitter twitter.com/howtechprojects