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.
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
- How to Use Formula for Car Loan Amortization in Excel
- How to Use Formula for 30 Year Fixed Mortgage in Excel
<< Go Back to Excel Mortgage Formula | Excel Formulas for Finance | Excel for Finance | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!