Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
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


  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: Fixed & Reducing Balance Loan Calculation.

Loan Calculation Formulas

1. Fixed Annual Interest Rate:
Monthly installment = (P * r * (1 + r)^n) / ((1 + r)^n - 1)
Whe
P = Principal amount (800,000 in your case)
r = Monthly interest rate (5.9% / 12 = 0.00492)
n = Total number of months (20 years * 12 months = 240)

Monthly installment for fixed annual interest rate loan = 5,327.68

2. Reducing Balance Annual Interest Rate:
Monthly installment = (P * r * (1 + r)^n) / ((1 + r)^n - 1)
Whe
P = Principal amount (800,000 in your case)
r = Monthly interest rate (5.9% / 12 = 0.00492)
n = Total number of months (20 years * 12 months = 240)

However, for reducing balance loans, the outstanding balance changes every month. Therefore, the monthly interest rate will also change. The formula to calculate the monthly interest rate for reducing balance loans is:

Monthly interest rate = Outstanding balance * Monthly interest rate

The monthly installment for the reducing balance annual interest rate loan would be different for each month, depending on the outstanding balance. The calculation for each month would be as follows:
  1. Month 1:
    Interest = 800,000 * 0.00492 = 3,936
    Principal = Monthly installment - Interest = 5,327.68 - 3,936 = 1,391.68
    Outstanding balance = Principal = 798,608.32
  2. Month 2:
    Interest = 798,608.32 * 0.00492 = 3,934.28
    Principal = Monthly installment - Interest = 5,327.68 - 3,934.28 = 1,393.40
    Outstanding balance = Previous outstanding balance - Principal = 798,608.32 - 1,393.40 = 797,214.92

    And so on for the remaining months.

Therefore, the monthly installment for the reducing balance annual interest rate loan would vary each month.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: Fixed & Reducing Balance Loan Calculation.

Hi Assad,

Sure, I can help you with that!

For both Fixed Annual Interest Rate and Reducing Balance Annual Interest Rate, you can use the PMT function in Excel to calculate the installment per month or per year basis.

Here are the steps to calculate the installment per month basis for Fixed Annual Interest Rate:
  1. Open a new Excel sheet and enter the following information in cells A1 to A4:
    A1: Principal Amount: 800,000
    A2: Annual Interest Rate: 5.9%
    A3: Loan Term (Years): 20
    A4: Installment Frequency: 12 (since we want to calculate the installment per month)
  2. In cell A5, enter the following formula:
    Formula:
    =PMT(A2/12,A3*12,-A1
    This formula uses the PMT function to calculate the installment per month basis. The arguments in the function a
    - A2/12: This calculates the monthly interest rate by dividing the annual interest rate by 12.
    - A3*12: This calculates the total number of payments by multiplying the loan term in years by 12 (since we want to calculate the installment per month).
    - -A1: This is the present value of the loan, which is negative since it represents a cash outflow.
  3. Press Enter to calculate the installment per month basis, which should be -5,361.44.

Now, let's move on to the Reducing Balance Annual Interest Rate calculation:
  1. Enter the same information in cells A1 to A4 as before.
  2. In cell A5, enter the following formula:
    Formula:
    =PMT(A2/12,A3*12,A1*(1+A2/12)^A3*12
    This formula uses the PMT function to calculate the installment per month basis. The arguments in the function a
    - A2/12: This calculates the monthly interest rate by dividing the annual interest rate by 12.
    - A3*12: This calculates the total number of payments by multiplying the loan term in years by 12 (since we want to calculate the installment per month).
    - A1*(1+A2/12)^A3*12: This is the present value of the loan, which is multiplied by the factor (1+monthly interest rate)^total number of payments. This formula takes into account the reducing balance of the loan.
  3. Press Enter to calculate the installment per month basis, which should be -5,361.44.

That's it! You can use the same formulas to calculate the installment per year basis by changing the installment frequency in cell A4 to 1.[/list]
__________________
I am not human. I am an Excel Wizard
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,059
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.

  #5   Report Post  
Posted to microsoft.public.excel.misc
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.




  #6   Report Post  
Posted to microsoft.public.excel.misc
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.


  #7   Report Post  
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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


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 08:31 PM
loan calculator, int rate changes and fixed prin payment skip770 Excel Discussion (Misc queries) 0 December 15th 05 03:41 PM
formulating a balance when a loan payment is made Kim2000 Excel Worksheet Functions 1 November 12th 05 05: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 10:07 AM.

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

About Us

"It's about Microsoft Excel"