How to Use Formula for Mortgage Principal and Interest in Excel

We’ll use the Excel PPMT function to find out the principal portion of the mortgage monthly payment. We’ll introduce 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 calculate 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 to be 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 do you calculate the principal portion of a loan payment in Excel?

Apply the PPMT formula to calculate the principal portion of the mortgage payment. Follow the steps below to calculate.

Steps:

Type the following formula in Cell C12. From the keyboard, press Enter.

=PPMT(C5/12,C9,C8,-C4,0,0)

We divided the interest rate by 12 because 5% is actually annual interest, and there are 12 months in a year.

The above formula will return the output below. Say that out of $1696.41 (the monthly payment of the mortgage), $1323.41 is the principal amount.

Excel IPMT Formula to Find Interest for a Mortgage Payment

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

Return Value

The IPMT function returns the interest amount as a number.

How do you calculate the interest of a mortgage payment in Excel?

Steps:

Type the below formula in Cell C5. Then hit Enter.

=IPMT(C5/12,C9,C8,-C4,0,0)

Get the result below. It indicates that $375 is the interest portion of our mortgage’s monthly payment ($1698.41).

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