Loan Calculator – Knowing the Monthly Payment With the Right Formula

Considering your mortgage options in preparation for buying or refinancing your home? The most important thing you will need to consider is that of your monthly payment amount. After all, if you take out a loan whereby your payment is too high, you could end up not being able to swing your payments. This could put you at risk for foreclosure.

Why You Need a Loan with the Right Monthly Payment

The amount you owe each month to your mortgage lender plays a huge role in your monthly finances. A good rule of thumb is that your monthly housing expense (which includes your mortgage, homeowner’s insurance and property tax payments) should not exceed 30% of your monthly income. Any more than that and you could be setting yourself up to fail financially.

Of course, the maximum amount you should be willing to pay will vary depending upon other factors such as the amount of other debt payments (like credit card debt) and the amount you have available to put up as a down payment on the mortgage.

So, start by setting for yourself a maximum monthly mortgage payment you will be able to afford.

The Factors that Determine Your Payment Amount

Next, it is a good idea to understand the various factors that influence how much you pay in mortgage fees each monthly. These are: the principal of the loan amount (P), the annual interest rate of the loan (I), and the loan term (L) in years.

Before you start performing calculations, it is a good idea first to open up a spreadsheet application like Excel and start inputting the various assumptions you want to try. We’ll call each set of assumptions a “scenario.” For example, one scenario might be a loan amount of $125,000, an interest rate of 6.2%, and a repayment term of 30 years. Another might be the same as the first, but with a loan amount of $150,000 (etc.).

Obviously, you can reduce the number of scenarios by setting realistic figures for each item. Four scenarios is probably a good way to start (maybe try two different interest rates and two loan amounts, for example).

Loan Calculator: Knowing the Monthly Payment

So, with all of that in mind, here is how to calculate your future would-be payments using pen & paper or in a spreadsheet application. First, let’s review the variables we discussed above and add a few more (which are simply derivations of the first set).


M = monthly payment (this is what you are going to calculate)

P = principal (initial amount borrowed)

L = loan term, in years

I = the annual interest rate (from 1 to 100)

J = monthly interest amount in decimal form, which is calculated: I / (12 x 100)

N = loan term, in months, which is just L x 12

Here is the formula (note that this formula assumes a standard loan wherein interest is compounded each month).

M = P * ( J / (1 – (1 + J) ^ -N)) For this notation, ^ means “to the power of”.

Step by step, here is how to figure out your monthly payment:

1. Calculate 1 + J, then take the result to the power of -N (minus N).

2. Subtract that from 1.

3. Take the inverse of this result (1 / X).

4. Now, multiply the result by J, then by P.

There you have it! Hint: if you use Excel, you can just set this up once and then copy/paste to create as many scenarios as you need. Then, plug in the different assumptions to see how they will affect your payment.