Perfect balance of the technical calculation and practical use and application. Not too little,... not too much,... not too long. Histogram is a good visual tool! Thanks for the content. Subscribed.
The formula in drawdown can be shortened: =B2/MAX(B$2:B2)-1. 1) It will not be a drawdown since we take out the initial MIN function but that is redundant because in the max drawdown cell we will use MIN applied to the B (or any) column. 2) Do not lock the column cell ref to avoid having to edit formulas when you drag it across multiple columns of data.
I think loss in terms of percentage is much more meaningful, especially if looking at a long term investment. For example, if you are looking at an investment that went from $10k to $25k in 10 years and it had a $1000 maximum drawdown, where the drawdown occurred could be the difference between a -10% drawdown (from $10k) or a 4% drawdown (from $25k). If you really want to imagine the dollar amount, you could just take the value of the investment before the drawdown, and apply the % change like in my example. Hope that helps!
You're right, I just tested the formula without the MIN wrapper and it produced the same result. I guess way back when I first started using it, I thought the MIN would be a fail-safe. This is the way I've always written the formula, but thank you for simplifying :)
Unfortunately it's been a while since I made this video and I don't have the spreadsheet I used anymore. You'll just have to pause the video and look at the formula. My apologies!
nice video. really helpful do you know how to calculate a drawdown for seperate days in a row... for example: i start with 20 € 01.01.2020 -1 02.01.2020 -2 03.01.2020 -3 04.01.2020 +3 05.01.2020 -5 06.01.2020 -1 i would like to find a formular which calculates the -1;-2;-3 and then starts new for the 05./06.01 cause there was a break at the 04.01 and it woul be cool if there would be a number for many days in row th stock was falling...
For drawdown of separate days, you could manually adjust the formula whenever there is a break. A bit time-consuming depending on your data but doable.. For negative day losing streak, you could try =IF(AND(A2
Reedan By representing them as positive numbers do you mean just flipping the sign? I.E. instead of -5%, 5%? If so, you could just multiply the numbers by -1 in an adjacent column. Thanks so much for your support!
There are two more points: 1. If the asset value goes up and the value is higher than the current peak, it's not drawdown. 2. Sometimes we want to see the maximum drawdowns for each of the peaks, for that purpose, we need to have columns such as peak values and trough values, in addition to drawdowns, and find the minimums for each peaks.
Perfect balance of the technical calculation and practical use and application. Not too little,... not too much,... not too long. Histogram is a good visual tool! Thanks for the content. Subscribed.
The formula in drawdown can be shortened: =B2/MAX(B$2:B2)-1.
1) It will not be a drawdown since we take out the initial MIN function but that is redundant because in the max drawdown cell we will use MIN applied to the B (or any) column.
2) Do not lock the column cell ref to avoid having to edit formulas when you drag it across multiple columns of data.
Yes I realized after the fact it can be shortened :)
Thank you for sharing! How can I adjust this sheet to account for investors making periodic investment contributions ? Thank you
Thanks. That's what I looked for. How would calculate maxDD in days, that is how long took to go back above water ?
You could create a column with a running total of periods, and then take the max of that column
The drawdown 'histogram' to visualize the real-time drawdowns was a pretty good bonus. Easy way to compare ETFs.
Thank you!
Very clear and precise explanation, thanks!
Glad it was helpful! Thanks for the comment :)
Great explanation and demonstration, very helpful. Thanks!
Glad it was helpful! Thank you for the comment :)
Hi!!. Do you have any video explaining the Absolute Drawdown, the loss in money instead of porcentage?. thanks in advance for your videos.
I think loss in terms of percentage is much more meaningful, especially if looking at a long term investment. For example, if you are looking at an investment that went from $10k to $25k in 10 years and it had a $1000 maximum drawdown, where the drawdown occurred could be the difference between a -10% drawdown (from $10k) or a 4% drawdown (from $25k).
If you really want to imagine the dollar amount, you could just take the value of the investment before the drawdown, and apply the % change like in my example. Hope that helps!
@@TactileTrade Thank you very much for your answer
Short question, why is the min(x; 0 ) needed anyway? the difference between B_t - max(B2:B_t) will always be negative or cero.
You're right, I just tested the formula without the MIN wrapper and it produced the same result. I guess way back when I first started using it, I thought the MIN would be a fail-safe. This is the way I've always written the formula, but thank you for simplifying :)
King that video was so good, thanks
Does the first formula for drawdown work in the exact same way for a reversed data sheet with the most recent date being at the top?
To save time, you can press Tab on your keyboard when entering equations to input the selected function. No need to mess with the mouse.
Could you please make this worksheet available for download?
Unfortunately it's been a while since I made this video and I don't have the spreadsheet I used anymore. You'll just have to pause the video and look at the formula. My apologies!
nice video. really helpful
do you know how to calculate a drawdown for seperate days in a row...
for example:
i start with 20 €
01.01.2020 -1
02.01.2020 -2
03.01.2020 -3
04.01.2020 +3
05.01.2020 -5
06.01.2020 -1
i would like to find a formular which calculates the -1;-2;-3 and then starts new for the 05./06.01 cause there was a break at the 04.01
and it woul be cool if there would be a number for many days in row th stock was falling...
For drawdown of separate days, you could manually adjust the formula whenever there is a break. A bit time-consuming depending on your data but doable..
For negative day losing streak, you could try =IF(AND(A2
Excellent video. Thank you so much!
Thank you! Glad you found it helpful.
This was really useful thank you!
Awesome! Great video. Do you know how to represent the drawdowns as positive numbers in excel?
Reedan By representing them as positive numbers do you mean just flipping the sign? I.E. instead of -5%, 5%? If so, you could just multiply the numbers by -1 in an adjacent column. Thanks so much for your support!
@@TactileTrade just switch min and max function ;)
thanks! great balance of info!
There are two more points:
1. If the asset value goes up and the value is higher than the current peak, it's not drawdown.
2. Sometimes we want to see the maximum drawdowns for each of the peaks, for that purpose, we need to have columns such as peak values and trough values, in addition to drawdowns, and find the minimums for each peaks.
Well done! Also, nothing wrong with loving Excel haha
Thank you!
Thanks a lot mate
Glad you liked it!
Great info!
Thank you :)
Nice
[F4] produces the absolute reference in Windows.
The first MIN( is useless