How to Use Formula for 30 Year Fixed Mortgage in Excel (3 Methods) 

In this article, we will learn about Excel Formula for 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 Mortgage like Loan Amount, Annual Interest Rate, Loan Tenure, Total Number of Payments. Using this dataset we will find out per installment payment & total payment.

excel formula for 30 year fixed mortgage


Download Practice Workbook


3 Ways to Use Formula for 30 Years Fixed Mortgage in Excel

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 PMT formula 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)
Explanation: Here C5 denotes Rate of Interest in Percentage, 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

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

excel formula for 30 year fixed mortgage

Read More: Mortgage Calculations with Excel Formula (5 Examples)


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 Fixed Mortgage.

Steps:

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

excel formula for 30 year fixed mortgage

  • Now upon clicking ENTER you will find the out Total Payment amount.

excel formula for 30 year fixed mortgage


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 Years 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.

=C8*C9

  • Then click on Cell C7 and type the formula.
=C5/1200

=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.

excel formula for 30 year 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 Amount Per Installment.
  • Type the formula.
=C9*C4
  • Again, press ENTER and this formula will multiply the Principal Amount with the Multiplier & return the desired Amount Per Installment.

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

=C9*C4


Practice Workbook

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


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

Asif Khan Pranto

Asif Khan Pranto

Hello! I'm Asif here, currently working with Exceldemy as an Excel & VBA Content Developer. I write articles on Exceldemy about various ways to get out of Microsoft Excel's stuck conditions. My goal is to work with an organization which will give myself a chance to upgrade besides having a real impact on our surroundings. I'm passionate about travelling new communities & trekking. In my leisure period I usually read books. I've completed graduation in Mechanical Engineering & now I am pursuing Master of Development Studies to experience a new spectrum of knowledge. I’ve always been interested in research and development. So, here I will be posting articles related to Microsoft Excel. Hoped this may help you. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo