In return of the loan, we pay some amount of extra money to the lender. When you take a loan from a bank or from anyone, then you pay some percentage of the loan, as interest, for a specified period, annually, monthly, quarterly, semi-annually, etc.
Have you ever thought of why you pay interest against the loan? How the interest paid on loan is calculated? How the regular payment paid, every year or month, is calculated?
To answer the first question, when people lend money, they can no longer use this money to fund their own purchases. The payment of interest makes up for this inconvenience. This is also known as the time value of money. Also, a borrower may default on the loan.
Before discussing how interest, capital repaid and payments made are calculated, let’s talk about interest.
Interest may be regarded as a reward paid by one person or organization (the borrower) for the use of an asset, referred to as capital, belonging to another person or organization (the lender).
Interest is of two types:
- Simple Interest: The essential feature of simple interest is that interest, once credited to an account, does not itself earn further interest. Suppose an amount C is deposited in an account that pays simple interest at the rate of (i X 100%) per annum. Then after n years, the deposit will have accumulated to:
- Compound Interest: The essential feature of compound interest is that interest itself earns interest. Suppose an amount C is deposited in an account that pays compound interest at the rate of (i X 100%) per annum. Then after n years, the deposit will have accumulated to:
A very common transaction involving compound interest is a loan that is repaid by regular installments, at a fixed rate of interest, for a predetermined term. Loans are mostly used by companies or individuals to raise funds, usually to buy buildings or equipment.
Now, let’s see from an example of how interest and regular payments are calculated. Assume a bank lends an individual ₹200000 for 3 years, in return for regular payments at the end of each month. The bank will charge an effective rate of interest of 7% per annum.
Let X be the monthly payment, then the equation of value for the transaction is given by:
So, by using annuities in arrear, we get a monthly payment of ₹6155.78. By using the PMT function in excel, we can find monthly payments. For that, we have to input some values like loan amount, annual interest rate, loan period in years and number of payments per year. For our example, this can be done as follows:
Here, monthly payment is named as “Instalment”. So, by using the PMT function, we get:
You can see that there is some difference in the installment by using annuity function and PMT function in excel.
One important point to note is that each repayment must pay first for interest due on the outstanding capital. The balance is then used to repay some of the capital outstanding. Each payment, therefore, comprises of both interest and capital repayment. It may be necessary to identify the separate elements of the payment.
∴ Installment = Interest + Capital re-paid
This can be shown in the form of a table, which is often known as “loan schedules”, having columns:
- Payment Number or Instalment Number
- The loan outstanding in the beginning
- Scheduled payment (which is our monthly installment)
- Interest due
- Capital repaid
- The loan outstanding in the end
Interest due is calculated by using the nominal interest rate. In our example, interest due in the first year would be calculated as:
and capital repaid is calculated as:
Capital repaid=Installment-Interest due
Also, loan outstanding is calculated as:
Loan outstanding in end=Loan outstanding in beginning-Capital repaid
So, in excel, the first row of payment will look like this:
Here, scheduled payment is our fixed monthly installment, which will be paid at the end of the first month and this will remain fixed till our last payment. Interest due, capital repair, and loan outstanding in the end are calculated by using the above formulae.
Now, while calculating the next row of loan schedule, our loan outstanding in the end will become loan outstanding in the beginning and the rest of the columns are calculated as before. So, our full loan schedule will look like this:
Here you can see that at the end of the third year, i.e., after paying all the 36 installments, our loan outstanding in the end will become zero, since we have paid all the installments due and the full amount of the loan taken, under the fixed period of 3 years.
Notice the pattern of interest due and capital repayment. Interest due is decreasing and capital repaid is increasing during the term of the loan. This happens because as we pay our capital, the loan outstanding decreases, decreasing the value of interest due and increasing the value of capital repaid. You can also see this from a chart for interest due and capital repaid.
So, till now, we have discussed the schedule where we have fixed installments. What happens when we pay extra money in between the period as extra capital against our loan?
Now, we’ll have one extra column of “Extra payment” in our loan schedule. Formulae will remain the same as before, except for capital repair and loan outstanding in the end. New formula for capital repair and loan outstanding in the end will be:
Capital Repaid=Installment-Interest due+Extra payment
For calculating the loan outstanding in the end, we’ll use IF function in excel as follows:
Now, if we pay ₹2000 extra capital at the end of year 1 and year 3, and ₹1500 at the end of year 2 and apply the above formulae. Then, the loan schedule will look like this:
If we don’t use IF function in calculating the loan outstanding in the end, then we’ll get negative values.
So, we have seen how to make loan schedule in excel and how to consider the extra payment made during the term of the loan.