Excel - Fixed & Reducing Balance Loan Calculation.

Asked By Assa on 11-Nov-07 01:51 AM

Could anyone, please guide me following two questions.

Loan Calculation For

Fixed Annual Interest Rate
Reducing Balance Annual Interest Rate

Prinicipale Amount : 800,000
Profit Rate:  5.9 % Per Anum
Years: 20

Advise formula for both type of calculation, to know installment per month
or per year basis.



joeu2004 replied on 11-Nov-07 02:44 AM
If you make monthly payments __and__ we are talking about a loan that
is amortized similar to US loans (specifically, not Canadian loans),
the minimum monthly payment can be determined by the following,
assuming a monetary system similar to the US dollar (i.e. with the
smallest coin equal to 1/100 of the currency unit):

=roundup(pmt(5.9%/12, 20*12, -800000), 2)

But please note that different countries might compute the monthly
rate (here:  5.9%/12) differently.  For example, I have noticed that
another method is used in the UK, at least according to some lenders'
web sites.

As for a per-year basis, I am not quite sure what you are asking.  If
you make monthly payments, obviously you pay 12 times that in a year.
If you make annual payments (surprise!), then with all the
aforementioned assumptions, the annual payment would be:

=roundup(pmt(5.9%, 20, -800000), 2)


(1) Some lenders might permit rounding down.  And some lenders prefer
to round up or down to a "dollar" or whatever the currency unit of the
region is.

(2) In rare circumstances, rounding up (usually to a much higher
degree, for example  10s of "dollars") might result in fewer
payments.  You can compute the number of payments as follows (for
monthly payments):

=roundup(nper(5.9%/12, p, -800000), 0)

(3) Rounding the payment will usually result in a somewhat different
last payment.  You can determine the last payment by the following
(for monthly payments):

=fv(5.9%/12, n - 1, p, -800000)*(1+5.9%/12)

where "p" is the rounded monthly payment computed above and "n" is the
number of periods computed in #2 or simply 20*12.

Assa replied on 11-Nov-07 03:13 AM
Thanks !

Much appreciated...

char replied on 21-Nov-07 09:35 AM
thnx for the EMI calculation... I need to find out the interest portion and
principal portion for each month using reducing balance.. would be gr8 if you
can provide the formula...