Loan Calculations are Simple

Loans — We can’t live with them, we can’t live without them.

Why do we take Loans?

1. The first reason is pretty obvious — we are running short on funds. With rising inflation and job layoffs, life has become very unpredictable.
2. The second reason is somewhat subjective. There are a lot of people who don’t want to spend all the money at once to buy something since that would mean they are left with very little amount in their savings. This brings in the anxiety of emergencies. We end up thinking what will happen if someone falls sick and we are left with no liquid funds.

No matter what the reason Loans have become a way of sustaining life these days.

How do Loans work?

Before you take a loan, it is very important to understand how they work. We will break down the calculations so sit tight.

The core principle of a loan works like this. You ask your friend for a certain amount. Your friend agrees to loan you the amount but needs some guarantee in return for the amount he is giving you. You provide him with a valuable asset as security till the amount is paid back. Once you pay it back you can take that asset back.

But then your friend gains nothing from this. So he puts a condition that you can take x amount but will have to pay him back x + (10% extra) amount. This 10% is the profit that he makes by lending you the money.

You agree to the terms and agree to pay a portion of the loan each month.

Principle — The amount you take from your friend.
Mortgage/Collateral — The asset that you give as security to your friend.
Interest Rate— The 10% profit that your friend makes after you repay.
Amount — The Principal and the Interest that you pay to your friend.
Tenure — The total time you take to pay back the Amount.
EMI — Each Monthly Installment

Taking a Loan from a Bank

We will now apply the same to a bank. Let us say you want to take a loan of 5,00,000 from ABC Bank at an interest of 12% for 5 years. Let us just assume that the banks don’t need any collateral since it is a small amount for now. As per our previous discussion here is how it looks —

Principal — 500000
Mortgage/Collateral — None
Interest Rate — 12%
Amount — Principle + Interest
Tenure — 2 years or 24 months

Here is how the above details look in an Excel sheet.

C2 — Since Interest Rate is in percentage, C2 is 12/100 = 0.12
B3 — Nothing but the Annual Interest Rate divided by 12 = 12/12 = 1
C3 — Same as C2. 1/100 = 0.01

We have 24 rows since we have 24 months to pay back the amount.

Now this is what the bank comes up with. I know this might look like a lot initially but believe me when I say it is very simple.

What you see above is how much Interest and Principal you will pay each month. Now if you look closely at the last highlighted cell, you will see they don’t match. Also if you see the Sum of all the EMIs paid, you will see it amounts to only 240000. But we took 500000.

This means this Excel sheet is WRONG! Wow, you would be cursing me now. Why is it WRONG? Well, your balance should become 0. That is when you have repaid the full loan.

Now let us dive deeper into how these values got populated.

Amount — This contains the amount you are left to pay back.

EMI — Ignore this for now. Our motto is to come up with the exact EMI amount that will allow you to get your loan paid.

What you have to know is each EMI has two portions — you pay a portion of the principal and a portion of the interest every month.

Interest — We multiply the amount in column B with the monthly Interest Rate. So the formula will be Amount*Monthly-Interest-Rate
Principal — EMI — Interest
Balance — What is left of the principal after the month’s payment.

Remember we reduce the principal paid to get the new Balance.

Now to get the actual EMI. Our task is to find an EMI that makes the balance go to 0. For that either we can keep changing the EMI amount and see what value gives 0 or we can use an Add-On. We will use Goal Seek AddOn for this.

This is what Goal Seek Addon looks like. What we have essentially done here is we told Goal Seek to set the value to 0 by changing the value of the EMI column.

Once you click on Solve, the AddOn will work its magic and give you something like this —

If you look closely, the balance has gone to 0. And the total EMI paid is 5,64,881. The sum of the Principal column is 5,00,000.

This essentially means you took a loan of 5,00,000 and repaid 5,64,882. So the bank made a profit of 64,882 over 2 years.

You can verify the same via any EMI calculator as well. Here is one EMI Calculator —

So next time, you think of taking a loan, you don’t have to spend hours on the phone with the bank manager trying to understand what tenure suits you. You can pull up a sheet and do it yourself.