sum function for multiple amounts w/various tenors

Ok, I have a complex sum function that involves a few components which I can't figure out how to put together.

I've attached a spreadsheet of what I'm looking to do.

Basically, I have counterparts that we're borrowing from and lending to. But for different dates, amounts. I want to add up our lending and borrowing activity and then have a total variance for those periods. In the spreadsheet, column K (variance) has the total done manually for the lending and borrowing activity, this is where I need a formula to make it mechanical. The difficulty comes in in terms of the tenors; borrowing and lending can begin and mature at different periods so there is overlap most of the time with the figures. I hope I'm making sense here. I tried to draft the example in the spreadsheet as best as I can.

Thanks for any help I can receive on this.

Hi finance14,

Fix your Title of thread and then read the rest.

These problems are a little hard to understand sometimes. I looked up Tenor and got all these great singers names. Then I found it is simply the term of a bond. That made more sense.

I believe you need to create a better table with your data and then do a running total column in a pivot table to show what you want. It looks you will need to track money in and out twice for your transactions. I've "fixed" your data on the attached to show how I'd do it. The pivot table has a second column of running total to show if you are in the black or not. See if the attached helps.

Hi Finance. Yes, you didn't explain it very well, but I gave it a shot anyway.

I came up with a formulaic solution that gives you an answer for each day, but you need to add a couple columns for each day. Expand the ranges as necessary.

The formula uses the "Sumifs" function. Please go read up on that if you're unfamiliar. Basically, it looks at a date, for instance 11/10/11, and tests each line of your data to see if the Settlement date is less than or equal to (<=) and the Maturity date is greater than the chosen date. A complicated 'between' if you will. It performs that test for Funds borrowed, then performs the test again for funds lent and subtracts it.

The two columns in blue are the new ones. I can't figure out how to get the "<=" into the sumifs formula itself. If I could, you wouldn't need these columns. Maybe someone else knows.

My answers are the same as the previous posters, but slightly different from yours. So either there is another condition we aren't aware of, or the manual calculations may not be correct. For instance, the 16th you show 10-2=8, but the 16th is a maturity date, and the 10 should be gone so it's just -2. At least, that is the rule that was applied on the 10th when the 50 wasn't counted. Capisce?

Hope this helps. See Luciano file attached. (Tenor humor... not much though)