Results 1 to 15 of 16

20090912, 13:56 #1
 Join Date
 Feb 2008
 Posts
 1,548
 Thanks
 138
 Thanked 11 Times in 11 Posts
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

20090912, 14:31 #2
 Join Date
 Feb 2002
 Location
 A Magic Forest in Deepest, Darkest Kent
 Posts
 5,681
 Thanks
 0
 Thanked 1 Time in 1 Post
[quote name='HowardC' post='793111' date='12Sep2009 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?Jerry

20090912, 14:33 #3
 Join Date
 Feb 2008
 Posts
 1,548
 Thanks
 138
 Thanked 11 Times in 11 Posts
[quote name='jezza' post='793114' date='12Sep2009 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

20090912, 14:42 #4
 Join Date
 Jul 2001
 Location
 Ottawa, Ontario, Canada
 Posts
 1,609
 Thanks
 0
 Thanked 1 Time in 1 Post
[quote name='HowardC' post='793111' date='12Sep2009 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)?Regards
Don

20090912, 19:43 #5
 Join Date
 Jul 2001
 Location
 Ottawa, Ontario, Canada
 Posts
 1,609
 Thanks
 0
 Thanked 1 Time in 1 Post
[quote name='HowardC' post='793111' date='12Sep2009 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.
Regards
Don

20090913, 00:39 #6
 Join Date
 Feb 2008
 Posts
 1,548
 Thanks
 138
 Thanked 11 Times in 11 Posts
[quote name='wdwells' post='793139' date='13Sep2009 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.
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

20090913, 03:54 #7
 Join Date
 Feb 2008
 Posts
 1,548
 Thanks
 138
 Thanked 11 Times in 11 Posts
[quote name='jezza' post='793114' date='12Sep2009 19:31']What is your interest interval, annual, monthly, daily?[/quote]
Hi Jezza
Thanks for the reply
My Interest interval is is daily
Regards
Howard

20090913, 07:38 #8
 Join Date
 Jul 2001
 Location
 Ottawa, Ontario, Canada
 Posts
 1,609
 Thanks
 0
 Thanked 1 Time in 1 Post
[quote name='HowardC' post='793148' date='13Sep2009 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
Regards
Don

20090913, 09:43 #9
 Join Date
 Feb 2008
 Posts
 1,548
 Thanks
 138
 Thanked 11 Times in 11 Posts
[quote name='wdwells' post='793168' date='13Sep2009 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

20090913, 10:21 #10
 Join Date
 Jul 2001
 Location
 Ottawa, Ontario, Canada
 Posts
 1,609
 Thanks
 0
 Thanked 1 Time in 1 Post
[quote name='HowardC' post='793179' date='13Sep2009 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
Regards
Don

20090913, 10:54 #11
 Join Date
 Feb 2008
 Posts
 1,548
 Thanks
 138
 Thanked 11 Times in 11 Posts
[quote name='wdwells' post='793182' date='13Sep2009 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

20090913, 11:12 #12
 Join Date
 Jul 2001
 Location
 Ottawa, Ontario, Canada
 Posts
 1,609
 Thanks
 0
 Thanked 1 Time in 1 Post
[quote name='HowardC' post='793190' date='13Sep2009 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?Regards
Don

20090913, 11:21 #13
 Join Date
 Feb 2008
 Posts
 1,548
 Thanks
 138
 Thanked 11 Times in 11 Posts
[quote name='wdwells' post='793192' date='13Sep2009 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

20090914, 11:22 #14
 Join Date
 Feb 2008
 Posts
 1,548
 Thanks
 138
 Thanked 11 Times in 11 Posts
[quote name='wdwells' post='793192' date='13Sep2009 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.
Please check your UDF formula and advise
Thanks
Howard

20090914, 12:18 #15
 Join Date
 Jul 2001
 Location
 Ottawa, Ontario, Canada
 Posts
 1,609
 Thanks
 0
 Thanked 1 Time in 1 Post
[quote name='HowardC' post='793296' date='14Sep2009 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.
Please check your UDF formula and advise
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.Regards
Don