Chat Support

(02)625-0655 / 843-8576 [email protected]


PMT Function Formula, Examples, How to Use PMT Function

how to calculate pmt

PMT is the abbreviation for payment, which makes the function name easy to remember. Learn these (and many more) functions of Excel by enrolling in my 30-minute free email course here. To master Excel functions, you better take the road from simpler Excel functions.

How to use the PMT Function in Excel?

When you select cell D2, the complete function appears in the formula bar above the worksheet. The number 0 means the end of the payment period, and 1 means the beginning of the payment period. Where you lease out a car today (and hence the loan starts today), and you pay an installment right away. Similarly, each installment is then paid at the beginning of the period.

how to calculate pmt

More Financial Calculators

The PMT Function in Excel calculates the periodic payments owed on a loan, assuming a fixed interest rate. This article provides detailed information that will assist you in calculating your mortgage payoff amount based on the terms of your loan. Contact your lender to confirm that your calculation is correct based on the particulars of your mortgage.

  1. The IPMT function in Excel calculates the interest part of the payment.
  2. Payments calculate through a financial formula used to determine the time value of money.
  3. If you would like to see the total amount that will be repaid, over the duration of the loan, use the following formula in cell C8.
  4. The PMT function below calculates the annual payment.

How to Calculate Mortgage Payoff

The same function can be used to write formulas that calculate periodic payments for a mortgage loan, a car loan, or a student loan. As long as the interest rate is constant, the PMT function can be used to determine the loan payment. Before you borrow money it’s good to know how a loan works. Thanks to the Excel financial functions such as RATE, PPMT and IPMT, computing the monthly or any other periodic payment for a loan is easy.

We’ll use an example for a house that costs $200,000, with a down payment of $30,000 and a loan amount of $170,000. The mortgage loan has a period of 30 years and an annual interest rate of 5.00%, and payments are due monthly. Like you might have to repay the entire loan in 5 years, but your periodic installment might fall due every month. In this case, your Nper would be 60 (12 monthly payments per year for 5 years).

If you would like to see the total amount that will be repaid, over the duration of the loan, use the following formula in cell C8. Since all the necessary assumptions have been provided, the next step is to convert our annual interest rate to a monthly interest rate by dividing it by 12. The number of periods must also be adjusted by multiplying the borrowing term in years (20 years) by the frequency of payments (quarters) per year (4x).

The Excel “PMT” function is used to determine the payments owed to a lender by a borrower on a financial obligation, such as a loan or bond. The PPMT function in Excel petty cash log calculates the principal part of the payment. Consider a loan with an annual interest rate of 5%, a 2-year duration and a present value (amount borrowed) of $20,000.

FV – This means any future value of the loan that would be left back after the last payment is made. That’s all about the required functions of the PMT function. We have created a reference to cell B3 that contains 5.

how to calculate pmt

They simply look at the underlying figures – imagine presenting your Manager with the above spreadsheet. They’d probably query why the monthly payment was negative. In this final example, the PMT function is used at the top of the worksheet, to calculate the monthly payment amount. Before you go ahead and borrow money, it stands to reason to compare different loan conditions to find out the options that suit you most. For this, let’s create our own Excel loan payment calculator.

For instance, if a borrower has taken out a twenty-year loan with an annual interest rate of 5.0% paid on a quarterly basis, then the monthly interest rate is 1.25%. The PMT function below calculates the monthly withdrawal. The first payment date is also entered at the top of the sheet, in cell A2, and a payment table calculates all the payment days, plus the interest and principal amounts each month. Since the fv and type arguments were omitted, Excel used their default value of zero.

Using Microsoft Excel, you can calculate a monthly payment for any type of loan or credit card. This will allow you to be more accurate in your personal budgeting and to allocate adequate funds for your monthly payments. The best way to calculate a monthly payment in Excel is by using the “functions” feature. PMT is a financial function that returns the periodic payment for a loan based on a constant interest rate, the number of periods, and the loan amount.


Leave a Reply

Your email address will not be published. Required fields are marked *

Track Your Item Now!
About Us

EMR Courier Services International Inc. aims to provide reliable and excellent quality of courier services for both mail and parcel, local and international for customer’s satisfaction at very reasonable rates/costs in the domestic and international communities thru land, sea and air ways transportation.

Office Hours

Monday - Friday: 9:00am - 6:00pm
Saturday: 9:00am - 6:00pm

Chat Support
Click Here to Chat
via FB Messenger
Make sure you are signed in to your FB first.
Like Us on Facebook