Excel formula: Estimate mortgage payment

Generic formula

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

Explanation

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.

How this formula works

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

Author

Dave Bruns

Related formulas

Payment for annuity

To solve for an annuity payment, you can use the PMT function. In the example shown C9 contains this formula: = PMT ( C6 , C7 , C4 , C5 , 0 ) Explanation An annuity is a series of equal cash flows, spaced equally in time. The goal in this example is...

Future value of annuity

To get the present value of an annuity, you can use the PV function. In the example shown, the formula in C7 is: = FV ( C5 , C6 , - C4 , 0 , 0 ) Explanation An annuity is a series of equal cash flows, spaced equally in time. In this example, a $5000...

Future value vs. Present value

This simple example shows how present value and future value are related. In the example shown, Years, Compounding periods, and Interest rate are linked in columns C and F like this: F5 = C9 F6 = C6 F7 = C7 F8 = C8 The formula to calculate future...

Present value of annuity

To get the present value of an annuity, you can use the PV function. In the example shown, the formula in C9 is: = PV ( C5 , C6 , C4 , 0 , 0 ) Explanation An annuity is a series of equal cash flows, spaced equally in time. In this example, an...

Related functions

Excel PMT Function

The Excel PMT function is a financial function that returns the periodic payment for a loan. You can use the NPER function to figure out payments for a loan, given the loan amount, number of periods, and interest rate.

Excel PV Function

The Excel PV function is a financial function that returns the present value of an investment. You can use the PV function to get the value in today's dollars of a series of future payments, assuming periodic, constant payments and a constant...

Excel FV Function

The Excel FV function is a financial function that returns the future value of an investment. You can use the FV function to get the future value of an investment assuming periodic, constant payments with a constant interest rate.

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.