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.
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.