# Loan Calculations are Simple

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

## Why do we take Loans?

- The first reason is pretty obvious — we are running
**short****on****funds**. With rising inflation and job layoffs, life has become very unpredictable. - 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.