How to Use Formula for Mortgage Principal and Interest in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.


Download Practice Workbook

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


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)

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

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.

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.


Similar Readings

Hosne Ara
Hosne Ara

Hi, This is Hosne Ara. Currently, I do write for ExcelDemy. I have a long experience working with different industries and I have seen how vast the scope of Microsoft Excel is. So, eventually, I started to write articles on Excel and VBA. Basically, my articles are targeted to help people who are working in Excel. By profession, I am an Engineer. Materials and Metallurgical Engineering is my major. Besides, I am a certified Project Manager (PMP) too. I have worked with Power Plant and IT industry earlier. As a person, I am detail-oriented and love doing research. Establishing a greener world is one of my mottos.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo