How to Calculate Mortgage Monthly Payment using Excel (with Formula) ?

Most people requires mortgage to purchase a house (property). You borrow money from the bank and promise to pay back (with interests) within a specified periods e.g. 25 years. The total amount of money you borrow from the bank is also called “The principal of the Loan”

Then, the formula to compute the monthly payment you need to pay back to the bank is:

M = P * i * (1 + i)ⁿ / ((1 + i)ⁿ – 1)

where
P = Principle of Loan
M = monthly payment
i = monthly interest rate (often needs to convert from annual rate by /12)
n = number of payments

The excel provides the PMT function so that you can easily compute, the syntax is

PMT(monthly rate, number of payments, loan, future value default to zero, type default to zero)

Note that the last two parameters are optional. The type when set to zero means that the payment is made at the end of each period, and when set to one means that the payment is made at the beginning of the payment.

mortgage-calculator-excel

You could Mortgage-Payment-Calculator file that shows the usage of the PMT mortgage calculation.

Excel Tutorial

• Microsoft's Excel Just Got Smarter: The New Copilot Function
• Excel Tutorial: SUMIF Function
• Iterative Computing Fib Number using Excel
• How to Transform the Values in Excel Files to a Column of Values
• Excel Sheet to Calculate the Miles Per Gallon Average Gas Cost
• How to Calculate Mortgage Monthly Payment using Excel with Formula
• Four Useful Cell Functions/Values in Excel
• Excel Tip: Faster Auto-Complete

–EOF (The Ultimate Computing & Technology Blog) —