How to Calculate Monthly Payments for an Amortization Table

by Grant D. McKenzie ; Updated July 27, 2017

Items you will need

  • Loan value
  • Loan term
  • Loan APR
  • Calculator

In Microsoft Excel, the function "PMT" automatically calculates the payment needed to create an amortization table. The function uses the total mortgage value, the interest rate and the term of the loan in a standard algebraic formula to obtain the value for the payment. The algebraic function can be evaluated by hand as long as the three variables are available. The term of the loan must be expressed in months, the interest rate must be a monthly value, and the value must be the total initial loan amount.

Step 1

Convert the loan APR to a monthly rate by dividing it by 12. If the APR is 6 perent, the monthly value would be 0.06/12, or 0.005.

Step 2

Convert the loan term to months by multiplying the term by 12. For a 15-year mortgage, the number of months is 15*12, or 180.

Step 3

Put the value of the loan and the other values into the formula: P = V[n(1+n)^t]/[(1+n)^t-1] t = term in months n = monthly interest rate V = loan value P = monthly payment For a $150,000 mortgage: P = 150,000[0.005(1+0.005)^180]/[(1+0.005)^180-1]

Step 4

Use a scientific calculator to obtain the payment. P = 150,000(0.0123/1.454) = $1268.91

Tips

  • You can simplify the calculations by solving the numerator and the denominator separately, and then multiplying the loan value.

bibliography-icon icon for annotation tool Cite this Article