 Freelancer
 Contest
 Excel
 Work out the right equation to calculate an output (financial data)
Work out the right equation to calculate an output (financial data)
 Status: Closed
 Prize: $110
 Entries Received: 7
 Winner: andrzejgrabowski
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 precalculated 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= ( (V2Vs)  (n/n0)*(V2V1) ) / ( n*(V2V1)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 precalculated 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.”
AntiMatt3r, United Kingdom.
Top entries from this contest

andrzejgrabowski Poland

vrts Ukraine

marcostango7 Ireland

supersystemng Nigeria

Munsir5551 Sri Lanka

Mahediatrai Bangladesh

faheemzaidi New Zealand
Public Clarification Board
How to get started with contests

Post Your Contest Quick and easy

Get Tons of Entries From around the world

Award the best entry Download the files  Easy!