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?
1 Answer
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.