One of the key ratios to take into account in a portfolio analysis is the drawdown, the time the price of an asset declines, or in other words, all the time the asset is not doing new historical highs. This is crucial because it allows us to determine the volatility it has, the time it spends in decline and the momentum.
The drawdown can be used to measure the risk, Identify warning signs and evaluate the performance of investment strategies.This case study analyzes the DD behaviour of the S&P 500, the most widespread stock investing nowadays, in a simple way using spreadsheets and filtering by percentage of decline.
<< Download Spreadsheet Copy>>
Step by step:
1. Download the historical data in a public source, in this case I used investing.com, and export as a csv. If necessary download by parts and merge the data.
2. Format every column in the right way (dates dd/mm/yyyy, percentages, integers...)
3. Sort the database by ascendent order by date.
4. Create columns for calculations and a pivot table to show the results and establish a filter. All the formulas are only the first cell, they should be dragged all over the column.
- Max Price =max($B$2:B2)
- Relative DD =(B2/H2)-1
- Days =IF(B2=H2, 0, J1+1)
- DD or not - Auxiliary Column =IF(J3=0,J2,0)
- Max DD% =IF(K2<>0,MIN(INDIRECT("I"&(ROW()-K2)&":I"&ROW())),"")
- Days until DD =IF(K2<>0,INDEX(J:J, MATCH(MIN(INDIRECT("I"&(ROW()-K2)&":I"&ROW())), INDIRECT("I"&(ROW()-K2)&":I"&ROW()), 0) + (ROW()-K2) - 1),"")
- MAX DD % FILTERED =IF(L2<>"",IF(L2<='PIVOT TABLE'!$B$2,L2,""),"")
- DAYS FILTERED =IF(N2<>"",M2,"")