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.


  • First, type the following formula in Cell C12. After that, from the keyboard, press Enter.

Calculating Principal Portion of a Loan Payment in Excel

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.


  • Initially, type the below formula in Cell C5. Then hit Enter.

Calculate Interest of a Mortgage Payment in Excel

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

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:


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.

Similar Readings

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

Get FREE Advanced Excel Exercises with Solutions!
Hosne Ara
Hosne Ara

Hosne Ara is a materials and metallurgical engineer who loves exploring Excel and VBA programming. To her, programming is like a time-saving superhero for dealing with data, files, and the internet. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. With a B. Sc. in Materials and Metallurgical Engineering from Bangladesh University of Engineering and Technology, she's shifted gears and now works as a content developer. In this role, she... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF