How loan instalments are calculated

By MUNGAI KIHANYA

The Sunday Nation

Nairobi,

04 December 2016

A month ago, I posted a loan planning Excel Workbook HERE and invited my readers to download it. So far, over 2,500 people have downloaded the Workbook. However, several readers have now asked me to explain how the Workbook works.

In the words of one reader: “I work in a bank and I checked whether your Workbook agrees with the system we use [and] it does. Unfortunately, I have never understood how the system works so I just accept the results as ‘gospel truth’. Can you explain the formula used?”

Well; the formula is developed in a logical, step-by-step process. First of all, interest rates are quoted on an annual basis. Thus when a bank say it will charge 14 per cent, this means 14 per cent per year.

However, the interest amount is calculated on a monthly basis – there are a few instances of quarter-, half-, and full-yearly calculations but they are very rare. For that reason, the annual interest rate must be divided by 12 to get the monthly figure.

If you took out a Sh100,000-loan at 14 per cent interest on December 1, 2016, your first monthly instalment will be on January 1, 2017. By that time, you will have kept the borrowed money for one month, so it will have earned 1.167 per cent interest. That is; 14 divided by 12. Therefore, your account will show that you owe the bank Sh101,167.

It is important to note that this loan balance at the end of the first month does not depend on the term of the loan! Whether it is a one-year or 5-year or 20-year, the amount owed will be the same Sh101,167.

The next question is this: how much instalment will you pay? That amount depends on the term of the loan. Most banks calculate a fixed figure that includes both the interest and the principal sum.

Suppose you pay Sh10,000; your new balance will now be Sh91,167. At the end of the second month, this will earn another 1.167 per cent and thus increase to Sh92,230.

If you pay another Sh10,000 at the end of the second month, the balance will drop to Sh82,230. This also earns 1.167 per cent interest rising to Sh83,190 by the end of the third month.

If you continue paying this Sh10,000 monthly, your balance at the end of the eleventh month will be Sh6,962. This will be the final amount needed to clear the loan. The question then becomes: is it possible to calculate an equal monthly payment that still clears the loan in eleven months?

The answer is yes; and we will see how the calculation is done next week. In the meantime, notice that the total payments come to Sh106,962; that is, just 7 per cent above the amount borrowed. But the interest rate was 14 per cent. The difference is because you didn’t keep the whole Sh100,000 for a full year.