View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.misc
Assad Assad is offline
external usenet poster
 
Posts: 6
Default Fixed & Reducing Balance Loan Calculation.

Thanks !

Much appreciated...

Assad

"joeu2004" wrote:

On Nov 10, 10:51 pm, Assad wrote:
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.


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 (he 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)

Caveats:

(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.

HTH.