The Volatility of Volatility for Stocks and Crypto, Part 4

Putting together the pieces

NTTP
10 min readMay 19, 2024
Four. Photo by Adrian Trinkaus on Unsplash

If you have read our prior articles on this topic, you may realize that we now have most of the pieces that we need to make the “final boss” Heston type stochastic volatility driven price path generator. We are still lacking the drift term, but… in due time. It looks like that can be tacked on at the end.

The Excel file for this article may be found here:

https://github.com/diffent/excel/blob/main/volofvol4D.xlsx

The Excel manipulation to combine all of this together is not difficult, but it is a little tricky since we are putting together formulas from more than one sheet.

Someday (maybe?) Copilot or Devin or whatever A.I. scheme is being touted these days as the program that can do “all the things” may make this easier, according to the purveyors of this type of technology.

Will A.I. Copilot take us here? I think not. Photo by Ryan Miller on Unsplash

“Hey ChatGPT, can you rearrange this Excel file so it is all on one sheet and easy to understand? Oh yeah and don’t mess up the formulas.”

However, we don’t think that you should hold your breath until this occurs.

For now, we have to adjust these equations manually.

Procedures

First, we copy the sheet from Part 3 of this article series to the Excel file from Part 2.

This new sheet we call HE2 since it deals with Heston equation 2… which shows how the volatility path is computed*, with some time dependency.

*Actually it seems to be the variance path; volatility is the square root of variance (see below).

Excel sheet tabs in the new example file. Note sheet HE2 at the right.

We extend the formula-filled row count on sheet HE2 to match the row count we want: 1000 total (defined rather arbitrarily in Part 1 of this article series to mean 1000 trading days).

Then we delete a sheet from the Part 2 Excel file (called randcorr) which was just for the initial build-up and motivation… to avoid confusion. Note that plain randcorr does not appear in the above snapshot of the sheet tabs… we have already deleted it.

Setting the correlated random generators to standard normal

For the two correlated random generator functions in the sheet randcorrWithFeedback which we set up in a prior article (columns D and E), we now set them to generate at mean 0, stdev 1 (standard normal). These will serve as the dW terms in the Heston model. We will adjust the width of the random buzz generated from them by scaling the random result rather than scaling the stdev, to match the analytic formulation. We hinted at doing this in Part 3.

Top of sheet randcorrWithFeedback. Mean 0 and stdev 1 for both generators now. We grey them out to indicate that you shouldn’t mess with these any more, though we did not lock the cells. target correl on the right will be a variable that you can tune. Don’t worry, this model has other tuning parameters.

Then instead of using the unconstrained NORMINV function in sheet HE2 (which we used to develop the concept of a time dependent / sticky volatility as described in Part 3 of this series), we will instead use the VolOfVol partially correlated standard normal generated numbers in column E of sheet randcorrWithFeedback that we coded up in Part 2 of this series. This VolOfVol is standard normal random when viewed or taken by itself, but is also partially correlated to the main random returns generator in column D of that sheet (also mean 0, stdev 1, and will eventually be scaled externally), per the Heston formulation that links its Eqn 1 and Eqn 2 (as we notate them), as described in Part 2 of this series.

Our custom formulation in column E to get correlated random values (from Part 2 of this article series).

Finally, when computing the daily asset return in column D of randcorrWithFeedback, instead of scaling the standard deviation like we did in the prior article build-ups, we just multiply the whole thing by the square root of the time-dependent return variance (volatility squared) that we had computed in sheet HE2 column B, taking care to truncate the latter at 0 (using the MAX function) so we don’t try to take the square root of a negative number.

After modification for this article, in column D formulas, we see the SQRT(.) multiplied by the rest of the stuff we had there, and we removed the extra volatility scaling that was at the end

We have not yet modeled the ‘drift’ term of the return from the Heston formulation (which also exists in the older Black-Scholes type models). This is still a “to do.” Meaning, drift bullish or bearish is 0 for now. This is not to say that we won’t see bullish or bearish price paths; it is just that those results will be strictly from the diffusion portion of the model. As we point out in previous articles, “random” does not necessarily mean “unpatterned,” it just means that the patterns don’t have any predictive power.

Now we can generate some individual price paths in Sheet1 — no changes needed on that sheet from the earlier articles — from this model, and view their character qualitatively… do they look like stock market price paths, or crypto paths? Again, since this is a stochastic model driven by randomness, we get different paths at each Excel refresh.

Example artificially generated price path with the returns in red below it. Note high volatility and low volatility time periods. Volatility is not fully random now as we had modeled in earlier sheets; it can get stuck at high vol, low vol, or any mid-level volatility levels due to the time dependence factor we added in Part 3 of this article series (from the Heston model!)
Here is another sheet refresh with the same model parameter settings. Totally different price path, similar non homogeneous volatility.

For this article, now that we have a nice varying volatility, we overlay the daily returns (in light red) on top of the price path so you can see how the price walk changes as volatility randomly gets larger or smaller over time, and kind of sticks at different levels sometimes.

Model control parameter summary

Since we have not added the mean drift term yet, we only have 4 control parameters; but with these, we can cause a lot of change in how price paths behave:

On sheet randcorrWithFeedback we have target correl with valid values [-1 to 1]

On sheet HE2, we have :

kappa 𝜘

How sticky is our volatility… close to and above 0 = sticky, 1 = not sticky at all. Crazy things may happen to your price paths if you set kappa > 1, but this seems to be kind of a benign zone. See our prior articles discussing overcorrection back to target volatility when kappa is > 1; this may be a usable modeling zone for this parameter. Crazy things will happen to your price paths if you set kappa < 0. You probably shouldn’t do that.

theta 𝜃

Theta is just the long term daily volatility of the asset in question that all this variable volatility stuff wanders around. As a starting point for this value, you could just use some historical volatility for an asset; but take care to use a daily (and not an annualized) version of it.

xi ξ

The volatility of volatility. Per our prior article discussions.

Neutral settings

We can set our model control parameters back to a state where the price paths generated are an ordinary diffusion model [no drift] (Brownian motion from a random returns chosen from a normal distribution). Here are the settings for this:

On sheet randcorrWithFeedback, set target correl to 0.

On sheet HE2, set kappa = 1, theta = 1, xi = 0.

The model back to Brownian motion reference levels, one Excel refresh.

See if you can visually tell the difference between these plain Brownian paths here (neutral settings, stochvol OFF essentially) versus the above stochvol paths.

Cell to formula map

A high level map may help you if anything got lost in translation from Part 3 to this article.

Original formulas from [Quantstart] with our annotations

And looking at this diagram, now we maybe see what is going on with the square roots over v(t). Eqn 2 [our discretized version of it from Quantstart] computes the variance = (stdev²) (2nd moment) and the sqrt(variance) is standard deviation or volatility. Well well. Should have noticed that before.

Wikipedia (snapshot below) adds the intermediate step here that Quantstart didn’t show: Volatility is sqrt(v(t)), but the recurrence relation that we use to find it computes variance (another v word of course; job security for statisticians). As far as Ito’s lemma and the other shout-outs to famous mathematicians, well… you’re on your own there for now.

We should probably go check if we mistakenly use “volatility” when we should say “variance” in this article series. See also: 2nd printing.

From Wikipedia

As there is sometimes confusion possible when manipulating and copy/pasting Excel formulas, if any reader sees an issue with our formulas, please let us know!

Next up: Monte Carlo

In spite of the fact that we have assembled the various pieces of the random path generator model for this stochastic volatility concept, and results seem reasonable, at least qualitatively —

that is, we do get time periods of volatility high and volatility low… and price paths seem to walk reasonably and don’t go crazy

— we are not done yet, because an individual random price path doesn’t tell us much. Stochastic models like this don’t purport to tell us exactly what a price path will be in the future. Rather, they try to estimate a probability distribution of future prices. For this, we will need to aggregate a lot of these randomly generated paths and see what’s up.

We do this in our MCarloRisk3D apps using return distributions that are not normally distributed (e.g. they are just sampled from historical reality), and we do have some aspects of the Heston model available for experimentation in those apps. We refer to the stochastic volatility model in our apps as Heston-inspired, since it departs from pure standard normal generating processes, and uses, in part, empirical return data. The apps use empirical return data for the returns portion of the path generator, and then build an ideal (correlated to the empirical data by a user-tunable factor — Greek rho in the Heston model, target correl in our sheet) standard normal distribution for the volatility path generation.

We did a more formal case study using this stochvol model in our app for Bitcoin price range estimates a while back if anyone is interested:

https://papers.ssrn.com/sol3/papers.cfm?abstract_id=3693387

As for now, you can play around with the parameters of this model and regen the Excel with different parameter settings to see how the model behaves until we have time to do the aggregation.

Unfortunately, it does not seem that there is a way to seed the random number generator in Excel, so it is a little difficult to keep the same random columns and then vary one or model parameters to see how a path changes with randomness held constant. You can do this by selecting, copying, and re-pasting “special” / “as value” one set of random values in columns A and B of randcorrWithFeedback sheet. We could probably find a seedable generator function, and/or hook something up for this to be more automatic, but… later.

Target correl = 0.3 w/ random values fixed in place.
Target correl = 0.1 w/ random values fixed in place. Note similar price curve shape since the same base random values were used.

Caution light🚦May 27 2024

And yet… there still seems to be another missing piece in the price path generation if we compare to (a) how Quantstart does what they call “spot price” path generation and to (b) even the non-stochastic volatility version of this type of model.

We noticed this when trying to figure out how to add the drift term to our Excel. We do not have a drift term in our MCR3D apps, because we just resample from historical returns. If it drifts, it drifts.

From [Quantstart]. Red box surrounds the drift term which we haven’t modeled yet. Yellow box surrounds the term we have modeled (we think!). But what of the EXP( ) function. Hmm…

We compute the price path (so far) in this Excel by merely multiplying up generated 1-centered returns with initial price value of 1 dollar without the EXP( ) in there. The following is a test case plotted using 1% daily return showing the difference between the two formulations. This may be obvious to all of you Guckheimer Sauerkraut Fellowship compute-in-your-head math whizzes, but us coders have to make a plot. Starting at 1 dollar, 1% daily return (0.01), 252 days (1 trading year):

At the end of the year, this yields $12.15 versus $12.30 (EXP method on top) or a difference of slightly over 1.25%. E.g. not great, not terrible… but notable if you are doing these computations “for real.” Perhaps less difference than us leaving out the drift term. Might we expect more than 1.25% drift if the annual risk free interest rate is 4%? Perhaps… Nonetheless, we will add in the EXP function in our Excel example as time permits.

Our confusion may stem from the way we compute the random paths in our MCarloRisk3D apps by direct resampling and remixing of actual daily historical returns as-is. There is no exponentiation needed in that. We are just shuffling around pre-observed returns.

In spite of this caution light which we hope to resolve soon and will post here the results, the following Part 5 aggregation article goes the same either way. Aggregation doesn’t care what kind of potato model we have to generate the random price walks.

Further reading

Hot off the presses May 24 2024, Part 5!

https://medium.com/@nttp/the-volatility-of-volatility-part-5-e415c4f8456e

Reference

[Quantstart] Heston Stochastic Volatility Model with Euler Discretisation in C++, https://www.quantstart.com/articles/Heston-Stochastic-Volatility-Model-with-Euler-Discretisation-in-C/

--

--