PMT Function in Excel Demystified: Accurate Payment Calculations Made Simple - OnRamp Analytics

PMT Function in Excel Demystified: Accurate Payment Calculations Made Simple

The PMT function in Excel is a financial function used to calculate the periodic payment for a loan or investment based on constant payments and a constant interest rate. This function is particularly useful for individuals and businesses looking to determine their payment obligations over time.

PMT Function Explained

Syntax

The syntax for the PMT function is as follows:

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

Components

  • rate: The interest rate for each period. For example, if you have an annual interest rate of 5% and make monthly payments, this value would be 5%/12.
  • nper: The total number of payment periods in an investment or loan. For a loan with a term of 15 years and monthly payments, this value would be 15*12.
  • pv: The present value, or the total amount that a series of future payments is worth now. For a loan, this would be the amount borrowed.
  • fv (optional): The future value, or the cash balance you want after the last payment is made. If omitted, Excel assumes a future value of 0.
  • type (optional): This indicates when payments are due. Use 0 if payments are due at the end of the period (default), or 1 if payments are due at the beginning.

Example PMT Calculation

Scenario: A borrower is considering a mortgage of $200,000 at an annual interest rate of 5% for 30 years. They want to calculate their monthly payment.

Identify Components:

  • Rate: 5% annual interest (C5) / 12 months = 0.004167 (monthly interest rate)
  • Nper: 30 years (C6) * 12 months/year = 360 months
  • PV: $200,000 (C4)

PMT Function:

The formula in Excel would be:

=PMT(C5/12, C6*12, -C4)

Result:

The result will provide the monthly payment amount, which, in this case, would be approximately $1,073.64.

Key Considerations

  • Negative Sign: The present value (pv) is entered as a negative number because it represents an outgoing payment (the loan amount).
  • Interest Rate Adjustments: Ensure the interest rate corresponds to the payment frequency. Adjust accordingly if the payments are not monthly.
  • Additional Payments: The PMT function does not account for additional payments or varying interest rates; it assumes a fixed rate and fixed payment schedule.

Conclusion

The PMT function in Excel is a valuable tool for financial planning and loan management.

Back to blog

Leave a comment