Issue annualizing returns

Hello,

I currently have a spreadsheet that consist of monthly returns from 1993 - 1998. However, the data is categorised into different period of which some do not reach a year of longevity and/or transition into another period before the calendar year is over?

How can I go about calculating the annualised returns in this case? I have attached an example of the file that I am working with

Thanks joeu2004. I think that is exactly what I what is looking for.

However, I have come one more issue I would like to calculate the Maximum Drawdown for a noncontinuous data set which contains different states (very bad, bad, gok, good).

So basically I would like to calculate the Maximum Drawdown for the each state but as you can see the dates are not continuous so I cannot figure out how to get around this without having to manually do this?

I have attached the example file for ease.

Thanks

I wonder if the following would be sufficient for your purposes:

{ =MIN(IF($B$2:$B$360=1,F2:F360)) - MAX(IF($B$2:$B$360=1,F2:F360)) }


Formulas displayed with curly brackets {...} are array-entered. Type the formulas without the curly brackets, then press ctrl+shift+Enter instead of just Enter. Excel displays the curly brackets in the Formula Bar to indicate that the formula is array-entered.

Technically, that is not the correct calculation of MDD, as I understand it, to wit (per Investopedia): "A maximum drawdown (MDD) is the maximum loss from a peak to a trough of a portfolio, before a new peak is attained. Maximum Drawdown (MDD) is an indicator of downside risk over a specified time period".

In other words, I believe we should look at each peak-to-trough series separately. That goes beyond the scope of what I willing to handle right now. And I'm not sure how that fits with your selection methodology for each state.

Ok yes in the past I have used a helper column and have done for example for each of the states (assuming data runs from B2 to B55)

Drawdwon = B2/(MAX($B$2:B2)-1
Drawdwon = B2/(MAX($B$2:B3)-1
....etc
Drawdown = B55/(MAX($B$2:B55)-1

And then from the results obtained I have done MIN(Drawdown). The issue here is the fact that data is not continuous for the different states so there would need to be a way to incorporate in the formula wether the following date is continous or not

Errata....

Originally Posted by joeu2004

{ =MIN(IF($B$2:$B$360=1,F2:F360)) - MAX(IF($B$2:$B$360=1,F2:F360)) }

After reading about MDD in more detail, the "possibly applicable" formula would be:

{ =MIN(IF($B$2:$B$360=1,F2:F360)) / MAX(IF($B$2:$B$360=1,F2:F360)) - 1 }

But as I noted previously, that might not be the appropriate MDD formula. It depends on how the subset of rows 2:360 (defined by 1 in column B, I presume) is selected.

Originally Posted by mena137

I have used a helper column and have done for example for each of the states (assuming data runs from B2 to B55) [corrected]

Drawdwon = B2/(MAX($B$2:B2)-1
Drawdwon = B3/(MAX($B$2:B3)-1
....etc
Drawdown = B55/(MAX($B$2:B55)-1

And then from the results obtained I have done MIN(Drawdown). The issue here is the fact that data is not continuous

Again, I am not entirely gronking how you are selecting rows based on column B, and whether the data fits the MDD model. I have not looked at the numbers in detail. Off-hand, I do not believe your method calculates the MDD, even if the data were continuous. Refer to the example in https://www.investopedia.com/terms/m/maximum-drawdown-mdd.asp.

Nevertheless, for your calculation, I wonder (wild guess) if the following would do the trick.

The helper formula for "very bad" (column B) category 1 (column F) should be:

L2: { =IF(B2=1, F2 / MAX(IF($B$2:B2=1, F$2:F2)) - 1, "") }

Copy L2 down through L360.

Then =MIN(L2:L360) ignores the rows that are not selected by column B because MIN ignores text (the null string).

The use of F$2 instead of $F$2 allows for copying L2:L360 into M2:M360 for "very bad" category 2 (column G).