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

Posted on by Asif Khan Pranto

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.
=PMT(C5/C6,C8,C4,0)
Explanation: 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.

excel formula for 30 year fixed mortgage

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

excel formula for 30 year 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.
=C8*C9

excel formula for 30 year fixed mortgage

  • Click ENTER to find the Total Payment amount.

excel formula for 30 year fixed mortgage

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.

=C8*C9

  • Click on Cell C7 and enter the formula.
=C5/1200

=C5/1200

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

  • Select Cell C8 and enter the formula.
=(1+C7)^C6
  • Press ENTER and this will compound the interest rate for 30-Years span of Fixed Mortgage.

excel formula for 30 year fixed mortgage

  • Press ENTER to get the value.

  • Select Cell C9 to calculate the Multiplier.
  • Enter the Formula.
=(C8*C7)/(C8-1)
  • 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.
=C9*C4
  • 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.

=C9*C4

Download Practice Workbook

Further Readings

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

Get FREE Advanced Excel Exercises with Solutions!

Leave a Reply

Your email address will not be published. Required fields are marked *

Advanced Excel Exercises with Solutions PDF