Work out the right equation to calculate an output (financial data)

Contest Brief

I need to get the right equation to calculate what is known as the "VXX roll yield".
Within the attached spreadsheet, I have all the required data to calculate this. I also have the target output (e.g. I have pre-calculated values for the VXX weekly roll yield (WRY)).
The calculation for the Daily Roll Yield (DRY) can be found here:

[url removed, login to view]

You will need to login to seekingalpha (it is a free/easy signup process) to see the whole story. This will give you some background about VXX and what the roll yield means. However, the equation to calculate the DRY from the above article is as follows:

DRY= ( (V2-Vs) - (n/n0)*(V2-V1) ) / ( n*(V2-V1)-n0*V2) )

Where:
n= number trading days remaining in the monthly cycle
n0= total number of trading days between the current monthly VIX futures settlement dates
V1 = Front month price
V2 = Second front month price
Vs = Spot VIX price

In the attached spreadsheet, I have tried to calculate the WRY using this formula. However, it gives a different output to the pre-calculated target values. If we plot the two against each other, we can see that they are correlated (84%). Therefore, my calculation is not quite correct.

I will award the prize to whoever can get the calculation correct so that it sufficiently predicts the expected output with 99% (e.g. r=0.99) or greater correlation.

One problem with my equation is that the above article on SeekingAlpha states that the equation is only an “approximation”. Therefore, to solve the problem you may need to use google to find the correct way to calculate the weekly roll yield of VXX. You will also need to problem solve and use your brain! Good luck!

Recommended Skills

Employer Feedback

“Clever guy. He had a novel approach to a difficult challenge, and also communicated well.”

Profile image AntiMatt3r, United Kingdom.

Top entries from this contest

View More Entries

Public Clarification Board

  • marcostango7
    marcostango7
    • 7 months ago

    R2 = 0.9955
    The equation for the expected value is quite long, but the correlation coefficient is incredibly high. It is precise for those 1659 values, but of course I cannot tell if it will work for any set of values.

    • 7 months ago
    1. AntiMatt3r
      Contest Holder
      • 6 months ago

      OK I have extended the contest. Post an entry, and then it would be good to privately show me your equation, and we can both run it on more data.

      • 6 months ago
    2. marcostango7
      marcostango7
      • 6 months ago

      I posted an entry, if you want send me a PM and I can send you the equation.

      • 6 months ago
  • AntiMatt3r
    Contest Holder
    • 7 months ago

    let me know your correlation

    • 7 months ago
    1. marcostango7
      marcostango7
      • 7 months ago

      Coefficient of determination (R^2) = 0.9955
      Correlation Coefficient (r) = 0.9978

      • 7 months ago
  • AntiMatt3r
    Contest Holder
    • 7 months ago

    What is your R2 or correlation with the expected output? Yes, I could extend, but it needs to be worthwhile...

    • 7 months ago
  • marcostango7
    marcostango7
    • 7 months ago

    I found the formula now, but it's too late to post it. If no one else has a better option, I can give you my one.

    • 7 months ago
  • marcostango7
    marcostango7
    • 7 months ago

    Is there any chance you could give us more time? I found a formula that works for the first 100 values, give me a bit of time for the rest if you can. Thank you.

    • 7 months ago
  • AntiMatt3r
    Contest Holder
    • 7 months ago

    The pre-calculated I did not work out. These were already done for me. I'm trying to work out how to get these values.

    • 7 months ago
  • arafatarr
    arafatarr
    • 7 months ago

    Can you please tell me how did you pre-calculated values for the VXX weekly roll yield (WRY) in column H?

    • 7 months ago

Show more comments

How to get started with contests

  • Post your contest

    Post Your Contest Quick and easy

  • Get tons of entries

    Get Tons of Entries From around the world

  • Award the best entry

    Award the best entry Download the files - Easy!

Post a Contest Now or Join us Today!