Find the Maximum number in a series of Data

[mayanksaini11]

I have an Excel file in which I record my equity Portfolio value on a daily basis at the end of every day. I have a column named 'Drawdown' where I calculate the portfolio value from the peak portfolio value. When the value is below zero that means my portfolio is running below the peak. On the contrary, where the Drawdown value is zero, it means that the portfolio value is at its peak.
Now with this, I have a series of values in the Drawdown column. What I am trying to do is to find a minimum value in a series of values whenever I am in the drawdown phase. The output that I am trying to display in column E is mentioned below: Please suggest to me the formula to display the below output in column E in excel 2010.

A1	B1	C1	D1	E1
Trade Date	Day	Portfolio Value	DrawDown	Max DrawDown
15/03/2023	Wednesday	16,848	-
16/03/2023	Thursday	2,075	-14,773
20/03/2023	Monday	-369	-17,217	Minimum
21/03/2023	Tuesday	1,313	-15,535
23/03/2023	Thursday	5,900	-10,948
24/03/2023	Friday	8,431	-8,417
27/03/2023	Monday	4,929	-11,919
28/03/2023	Tuesday	1,008	-15,840
29/03/2023	Wednesday	6,295	-10,553
31/03/2023	Friday	3,821	-13,027
03/04/2023	Monday	18,594	-
05/04/2023	Wednesday	20,683	-
06/04/2023	Thursday	28,711	-
11/04/2023	Tuesday	33,247	-
12/04/2023	Wednesday	38,362	-
18/04/2023	Tuesday	36,708	-1,653
19/04/2023	Wednesday	35,694	-2,667	Minimum
20/04/2023	Thursday	43,699	-
24/04/2023	Monday	38,567	-5,132	Minimum
25/04/2023	Tuesday	40,795	-2,904
26/04/2023	Wednesday	43,977	-

 

[mayanksaini11]

Okay, I have it 50% match. You need to tell me what is wrong.

mr excel questions 29.xlsm
	A	B	C	D	E	F
1	Trade Date	Day	Portfolio Value	DrawDown	Max DrawDown	Your Expectation
2	2023-03-15	Wednesday	16,848		Minimum
3	2023-03-16	Thursday	2,075	-14,773
4	2023-03-20	Monday	-369	-17,217	Minimum	Minimum
5	2023-03-21	Tuesday	1,313	-15,535
6	2023-03-23	Thursday	5,900	-10,948
7	2023-03-24	Friday	8,431	-8,417
8	2023-03-27	Monday	4,929	-11,919
9	2023-03-28	Tuesday	1,008	-15,840
10	2023-03-29	Wednesday	6,295	-10,553
11	2023-03-31	Friday	3,821	-13,027
12	2023-04-03	Monday	18,594	0
13	2023-04-05	Wednesday	20,683	0
14	2023-04-06	Thursday	28,711	0
15	2023-04-11	Tuesday	33,247	0
16	2023-04-12	Wednesday	38,362	0
17	2023-04-18	Tuesday	36,708	-1,654
18	2023-04-19	Wednesday	35,694	-2,668	Minimum	Minimum
19	2023-04-20	Thursday	43,699	0
20	2023-04-24	Monday	38,567	-5,132		Minimum
21	2023-04-25	Tuesday	40,795	-2,904	Minimum
22	2023-04-26	Wednesday	43,977	0
Mayanksaini11

Cell Formulas
Range	Formula
E2:E22	E2	=IF(CHOOSE(IFS(ROW(E2)<=3,4,ROW(E2)<=12,1,ROW(E2)<=17,4,ROW(E2)<=19,2,ROW(E2)=20,4,ROW(E2)<=22,3,ROW(E2)>22,4),MIN($D$4:$D$12),MIN($D$18:$D$19),MIN($D$21:$D$22),"")=D2,"Minimum","")
D3:D22	D3	=C3-MAX($C$2:C3)

Yes, You have reached near. I would like to put some light on some discrepancies, which you have already highlighted in Yellow. Let me just clarify the flow of Data with the help of below image:

Here I have added for you 2 columns 'Daily PL' and 'Just for understanding' to explain the data more. Daily PL may be positive or negative, it affects the Cumulative Portfolio value. You can observe how this is happening.
Coming to the DD column, I have highlighted the different phases for better understanding. The data is Black and White, either the Portfolio is at its Peak/Highest Point/All time High or it is in the DD phase. There is no third scenario. Now when the Portfolio is at its Peak, there the DD is Zero, so there is no question of Max DD. Therefore in row 3 where the Portfolio is at All time high/Peak, there is no DD mentioned.

Phase 1
1st DD phase starts from row 4 where the Portfolio has come down a bit as compared to the previous value hence there is DD. This is the 1st day of 1st DD phase. This DD phase will go on till the Portfolio makes a new high or in other words, DD becomes zero which will happen in row 13. Therefore the Max DD value of this 1st phase i.e. E4-E12 is in cell E5 -17217.

From row 13 till Row 17 every day the Portfolio was making an all-time high. Therefore no DD, there is no question on Max DD.

Phase 2
2nd DD phase lasts for just two days in rows no. 18 and 19. The DD values have come from the formula. Out of the two days, on 19/04 the portfolio value was at minimum or DD was highest in the 2nd DD phase.

On 20/04/2023 the Portfolio was at All time high/Peak, hence no DD, no question of Max DD

Phase 3
Similarly, on 24/04/2023 the Portfolio has fallen from its peak and entered another DD phase. It lasts for only two days before it makes another All time high/Peak. In these two days, the Maximum DD was on 24-04-2023 which was -5132, or in other words the Portfolio value was at its minimum/Trough which was at 38567. On 25/04/2023 the Portfolio was on the 2nd day of the 3rd DD phase, though recovered from the Lowest point.

On 26/04/2023 Portfolio was at All time high, there is no DD and no question of Max DD.

So going by your formula, the 1st and 2nd Phase Max DD/Minimum portfolio point is well computed. The 3rd phase max DD is not well computed. It should be on -5132 and not -2904. Also on Day 1 when there was profit, the portfolio was at All time high there was no DD, the formula must be tweaked here also.

Really Appreciate your efforts.

 

[awoohaw]

Okay, I realized I did not start the worksheet section on the correct row and that was putting the "Minimum" calculation on the wrong rows.

As I read your statement above, which is quite confusing as it uses terminology I am not comfortable with. I've asked you to just use column and value references. I'm trying to understand.

But there is one point of clarification that I need you to confirm:
when you say "Not in the drawn down period" you have stated that it is FIXED at:
1st phase of DD is within Cells D4 to D12,
2nd phase of DD is within Cells D18 to D19
3rd phase of DD is within Cells D21 to D22.

and then above you say it is based on the values in the cells (they are not negative)?

So, if it is the later, this is a clear example of how you have confused the reader by saying things that are not a part of any calculation.
So: Is your requirement for "Minimum" in column E really:

I want to identify the minimum value of consecutive groups of negative numbers in Column D?​

(no investment terminology ... just a column letter and how values in the column are to be considered.)

Please clarify.

 

[mayanksaini11]

Okay, I realized I did not start the worksheet section on the correct row and that was putting the "Minimum" calculation on the wrong rows.

As I read your statement above, which is quite confusing as it uses terminology I am not comfortable with. I've asked you to just use column and value references. I'm trying to understand.

But there is one point of clarification that I need you to confirm:
when you say "Not in the drawn down period" you have stated that it is FIXED at:
1st phase of DD is within Cells D4 to D12,
2nd phase of DD is within Cells D18 to D19
3rd phase of DD is within Cells D21 to D22.

and then above you say it is based on the values in the cells (they are not negative)?

So, if it is the later, this is a clear example of how you have confused the reader by saying things that are not a part of any calculation.
So: Is your requirement for "Minimum" in column E really:

I want to identify the minimum value of consecutive groups of negative numbers in Column D?​

(no investment terminology ... just a column letter and how values in the column are to be considered.)

Please clarify.

Sorry for the inconvenience. The image that I pasted in my last message started the data range from row 2 and what you used in XL2bb was from row 1. So in your example, the DD ranges are:
1st phase of DD is within Cells D3 to D11 as against D4 to D12 in my example in image posted earlier
2nd phase of DD is within Cells D17 to D18 as against D18 to D19 in my example in image posted earlier
3rd phase of DD is within Cells D20 to D21 as against D21 to D22 in my example in image posted earlier


But there is one point of clarification that I need you to confirm:
when you say "Not in the drawn down period" you have stated that it is FIXED at:
"Not in the drawdown period" means there is no DD. DD is zero. DD is not fixed based on rows. It is based on the values.

So: Is your requirement for "Minimum" in column E really:

I want to identify the minimum value of consecutive groups of negative numbers in Column D?​

​

Yes. In the DD column, there are 3 sets of consecutive numbers, I want the minimum value of each set separately.​

​

I hope I could clarify.​

​

​

 

[awoohaw]

Okay, and I' not sure how to do that with formulas in Excel 2010, i've been trying to figure a way to select the top of the second group, and I'm having difficulties.
It is possible with 365 using the filter and xmatch functions, among others.
Since you have 2010 (you should update your profile button to display that by the way),
I think your best bet at this is a VBA solution. I am not proficient in that. I wish you luck.