Calculate of interest with different interest rates per

Dear All,

I have a problem calculating a total interest for an input period that has different interest rates. You can see the attached file: INTEREST.xls

I have used a function but the result comes out wrong. Can I fine tune it or do I have to change my method of calculating.

Can anyone help?

Thanks in advance

you seem to have 2 errors
01/01/1993 15/06/1993 37.00% 165
16/06/1993 30/09/1993 31.00% 107
01/10/1993 25/10/1993 34.00% 25
26/10/1993 31/12/1993 32.00% 67
the formula =I5-H5 is not inclusive so ok for first range as you seem to want to ignore day 1
but the rest need 1 day added to be inclusive
=(I6-H6)+1

as you don't count the first day i assume that's what the -1 in the lookup is supposed to do
but that would need to be
=SUMPRODUCT(LOOKUP(ROW(INDIRECT(A24+1&":"&B24)),A2:C11))/365*C24 ignoring the first day
that would give both methods a total of
34.016438

Thank you for your answer but the correct value is 33,84. That is my target number.

if you dont count all the days how will it be correct?
you show
16/06/1993 30/09/1993 as 106 days
it is not it is 107 days

I would not argue with this. The result that I have is from an online calculator that everyone uses here and after I input the from to to dates it gives me back as a result 33,84. I do not know the reasoning behind this but in order to agree with the online result I have to have an answer of 33,84. If it is to count 1 less day so be it. How to make the =SUMPRODUCT formula correct I have a problem cause even if you subtract or add a day I cannot get 33,84 like in the step by step method.

well you'll have to find the logic the on line calculator uses
the formula is calculating correctly
this bit
LOOKUP(ROW(INDIRECT(A24&":"&B24) grabs all the values for each date between your first date and last date
LOOKUP(ROW(INDIRECT(A24+1&":"&B24) starts a day later

Formula:

Please Login or Register  to view this content.

gives
33.83835616
or rounded
33.84

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.

Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.

Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.