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 calculatorMortgage Calculator A 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 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 functionPMT Function PMT 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.

PMT function includes 3 mandatory and 2 optional parameters.

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.

You are free to use this image on 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

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.

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.

Enter all this information in Excel.
Open PMT function in the B7 cell.
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.
NPERNPER NPER, commonly known as the number of payment periods for a loan, is a financial term and an inbuilt financial function in Excel that can be used to calculate NPER for any loan. This formula takes rate, payment made, present value and future value as input from a user.read more is the number of payments to clear the loan. So loan tenure is 3 years i.e. 3*12 = 36 months.
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.
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.