# How to Create a Mortgage Calculator in Excel – 2 Methods

The dataset showcases Market Price of Asset, Total Loan Amount, Down Payment, Loan Repayment Tenure, and Rate of Interest.

### Method 1 – Use the PMT Function to Create a Mortgage Calculator

The formula of the PMT function is

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

Where

• Rate (required) – the constant interest rate per period (a percentage or decimal number).
• Nper (required) – the number of payments for the loan.
• Pv (required) – the present value: the borrowed amount.
• Fv (optional) – the future value after the last payment. If omitted, the future value of the loan is assumed to be zero.
• Type (optional) – specifies when payments are due.

• Select D10 and use the following formula.
• Press Enter.

`=PMT(D9/12,D8*12,-D6)`

### Method 2 – Applying the PPMT Function to Generate a Mortgage Calculator

The syntax of the PPMT function is

`PPMT(rate, per, nper, pv, [fv], [type])`

A value for Period was added.

• Select D11 and use the following formula.
• Press Enter.

`=PPMT(D10/12,D9,D8*12,-D6)`

## How to Create a Loan Amortization Schedule in Excel?

Combine the IPMT function and arithmetic calculations.

Create an amortization table with: Total Loan Amount, Loan Repayment Tenure, Payments Per Year, and Annual Rate of Interest.

Calculate the Payment amount using the PMT function.

• Enter the following function in C11 and press Enter.

`=PMT(\$D\$8/\$D\$7,\$D\$6*\$D\$7,\$D\$5)`

• Drag the formula to column C.

To calculate the Principal, use the PPMT function.

• Select D11, enter the following formula, and drag it down.

`=PPMT(\$D\$8/\$D\$7,B11,\$D\$6*\$D\$7,\$D\$5)`

To calculate the interest of each periodic payment, use the IPMTÂ function.

`=IPMT(\$D\$8/\$D\$7,B11,\$D\$6*\$D\$7,\$D\$5)`

• Use the formula in column E.

To calculate the balance for each period, use two different formulas.

• To find the balance after the first payment in C11, add the loan amount (D5) and the principal of the first period (D11).

`=D5+D11`

• To find the balance after the second period, sum the previous balance and the principal of the second period.

`=F11+D12`

• Drag down the formula.

It will take 24 months to pay the loan.

## Which Things Should You Remember?

• These examples are applicable to compound interest.

### 1. How can I calculate the remaining mortgage balance after making payments in Excel?

Determine the remaining mortgage balance by deducting the principal paid from the original loan amount. By eliminating the interest component of the payment from the entire payment, you can determine the amount of the principal payment. Calculate the principal payment using the PPMT function.

### 2. How can I calculate the effective interest rate of a loan in Excel?

Use the RATE function to calculate the effective interest rate. The syntax is “=RATE(nper, pmt, pv, [fv], [type])“: “nper” is the total number of payment periods, “pmt” is the monthly payment, “pv” is the present value or loan amount, “[fv]” is the future value (optional), “[type]” indicates whether payments are due at the beginning or end of the period (optional).

### 3. Can I calculate loans with irregular payment amounts and schedules in Excel?

Yes, you can create a unique amortization schedule that takes into account various payment amounts and due dates.

## Excel Mortgage Calculator: Knowledge Hub

<< Go Back to Finance Template |Â Excel Templates

Get FREE Advanced Excel Exercises with Solutions!
Beni Yameen Jonayed

Beni Yameen Jonayed, holding a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, works as an Excel & VBA Content Developer at ExcelDemy. His deep interest in research and innovation extends to his passion for Excel. In his role, Jonayed not only skillfully addresses challenges but also shows enthusiasm and expertise in handling tough situations with finesse, emphasizing his dedicated commitment to delivering outstanding content. He is interested in Data Analysis with MS... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF