How to Calculate Monthly Mortgage Payment in Excel (2 Ways)

Get FREE Advanced Excel Exercises with Solutions!

A mortgage is a long-term loan made to assist you in purchasing a home. You have to pay interest to the lender in addition to the principal. The house and the surrounding property act as collateral. However, you need to be aware of more specifics if you want to buy a house. In this article, we will show you how to calculate monthly mortgage payment in Excel.


Monthly Mortgage Payment

What you pay each month to the lender to pay back your loan is known as your monthly payment. Your mortgage loan’s terms will determine how much you pay each month. This comprises both the loan’s interest and principal, which is the real amount owed.

A mortgage is a type of loan that can be used to purchase or maintain a home, a plot of land, or other mortgage lendings. The lender decides to pay the lender on a regular basis, typically in the form of a number of installments that are divided into interest and principal. The asset then serves as loan collateral. Borrowers must ensure they meet a number of requirements before buying a house, including minimal credit rating and down payments. Mortgage applications go through a lengthy underwriting process before settlement. The various mortgage options, including fixed-rate and monthly loans, will be determined by the lender’s needs.


How to Calculate Monthly Mortgage Payment in Excel: 2 Ways

Here are two convenient ways to calculate your monthly mortgage payment in Excel we will demonstrate to you. So, in the following two ways, you will learn how to calculate monthly mortgage payment in Excel utilizing the PMT function and applying VBA Code. Let’s suppose we have a sample data set for a monthly mortgage calculation.

Sample Data


1. Utilizing PMT Function to Calculate Monthly Mortgage Payment

The PMT function returns the periodic payment for a loan, which is a financial function. Given the loan amount, the number of repayment periods, and the interest rate, you can use the PMT function to calculate the loan’s installments. In this part, we will show you how to calculate monthly mortgage payment utilizing the PMT function in Excel.

Step 1:

  • At the start of this section, choose the C8 cell first to assess the down payment.
  • Here, write down the following formula.
=C5*C7

Sample Data

Step 2:

  • As a result, you will see the down payment in the C8 cell.

Sample Data

Step 3:

  • Similarly, select the C9 cell to evaluate the Loan Amount.
  • Then, note down the following formula here.
=C5-C8

Handy Ways to Calculate Monthly Mortgage Payment in Excel

Step 4:

  • Consequently, the loan amount will be visible in cell C9.

Handy Ways to Calculate Monthly Mortgage Payment in Excel

Step 5:

  • Now, pick out the C12 cell for showing the monthly mortgage payment.
  • And for this, write down the following formula with the PMT function in the C12 cell.
=PMT(C6/12,C10*12,-C9,0,0)
  • The syntax of the PMT function is shown below with arguments.
=PMT(rate, nper, pv, [fv], [type])
  • rate (required) – This argument represents the interest rate for the loan. Here, cell C6 shows the interest rate of the loan.
  • nper (required) – The number of payments for the loan and we apply this argument in cell C10.
  • pv (required) – This argument demonstrates the present value or the total value of all payments. Here, cell C9 conveys this argument with a negative sign.
  • fv (optional) – The amount of cash you want to have left over after the final payment. The future value of the loan is taken to be zero (0) if it is missing. For this argument, we put a default value as zero (0).
  • type (optional) -It identifies the deadline for payment. Here, zero (0) is for the end of the period and then one (1) is for the beginning of the period. Here, we choose zero (0) as for the end of the period.

Sample Data

Step 6:

  • Lastly, you will get the monthly mortgage payment in the C12 cell below.

Handy Ways to Calculate Monthly Mortgage Payment in Excel

Read More: How to Calculate Monthly Payment with APR in Excel


2. Applying VBA Code to Calculate Monthly Mortgage Payment in Excel

VBA is a programming language that performs a variety of tasks, and different types of users can use it for those tasks. Using the Alt + F11 keyboard shortcut, you can launch the VBA editor. In the last section, we will generate a VBA code that makes it very easy to calculate monthly mortgage payment in Excel.

Step 1:

  • Firstly, we will open the Developer tab.
  • Then, we will select the Visual Basic command.

Handy Ways to Calculate Monthly Mortgage Payment in Excel

Step 2:

  • Therefore, the Visual Basic window will open.
  • After that, from the Insert option, we will choose the new Module to write a VBA code.

Handy Ways to Calculate Monthly Mortgage Payment in Excel

Step 3:

  • Now, paste the following VBA code into the Module.
  • To run the program, click the “Run” button or press F5 here.
Sub Mortgage_Calculation()

'Declaring variables
Dim loan_amount As Long, interest_rate As Double, Nper As Integer

'Specifying the cell range for variables
loan_amount = Range("C9").Value
interest_rate = Range("C6").Value
Nper = Range("C10").Value

'Using the argument in function
interest_rate = interest_rate / 12
Nper = Nper * 12

'Specifying the cell range for mortgage amount
Range("C12").Value = -1 * WorksheetFunction.Pmt(interest_rate, Nper, loan_amount)

End Sub

Handy Ways to Calculate Monthly Mortgage Payment in Excel

VBA Code Breakdown

  • Firstly, we will call our subject as Sub Mortgage_Calculation().
  • Secondly, we declare the variable as Dim loan As Long, rate As Double, nper As Integer.
  • Then, we specify the cell range for every variables as loan = Range(“C9”).Value, rate = Range(“C6”).Value and, nper = Range(“C10”).Value.
  • Finally, we specify the cell range for monthly mortgage payments using the Pmt function  as Range(“C12”).Value = -1 * WorksheetFunction.Pmt(rate, nper, loan).

Step 4:

  • Finally, the C12 cell below conveys the monthly mortgage payment.

Handy Ways to Calculate Monthly Mortgage Payment in Excel

Read More: How to Calculate Monthly Payment on a Loan in Excel


Download Practice Workbook

You may download the following Excel workbook for better understanding and practice it by yourself.


Conclusion

In this article, I’ve covered 2 handy ways to calculate monthly mortgage payments in Excel. I sincerely hope you enjoyed and learned a lot from this article. If you have any questions, comments, or recommendations, kindly leave them in the comment section below.


Related Articles


<< Go Back to Calculate Payment in Excel | Excel for Finance | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Bishawajit Chakraborty
Bishawajit Chakraborty

Bishawajit Chakraborty, a Rajshahi University of Engineering & Technology graduate with a B.Sc. in Mechanical Engineering, has been associated with ExcelDemy since 2022. Presently, he is a content developer, specializing in Excel Power Query, Data Analysis and VBA. It is worth mentioning that he has authored more than 90 articles on VBA content development. His profound interest lies in the fields of data analytics and data science. He possesses expertise in VBA, Power BI, machine learning, and Python... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo