Calculate Recovery Time

[lorena.]

Hi everyone

I want to calculate the Revovery Time after a Maximum Drawdown of an asset.
I managed to calculate the Maximum Drawdown, however I do not know how to get the Recovery Time.
Recovery Time = Date when Price >= Peak before Max. Drawdown.

Attached you can find my Excel-File.

Thank you!

[davsth]

I am not sure what you want can you explain in words how you reach this result.

Are you finding the time it takes the value to above the maximum attained before the low?
so the maximum is
=MAXIFS(B2:B28,A2:A28,"<"&G2) if this in in F5

then as an array
=MIN(IF((A2:A28>G2)*(B2:B28>F5)*(A2:A28),A2:A28,""))

or combined
=MIN(IF((A2:A28>G2)*(B2:B28>MAXIFS(B2:B28,A2:A28,"<"&G2))*(A2:A28),A2:A28,""))

[lorena.]

I calculated the maximum Drawdown, which is the difference between the highest and the lowest price value in a period. This is the value is G1.
The highest price value is the peak of 8906.90268, which can be calculated with this formula you gave me:
=MAXIFS(B2:B28,A2:A28,"<"&G2)
The lowest price value after this peak is in B26 on the 25.01.2020 (G2) which results in my calculated Max. Drawdown (max loss) in G1.

Now in G3 I want to calculate on which date the price has for the first time after the loss (after B26) again reached a higher value than the peak (B19) before. In my example this would give me an output of 27.01.2020 because there the price is for the first time higher than 8906.90268 (peak). What I want is the formula to calcualte this in G3.

[lorena.]

or combined
=MIN(IF((A2:A28>G2)*(B2:B28>MAXIFS(B2:B28,A2:A28,"<"&G2))*(A2:A28),A2:A28,""))

This seems to work I guess, but could you explain in words what the red parts do? I do not quite understand the synthax.

[davsth]

Simple bit first the & just joins the bits together (concatenate)

If you put John in a1 and Smith in a2 =a1&" "&a2 would give John Smith

here to make the maxifs work you are say <g2, we are wanting to find the maximum value prior to the minimum that you have calculated, so it is the maximum value before the date of the minimum

If you put a logic expresssion in excel it evalutes as true or false

so dates greater than minimum produces a list of true and falses, as does values greater than the previous maximum, if you multiply them together and multiply them by the dates, this gives you a list of all the dates since the minimum where the value was above the previous maximum. You want the first time this happens which is the smallest date so a minimum

it could be simplified to
=MIN(IF((A2:A28>G2)*(B2:B28>MAXIFS(B2:B28,A2:A28,"<"&G2)),A2:A28,""))

[lorena.]

Okay perfect thank you!

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.