Hosne Ara
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)
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*C7Conclusion
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
- Interest Only Mortgage Calculator with Excel Formula (A Detailed Analysis)
- Loan Amortization Schedule with Variable Interest Rate in Excel
- Excel Interest Only Amortization Schedule with Balloon Payment Calculator
- How to Use Formula for Car Loan Amortization in Excel (with Quick Steps)
- Mortgage Repayment Calculator with Offset Account and Extra Payments in Excel
- How to Use Formula for 30 Year Fixed Mortgage in Excel (3 Methods)
- Creation of a Mortgage Calculator with Taxes and Insurance in Excel
