Summary

To calculate an estimated mortgage payment in Excel with a formula, you can use the PMT function. In the example shown, the formula in F4 is:

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

When assumptions in column C are changed, the estimated payment will recalculate automatically.

Generic formula

=PMT(rate/12,term*12,-C9)

Explanation 

The PMT function calculates the required payment for an annuity based on fixed periodic payments and a constant interest rate. An annuity is a series of equal cash flows, spaced equally in time. A mortgage is an example of an annuity.

To calculate the monthly payment with PMT, you must provide an interest rate, the number of periods, and a present value, which is the loan amount. In the example shown, the PMT function is configured like this:

  • rate = C5/12
  • nper = C6*12
  • pv = -C9

Because mortgage rates are annual, and terms are stated in years, the arguments for rate and periods are adjusted in this example. The rate is divided by 12 to get a monthly rate, and the term in years is multiplied by 12 to get the total number of monthly payments (nper). The present value (pv) comes from C9 which holds the loan amount. We use a minus operator to make this value negative, since a loan represents money owed, and is a cash outflow.

Note: When using PMT, always be consistent with the units provided for rate and periods.

Other formulas

The down payment amount in C8 is calculated with:

=C4*C7

The loan amount in C9 is calculated with:

=C4-C8
Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.