We’ll use the Excel **PPMT** function to find out the principal portion of the mortgage monthly payment. We’ll introduce 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 calculate 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 to be 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 do you calculate the principal portion of a loan payment in Excel?

Apply the **PPMT** formula to calculate the principal portion of the mortgage payment. Follow the steps below to calculate.

**Steps:**

- Type the following formula in
**Cell C12**. From the keyboard, press**Enter**.

`=PPMT(C5/12,C9,C8,-C4,0,0)`

We divided the interest rate by **12** because 5% is actually annual interest, and there are** 12** months in a year.

- The above formula will return the output below. Say that out of
**$1696.41**(the monthly payment of the mortgage),**$1323.41**is the principal amount.

## Excel IPMT Formula to Find Interest for a Mortgage Payment

### 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 those for the **PPMT** function (explained earlier in this article).

**Return Value**

The **IPMT** function returns the interest amount as a number.

### How do you calculate the interest of a mortgage payment in Excel?

**Steps:**

- Type the below formula in
**Cell C5**. Then hit**Enter**.

`=IPMT(C5/12,C9,C8,-C4,0,0)`

- Get the result below. It indicates that $375 is the interest portion of our mortgage’s monthly payment ($1698.41).

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

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