1

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
  • I applied your formula for calculating EMI for Monthly, Quaterly, Half Yearly and Yearly and it is working as per the expectation. I am really thankful to your suggestion. Vishal Apr 9, 2019 at 11:12

1 Answer 1

2

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.

enter image description here

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .