# How to Use Formula for Mortgage Principal and Interest in Excel

In this article, I will discuss how you can calculate the principal and interest for a mortgage using a formula in Microsoft Excel. Suppose a \$90,000 home at 5% interest with a 5-year mortgage. We already calculated the monthly payment (here, \$1698.41) for this mortgage using the PMT function. Now, if you want to know the principal and interest amount of this monthly payment, excel functions can help. In this article, I will calculate the principal of the mortgage monthly payment using the PPMT function. On the other hand, I will use the IPMT function to find out the Interest portion of the monthly payment. ## Excel PPMT Formula to Calculate Principal for a Mortgage Payment

As I have mentioned earlier, I will use the excel PPMT function to find out the principal portion of the mortgage monthly payment. Let’s get introduced to the PPMT function first.

### Introduction to Excel PPMT Function

• Summary

The PPMT function calculates the payment on the principle for a given investment based on periodic, constant payments and a constant interest rate.

• Syntax

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

• Arguments
Argument Requirement Explanations
rate Required The annual interest rate of the mortgage (here, 5%).
per Required The period we want to work with. Here, we will enter per as 1 as we are calculating the principal amount for the first loan payment.
nper Required The total number of payments per mortgage (here, 60).
pv Required Present value; the principal amount (here \$90,000).
[fv] Optional The future value; is considered as 0.
[type] Optional This indicates when the payment is due; usually entered as 0 or 1.
• Return Value

The PPMT function returns the principal amount as a number.

### Calculating Principal Portion of a Loan Payment in Excel

Now, I will apply the PPMT formula to calculate the principal portion of the mortgage payment. Follow the below steps to do the calculation.

Steps:

• First, type the following formula in Cell C12. After that, from the keyboard, press Enter.
`=PPMT(C5/12,C9,C8,-C4,0,0)` Here, we have divided the interest rate by 12. This is because 5% is actually annual interest and there are 12 months in a year. • As a result, the above formula will return the below output. From this result, we can say that out of \$1696.41 (monthly payment of the mortgage), \$1323.41 is the principal amount.

## Excel IPMT Formula to Find Interest for a Mortgage Payment

This time we will calculate the Interest portion of the mortgage payment using the IPMT formula.

### Introduction to Excel IPMT Function

• Summary

The IPMT function calculates interest payment for a given investment based on periodic, constant payments and a constant interest rate.

• Syntax

IPMT(rate, per, nper, pv, [fv], [type])

• Arguments

All the arguments mentioned here are similar to that one for the PPMT function (explained earlier in this article).

• Return Value

The IPMT function returns the interest amount as a number.

### Calculate Interest of a Mortgage Payment in Excel

To find out the interest portion of our mortgage payment, follow the below instructions.

Steps:

• Initially, type the below formula in Cell C5. Then hit Enter.
`=IPMT(C5/12,C9,C8,-C4,0,0)` • Consequently, we will get the below result. The result indicates that \$375 is the interest portion of the monthly payment (\$1698.41) of our mortgage. Read More: Mortgage Calculations with Excel Formula (5 Examples)

## Things to Remember

• For ease of calculation, we have supplied pv (Loan Amount) as a negative number. You can enter pv as it is. In that case, the ultimate result will be negative because money will be subtracted from your bank account.
• While calculating the nper, multiply the loan period by the number of payments per year. In my case, I have used the below formula to get nper:
`=C6*C7`

## Conclusion

In the above article, I have tried to discuss several methods to calculate mortgage principal and interest using a formula in excel. Hopefully, these methods and explanations will be enough to solve your problems. Please let me know if you have any queries.  