How to calculate principal and interest in a mortgage?

Dear daughter,

I recently found out about a problem with my mortgage interest payments. Because of that, I want to show how to calculate interest and principal in a mortgage so that when the time comes, you are prepared to make an informed decision.

I already explained to you what a mortgage is in one of my letters. By now, you should know that a mortgage payment is divided into two: interest (the lender’s, usually a bank, earnings) and principal (the money you borrow).

But how the interest and principal are calculated?

Anytime you borrow money, you must pay interest on the remaining amount you borrow. This is easier to understand with numbers. Let me show an example.

Example of Interest and principal payment

Let’s say you borrow $100 at a fixed 10% interest rate. This is a loan for a small period, so you are asked to pay just once the total amount of $110.  Here, the interest is $10 (the 10% of the principal) and the principal is $100.

When you get into a mortgage, the calculation becomes a bit more complex. Because you have to pay the interest and the principal throughout a period, usually 15 years or more.

In the beginning, you pay more interest than the principal. See the picture below.

As you can see, in the first month you pay an interest of $958.33 and a principal of $208.81.

You always pay interest on the remaining amount.

In the second month, the interest you must pay should be a bit less because now, you owe less. Notice that in the first month, an amount of $208.81 is paid to the principal. So, the money you owe is now a bit less, therefore, when you pay interest next time, it also should be a bit less. That is why paying extra money to the principal, is always good as reduces the interest you must pay.

A word of caution, don’t expect the interest to be a lot less in a couple of months.

Sometimes, certain people from financial institutions will tell you that these calculations are complex so you don’t learn how to make them on your own. In that way, you will always depend on them. They cannot be farther from the truth.

In any case, if they learned how to do it, we can also learn it. Isn’t it so?

Calculate interest payment on a loan

What the financial specialists use to do these calculations are formulas that come from financial mathematics. Nothing special, just formulas, and we know how to work with formulas.

And great news, the formula is already integrated into Microsoft Excel (as expected). The name of the formula is IPMT.

So, how does it works?

The function takes 6 parameters as you can see below.

• IPMT(rate, per, nper, pv, [fv], [type])

• rate is the percentage of the interest you must pay

• per is the period that you want to know how much interest you must pay. The first month of your payment is period 1, the second month is period 2, and so on.

• nper is the number of payments you will make in a year.

• pv is the present value of the loan.

• The other two parameters are optional. Fv is usually omitted because is the future value of the loan, we want that value to be 0 and it is 0 by default. The value type indicates when the payments are due, the default value is 0, and indicates the beginning of the period.

Let’s run an example.

Let’s say you borrow $100 000 for a period of 20 years at a 10% interest rate. And you want to know how much you have to pay in interest in the first three months. See the result below.

The formulas to calculate the values are:

• IPMT(10%/12, 1, 240, 100000) for the first month.
• IPMT(10%/12, 2, 240, 100000) for the second month.
• IPMT(10%/12, 3, 240, 100000) for the third month.

Notice that I divided the 10% by 12. The reason is that you must pay interest every month, or 12 times a year.

The values that will result from the formula will be negative. If you like more to see them as positive numbers, just add a minus before the formula, and excel will multiply them by -1, showing a positive value.

Quite easy, isn’t it?

Now let me show you how to calculate the amount that goes to the principal for the first three months.

Calculate the principal payment on a loan

In the same way as the interest, there is a formula to calculate the amount that goes to the principal: PPMT.

• PPMT(rate, per, nper, pv, [fv], [type])

The values you use here are the same as in the function/formula IPMT. Let’s see the result.

As you can see, with the time, the amount you pay in interest must go down, because you owe less, and what you pay to the principal will grow.

I did the previous calculations using a fixed interest rate. If the interest rate goes higher, the interest will go up, and therefore, the total amount you pay in a month will go up as well.

This is how the banks calculate what you must pay back to them. There might be small differences in the numbers due to several reasons, but these numbers are a great approximation of what should happen if you get into a loan.

Dear daughter, as you can see, these numbers are very easy to calculate. Don’t allow anyone to tell you what you can learn or know. Go out there and always looks for the truth.

And as I always tell you, in any financial decision, only the numbers can show you the truth. Don’t listen to long explanations with technical vocabulary. Just ask for the numbers and compare them with the numbers you calculate on your own. At that moment, you will be able to ask the right questions.

Love you, Dad.