zakruti.com » IT - Software » Geeks Tutorial

How to Use Excel PMT Function Calculate Monthly Loan Payment Amount

video description

Rating: 4.0; Vote: 1

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. Lets say youre 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, lets calculate the monthly payment using the PMT function. First, lets 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, lets 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, lets select the cell with total months here. Present value is the total amount being financed. So lets 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, lets select this cell over here. If you dont 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 thats it! Which Excel function would you like to know more about?
Date: 2023-07-08

← How to Count Words in Excel Using VBA Function to Count Words in Excel

How to Unprotect Excel Sheets and Workbooks 3 Ways to Unprotect Excel File with Password →

Related videos

Der8auer Has Gone Mad with Power - Heatsink Manufacturing - Made in Germany

• Gamers Nexus

Still Using Drop Shadow Fix This - Photoshop in Minutes

• PiXimperfect

How to Delete Google Account Permanently (Quick & Easy)

• Geeks Tutorial

Computing History: Exploring The TI-99/4A

• PC World

End of Real Photography

• PiXimperfect

Is Selecting Hair STILL a Pain in Photoshop [SOLUTION]

• PiXimperfect

Comments and reviews: 2

CeeC
How would we do PMT in reverse? For example Im paying $300 a month, for 72months (6 years, with an interest rate of 5%. Whats the formula to find the total loan amount? (I keep seeing =PV, but Im confused at how when I use PV, the outcome is a lesser number then if I multiply monthly payment (300) by Term (72 months. How is it more expensive at 0%? I just want to know how to find a total loan amount when all we know is Monthly payment, Rate, and term.
reply

Eref
I am always getting the negative result. I know why it is negative but I want to make it positive as you did in this example, without adding the minus sign before function because I will use the result and minus sign affects the others. Thanks.
reply

Add a review, comment