Help me figure out the Target Volatility formula

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
ocrtech
Posts: 47
Joined: Sat Jul 21, 2012 2:18 pm

Re: Help me figure out the Target Volatility formula

Post by ocrtech » Fri Feb 07, 2020 8:32 pm

Uncorrelated wrote:
Fri Feb 07, 2020 4:05 pm

My criticism really isn't that backtesting is useless, just that it is extremely hard to use correctly. Go back to the fundamentals. Figure out what assumptions are used in the strategy. Determine your personal risk tolerance. Do some math. Then start backtesting. If you skip directly to backtesting, you will miss many valuable insights.
I agree with this approach to using backtesting. I do know some use it to data mine or "optimize" their model and often run into the issue of overfitting. In the specific case of the hedgefundie approach, I believe he did a good job of explaining his reasoning and describing his assumptions. The backtesting was used to validate the model he put forth.
ocrtech wrote:
Fri Feb 07, 2020 1:17 pm
I'm not sure where @uncorrelated came up with his estimate of 500 data points. If I recall correctly, the raw data consists of nearly 10k daily trading values. Depending upon the hedgefundie variant being tested, the volatility window being used, and the rebalance timeframe being considered, significantly more data points are utilized in the backtests.
Uncorrelated wrote:
Fri Feb 07, 2020 4:05 pm
The target volatility backtester at portfolio visualizer changes the allocation monthly. This equates to around 500 data points for 40-50 years of data.
I understand where you got your number now. I didn't use PV to run my backtests. I use an exponential weighting of the daily stddev numbers to arrive at my volatility number and this isnt available in PV. I also wanted to use a daily sliding window to generate a new composite volatility window for each day rather than just a single one once a month. This also wasn't available in PV. Using the above method, I had in excess of 10k data points to validate against.

parval
Posts: 17
Joined: Tue Oct 22, 2019 9:23 pm

Re: Help me figure out the Target Volatility formula

Post by parval » Mon Feb 10, 2020 11:43 am

Hydromod wrote:
Fri Jan 31, 2020 10:08 am
I think you are somehow using returns as a surrogate for volatility. PV doesn't report volatility, so you need to externally pull down the daily returns and calculate the standard deviations yourself.

For those looking for the Excel approach (consider Google Sheets too, which directly downloads returns):

Formulas for one-month lookback when
  • column A is UPRO daily adjusted price
  • column B is TMF daily adjusted price
  • one trading month (21 days + 1 end of last month) are rows 2 through 23
  • targU is a target annualized volatility
Construct new columns C and D for daily returns

C3 = (A3/A2) - 1, C4 = (A4/A3) - 1, ...
D3 = (B3/B2) - 1, D4 = (B4/B3) - 1, ...

If working with downward-only volatility (some like this measure) replace with

C3 = IF(A3<A2,"",A3-A2)

Calculate annualized volatility

SDU = stdev(C3:C23) * sqrt(252)
SDT = stdev(D3:D23) * sqrt(252)

(the sqrt(252) is only needed to match target volatility)

rSDU = 1/SDU
rSDT = 1/SDT

Risk parity (volatility):
fracU = rSDU/(rSDU + rSDT)

Risk parity (variance):
fracU = rSDU^2/(rSDU^2 + rSDT^2)

Target volatility:
fracU = min(targU/SDU, fracUmax)

where fracUmax is the maximum UPRO fraction allowed (e.g., 0.8)

In all cases
fracT = 1 - fracU

The risk parity formulas assume that UPRO and TMF are both assigned half of the risk budget. One can also adaptively change the risk budget, perhaps based on the unemployment rate index (UEI).

Assume the fraction of risk assigned to UPRO is riskfracU.

While UEI is falling (signal for non-recession), perhaps riskfracU might be 0.7 or 0.8, and while UEI is rising and accelerating (signal for recession), perhaps riskfracU might be 0 to 0.3. Adjusting riskfracU step by step over several months may lessen whipsaw.

Risk parity (volatility):
rSDU = riskfracU * rSDU

Risk parity (variance):
rSDU = sqrt(riskfracU) * rSDU


I think these are right, folks with more experience please confirm so I don't inadvertently mislead.

Edit: Of course I was wrong right and left to start. That's what I get for doing this off the top of my head. Thanks OCRtech. I've fixed the formulas above.
Thanks for detailing all of this! I made a google sheets, does it seem okay to you? I cross check the monthly with PV and seems ...close?

https://docs.google.com/spreadsheets/d/ ... sp=sharing

User avatar
willthrill81
Posts: 16010
Joined: Thu Jan 26, 2017 3:17 pm
Location: USA

Re: Help me figure out the Target Volatility formula

Post by willthrill81 » Wed Feb 12, 2020 4:20 pm

danielc wrote:
Fri Feb 07, 2020 12:51 am
lock.that.stock wrote:
Thu Feb 06, 2020 8:24 pm
If OP is working on putting together a target volatility calculation spreadsheet, with help from others - would you consider uploading the sheet publicly for review and for others to use?
Good idea! So I went ahead and made a new spreadsheet meant for public consumption:

https://docs.google.com/spreadsheets/d/ ... sp=sharing

This spreadsheet looks at 1 month of daily returns of UPRO and TMF and produces several possible asset allocations according to various criteria that I have been experimenting with:
  • Target Volatility. I actually implemented four versions of target volatility:
    • Use only the volatility of UPRO; assume that TMF has zero volatility. This is how PV works.
    • Use the volatilities of both UPRO and TMF, assuming zero correlation.
    • Use the volatilities of both, and the measured covariance between them.
    • Use the volatilities of both, and assume a permanent correlation of -0.1.
  • Inverse Volatility
  • Maximum Diversification (described on page 16 of this paper).
  • Equal Risk Contribution (described on page 19 of the same paper).
  • Maximum Sharpe Ratio
I chose those purely because they grabbed my interest and I wanted to see what they did.

EDIT: For the Maximum Sharpe Ratio I used iShares SHV as a proxy for the risk-free return rate. Something like the 3-month Treasury Bill or LIBOR would be a better choice, but I have no idea how to pull that data automatically into a Google spreadsheet.
Thank you for sharing this! It just might enable me to avoid subscribing to Portfolio Visualizer. :wink:

Where do you download the daily returns from?
“It's a dangerous business, Frodo, going out your door. You step onto the road, and if you don't keep your feet, there's no knowing where you might be swept off to.” J.R.R. Tolkien,The Lord of the Rings

MotoTrojan
Posts: 8163
Joined: Wed Feb 01, 2017 8:39 pm

Re: Help me figure out the Target Volatility formula

Post by MotoTrojan » Wed Feb 12, 2020 4:23 pm

willthrill81 wrote:
Wed Feb 12, 2020 4:20 pm


Thank you for sharing this! It just might enable me to avoid subscribing to Portfolio Visualizer. :wink:

Where do you download the daily returns from?
I wonder if all the target-volatility users from hedgefundie's thread pushed PV to try to monetize 8-) . I would bet there was a sizable boost in their market-timing toolset users!

User avatar
willthrill81
Posts: 16010
Joined: Thu Jan 26, 2017 3:17 pm
Location: USA

Re: Help me figure out the Target Volatility formula

Post by willthrill81 » Wed Feb 12, 2020 4:26 pm

MotoTrojan wrote:
Wed Feb 12, 2020 4:23 pm
willthrill81 wrote:
Wed Feb 12, 2020 4:20 pm


Thank you for sharing this! It just might enable me to avoid subscribing to Portfolio Visualizer. :wink:

Where do you download the daily returns from?
I wonder if all the target-volatility users from hedgefundie's thread pushed PV to try to monetize 8-) . I would bet there was a sizable boost in their market-timing toolset users!
I'm still trying to work out the easiest way to calculate relative strength of various funds. It can probably be done in a Google Doc, but I'm a complete noob with that.

Edit: Right now, it's looking like Morningstar is the easiest way to compare the relative strength of funds. I'm getting the exact same numbers as from PV with very little additional input from me. I just save the relevant tickers in an Excel file.

That saves me $240/year! :D
Last edited by willthrill81 on Wed Feb 12, 2020 4:48 pm, edited 1 time in total.
“It's a dangerous business, Frodo, going out your door. You step onto the road, and if you don't keep your feet, there's no knowing where you might be swept off to.” J.R.R. Tolkien,The Lord of the Rings

Lost Halls
Posts: 3
Joined: Sun Aug 11, 2019 9:09 pm

Re: Help me figure out the Target Volatility formula

Post by Lost Halls » Wed Feb 12, 2020 4:42 pm

willthrill81 wrote:
Wed Feb 12, 2020 4:20 pm
danielc wrote:
Fri Feb 07, 2020 12:51 am
lock.that.stock wrote:
Thu Feb 06, 2020 8:24 pm
If OP is working on putting together a target volatility calculation spreadsheet, with help from others - would you consider uploading the sheet publicly for review and for others to use?
Good idea! So I went ahead and made a new spreadsheet meant for public consumption:

https://docs.google.com/spreadsheets/d/ ... sp=sharing

This spreadsheet looks at 1 month of daily returns of UPRO and TMF and produces several possible asset allocations according to various criteria that I have been experimenting with:
  • Target Volatility. I actually implemented four versions of target volatility:
    • Use only the volatility of UPRO; assume that TMF has zero volatility. This is how PV works.
    • Use the volatilities of both UPRO and TMF, assuming zero correlation.
    • Use the volatilities of both, and the measured covariance between them.
    • Use the volatilities of both, and assume a permanent correlation of -0.1.
  • Inverse Volatility
  • Maximum Diversification (described on page 16 of this paper).
  • Equal Risk Contribution (described on page 19 of the same paper).
  • Maximum Sharpe Ratio
I chose those purely because they grabbed my interest and I wanted to see what they did.

EDIT: For the Maximum Sharpe Ratio I used iShares SHV as a proxy for the risk-free return rate. Something like the 3-month Treasury Bill or LIBOR would be a better choice, but I have no idea how to pull that data automatically into a Google spreadsheet.
Thank you for sharing this! It just might enable me to avoid subscribing to Portfolio Visualizer. :wink:

Where do you download the daily returns from?
The spreadsheet automatically pulls the daily returns for the given ticker. If you want to see daily returns for this month (February) simply change the "Month" value under "Target Month" to 2. Adding onto that, If you wish to use another ETF besides UPRO / TMF you can edit the ticker that the spreadsheet pulls returns for by simply changing the ticker of "UPRO" or "TMF" in cells above the daily returns to a different one.

Hydromod
Posts: 344
Joined: Tue Mar 26, 2019 10:21 pm

Re: Help me figure out the Target Volatility formula

Post by Hydromod » Wed Feb 12, 2020 4:48 pm

willthrill81 wrote:
Wed Feb 12, 2020 4:20 pm
danielc wrote:
Fri Feb 07, 2020 12:51 am
EDIT: For the Maximum Sharpe Ratio I used iShares SHV as a proxy for the risk-free return rate. Something like the 3-month Treasury Bill or LIBOR would be a better choice, but I have no idea how to pull that data automatically into a Google spreadsheet.
Thank you for sharing this! It just might enable me to avoid subscribing to Portfolio Visualizer. :wink:

Where do you download the daily returns from?
To get the last 91 days, I usually do something like entering

=GOOGLEFINANCE(B1,"price",today()-91, today(), "DAILY")

in cell A2

and have the fund ticker in cell B1. This fills out a 2-column table.

Unfortunately this doesn't update with the end of day returns until well after market close.

User avatar
willthrill81
Posts: 16010
Joined: Thu Jan 26, 2017 3:17 pm
Location: USA

Re: Help me figure out the Target Volatility formula

Post by willthrill81 » Wed Feb 12, 2020 4:50 pm

Lost Halls wrote:
Wed Feb 12, 2020 4:42 pm
The spreadsheet automatically pulls the daily returns for the given ticker. If you want to see daily returns for this month (February) simply change the "Month" value under "Target Month" to 2. Adding onto that, If you wish to use another ETF besides UPRO / TMF you can edit the ticker that the spreadsheet pulls returns for by simply changing the ticker of "UPRO" or "TMF" in cells above the daily returns to a different one.
Hydromod wrote:
Wed Feb 12, 2020 4:48 pm
To get the last 91 days, I usually do something like entering

=GOOGLEFINANCE(B1,"price",today()-91, today(), "DAILY")

in cell A2

and have the fund ticker in cell B1. This fills out a 2-column table.

Unfortunately this doesn't update with the end of day returns until well after market close.
Thanks to you both!
“It's a dangerous business, Frodo, going out your door. You step onto the road, and if you don't keep your feet, there's no knowing where you might be swept off to.” J.R.R. Tolkien,The Lord of the Rings

User avatar
Topic Author
danielc
Posts: 1031
Joined: Sun Dec 10, 2017 4:48 am
Location: Iowa, USA
Contact:

Re: Help me figure out the Target Volatility formula

Post by danielc » Wed Feb 12, 2020 5:22 pm

willthrill81 wrote:
Wed Feb 12, 2020 4:20 pm
Thank you for sharing this! It just might enable me to avoid subscribing to Portfolio Visualizer. :wink:

Where do you download the daily returns from?
I'm glad that it's useful. To get the daily returns, Google Sheets has a function called GOOGLEFINANCE() that gives you a column of daily or weekly data. For example:

Code: Select all

=GOOGLEFINANCE("UPRO", "close", TODAY()-365, TODAY()-1, "DAILY")
This should produce a table with all the daily closing prices for the past year. In my spreadsheet I compute the returns manually based on closing prices. Apparently there is an option to ask for the daily returns directly, but I couldn't figure out how to make it work.

Stump909
Posts: 31
Joined: Mon Jan 27, 2020 12:13 pm

Re: Help me figure out the Target Volatility formula

Post by Stump909 » Wed Feb 12, 2020 11:51 pm

Yahoo has easily accessible daily returns.

Stump909
Posts: 31
Joined: Mon Jan 27, 2020 12:13 pm

Re: Help me figure out the Target Volatility formula

Post by Stump909 » Fri Feb 14, 2020 12:54 pm

danielc wrote:
Fri Feb 07, 2020 12:51 am
lock.that.stock wrote:
Thu Feb 06, 2020 8:24 pm
If OP is working on putting together a target volatility calculation spreadsheet, with help from others - would you consider uploading the sheet publicly for review and for others to use?
Good idea! So I went ahead and made a new spreadsheet meant for public consumption:

https://docs.google.com/spreadsheets/d/ ... sp=sharing

This spreadsheet looks at 1 month of daily returns of UPRO and TMF and produces several possible asset allocations according to various criteria that I have been experimenting with:
  • Target Volatility. I actually implemented four versions of target volatility:
    • Use only the volatility of UPRO; assume that TMF has zero volatility. This is how PV works.
    • Use the volatilities of both UPRO and TMF, assuming zero correlation.
    • Use the volatilities of both, and the measured covariance between them.
    • Use the volatilities of both, and assume a permanent correlation of -0.1.
  • Inverse Volatility
  • Maximum Diversification (described on page 16 of this paper).
  • Equal Risk Contribution (described on page 19 of the same paper).
  • Maximum Sharpe Ratio
I chose those purely because they grabbed my interest and I wanted to see what they did.

EDIT: For the Maximum Sharpe Ratio I used iShares SHV as a proxy for the risk-free return rate. Something like the 3-month Treasury Bill or LIBOR would be a better choice, but I have no idea how to pull that data automatically into a Google spreadsheet.
Any clue why options 2, 3, and 4 break with a TV below 24%?

User avatar
Topic Author
danielc
Posts: 1031
Joined: Sun Dec 10, 2017 4:48 am
Location: Iowa, USA
Contact:

Re: Help me figure out the Target Volatility formula

Post by danielc » Fri Feb 14, 2020 2:04 pm

Stump909 wrote:
Fri Feb 14, 2020 12:54 pm
Any clue why options 2, 3, and 4 break with a TV below 24%?
What probably happened is that UPRO and TMF both had a volatility higher than your TV. Option 1 pretends that TMF has a volatility of zero, while options 2,3, and 4 try take into account TMF's volatility. The flip side of that is that if both assets are more volatile your TV, then the problem has no solution. You end up trying to take the square root of a negative value and you get a #NUM! error.

So what you'd want to do is figure out what portfolio you want to have if your TV is not achievable with UPRO + TMF.

Stump909
Posts: 31
Joined: Mon Jan 27, 2020 12:13 pm

Re: Help me figure out the Target Volatility formula

Post by Stump909 » Tue Feb 18, 2020 12:18 pm

danielc wrote:
Fri Feb 14, 2020 2:04 pm
Stump909 wrote:
Fri Feb 14, 2020 12:54 pm
Any clue why options 2, 3, and 4 break with a TV below 24%?
What probably happened is that UPRO and TMF both had a volatility higher than your TV. Option 1 pretends that TMF has a volatility of zero, while options 2,3, and 4 try take into account TMF's volatility. The flip side of that is that if both assets are more volatile your TV, then the problem has no solution. You end up trying to take the square root of a negative value and you get a #NUM! error.

So what you'd want to do is figure out what portfolio you want to have if your TV is not achievable with UPRO + TMF.
Yikes...that should have been apparent. Thanks.

Post Reply