4 Ways To Calculate Mortgage Payments

For simplicity, we will be focusing on Microsoft Excel’s PMT function here. The process and inputs will likely be identical or very similar for any other program you are using. Consult the help tab or customer service if you have any problems using these functions.

rate stands for the monthly interest rate. Note that this will be your annual interest rate (the quoted rate on your loan agreement, like 4 or 5 percent) divided by 12. It should also be expressed as a decimal. For example, if your annual interest rate is 6%, you would divide this number by twelve to get your monthly interest rate. This would be 6%/12, or 0. 5%. However, this number must be input in the equation as decimal, so we divide again by 100. So we have 0. 5%/100, which equals 0. 005. This will be your monthly interest you will use to calculate mortgage payments. These calculations can also be done in a different order (6%/100 = 0. 06, 0. 03/12 = 0. 005). nper is short for “number of periods” and simply represents how many payments you will make on your loan. For a monthly payment, this would be 12 times the number of years on your loan. Imagine for this example that you have a 15-year mortgage. So, your “nper” value, or your number of payments, would be 12*15, or 180. pv stands for “present value” but here it simply means the principal of your loan. For this example, imagine you have a $100,000 loan. This will be your “pv”. Don’t worry about the other two values; leaving them blank will make the program assume their correct value of 0.

For example, if your annual interest rate is 6%, you would divide this number by twelve to get your monthly interest rate. This would be 6%/12, or 0. 5%. However, this number must be input in the equation as decimal, so we divide again by 100. So we have 0. 5%/100, which equals 0. 005. This will be your monthly interest you will use to calculate mortgage payments. These calculations can also be done in a different order (6%/100 = 0. 06, 0. 03/12 = 0. 005).

Imagine for this example that you have a 15-year mortgage. So, your “nper” value, or your number of payments, would be 12*15, or 180.

For this example, imagine you have a $100,000 loan. This will be your “pv”.

In the example above, this information would be entered as =PMT(0. 005, 180, 100000).

The spreadsheet should return -$843. 86 when you enter your function as described above. Multiply this number by -1 to get your monthly payment of $843. 86.

M is your monthly payment. P is your principal. r is your monthly interest rate, calculated by dividing your annual interest rate by 12. n is your number of payments (the number of months you will be paying the loan)[6] X Research source

For example, imagine you have a $100,000 mortgage loan with 6 percent annual interest over 15 years. Your input for “P” would be $100,000. For “r,” you would use your monthly interest rate, which would be 0. 06 (6 percent) divided by 12, or 0. 005 (0. 5 percent). For “n” you would use your total number of payments, one for each month in fifteen years, which would be 12*15, or 180. In this example, your complete equation would look like this:M=$100,0000. 005(1+0. 005)180(1+0. 005)180−1{\displaystyle M=$100,000{\frac {0. 005(1+0. 005)^{180}}{(1+0. 005)^{180}-1}}}

After this step, your sample equation would look like this:M=$100,0000. 005(1. 005)180(1. 005)180−1{\displaystyle M=$100,000{\frac {0. 005(1. 005)^{180}}{(1. 005)^{180}-1}}}

This is done by entering the value to be raised, (1. 005) in the example equation, then pressing the exponent button, then entering your value for “n” and pressing enter or =. In the example, the result comes out as 2. 454. If you don’t have such a calculator, type your values from the last equation into Google followed by ^(n) while replacing the “n” in parentheses with your value for “n. " The search engine will calculate this value for you. Keep in mind that only the figures inside the parentheses will be raised to this power, not the “r” outside of them (at the front) or the -1 at the end of the equation. After this step the sample equation would look like this:M=$100,0000. 005(2. 454)2. 454−1{\displaystyle M=$100,000{\frac {0. 005(2. 454)}{2. 454-1}}}

The same equation would look like this after this step:M=$100,0000. 012271. 454{\displaystyle M=$100,000{\frac {0. 01227}{1. 454}}}

In the example, your equation would now be:M=$100,000∗(0. 008439){\displaystyle M=$100,000*(0. 008439)}

In the example, this would be ($100,000)*(0. 008439), or $843. 90. This represents your monthly payment.

Payment number. Payment amount. Principal payment. Interest payment. Loan balance. [8] X Research source

Cell A7 should contain your first payment number, 1. Cell C7 should contain the payment amount. [9] X Research source

If your loan payment numbers don’t update down the amortization schedule. Type “=(A7+1)” into cell A8 (payment 2) and drag that down to the end of your schedule. The rest of the numbers will then update.