**Example 1-Â Formula for the Monthly Mortgage Payment in Excel**

Consider that, you want to start a business and take a loan: $150,000 (**C7**).

The annual interest rate**Â **is 6% (**C8)**, the duration is 2 years (**C9**) and the loan is paid monthly.

Use **the PMT function**.

**STEPS:**

- Select a cell to calculate the monthly payment. Here,
**C13**.

- Enter the formula.

`=PMT(C8/C10,C11,C7,0)`

- Press
**Enter**.

- The monthly mortgage payment is displayed.

**2. Excel Mortgage Formula to Fixed Periodic Payment**

The loan amount is $150,000 (**C7**).

The annual interest rate**Â **is 6% (**C8)**, the duration is 2 years (**C9**) and the total number of months is 12 (**C10**).

Calculate the fixed periodic payment:

**STEPS:**

- Select a cell to calculate the monthly payment. Here,
**C12**.

- The generic formula for a fixed periodic payment is:

`=loan amount((rate of interest/number of payment per year)*(1+rate of interest/number of payment per year)^(number of payment per year*life loan))/((1+rate of interest/number of payment per year)^(number of payment per year*life loan)-1)`

- Enter the formula.

`=C7*((C8/C10)*(1+C8/C10)^(C10*C9))/((1+C8/C10)^(C10*C9)-1)`

- Press
**Enter**.

- The fixed periodic payment is displayed.

**3. Find Out Excel Outstanding Loan Balance**

To find the outstanding loan balance, slight modifications were made to the dataset: the loan amount was decreased and the duration of the loan increased.

Calculate the outstanding loan amount with one intermediate period.

**STEPS:**

- Select a cell to see the result. Here,
**C13**.

- The generic formula for a fixed periodic payment is:

`=loan amount((rate of interest/number of payment per year)*(1+rate of interest/number of payment per year)^(number of payment per year*life loan))-((1+rate of interest/number of payment per year)^(number of payment per year*life loan)-1))`

- Enter the formula.

`=(C7*((1+C8/C10)^(C10*C9)-(1+C8/C10)^(C10*C11))/((1+C8/C10)^(C10*C9)-1))`

- Press
**Enter**.

- The result is displayed in
**C13**.

**Example 4 – Using a Mortgage Formula to Calculate the Monthly Payments for a Credit Card Debt**

Use **the PMT function**.

Consider the due balance and the annual interest rate in **C7 **and** C8**.

**STEPS:**

- Select
**C10**.

- Enter the following formula:

`=PMT(C8/12,2*12,C7)`

- Press
**Enter**.

This is the output.

**Example 5 – Using an Excel Mortgage Formula to calculate the Principal Amount Repayment in the 24th Month**

The principal to be repaid in the 24th month can be computed by deducting the outstanding balance after two years from the outstanding balance after 23 months.

**STEPS:**

- Calculate the loan balance in period 1. Select
**C14**.

- The generic formula for a fixed periodic payment is:

`=loan amount((1+rate of interest/number of payment per year)^(number of payment per year*life loan))-((1+rate of interest/number of payment per year)^(number of payment per year*intermediate period 2))/(((1+rate of interest/number of payment per year)^(number of payment per year*life loan)-1)`

- Enter the formula:

`=(C7*((1+C8/C10)^(C10*C9)-(1+C8/C10)^(C10*C11))/((1+C8/C10)^(C10*C9)-1))`

- Press
**Enter**. - The generic formula for a fixed periodic payment is:

`=loan amount((1+rate of interest/number of payment per year)^(number of payment per year*life loan))-((1+rate of interest/number of payment per year)^(intermediate period 2))/((1+rate of interest/number of payment per year)^(number of payment per year*life loan)-1)`

- For the loan balance in period 2, the formula is:

`=(C7*((1+C8/C10)^(C10*C9)-(1+C8/C10)^(C12))/((1+C8/C10)^(C10*C9)-1))`

- Press
**Enter**.

- Subtract the loan balance in period 1 from the loan balance in period 2. The formula will be:

`=C15-C14`

- Press
**Enter**. - You will see the result.

**Download Practice Workbook**

Download the workbook and practice.

## Excel Mortgage Formula: Knowledge Hub

- How to Use Formula for 30 Year Fixed Mortgage in Excel?
- How to Use Formula for Mortgage Principal and Interest in Excel?
- How to Use Formula for Car Loan Amortization in Excel?

**<< Go Back to ****Excel Formulas for Finance**** | ****Excel for Finance**** | ****Learn Excel**