**Download Practice Workbook**

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.

## Frequently Asked Questions

### 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**