PMT Function – Mortgage Calculator in Excel

PMT Function – Create a Mortgage Calculator

The PMT function calculates the payment for a loan that has constant payments and a constant interest rate. This formula will not work with a flexible interest rate.

This is the following formula for the PMT Function:

PMT( rate, nper, pv, [fv], [type] )

Rate: Interest Rate per period

Nper: The number of periods

Pv: Present value of loan/investment

Fv: Future value of the loan/investment – This is optional. If left blank, it will default to zero.

Type: Defines whether the payment is made at the start or end of the period. If left blank, defaults to zero.

0 – Payment is made at the end of the period
1 – Payment is made at the beginning of the period

We show how to use the PMT function in the example below. Download the sample file to follow along.

Mortgage Calculator – Download File

PMT Function

In the above example, we are taking out a loan for $225,000 with a 30 year fixed interest rate of 3.75% (Very good rate ). Over 30 years, we end up paying $375,123.63, which $150,123.63 is interest.

The above calculation does not account for Homeowners Insurance or local/state taxes.

Was this article helpful?

Related Articles

Leave a Reply