# Mortgage Calculations with an Excel Formula – 5 Examples

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

## Excel Mortgage Formula: Knowledge Hub

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

Get FREE Advanced Excel Exercises with Solutions!
Sabrina Ayon

Sabrina Ayon, a Computer Science and Engineering graduate from United International University, has been an integral part of the ExcelDemy project for two years. She authored 150+ articles, excelling in instructing through visually engaging Excel tutorials. With a passion for teaching, Sabrina conducted sessions on Excel VBA, sharing her knowledge and insights with others. Currently holding the position of Project Manager for the ExcelDemy Visual Development Project, she oversees various aspects of the project, ensuring its smooth operation... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF