Standard Deviation conversion question

Research
1

In optimizer, P123 provides CAGR and standard deviation for each permutation. I believe it is possible to use those two inputs to somehow calculate the standard deviation of the lognormal returns. I’m having trouble making it work, and am wondering if anyone can help.

I attached a spreadsheet. I shaded in green the two cells that P123 provides, and I shaded in yellow the 1 cell I’m trying to calculate that P123 doesn’t provide in optimizer. I know it’s possible to do the calculation directly on the portfolio equity curve of each permutation (as I did in the attachment), but that’s a lengthy process when you’re going through lots of sim permutations, and I’m hoping there’s a math whiz reading this that can help.

I did try Log-normal distribution - Wikipedia and the “Notation section” has equations that make it appear that it’s possible to do what I’m trying to do. However, when I test those equations with my sample data, the equations don’t seem to hold. Not sure, where I’m going wrong.

Thanks!

StDev conversion.xlsx (137 KB)

2

Top of my head, here’s what I might try…

Volatility(p123) / sqrt(252) = StdDev(Pct)
Ln(1+StdDev(pct)) = StdDev(Ln)
exp^(StdDev(Ln)*sqrt(252))-1 = Volatility(Ln)

Not sure if the jump from StdDev(pct) to StdDev(Ln) is warranted though. I played with this a while ago in VBA, but would have to track down the code. If I were look for the code, would you interested in seeing it?

  • David
3

Are you sure that P123’s stdev is not already calculated from log returns? It should always be calculated that way, I think they know it too.

Primus:
Ln(1+StdDev(pct)) = StdDev(Ln)
No!!

4

x

5

On a side note, I look at the standard deviation for the annual returns versus the daily returns thatP123 reports. I think annual std deviation gives a more realistic assessment of volatility and the likelihood that the AR can be met in the future.

6

@primus - If your VBA code has an equation that converts stdev(pct) and cagr to stdev(ln) I’d be interested. thnx

@aurelaurel - Yes, I’m sure P123 doesn’t calculate it with log returns. If you run a sim with a highly volatile return stream, you’ll be able to see the difference. For low volatility sims, it seems stdev(pct) and stdev(ln) are almost the same.

@davidbv - If you’re taking standard deviation of annual returns, you only have about 15 data points (~15 years in the sim). Why is that a more realistic estimate of volatility? Do you use that standard deviation for statistical significance testing?