Data bounded by high/low that resets when threshold exceeded

[quantmaven]

Hi,
How can I make a more intelligent formula that does this:
If any data in the previous row is out of the boundaries, data in next row (yellow) resets back to its threshold? I made an example with two columns that work but it's poorly written and it would become a nightmare with many columns. I feel an array must be used but I'm not sure how to do it. Thanks!

Reset_Value.xlsx
	A	B	C
1	RESET VALUE	50%	50%
2	HI BOUND	60%	60%
3	LO BOUND	40%	40%
4
5	DATE	ABC	XYZ
6	2012-12-31	61.0%	39.0%
7	2013-01-31	50.0%	50.0%
Sheet1

Cell Formulas
Range	Formula
C6	C6	=1-B6
B7:C7	B7	=IF(OR($B6>$B$2,$B6<$B$3,$C6>$C$2,$C6<$C$3),B1,B6)

 

[StephenCrump]

I think this is what you're after - all four columns reset because column E is outside its Hi Bound?

	A	B	C	D	E
1	RESET VALUE	50%	50%	40%	60%
2	HI BOUND	60%	60%	55%	65%
3	LO BOUND	40%	40%	25%	55%
4
5
6		60%	41%	50%	66%
7	Excel365	50%	50%	40%	60%
8	Earlier versions	50%	50%	40%	60%
Sheet9

Cell Formulas
Range	Formula
B7:E7	B7	=IF(AND(B6:E6<=B2:E2,B6:E6>=B3:E3),B6:E6,B1:E1)
B8:E8	B8	=IF(AND($B6:$E6<=$B2:$E2,$B6:$E6>=$B3:$E3),B6,B1)
Dynamic array formulas.

 

[quantmaven]

Hi Stephen, thanks alot, I'm still trying to see how to implement your solution on my actual excel file:

https://factorbased.am/wp-content/uploads/2021/01/Rebalancing_Boundaries-1.xlsx

 

[StephenCrump]

For the two asset portfolio rebalance you've shown, we can simplify the formula as follows (If one is overweight, the other must be correspondingly underweight and vice versa). The anomalous return highlighted is just to illustrate:

	A	B	C	D	E	F	G	H	I	J
1	Values			Returns			5%	50%	50%
2							LO	45%	45%
3	Date	USL	INT	Date	USL	INT	HI	55%	55%
4	31/12/2012	623.2	967.7	31/12/2012	0.0%	0.0%	31/12/2012	50.0%	50.0%	31/12/2012
5	31/01/2013	656.9	1010.7	31/01/2013	5.4%	4.5%	31/01/2013	50.2%	49.8%	31/01/2013
6	28/02/2013	660.7	1040.7	28/02/2013	0.6%	3.0%	28/02/2013	49.6%	50.4%	28/02/2013
7	28/03/2013	690.6	1072.5	28/03/2013	4.5%	3.1%	28/03/2013	50.0%	50.0%	28/03/2013
8	30/04/2013	714.3	1087.4	30/04/2013	3.4%	-20.0%	30/04/2013	55.0%	45.0%	30/04/2013
9	31/05/2013	730.3	1151.8	31/05/2013	2.2%	5.9%	31/05/2013	54.1%	45.9%	31/05/2013
10	28/06/2013	734.2	1138.5	28/06/2013	0.5%	-1.2%	28/06/2013	54.5%	45.5%	28/06/2013
11	31/07/2013	757.7	1209.6	31/07/2013	3.2%	6.3%	31/07/2013	53.8%	46.2%	31/07/2013
12
Sheet1

Cell Formulas
Range	Formula
H2:I2	H2	=H1-$G$1
H3:I3	H3	=H1+$G$1
G4:G11,J4:J11	G4	=D4
H4:I4	H4	=H1
H5:I11	H5	=MEDIAN(H$2,H$3,H4*(1+E5)/(SUMPRODUCT($H4:$I4,1+$E5:$F5)))
F5:F7,E9:F11,E5:E8	F5	=C5/C4-1

But how do you want to generalise this? In the example below, will you reset all three assets (option A) or take the Option B minimal approach. What if there were 10 assets? Do you rebalance all assets (with associated transaction costs) every time one gets out of its permitted range?

	A	B	C
Permitted range	45-65	30-50	0-10
Neutral	55	40	5
Actual	60	25	15
Options:
A) Reset	55	40	5
B) Buy B, sell C	60	35	5

 

[quantmaven]

I made it two assets to simplify the basic formula but you make an interesting point about transactions costs if it is a full reset. Yeah it is smarter with your B) option.

Would you be down to do check this out with me and I paypal/xoom you some money for the time? I need to find a solution to my spreadsheet of like 7 assets. Thanks

Send me an email at quantmaven@gmail.com

 

[StephenCrump]

This is a volunteer forum. Please see Rules #4 and #5: Message Board Rules

The website does provide some consulting services options: Consulting Services