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

How to Calculate the 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 the one for the PPMT function (explained earlier in this article).

Return Value

The IPMT function returns the interest amount as a number.

How to Calculate the 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.

Download Practice Workbook

You can download the practice workbook that we have used to prepare this article.

Formula for Mortgage Principal and Interest.xlsx

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.