Divide Time Period across different Time Frames (Calculating Interest)

Hi all,
In the attached excel file:

Interest.xlsx

I've got a time period "C3:D3" (time periods can be altered by user) and a sum of money for which i need to calculate interest across different time periods with different interest rates.
You can check out my formula in "E6:E" but somehow I'm not postive it's correct.
I want the formula to divide days of the above period of time across the different int. rates time frames (return "0" when int.rates times frames are before or after my time period).

Also, I'm not certain how to handle the last int. rate time frame (ie the last time int. rate was changed so it's not a closed time frame B50:C50). I have the last date int. rate was changed in B50 and C50 equals above mentioned D3. Does it make any sense?

Thanks for any help!

Originally Posted by daffodil11

Where you have Days in Column E, change the formula to:

=MAX(0,MIN($D$3,C6)-MAX($C$3,B6)+1)

and then copy down.


This is the sum of days between the 4 dates that overlap.

Then you're just left with the Interest accrued on dates that overlap your range. I came out with 58 days @ 8.5%, 62 days @ 8.75%, and 6 days @ 7.5% = 2.96 English monies.

Could you clarify why the "+1"? In that way days add up to 58+62+6=126 but D3-C3 gives 123 days. So???
Thanks in advance!

Thank you very much for your time!