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?
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 = P ∴ Bal[x] = (EMI + (1 + r)^x (P r - EMI))/r
Bal = 1000 Bal = 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
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 = 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.