Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 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
    Attached Files Attached Files

  2. #2
    Platinum Lounger
    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='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?
    Jerry

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    [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. #4
    Silver Lounger
    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='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)?
    Regards
    Don

  5. #5
    Silver Lounger
    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='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).
    Attached Files Attached Files
    Regards
    Don

  6. #6
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    [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
    Attached Files Attached Files

  7. #7
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    [quote name='jezza' post='793114' date='12-Sep-2009 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

  8. #8
    Silver Lounger
    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='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
    Please correct my understanding.
    Regards
    Don

  9. #9
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    [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
    Please correct my understanding.[/quote]


    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. #10
    Silver Lounger
    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='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
    Please be specific about what you want changed.
    Attached Files Attached Files
    Regards
    Don

  11. #11
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    [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
    Please be specific about what you want changed.[/quote]


    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. #12
    Silver Lounger
    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='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?
    Regards
    Don

  13. #13
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    [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. #14
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    [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.

    Please check your UDF formula and advise

    Thanks

    Howard
    Attached Files Attached Files

  15. #15
    Silver Lounger
    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='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.

    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.
    Attached Files Attached Files
    Regards
    Don

Page 1 of 2 12 LastLast

Posting Permissions

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