+ Reply to Thread
Results 1 to 2 of 2

Calculating cumulative balance....2 Criteria

  1. #1
    Registered User
    Join Date
    11-15-2012
    Location
    Houston, TX
    MS-Off Ver
    Excel 2010
    Posts
    1

    Calculating cumulative balance....2 Criteria

    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
    Attached Files Attached Files
    Last edited by millerhl; 11-16-2012 at 03:43 PM. Reason: Add File

  2. #2
    Valued Forum Contributor
    Join Date
    09-10-2012
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010
    Posts
    929

    Re: Calculating cumulative balance....2 Criteria

    Hi miller,

    Try this... it might bog down your computer if you have a ton of cells to do this, but it should get the job done...

    Let me know if you see something not right...

    Take care,
    Dennis
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1