A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Fixed & Reducing Balance Loan Calculation.



 
 
Thread Tools Display Modes
  #1  
Old November 11th 07, 06:51 AM posted to microsoft.public.excel.misc
Assad
external usenet poster
 
Posts: 6
Default Fixed & Reducing Balance Loan Calculation.

Hi.

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.

Regards.

Assad


Ads
  #2  
Old November 11th 07, 07:44 AM posted to microsoft.public.excel.misc
joeu2004
external usenet poster
 
Posts: 2,061
Default Fixed & Reducing Balance Loan Calculation.

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.

  #3  
Old November 11th 07, 08:13 AM posted to microsoft.public.excel.misc
Assad
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.
>
>

  #4  
Old November 21st 07, 02:35 PM posted to microsoft.public.excel.misc
chary
external usenet poster
 
Posts: 1
Default Fixed & Reducing Balance Loan Calculation.

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

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

  #5  
Old May 1st 13, 05:05 PM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Fixed & Reducing Balance Loan Calculation.

On Sunday, November 11, 2007 12:21:00 PM UTC+5:30, Assad wrote:
> Hi.
>
> 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.
>
> Regards.
>
> Assad


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Daily loan calculation Dr. Zhivago Excel Discussion (Misc queries) 5 August 27th 06 11:02 PM
Simple interest, multiple payments, loan balance NinasNumber Excel Worksheet Functions 1 January 9th 06 07:31 PM
loan calculator, int rate changes and fixed prin payment skip770 Excel Discussion (Misc queries) 0 December 15th 05 02:41 PM
formulating a balance when a loan payment is made Kim2000 Excel Worksheet Functions 1 November 12th 05 04:30 PM
Make a template for fixed payment loan DSP New Users to Excel 1 October 8th 05 06:40 PM


All times are GMT +1. The time now is 03:09 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.