Mortgage Payment Loan Calculator in Excel
A mortgage calculator in Excel is not a built-in feature in Excel. But, we can make our 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 types and data to be inserted. Then, we can use the formula for mortgage calculation in one cell. Now for the future, we can change the values and have our mortgage calculator in Excel.
Table of contents
Formula to Calculate Mortgage Payment in Excel
Like many other excel mortgage calculatorMortgage CalculatorA mortgage calculator is used to compute the value of the monthly installment payable by the borrower on the mortgage loan. It considers the loan amount, the annual rate of interest, and the repayment frequency for calculation.read more, we also have the formula to calculate the monthly EMI amount. In addition, we have a built-in PMT functionPMT FunctionPMT function is an advanced financial function to calculate the monthly payment against the simple loan amount. You have to provide basic information, including loan amount, interest rate, and duration of payment, and the function will calculate the payment as a result.read more to calculate the monthly EMI in Excel.
PMT function includes three mandatory and two optional parameters.
- Rate: This is the interest rate applicable to the loan. If the interest is per annum, then you need to convert this to a monthly payment by dividing the interest rate by 12.
- Nper: This is simply the duration of the loan. How many EMIs are you going to clear the loan? For example, if the loan tenure is for 2 years, then the 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.
- [FV]: This is the future valueFuture ValueThe Future Value (FV) formula is a financial terminology used to calculate cash flow value at a futuristic date compared to the original receipt. The objective of the FV equation is to determine the future value of a prospective investment and whether the returns yield sufficient returns to factor in the time value of money.read more 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 or end of the month. If it is at the beginning, the argument is 1. If the payment ends at the month’s end, the argument will be zero (0). By default, the argument will be zero.
You are free to use this image o your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Mortgage Calculator in Excel (wallstreetmojo.com)
Examples
Mr. A wants to buy a car, which costs ₹600,000. So he approached the bank, and the bank agreed to sanction the loan based on the below conditions.
- 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.
- We must first insert all this information in Excel.
- Open PMT function in the B7 cell.
- The first thing is the rate, so the interest rate selects the B6 cell. Since the interest rate is per annum, we need to convert it to month by dividing the same by 12.
- NPER is the number of payments to clear the loan. So, the loan tenure is 3 years, 3*12 = 36 months.
- PV is nothing but the loan amount Mr. A is taking from the bank. Since this amount is a credit given by the bank, we need to mention this amount is negative.
- Close the bracket and press the “Enter” key. We have an EMI payment per month.
So, to clear the loan of ₹450,000 in 3 years at an interest rate of 15%, Mr. A has to pay ₹15,599 per month.
Things to Remember
- It would help if we filled the only orange-colored cell.
- The loan amortization tableLoan Amortization TableLoan amortization schedule refers to the schedule of repayment of the loan. Every installment comprises of principal amount and interest component till the end of the loan term or up to which full amount of loan is paid off.read more may 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 article is a guide to Mortgage Calculator in Excel. We discuss preparing a mortgage loan payment calculator using the PMT formula, practical examples, and a downloadable Excel template. You may learn more about Excel from the following articles: –
