1
$\begingroup$

The Microsoft Excel at my investment bank has an .xll add-in with a function whose coded functionality I cannot observe. This function is called VolInterp and as the name suggests, calculates the interpolated sample volatility.

The problem is I cannot yield the same numbers as this function through a manual calculation. I'm therefore questioning my understanding of how the overall project is utilising these volatilities.

My understanding of interpolation is that through an iid assumption, the variances of a data series scaled for time are additive and hence the linear interpolation occurs at the variance level before square rooting to retrieve the volatility. The result of this logic does not match that of the VolInterp() function.

I am hoping that one of the many intelligent people on this website may crack the coded functionality behind the VolInterp() function. To help, I will provide the numbers I am working with including the result of the VolInterp() function.

Many thanks in advance

Times

T1 = 30 days

T2 = 61 days

t = 31 days

Volatilities

V1 = 13.5611203572058%

V2 = 13.132597021628%

Interpolated Volatility via VolInterp() Function

v = 13.5343228915993%

My Answer

$$ v = \sqrt{\left(\frac{t-T_{1}}{T_{2}-T_{1}}\right)V_{2}^{2}+ \left(\frac{T_{2}-t}{T_{2}-T_{1}}\right)V_{1}^{2}}$$

$v = $13.5475085970615%

Function Description:

The value returned is the square root of the result of linearly interpolating "yvals $\times$ yvals $\times$ xvals" divided by the square root of x. So if x and xvals are year fractions and yvals are annualized volatilities then the returned value is an annulaized volatility obtained by linearly interploating the variances.

Improve this question
$\endgroup$
2
  • $\begingroup$ You logic is sound. I'm unsure why the description says "divided by square root of x'; but clearly your best option is to simply find out how the method is coded - why can't you see the code of the method? $\endgroup$
    – rbm
    Commented Feb 24, 2018 at 11:02
  • $\begingroup$ @rbm The functions in the .xll add-in are created via C/C++ and embedded into Excel through a DLL file. Viewing the code is therefore near impossible unless I can find the developer. I'll perhaps try that. $\endgroup$
    – Gustavo Louis G. Montańo
    Commented Feb 24, 2018 at 20:19

1 Answer 1

Reset to default
3
$\begingroup$

They are lineary interpolating in total variance. I find the exact same answer as your add-in function returns.

In other words the interpolation is made wrt time and between $z_1 = T_1 \times v_1 \times v_1$ and $z_2 = T_2 \times v_2 \times v_2$.

$$z_t = \frac{t-T_1}{T_2-T_1} \times (z_2-z_1) + z_1.$$

$$v_t = \sqrt{z_t / t} = 13.5343.$$

Your formula works on annualized variance rather than total variance.

Improve this answer
$\endgroup$
4
  • $\begingroup$ I knew someone would figure it out! Thank you @Ivan. Although, I need to understand the logic. First, you use the term "total variance". How does this differ from the normal variance? Second, if $v_{1}$ is already annualised, then what is the impact of multiplying by $T_{1}$? $\endgroup$
    – Gustavo Louis G. Montańo
    Commented Feb 25, 2018 at 21:52
  • 1
    $\begingroup$ Once multiplied it’s not annualized anymore, it is instead the total variance of the process over the time period. There is more variability over two years than over two days, and this is that number. $\endgroup$
    – Ivan
    Commented Feb 26, 2018 at 11:12
  • $\begingroup$ Yes, that makes perfect sense. It seems that the interpolation occurs between the variances of the distribution of sample log returns, and not the annualised variances. Interesting. Do you have any thoughts on the differences between methodologies? $\endgroup$
    – Gustavo Louis G. Montańo
    Commented Feb 26, 2018 at 21:50
  • 1
    $\begingroup$ I don’t recall having seen a theoretical justification although there may well be one in the Gatheral book (The volatility surface: a practitioner’s guide) but it “feels like” a simple way to use the information one has: since what you know about the process from the data at hand is its variance to T1 and its forward variance after T1, the simpler assumption is to assume that this fwd variance is evenly “accumulated” over T1-T2, and this is effectively what this interpolation achieves. Anything else implicitely assumes a non-uniform accumulation of fwd variance after T1. $\endgroup$
    – Ivan
    Commented Feb 27, 2018 at 10:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.