Hi Guys,
I have a portfolio of transactions where the "Daily Balance" column is duplicated see below
Tier | Portfolio | Range Start | Range End | Daily Balance |
1 | AAA | 0 | 500000 | 2000000 |
2 | AAA | 500001 | 1000000 | 2000000 |
3 | AAA | 1000001 | 2500000 | 2000000 |
4 | AAA | 2500001 | 99999999 | 2000000 |
The daily balance column should reflect how much was used in that Particular Tier - For example the 1st Row Tier 1 the 'Daily Balance of $2M is greater than the Range End of 500k - So the daily balance should reflect 500k for Tier 1 (amount used)
Desired outcome something like as follows :
Tier | Portfolio | Range Start | Range End | Daily Balance (Bal Used) | Bal Carried Fwd Next Tier |
1 | AAA | 0 | 500000 | 500000 | 1500000 |
2 | AAA | 500001 | 1000000 | 499999 | 1000001 |
3 | AAA | 1000001 | 2500000 | 1000001 | 0 |
4 | AAA | 2500001 | 99999999 | 0 | 0 |
I.E now we can see the daily balance that was used in each range .
Note I will often only have 2 tiers for a portfolio however, my daily balance will only stay in Tier 1 as it is small - Any solution will need to catch this scenario -
Sample file attached below
@Tommy_Alterax I think you could use a standard formula tool to calculate the Daily Balance (Bal Used) like this:
max(0,min([Daily Balance]-[Range Start]+[Tier]-1,[Range End]-[Range Start]))
Hopefully that helps point you in the right direction!