Excel Functions Tutorials
- Excel Tips
- Excel vs Access
- Excel Rows vs Columns
- Apple Numbers vs Excel
- 3D Reference in Excel
- Absolute Reference in Excel
- Mixed References in Excel
- Excel Reference to Another Sheet
- Array Formulas in Excel
- Arrays in Excel VBA
- Auto Numbering in Excel
- AutoFit in Excel
- AutoCorrect in Excel
- AutoSave in Excel
- AutoRecover in Excel
- Bullet Points in Excel
- Break Links in Excel
- Barcode in Excel
- Change Case in Excel
- CAGR Formula in Excel
- Calculate Age in Excel
- Calculate Percentage in Excel Formula
- Cell Reference in Excel
- Checklist in Excel
- Circular Reference in Excel
- Column Sort in Excel
- Column Lock in Excel
- Move Columns in Excel
- Custom List in Excel
- Consolidate in Excel
- Combine Cells in Excel
- Compare Two Columns in Excel
- Compare and Match Columns in Excel
- Compound Interest Formula in Excel
- Convert Columns to Rows in Excel
- Convert Date to Text in Excel
- Convert Numbers to Text in Excel
- Convert Text to Numbers in Excel
- Convert Excel to CSV
- Count Characters in Excel
- Count Rows in Excel
- Count Unique Values in Excel
- Countif not Blank in Excel
- Create Templates in Excel
- Family Tree in Excel Template
- Custom Number Format in Excel
- Delete Row Shortcut in Excel
- Divide in Excel Formula
- Drop Down List in Excel
- Dynamic Tables in Excel
- Dashboard in Excel
- KPI Dashboard in Excel
- Date to Text in Excel
- Date Format in Excel
- Database in Excel
- Delta Symbol in Excel
- $ Symbol in Excel
- Excel Column to Number
- Edit Drop-Down List in Excel
- Equations in Excel
- Exponents in Excel
- Excel Extensions
- Excel Translate
- Excel Not Responding
- Excel Find and Replace
- Find and Select in Excel
- Excel Subtraction Formula
- Excel Formula for Grade
- Excel as Calculator
- Excel Formula Not Working (Updating)
- Excel Table Styles & Formats
- Excel vs Google Sheets
- External Links in Excel
- Excel Alternate Row Color
- Excel Worksheet Tab
- Extract Number from String Excel
- Evaluate Formula in Excel
- Find Duplicates in Excel
- Finding Links in Excel
- Filter Shortcut in Excel
- Formatting in Excel
- Format Numbers to Millions & Thousands in Excel
- Format Phone Numbers in Excel
- Formula Errors in Excel
- Fractions in Excel
- Frequency Distribution in Excel
- Group in Excel
- Group Worksheets in Excel
- Group Columns in Excel
- Hide Formula in Excel
- Hiding a Column in Excel
- Highlight Every Other Row in Excel
- Highlight Duplicates in Excel
- How to Create a Formula in Excel?
- How to Create an Excel Spreadsheet?
- How to Add Text in Excel Formula?
- How to Create Dashboard in Excel?
- How to Copy Sheet in Excel?
- How to Delete Pivot Table?
- How to Calculate Percentage Increase in Excel?
- How to Multiply in Excel Formula?
- How to Unhide Columns in Excel?
- Insert Date in Excel
- Insert Calendar in Excel
- Import Data into Excel
- Insert Comment in Excel
- Insert Hyperlinks in Excel
- Insert Multiple Rows in Excel
- Insert Row Shortcut in Excel
- Insert New Worksheet in Excel
- Insert (Embed) an Object in Excel
- Insert Image in Excel Cell
- Insert Page Break in Excel
- Line Breaks in Excel
- Linear Interpolation in Excel
- Leading Zeros in Excel
- Last Day of the Month in Excel
- Logical Operators in Excel
- Lookup Table in Excel
- Mortgage Calculator in Excel
- Moving Average in Excel
- Not Equal to in Excel
- Numbering in Excel
- Name Manager in Excel
- Page Numbers in Excel
- Page Break in Excel
- Personal Budget Template in Excel
- Project Management Template in Excel
- Percentage Difference in Excel (Increase / Decrease)
- Pivot Table Calculated Field & Formula
- Pivot Table Sort
- Pivot Table From Multiple Sheets
- Print Comments in Excel
- Print Excel Gridlines
- Print in Excel
- Print Preview in Excel
- Print Area in Excel
- Print Titles in Excel
- Print Labels From Excel
- Project Timeline in Excel
- Protect Sheet in Excel
- Ratio in Excel Formula
- Random Numbers in Excel
- Randomize List in Excel
- Refresh Pivot Table in Excel
- Relative References in Excel
- Remove Blank Rows in Excel
- Remove Duplicates in Excel
- Remove Duplicates from Excel Column
- Remove Hyperlinks in Excel
- Remove Space in Excel
- Remove Leading Spaces in Excel
- Remove Watermark in Excel
- Row Limit in Excel
- Rows and Columns in Excel
- Rows to Columns in Excel
- Row Header in Excel
- Search in Excel
- Search For Text in Excel
- Share an Excel Workbook
- Shortcut to Merge Cells in Excel
- Show Formula in Excel
- Split Cells in Excel
- Separate Text in Excel
- Strikethrough in Excel
- Strikethrough Text in Excel
- Sum by Color in Excel
- Subscript in Excel
- Superscript in Excel
- Themes in Excel
- Timesheet Calculator in Excel
- Top 20 Keyboard Shortcuts in Excel
- Unmerge Cells in Excel
- Uppercase in Excel
- Word Count in Excel
- Word Cloud in Excel
- Watermark in Excel
- Weighted Average in Excel
- Wildcard in Excel
- Financial Functions in Excel (17+)
- Logical Functions in Excel (15+)
- TEXT Functions in Excel (29+)
- Lookup Reference in Excel (44+)
- Maths Functions in Excel (52+)
- Date and Time Function in Excel (22+)
- Statistical Function in Excel (50+)
- Information Functions in Excel (5+)
- Excel Charts (48+)
- Excel Tools (98+)
- VBA (162+)
- Power Bi (35+)
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.
Mortgage Payment Loan Calculator in Excel
The mortgage is nothing but your car loan, home loan, bike loan, or any other loan taken. If you search on google about mortgage loan calculator in excel you will find plenty of online websites which can help in this.
However, in this article, I will show you how to build a mortgage loan calculator template in excel. This article covers all the things you need to know about loan calculator with a ready excel template at your plate.
Factors Involved in Mortgage Payment Calculator in Excel
Assume you are taking a loan to buy your dream car, you need to look into several factors which affect your monthly EMI. Every loan includes the rate of interest, loan tenure, loan amount, and down payment (if any). There are financial institutions who give a loan without down payment as well but at a higher interest rate. For the better understanding look at the below example.
Mr. Peter wants to buy a bike which is costing him 85,000 overall. But he is short of money and decided to go for a loan with one of the bank. Bank decided to give 100% loan for the bike. Loan duration is for 2 years at an interest rate of 18% per anum.
- Loan Amount = 85000
- Interest Rate = 18% PA.
Note: When the interest rate is per anum we need to convert it to per month by dividing the interest rate by 12. In this case interest rate is 18/12 i.e. 1.5% per anum.
- Loan Duration = 2 years i.e. 2*12 = 24 months.
These are the basic factors involved in any kind of excel mortgage loan calculator. Using this information we can calculate monthly EMI amount to decide whether we need to go for a loan or not.
Mortgage Payment Calculator in Excel Formula
Like many other excel mortgage calculator formulas, 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 PMT function.
PMT function includes 3 mandatory and 2 optional parameters.
- Rate: This is nothing but the interest rate applicable for the loan. If the interest is per anum then you need to convert this to 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 loan amount you are taking. Simply the borrowing money i.e. loan mount.
All the above three parameters are good enough to calculate the monthly EMI but on top 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.
Examples of Mortgage Payment Calculator
Mr. A wants to buy a car and the cost of the car is Rs 600,000. He approached the bank and bank agreed to sanction the loan based on the below conditions.
- Down Payment: Rs150,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.
Step 2: Open PMT function in B7 cell.
Step 3: First thing is the rate, so interest rate select B6 cell. Since the interest rate is per anum we need to convert it to month by dividing the same by 12.
Step 4: NPER is the number of payments to clear the loan. So loan tenure is 3 years i.e. 3*12 = 36 months.
Step 5: PV is nothing but 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.
Step 6: Close the bracket and hit enter. We have an EMI payment per month.
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.
Mortgage Loan Calculator Excel Template
In the above example, we have seen the simple method to calculate the loan monthly repayment amount. The above just showed the monthly amount required to clear off the loan, but I have built a template which will give the complete breakup of the principal amount, interest amount and how much extra you have paid in detail.
You need to download the workbook and you need to fill the orange colored cells. All the green colored cells will have a formula and work automatically.
Things to Remember
- You need to fill the only orange colored cell.
- Loan amortization table will show the breakup of Principal Amount and Interest Amount each month.
- It will also show you the extra amount you are paying as a percentage.
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 downloadable excel template. You may learn more about excel from the following articles –