How to calculate monthly interest mortgage payment in Excel?


Calculate your monthly interest mortgage payment in Excel using the PMT function. You will need to know the loan amount, the interest rate and the number of periods to pay the mortgage.

The Excel PMT function takes that the interest rate does not fluctuate, and monthly payments remain the same throughout the lifetime of the loan.

Steps for Calculating Monthly Principal and Interest Mortgage Payment in Excel

For example, the loan balance is $200,000 at 5.75% yearly interest rate. How big is your PI (principal and interest) payment?

  1. Type =PMT(5.75%/12,30*12,200000) in a worksheet cell.
  2. 5.75%/12 will give the monthly rate.
  3. The loan is scheduled to amortize in 30*12 is 360 monthly payments.
  4. After pressing the ENTER button, the number -1,167.15 will appear in the selected cell. It will be in red and with a minus sign in front as this is the monthly amount due.

In a very similar manner, the Excel PMT function can be used to calculate monthly payment on a credit card or a car loan, or any fixed rate loan monthly payment.

Mortgage rates hit their lowest since 1955. Ask the home loan experts we recommend Quicken Loans how to take advantage of them.
Was this Mortgage QnA helpful?
Not at all
  • Currently 2.9/5 Stars
  • 1
  • 2
  • 3
  • 4
  • 5
Add to this Answer

Mortgage QnA is not a common forum. We have special rules:

  • Post no questions here. To ask a question, click the Ask a Question link
  • We will not publish answers that include any form of advertising
  • Add your answer only if it will contrubute to the quality of this Mortgage QnA and help future readers
If you have trouble reading the code, click on the code itself to generate a new random code. Verification Code Above:
Bookmark and share this QnA: