How to Use Excel PMT Function

    how to use excel pmt function

    Quick Intro

    Want to calculate loan payments? Use PMT Function.

    PMT Function is a part of excel’s most useful financial functions. You can use PMT Function to a calculate loan payment based on fixed monthly payments and constant rate of interest.

    You can use PMT Function to a calculate loan payment based on fixed monthly payments and constant rate of interest. For example: If you are purchasing a home for $200000 with an interest rate of 5% for 20 years to pay it off. The monthly mortgage payment will $1319.91.

    Syntax

    PMT(rate, nper, pv, [fv], [type])

    • Rate The rate of interest for the loan. This rate of interest should be constant.
    • nper The total number of payments.
    • pv Present value or the total amount of loan.
    • [fv] The future value or the cash balance which you want after the last payment. The default value is 0.
    • [type] Use 0 or 1 to specify the due time of payment. You can use 0 when payment is due at the end of the each payment or 1 if payment is due at the start of the each period. If you omit to specify the type, it will assume 0.

    More Information on Excel PMT Function

    • The amount of payment return by PMT Function only includes payment and interest but not includes taxes and other fees which are related to the loan.
    • You have to be sure while specifying the value for rate and nper arguments. If you want to pay monthly installments on a five-year loan at an annual interest rate of 8 percent, use 8%/12 for rate and 5*12 for nper. For annual payments on the same loan, use 8 percent for rate and 5 for nper.

    Example

    Suppose you want to take a 20 years mortgage loan for $250000 by assuming 2.5% as an interest rate. Now here we can use PMT Function to calculate your monthly installment.

    We will use below formula to calculate it.

    use excel pmt function to calculate mortgage loan
    In above calculation, we have converted annual interest rate into monthly by dividing with 12. And, years into months by multiplying with 12.

    We have not mentioned any future value, and payment type is a default. And, we have a negative value because amount $987.80 you have to pay every month for 30 years.

    Sample File

    download sample file to learn more about this tips

    What’s Next?

    To learn more about Excel PMT Function you can check Microsoft’s Help Section. And, if you have a unique idea to use it, I would love to hear from you.

    There are also you have FV | PV which is highly useful. Apart from this, I have a list of excel functions and some real life formulas examples.