How to Calculate Mortgage Payments (with Examples) - wikiHow
Four Methods:Calculating Mortgage Payments Using a Spreadsheet ProgramCalculating Mortgage Payments with an EquationCreating an Amortization ScheduleUseful Documents
If you're considering buying a house or another type of property, you'll likely have to shop around for a mortgage loan. This type of loan is specific to property purchases and usually carries a low interest rate compared to other loans. This is because the loan is secured using the property, meaning that the lender, in many cases a bank, has the right to seize the property in the event that the borrower fails to pay them back.[1] As such, it's important to find the loan with the cheapest rate you can so that you can pay it back responsibly and in a reasonable amount of time. Use the following methods to calculate your monthly payments so that you can make the right choice.
Understand the function used. Mortgage payments can be easily found using your chosen spreadsheet program. This function, in all major spreadsheet programs (Microsoft Excel, Google Spreadsheet, and Apple Numbers), is known as PMT, or the payment function.[2] It combines information like your interest rate, number of periods, and principal to arrive at an amount for each monthly payment.
Start using the PMT function. Start using the PMT function by typing =PMT( into your spreadsheet. The program will then prompt you for the proper entries into each part of the function by showing the following: PMT(rate, nper, pv, [fv], [type]). The first three represent required inputs, while the last two are optional.[3]
Enter this information and press enter. The program will display your monthly payment amount in the same cell you entered the formula into. Note that this number will be negative, this is simply the program expressing it as a payment (or expense).[4]
Analyze your result. The PMT function will return an amount that represents the total amount you will pay on the loan each month. Know that this number will be expressed as a negative number. This doesn't mean you entered your information incorrectly, but simply that the program represents payments as an expense and therefore, a negative number. Multiply by -1 if this helps you understand and use the figure.[5]
Understand the equation. In order to calculate the monthly payment, we can rely on a relatively simple equation. The monthly payment equation can be represented as follows: M=Pr(1+r)n(1+r)n− 1{\displaystyle M=P{\frac {r(1+r)^{n}}{(1+r)^{n}-1}}}. These variables represent the following inputs:
Solve the exponents. The results inside the parentheses, (1+ r), from the previous step must now be raised to the power of "n." Again, this "n" represents the total number of payments. This step requires a calculator with an exponent function, which is usually represented like this: xy{\displaystyle x^{y}}.
Set up your amortization schedule. An amortization schedule will tell you exactly how your monthly mortgage payments will be split between paying off principal and interest and what your balance will be at the end of each month. Start by inputting the basics of your loan information in the top left of a spreadsheet program. For example, in cell A1, write out "annual interest rate." Then, input your annual interest rate as a percentage in the next cell over, B1. Continue down to cell A2 for the loan duration in years, inputting the amount in column B as before. Do the same for payment per year and loan principal in cells A3 and A4, respectively.[7]
Loan balance.[8]
Cell C7 should contain the payment amount.[9]
Complete your amortization schedule. Select the range from cell A7 to E7. Then, drag the calculations down to the last payment. At this point, the loan balance in column E should be $0. Remember that your number of payments is calculated by multiplying the number of annual payments by the loan duration in years.[10]
It's easiest to use an online mortgage loan calculator. Online calculators that can find your monthly payment with the simple input of a few pieces of key information. Try searching for "mortgage loan calculator" using your preferred search engine. Usually, you'll have to input details of your loan, like the number of years, annual interest rate, and value of your principal. Then, simply hit "calculate" and the provided readout should tell you anything else you need to know.[11]