How to calculate EMI for Quarterly, Half yearly and Annual payments and generate amortization schedule?

Question

I am a software engineer by profession. I am a newbie to finance. I am trying to create a loan amortization schedule. I have the formula for EMI= P*(r*(1+r)^n)/((1+r)^n)-1. How to apply it for quarterly, half yearly etc. How to generate amortization schedule from the EMI? What is the difference reducing interest and reducing installment payment? Is the formula different for the two types? How to generate an amortization schedule for 365 days?

Answer 1

Here is a small model example. Consider a loan with

Principal, P = 1000
Nominal interest, i = 10% compounded monthly
Number of periods, n = 36 months

The monthly rate, r = i/12 = 10/100/12 = 0.00833333

The equated monthly instalment, EMI = (P r (1 + r)^n)/((1 + r)^n - 1) = 32.2672

If the interest rate is reduced the EMI or the number of periods to repay will reduce. If the EMI is reduced and the interest rate stays the same the number of periods will increase, as given by

n = -(Log[1 - (P r)/EMI]/Log[1 + r])

You will generally have to operate to a round number of periods which means, if n turns out to be fractional, you should find the balance on the last whole period and apply a further month's interest.

For the amortisation table, from period x to period x + 1 the balance of the principal remaining can be expressed as

Bal[x + 1] = Bal[x] (1 + r) - EMI where Bal[0] = P

∴ Bal[x] = (EMI + (1 + r)^x (P r - EMI))/r

I.e.

Bal[0] = 1000
Bal[36] = 0

For a quarterly example using the same figures.

You can convert between compounding frequencies via the effective interest rate.

Effective annual rate, EAR = (1 + i/12)^12 - 1 = 10.4713 %
Nominal rate compounded quarterly, qr = 4 ((1 + EAR)^(1/4) - 1) = 10.0836 %
Number of periods, n = 12 quarters

The quarterly rate, r = qr/4 = 0.0252089

The equated quarterly instalment, EQI = (P r (1 + r)^n)/((1 + r)^n - 1) = 97.6105

The balance of the principal remaining throughout the amortisation is

Bal[x] = (EQI + (1 + r)^x (P r - EQI))/r

Plotting both examples in one chart.

The interest paid in period x + 1 is r Bal[x].

The total interest ti paid to period x is given by

ti[x] = P ((1 + r)^x - 1) + (EMI (1 + x r - (1 + r)^x))/r

and the total interest throughout is n EMI - P.

So for the monthly example ti[36] = n EMI - P.

Of course you can calculate the amortisation iteratively, which is the usual spreadsheet method. The formulas are helpful though, if, for example, you need to jump straight to the point in say 10 years where the rate changes and you need to know the balance and interest paid up to then.

Lastly, for the daily calculation

Nominal rate compounded daily, dr = 365 ((1 + EAR)^(1/365) - 1) = 9.95992 %
Number of periods, n = 365 days

etc.