Morning,
I have Excel 2010. I have a year's worth of investment transactions with various face values and majurity periods. There is a policy that says the cumulative balance of investments can never exceed $20M from any one source/issuer. I need to be able to test to see if the cumulative balance ever exceeded $20M. As far as date fields, I have the beginning date (settlement date) and ending date (maturity date) of the investment transaction only, so I need the formula to be able to know when each date is within the beginning and ending date range of the transaction (>= beginning date and < ending date). Please help! If you have a better suggested output format, please feel free to provide alternatives.
Thanks,
Here is small subset of the investment transactions (with some changes made to protect the information).
INPUT
Face Settlement_Date Maturity_Date SourceID
15000000 2/27/2012 2/28/2012 AKZO
3147000 3/28/2012 3/29/2012 AKZO
20000000 9/4/2012 9/5/2012 APACHE
5000000 9/5/2012 9/7/2012 APACHE
8000000 9/6/2012 9/7/2012 APACHE
5000000 9/6/2012 9/10/2012 APACHE
4150000 9/7/2012 9/10/2012 APACHE
Here is my initial though of what the output needs to look like.
OUTPUT 2/27/2012 2/28/2012…3/28/2012 3/29/2012…9/4/2012 9/5/2012 9/6/2012 9/7/2012
AKZO 15000000 0 3147000 0 0 0 0 0
APACHE 0 0 0 0 20000000 5000000 18000000 9150000
Bookmarks