# Thread: Interest Computation using multiple Interest Rates

1. I have a worksheet where i need to calculate the following

1) No Of days at say 9%, 9.5% etc
2) Calculate of Interest on each amount
3) Calculate Accumulated Interest
4) Calculate Accumulated Int & Advances

Your assistance will be most appreciated

Regards

Howard

2. [quote name='HowardC' post='793111' date='12-Sep-2009 19:56']I have a worksheet where i need to calculate the following

1) No Of days at say 9%, 9.5% etc
2) Calculate of Interest on each amount
3) Calculate Accumulated Interest
4) Calculate Accumulated Int & Advances

Your assistance will be most appreciated

Regards

Howard[/quote]

What is your interest interval, annual, monthly, daily?

3. [quote name='jezza' post='793114' date='12-Sep-2009 20:31']What is your interest interval, annual, monthly, daily?[/quote]

Thanks for the reply. Need to calculate interest daily

Your assistance in this regard will be most appreciated

Regards

Howard

4. [quote name='HowardC' post='793111' date='12-Sep-2009 14:56']I have a worksheet where i need to calculate the following

1) No Of days at say 9%, 9.5% etc
2) Calculate of Interest on each amount
3) Calculate Accumulated Interest
4) Calculate Accumulated Int & Advances

Your assistance will be most appreciated

Regards

Howard[/quote]
How often is the interest compounded? Daily?
Are all answers to be calculated to the time of calculation (today)?

5. [quote name='HowardC' post='793111' date='12-Sep-2009 14:56']I have a worksheet where i need to calculate the following

1) No Of days at say 9%, 9.5% etc
2) Calculate of Interest on each amount
3) Calculate Accumulated Interest
4) Calculate Accumulated Int & Advances

Your assistance will be most appreciated

Regards

Howard[/quote]
The attached solution is based on the financial status as of the date of calculation (now) with interest compounded daily.

It requires that:
• the "Interest Rate Table" be a named range (IRT);
• the dates in the Interest Rate Table be actual dates, as received they were not recognizable as dates;
• a third column be added to the Interest Rate Table identifying the duration of each rate regime.
Should you wish to work with a different end date than now, the formula in F:F will need to refer to the appropriate cell(s).

6. [quote name='wdwells' post='793139' date='13-Sep-2009 00:43']The attached solution is based on the financial status as of the date of calculation (now) with interest compounded daily.

It requires that:
• the "Interest Rate Table" be a named range (IRT);
• the dates in the Interest Rate Table be actual dates, as received they were not recognizable as dates;
• a third column be added to the Interest Rate Table identifying the duration of each rate regime.
Should you wish to work with a different end date than now, the formula in F:F will need to refer to the appropriate cell(s).[/quote]

Hi Don

Thanks for the help. I need you to calculate the interest on each of the advances from Date of the advance to now at the varying rates of Interest

Your assistance will be most appreciated

Regards

Howard

7. [quote name='jezza' post='793114' date='12-Sep-2009 19:31']What is your interest interval, annual, monthly, daily?[/quote]

Hi Jezza

My Interest interval is is daily

Regards

Howard

8. [quote name='HowardC' post='793148' date='13-Sep-2009 01:39']Thanks for the help. I need you to calculate the interest on each of the advances from Date of the advance to now at the varying rates of Interest[/quote]
I need your help to understand. I see four transactions which are unrelated but subject to the the same variable rate interest regime.

Discussing the first loan:
• Date borrowed: 18 Aug 2005
• Principal loaned: 65,000.00
• Initial annual interest: 9.00%
• Principal plus interest at 8 Jun 2006 when the interest changed to 9.50% 69,886.43
• Principal plus interest at 14 Dec 2006 when the interest changed to 10.00% 73,409.18
• Principal plus interest today (13 Sep 2009) 96,649.18
• Accumulated interest over the total period 31,649.18

9. [quote name='wdwells' post='793168' date='13-Sep-2009 13:38']I need your help to understand. I see four transactions which are unrelated but subject to the the same variable rate interest regime.

Discussing the first loan:
• Date borrowed: 18 Aug 2005
• Principal loaned: 65,000.00
• Initial annual interest: 9.00%
• Principal plus interest at 8 Jun 2006 when the interest changed to 9.50% 69,886.43
• Principal plus interest at 14 Dec 2006 when the interest changed to 10.00% 73,409.18
• Principal plus interest today (13 Sep 2009) 96,649.18
• Accumulated interest over the total period 31,649.18

Hi Don

Thanks for the reply. The Principal amount loaned on 18 Aug 2005 was 65000.00 at 9%. The Interest on the 65000 will be @ 9% 65000 from 18/08/2005 until 07/06/2006 and then @ 9.5% up to 13/12/2006 and therafter at 10% up to now

Hope this is a lot clearer. If not, please let me know

Thanks for your patience & help so far

Regards

Howard

10. [quote name='HowardC' post='793179' date='13-Sep-2009 10:43']Hi Don

Thanks for the reply. The Principal amount loaned on 18 Aug 2005 was 65000.00 at 9%. The Interest on the 65000 will be @ 9% 65000 from 18/08/2005 until 07/06/2006 and then @ 9.5% up to 13/12/2006 and therafter at 10% up to now

Hope this is a lot clearer. If not, please let me know

Thanks for your patience & help so far

Regards

Howard[/quote]
Howard

The attached provides in the various columns:
• D - Total interest to date on a given advance
• E - Cumulative total interest on advances
• F - Total of Advance plus interest to date on a given advance
• G - Cumulative value of column F

11. [quote name='wdwells' post='793182' date='13-Sep-2009 15:21']Howard

The attached provides in the various columns:
• D - Total interest to date on a given advance
• E - Cumulative total interest on advances
• F - Total of Advance plus interest to date on a given advance
• G - Cumulative value of column F

Hi Don

Thanks for the help so far. The Interest on say the first amount of 65000.00 needs to be calculated as follows:

65000 @9% up until 07/06/2006 , which is the period at which the Interest rate remained at 9% from 08/06/2006 until 13/12/2006 @9.5% and thereafter until now at the latest Interest Rate. I want this principle to be applied to the Interest calculation on all the amounts as there are varying interest rates to be charged. The computation is to be the same as the banks would calculate the interest on an overdraft or deposit. Apply the same principle as the bank would apply when calculating the interest on a deposit, savings or overdraft account where the interest rate changes and there are multiple capital amounts on which Interest is calculated daily

Hope this is a lot clearer of what i'm trying to do

Regards

Howard

12. [quote name='HowardC' post='793190' date='13-Sep-2009 11:54']Hi Don

Thanks for the help so far. The Interest on say the first amount of 65000.00 needs to be calculated as follows:

65000 @9% up until 07/06/2006 , which is the period at which the Interest rate remained at 9% from 08/06/2006 until 13/12/2006 @9.5% and thereafter until now at the latest Interest Rate. I want this principle to be applied to the Interest calculation on all the amounts as there are varying interest rates to be charged. The computation is to be the same as the banks would calculate the interest on an overdraft or deposit. Apply the same principle as the bank would apply when calculating the interest on a deposit, savings or overdraft account where the interest rate changes and there are multiple capital amounts on which Interest is calculated daily

Hope this is a lot clearer of what i'm trying to do

Regards

Howard[/quote]
Is that not what I have provided?

13. [quote name='wdwells' post='793192' date='13-Sep-2009 16:12']Is that not what I have provided?[/quote]

Hi Don

My apologies, did not realise that you set up a UDF function to take the varying Interest rates into account

Thanks for all the help, much appreciated

Regards

Howard

14. [quote name='wdwells' post='793192' date='13-Sep-2009 16:12']Is that not what I have provided?[/quote]

Hi Don

I changed the date of cell A11 to 14/12/2006, so that I can check the Interest calculation. My calculation comes to \$1239.04, Your Calc \$1426.19.

Thanks

Howard

15. [quote name='HowardC' post='793296' date='14-Sep-2009 12:22']Hi Don

I changed the date of cell A11 to 14/12/2006, so that I can check the Interest calculation. My calculation comes to \$1239.04, Your Calc \$1426.19.

Thanks

Howard[/quote]
Hi Howard

The difference is that I am compounding daily; you are not compounding at all. The calculation is provided at cell C20.

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•