In this article, we will learn about the Excel Formula for a 30-Year Fixed Mortgage. Here we will use several formulas related to 30 Year Fixed Mortgage. We will learn how to find out the amount to be paid per installment & total Mortgage amount.

Here we have a dataset having general information on Fixed Mortgages like Loan Amount, Annual Interest Rate, Loan Tenure, and Total Number of Payments. Using this dataset we will find out per installment payment & total payment.

## How to Use Formula for 30 Year Fixed Mortgage in Excel: 3 Ways

### Method 1. Calculating Per Installment Payment for 30 Years Fixed Mortgage Using PMT Formula

In this method, we will see how to **Calculate **the per installment payment of a **Fixed Mortgage**. We will use the **PMT** **function** to calculate this.

**Steps:**

- Select the
**Cell**where you want to**Calculate**your per installment Payment. - I have selected
**Cell C9**. - Here type the
**PMT formula**.

**=PMT(C5/C6,C8,C4,0)**

**C5**denotes the

**Rate of Interest**in

**Percentage**, and

**C6**is the

**installment Per Year**.

**C8**indicates the

**Total Number of installments**.

**C4**is the

**Total Principal loan amount**.

**Zero**is the

**function argument**.

- Now upon pressing
**ENTER**it will return the amount per installment payment of**Fixed Mortgage**.

### Method 2. Calculating Total Payment for 30 Years Fixed Mortgage

In this part, I will show you how to Calculate the total payment for **30 Years of Fixed Mortgage**.

**Steps:**

- First, select a
**Cell**where you want to see your**total payment**. - I have selected
**Cell C10**. - Now we will use a simple Multiplication formula to calculate the
**Total payment**. - Multiply the value of
**Payment Per Installmen**t with the**Total Number of Payment**. - Here I have multiplied
**Cell C8**&**C9**having those data.

`=C8*C9`

- Now upon clicking
**ENTER**, you will find the**Total Payment**amount.

### Method 3. Find out the Payment Per Installment for 30 Years Fixed Mortgage Manually

In this method, we will find out the payment per installment for a 30-year **FixedÂ Mortgage** using a series of Formulas.

**Steps:**

- To find out manually remember to write down the
**Annual Interest Rate**in**Cell C5**using**only numbers**, do**not**use the**Percentage format**.

- Then click on
**Cell C7**and type the formula.

`=C5/1200`

- Press
**ENTER**and this formula returns the**Monthly Interest Rate**in decimals.

- Now select
**Cell C8**and Type the formula.

`=(1+C7)^C6`

- Now, press
**ENTER,**and this will**compound**the interest rate for**30-Years**span of**Fixed Mortgage**.

- Upon pressing
**ENTER**we will get the value.

- Now select
**Cell C9**to calculate the**Multiplier**. - Type the Formula.

`=(C8*C7)/(C8-1)`

- Here, press
**ENTER**, and this formula will return a Multiplier in**Cell C9**.

- Finally, select
**Cell C10**where you want to calculate the**Amount Per Installment**. - Type the formula.

`=C9*C4`

- Again, press
**ENTER**and this formula will multiply the**Principal Amoun**t with the**Multiplier**& return the desired**Amount Per Installment**.

- Now using the
**Currency**format turn**Cell C10**value into**Currency**& see the**Amount Per Installmen**t for**30 Year Fixed Mortgage**.

## Practice Workbook

We have provided you with a practice worksheet. Do it yourself.

**Download Practice Workbook **

## Conclusion

In the article above we have learned Excel Formula for 30 Year Fixed Mortgage. Now you can easily calculate your loan amounts. If you have any further questions, leave us a comment.

**Further Readings**

- How to Use Formula for Mortgage Principal and Interest in Excel
- How to Use Formula for Car Loan Amortization in Excel

**<< Go Back to Excel Mortgage Formula** **| ****Excel Formulas for Finance**** | ****Excel for Finance**** | ****Learn Excel**