WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Mortgage Calculator in Excel

Mortgage Calculator in Excel

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

Mortgage Payment Loan Calculator in Excel

Mortgage Calculator in excel is not a built-in feature in excel but we can make our own mortgage calculator using some formulas, to make a mortgage calculator and calculate the amortization schedule we need to create our categories column for all the categories and data to be inserted and then we can use the formula for mortgage calculation in one cell, now for future, we can change the values and we have our mortgage calculator in excel.

Formula to Calculate Mortgage Payment in Excel

Like many other excel mortgage calculator, we have the formula to calculate the monthly EMI amount as well. In order to calculate the monthly EMI in excel, we have a built-in function called the PMT function.

PMT function includes 3 mandatory and 2 optional parameters.

PMT Formula

  • Rate: This is nothing but the interest rate applicable to the loan. If the interest is per annum, then you need to convert this to a monthly payment by just dividing the interest rate by 12.
  • Nper: This is simply the duration of the loan. In how many EMI’s you are going to clear the loan. For example, if the loan tenure is for 2 years, then loan tenure is 24 months.
  • Pv: This is the present value of the loan amount you are taking. Simply borrowing money, i.e., loan amount.

All the above three parameters are good enough to calculate the monthly EMI, but on top of this, we have two other optional parameters as well.

  • [FV]: This is the future value of the loan. If you ignore this default value will be zero.
  • [Type]: This is nothing but whether the loan repayment is at the start of the month or at the end of the month. If it is at the start, then the argument is 1, and if the payment is at the end of the month, then the argument will be zero (0). By default, the argument will be zero.

Mortgage Calculator in Excel

Examples

You can download this Mortgage Calculator Excel Template here – Mortgage Calculator Excel Template

Mr. A wants to buy a car, and the cost of the car is Rs 600,000. He approached the bank, and the bank agreed to sanction the loan based on the below conditions.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course
  • Down Payment: Rs 150,000
  • Loan Tenure: 3 years and above
  • Interest Rate: 15% PA.

Now Mr. A wants to evaluate his monthly savings and decide on the loan taking possibilities. In excel, using the PMT function, we can calculate the EMI.

  • Step 1: Enter all this information in Excel.

Mortgage Calculator Step 1

  • Step 2: Open PMT function in the B7 cell.

Mortgage Calculator Step 2

  • Step 3: The first thing is the rate, so interest rate select B6 cell. Since the interest rate is per annum, we need to convert it to month by dividing the same by 12.

Mortgage Calculator Step 3

  • Step 4: NPER is the number of payments to clear the loan. So loan tenure is 3 years i.e. 3*12 = 36 months.

Mortgage Calculator Step 4

  • Step 5: PV is nothing but the loan amount Mr. A taking from the bank. Since this amount is a credit given by the bank, we need to mention this amount is negative.

emi payment Step 5

  • Step 6: Close the bracket and hit enter. We have an EMI payment per month.

emi payment Step 6

So, in order to clear the loan of Rs 450,000 in 3 years at an interest rate of 15%, Mr. A has to pay Rs15,599 per month.

Things to Remember

  • You need to fill the only orange colored cell.
  • The loan amortization table will show the breakup of the Principal Amount and Interest Amount each month.
  • It will also show you the extra amount you are paying as a percentage.

Recommended Articles

This has been a guide to Mortgage Calculator in Excel. Here we discuss how to prepare a mortgage loan payment calculator using PMT Formula along with practical examples and a downloadable excel template. You may learn more about excel from the following articles –

  • Excel PMT Formula
  • Formula of Mortgage
  • Mortgage APR vs. Interest Rate
  • PPMT in Excel
123 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download Mortgage Calculator Excel Template

Special Offer - All in One Excel VBA Bundle (35 Courses with Projects) View More