Excel Formula to Calculate Mortgage Payments – A Quick Calculation Method

Excel is a great tool for organizing your personal finances for so many reasons. It allows you to store information about your monthly payments, make calculations about your household budget, and even manage your finances.
A way to use Excel is to perform what-if scenarios when facing a major financial decision. One such decision for which Excel is commonly used is to figure out the would-be mortgage payments for different mortgage loan scenarios you are considering.
As you shop for different loan offers from different lenders, you will want to quickly and easily determine the monthly payment amounts for each. And, knowing your payments allows you to figure out which combination of three important variables (mortgage period, interest rate, and loan amount) that will allow you to be able to best afford your mortgage.
If you are looking for an Excel formula to calculate mortgage payments, here is how to set up a quick calculation:
1. Write down the three relevant variables: The variables that matter in this calculation are the interest rate, mortgage period (expressed in years), and loan amount.
2. Type the PMT() formula into a free cell in your Excel spreadsheet: Here’s how: let’s assume you are considering a 5% interest rate, 30-year loan period, and a loan amount of $100,000. Here is the formula you would type into Excel:
=PMT(5%/12,30*12,100000)
(The correct result, in this case, is: $536.82)
Note that your result will be expressed as a negative number since this is the amount you will owe each month. If you are more comfortable viewing the result as a positive number, just type the formula like this:
=ABS(PMT(5%/12,30*12,100000))
3. Compare different mortgage scenarios by copying the formula down or across multiple cells: To compare multiple scenarios, just copy this formula into multiple cells and enter different numbers for the three variables mentioned above.
As you get the hang of this, you can also create a simple table with different combinations of the three variables (viz., home value, interest rate, and mortgage period). Then, you can substitute the actual numbers in the PMT() formula (above) with cell references that point to each row in the table that represents a different combination of these variables. This will make it easier for you to quickly compare which combination yields which monthly payment.

John Abrams a Microsoft Office expert has been working in the technology industry for the last 5 years. As a technical expert, he has written technical blogs, white papers, and reviews for many websites such as www.office.com/setup

Leave a Reply

Your email address will not be published. Required fields are marked *