Re: [Quantlib-users] Sensitivity analysis in QuantLibXL

[Ferdinando A. <na...@am...>]

Hi Gerardo

> 1. In QuantLib's source code I read that the bucketAnalysis function (in
> its various incarnations) calculates and returns both Delta and Gamma
> (provided that SensitivityAnalysis == Centered). However, QuantLibXL's
> function qlBucketAnalysis seems to return only Delta.

yes it does.
qlBucketAnalysis calculates the sensitivity analysis for the
aggregated NPV (i.e. a single number) of a collection of Instruments
with respect to a matrix of quotes. As such it should return a matrix
of deltas and a matrix of gammas, but being limited by gensrc/Excel to
have a matrix return it just returns the delta matrix.

> I expanded the
> function call into a matrix of cells, each row corresponds to a quote, I
> was expecting to see two columns, with the Delta on the first column and
> the Gamma on the second. Instead, no matter how much I expand the
> matrix, I get always the same column repeated over and over.

This must be because your input is a vector of quotes, so the result
is a degenerate one column matrix, i.e. a vector.
The duplication is just Excel usual confusing behavior when dealing
with multidimensional output ranges larger than the actual
multidimensional result.

> how can I get the Gamma?

It would be a quick hack to duplicate the function to have the gamma
matrix, but this would require two full evaluations.
A better way would be to have a different function limited to an input
vector of Quotes: in this case the delta would be just a vector and a
second Gamma vector could be added side by side as you were expecting.
The third best solution might be to have a persistent object in
QLAddin which would lazily calculate both delta and gamma and provide
two separated inspectors for delta and gamma, independently from their
dimension.
I was planning to do the latter, but since the current implementation
completely satisfy my current needs this got low on my to-do list

> 2. What is the Parameters argument in qlBucketAnalysisDelta and
> qlBucketAnalysisDelta2?

It is used to calculate the sensitivity of some model parameters to
market quotes. The usual scenario is to calibrate a model's parameters
in a given market scenario and then tweak the market quotes to inspect
the parameters' sensitivity.
This is similar to NPV sensitivity, but where NPV is just one number,
parameters are usually more than one.
qlBucketAnalysisDelta calculates the sensitivity of a vector of
Parameters to a single Quote, returning a vector. In this case a gamma
column could be added, but frankly I don't know why I exported that
function since the single quote scenarios is really unrealistic.
qlBucketAnalysisDelta2 calculates the sensitivity of a vector of
Parameters to a vector of Quote, returning a matrix. Here we have the
same problem for Gamma as in qlBucketAnalysis: even if Quotes are just
a vector, Parameters are already making up for a second dimension, the
result being a matrix.

> The documentation doesn't explain. Reading the
> source code I see that it must be a vector of quotes. I can't make sense
> of that.

you're right. The function should have been at least called
qlNPVBucketAnalysis and qlParametersBucketAnalysis.
Point is that I would love to revisit them implementing an object
which would provide result persistance, solving this way the
delta/gamma result issue.
Besides I would love to implement something like a Quote wrapper for
the NPV of a collection of Instruments; this way only the Parameters
Sensitivity Analysis should be provided, consolidating the amount of
code.

> 3. What is the "BPS" (basis point sensitivity) calculated by
> qlSwapLegBPS?

the change in the leg NPV due to a basis point increase of its coupons

> I was supposing it must be the Delta calculated by
> shifting the yield curve up and down by 1 basis point

a 1bp change in the curve is not well defined: is it a change in its
market quotes, its instantaneous forward rates, its zero rates, etc. ?
Whatever you are referring to, in no way such a change would generally
imply the same change in the coupons being payed on a given leg

hope it helps

ciao -- Nando