Financial Data and Arranging into Tiers

[Tommy_Alterax]

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 

[patrick_digan]

@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!