Calculate payment for a loan

Calculate payment for a loan Excel formula

To calculate payment for a loan in Excel, based on an interest rate, loan term, and amount, use the PMT function.

How to calculate payment for a loan?

Steps to calculate payment for a loan in Excel:

  1. Select cell C7.
  2. Type =PMT(C3/12, C4, -C2).
  3. Press Enter.
  4. The formula calculates the monthly payment for a loan where C3 is the annual interest rate (divided by 12 to get the monthly rate), C4 is the total number of payments (loan term in months), C2 is the loan principal (entered as a positive number; the formula uses -C2 to indicate it’s an outgoing payment).

Example

In this example, we have a dataset in cells B2:C6 representing a loan calculation scenario, including the loan amount, interest rate, number of payment periods, and compounding periods per year. The goal is to write a formula to calculate the monthly loan payment using the Excel PMT function.

Formula:

=PMT(C3/12,C4,-C2)

Calculate payment for a loan Excel formula

The formula in cell C7 divides the annual interest rate by the number of periods per year, uses the total number of periods, and considers the loan amount to compute the monthly payment. This setup allows for a precise calculation of periodic payments based on the input parameters.

As a result of the formula, we have to pay $107.15 per month for 120 months.

Explanation

Evaluate the formula:

PMT function: The PMT function calculates the payment for a loan based on constant payments and a constant interest rate.

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

  • rate: Interest rate for each period.
  • nper: Total number of payment periods.
  • pv: Present value, or loan amount.
  • fv (optional): Future value, which defaults to 0 (loan paid off).
  • type (optional): When payments are due – 0 for end of the period (default), 1 for beginning.

C3/12 (Monthly interest rate): C3 contains the annual interest rate (6.35% in this example). Dividing C3 by 12 converts the annual rate into a monthly rate. Result: 0.0635/12 = 0.00529167 (or about 0.5292%).

C4 (Number of periods): C4 contains the total number of months for the loan (120 months, or 10 years). This represents the total payment periods.

-C2 (Loan amount as present value): C2 contains the loan amount ($9,500). We use the minus sign (-C2) because payments (outflow) are typically negative cash flows, while the loan amount (inflow) is positive.

The formula calculates that the borrower will pay $107.15 per month for 120 months to fully repay the loan of $9,500 with a 6.35% annual interest rate, compounded monthly.

Download the practice file.