Skip to main content
added 2402 characters in body
Source Link
Attack68
  • 12.1k
  • 2
  • 20
  • 51

I faced this problem trying to price bund yields from Bloomberg ticks. I found the fastest method was to price three static yields from three static prices and determine a quadratic function for those datapoints. Then any yield could be determined from that function. Although not exactly correct the error is so minimal it is practically un-noticeable, but the speed is effectively instantaneous.

Edit

As an Approximator

To give a bit of context to this, if you do not have access to a bond library in Excel the bloomberg "BDP" function can be used to determine the "YAS_BOND_YLD" parameter from a given price and settlement. But, that function appears to work very slowly, or is server based meaning the round trip is slow. With live ticking prices the function can exist in a permanent state of calculation if a tick change forces a restart of the calculation.

Thus the idea of deriving a quadratic price/yield function is done in the following way.

  1. Pick 3 sensibly spaced prices, $P_1, P_2, P_3$ and determine their yields, $y_1, y_2, y_3$

  2. Assert that each yield is a result of a quadratic equation: $y = x_0 P^2 + x_1 P + x_2 $ and the coefficients are then the solution of the linear system:

$$ \begin{bmatrix} P_1^2 & P_1 & 1 \\\\ P_2^2 & P_2 & 1 \\\\ P_3^2 & P_3 & 1 \end{bmatrix} \begin{bmatrix} x_0 \\\\ x_1 \\\\ x_2 \end{bmatrix} = \begin{bmatrix} y_1 \\\\ y_2 \\\\ y_3 \end{bmatrix} $$

This is reasonably easy to code in VBA for use in an excel cell (and is faster if you cache the quadratic coefficients and dont re-derive them each time)

Function BondYieldInterp(known_prices, known_yields, price)
Dim p_arr As Variant: p_arr = known_prices
Dim y_arr As Variant: y_arr = known_yields
Dim m_inv As Variant: ReDim m_inv(3, 3)
Dim m As Variant: ReDim m(3, 3)
Dim i, j As Integer
For i = 1 To 3
   m(i, 1) = p_arr(1, i) ^ 2
   m(i, 2) = p_arr(1, i)
   m(i, 3) = 1
Next i
m_inv = Application.WorksheetFunction.MInverse(m)
Dim beta As Variant
beta = Application.WorksheetFunction.MMult(m_inv, Application.WorksheetFunction.Transpose(known_yields))
BondYieldInterp = price ^ 2 * beta(1, 1) + price * beta(2, 1) + beta(3, 1)
End Function

As a Converger

In Python I have implemented both scipy.optimise.brentq and my own Python brents method to iterate a yield-to-maturity. Since the scipy version is coded in C it is faster. However, these optimisers are designed for arbitrary continuous functions. The signature of a bond price from yield or vice versa is close to a shallow quadratic curve. It is possible to use the above method in reverse to define a convergence routine based on this quadratic interpolator. The basic iterator I have coded in pure python is close to the performance of scipy brentq.

I faced this problem trying to price bund yields from Bloomberg ticks. I found the fastest method was to price three static yields from three static prices and determine a quadratic function for those datapoints. Then any yield could be determined from that function. Although not exactly correct the error is so minimal it is practically un-noticeable, but the speed is effectively instantaneous.

I faced this problem trying to price bund yields from Bloomberg ticks. I found the fastest method was to price three static yields from three static prices and determine a quadratic function for those datapoints. Then any yield could be determined from that function. Although not exactly correct the error is so minimal it is practically un-noticeable, but the speed is effectively instantaneous.

Edit

As an Approximator

To give a bit of context to this, if you do not have access to a bond library in Excel the bloomberg "BDP" function can be used to determine the "YAS_BOND_YLD" parameter from a given price and settlement. But, that function appears to work very slowly, or is server based meaning the round trip is slow. With live ticking prices the function can exist in a permanent state of calculation if a tick change forces a restart of the calculation.

Thus the idea of deriving a quadratic price/yield function is done in the following way.

  1. Pick 3 sensibly spaced prices, $P_1, P_2, P_3$ and determine their yields, $y_1, y_2, y_3$

  2. Assert that each yield is a result of a quadratic equation: $y = x_0 P^2 + x_1 P + x_2 $ and the coefficients are then the solution of the linear system:

$$ \begin{bmatrix} P_1^2 & P_1 & 1 \\\\ P_2^2 & P_2 & 1 \\\\ P_3^2 & P_3 & 1 \end{bmatrix} \begin{bmatrix} x_0 \\\\ x_1 \\\\ x_2 \end{bmatrix} = \begin{bmatrix} y_1 \\\\ y_2 \\\\ y_3 \end{bmatrix} $$

This is reasonably easy to code in VBA for use in an excel cell (and is faster if you cache the quadratic coefficients and dont re-derive them each time)

Function BondYieldInterp(known_prices, known_yields, price)
Dim p_arr As Variant: p_arr = known_prices
Dim y_arr As Variant: y_arr = known_yields
Dim m_inv As Variant: ReDim m_inv(3, 3)
Dim m As Variant: ReDim m(3, 3)
Dim i, j As Integer
For i = 1 To 3
   m(i, 1) = p_arr(1, i) ^ 2
   m(i, 2) = p_arr(1, i)
   m(i, 3) = 1
Next i
m_inv = Application.WorksheetFunction.MInverse(m)
Dim beta As Variant
beta = Application.WorksheetFunction.MMult(m_inv, Application.WorksheetFunction.Transpose(known_yields))
BondYieldInterp = price ^ 2 * beta(1, 1) + price * beta(2, 1) + beta(3, 1)
End Function

As a Converger

In Python I have implemented both scipy.optimise.brentq and my own Python brents method to iterate a yield-to-maturity. Since the scipy version is coded in C it is faster. However, these optimisers are designed for arbitrary continuous functions. The signature of a bond price from yield or vice versa is close to a shallow quadratic curve. It is possible to use the above method in reverse to define a convergence routine based on this quadratic interpolator. The basic iterator I have coded in pure python is close to the performance of scipy brentq.

Source Link
Attack68
  • 12.1k
  • 2
  • 20
  • 51

I faced this problem trying to price bund yields from Bloomberg ticks. I found the fastest method was to price three static yields from three static prices and determine a quadratic function for those datapoints. Then any yield could be determined from that function. Although not exactly correct the error is so minimal it is practically un-noticeable, but the speed is effectively instantaneous.