Need to sum values if certain criteria is met until a value is met, after that all other values that meet criteria return zero.

[Checkhe]

Hey all,

New to posting so I apologies if I fumble translating my excel need into words. Happy to clarify any confusion.

I am working on a borrowing base model where Notes are pledged rather than typical A/R and Inventory. I have worked in certain limiting factors such as state, property type, days past due, and LTV to name a few. The way I have done it is not likely the most efficient way, but the model is working as intended. I'm certainly not the most proficient in excel.

My goal, while keeping all the other limitations in place, is this:

On an aggregate basis, no notes located in Oregon may comprise more than 20% of the asset pool. Once the sum of all notes located in Oregon reach 20% of the asset pool, a $0 value is return for any following notes located in Oregon.

1. The state and UPB are manual inputs.
2. The % of asset pool is a formula.
3. I have hardcoded the numbers in the "Needed Return" column to show what number I need returned from a formula. The "As a %" is just to display the aggregate percentage.
   • Please note that neither the "Needed Return" nor "As a %" columns exist in the model.
4. Since the first Oregon note of $5MM is only 12.5% of the total asset pool, the full amount carries through.
5. Since the second Oregon note of $5MM on an aggregate basis with the note above exceeds 20% of the total asset pool, only $3MM carries through. This results in these two Oregon notes equaling 20% of the asset pool.
6. Given that the 20% of asset pool has already been met for Oregon, the third note of $10MM (and all following Oregon notes) carries through no amount and returns a zero.

Any assistance that anyone can provide would be greatly appreciated. Thank you!

 

[KRice]

Do you know the size of the asset pool ahead of time (in this case $40M)? I'm assuming you do, otherwise you wouldn't be able to process the first note for OR. Maybe something like this?

MrExcel_20230623.xlsx
	A	B	C	D	E
1	State	UPB	% of Asset Pool	Needed Return	As a %
2	OR	$ 5,000,000.00	12.5%	$ 5,000,000.00	12.5%
3	OR	$ 5,000,000.00	12.5%	$ 3,000,000.00	7.5%
4	OR	$ 10,000,000.00	25.0%	$ -	0.0%
5	AZ	$ 10,000,000.00	25.0%	$ 10,000,000.00	25.0%
6	AZ	$ 10,000,000.00	25.0%	$ 10,000,000.00	25.0%
7		$ 40,000,000.00	100.0%	$ 28,000,000.00	70.0%
Sheet2

Cell Formulas
Range	Formula
C2:C6,E2:E6	C2	=B2/$B$7
D2:D6	D2	=IF(A2="OR",MAX(0,MIN(B2,0.2*$B$7-SUMIF(A$1:A1,"OR",B$1:B1))),B2)
B7:E7	B7	=SUM(B2:B6)

 

[Checkhe]

Do you know the size of the asset pool ahead of time (in this case $40M)? I'm assuming you do, otherwise you wouldn't be able to process the first note for OR. Maybe something like this?

MrExcel_20230623.xlsx
	A	B	C	D	E
1	State	UPB	% of Asset Pool	Needed Return	As a %
2	OR	$ 5,000,000.00	12.5%	$ 5,000,000.00	12.5%
3	OR	$ 5,000,000.00	12.5%	$ 3,000,000.00	7.5%
4	OR	$ 10,000,000.00	25.0%	$ -	0.0%
5	AZ	$ 10,000,000.00	25.0%	$ 10,000,000.00	25.0%
6	AZ	$ 10,000,000.00	25.0%	$ 10,000,000.00	25.0%
7		$ 40,000,000.00	100.0%	$ 28,000,000.00	70.0%
Sheet2

Cell Formulas
Range	Formula
C2:C6,E2:E6	C2	=B2/$B$7
D2:D6	D2	=IF(A2="OR",MAX(0,MIN(B2,0.2*$B$7-SUMIF(A$1:A1,"OR",B$1:B1))),B2)
B7:E7	B7	=SUM(B2:B6)

Hi KRice,

To answer your question, the size of the asset pool will scale up and down based on the number of notes input into the model. With that said, I used the formula you provided and it appears to be a working solutions. I will take some time this weekend to stress test the model for scenarios that break the functionality. If I do find a scenario, I'll reach back out. If not, thank you very very much for taking the time to respond! Cheers!!

 

[KRice]

That sounds good...just let me know what you discover. I suspect one issue then will be the reference to cell $B$7 where I've assumed (probably incorrectly) that it represents the sum of notes above it. Instead, it sounds as if $B$7 should refer to some schedule relating the number of notes above it to a total pool amount. If that is true, then you will probably want another logic check to confirm that any potential note to be added will not, when considered in the aggregate will all other notes, exceed the total pool amount?

 

[Checkhe]

That sounds good...just let me know what you discover. I suspect one issue then will be the reference to cell $B$7 where I've assumed (probably incorrectly) that it represents the sum of notes above it. Instead, it sounds as if $B$7 should refer to some schedule relating the number of notes above it to a total pool amount. If that is true, then you will probably want another logic check to confirm that any potential note to be added will not, when considered in the aggregate will all other notes, exceed the total pool amount?

For my use, I haven't yet found a way to break your solution. I will keep trying to find something in the meantime, but I greatly appreciate you taking the time to respond to my post. Thank you greatly!

 

[KRice]

You're welcome...I'm happy to help.