How to Use Formula for 30 Year Fixed Mortgage in Excel?

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

Steps:

• Select the Cell where you want to Calculate your per installment Payment. We have selected Cell C9.
• Enter the following PMT formula.

• Press ENTER and it will return the amount per installment payment of Fixed Mortgage.

Method 2 – Calculating Total Payment for 30 Years Fixed Mortgage

Steps:

• Select a Cell where you want to see your total payment. We have selected Cell C10.
• We will use a Multiplication formula to calculate the Total payment.
• Multiply the value of Payment Per Installment with the Total Number of Payment.
• We have multiplied Cell C8 & C9.

• Click ENTER to find the Total Payment amount.

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

Steps:

• Enter the Annual Interest Rate in Cell C5 using only numbers, do not use the Percentage format.

• Click on Cell C7 and enter the formula.

• Press ENTER and this returns the Monthly Interest Rate in decimals.

• Select Cell C8 and enter the formula.

• Press ENTER and this will compound the interest rate for 30-Years span of Fixed Mortgage.

• Press ENTER to get the value.

• Select Cell C9 to calculate the Multiplier.
• Enter the Formula.

• Press ENTER and it will return a Multiplier in Cell C9.

• Select Cell C10 where you want to calculate the Amount Per Installment.
• Enter the formula.

• Press ENTER and this will multiply the Principal Amount with the Multiplier & return the desired Amount Per Installment.

• Use the Currency format to turn Cell C10 value into Currency & see the Amount Per Installment for 30 Year Fixed Mortgage.

Download Practice Workbook

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