Question:

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.