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