![[<<] PriceDerivatives blog](https://www.pricederivatives.com/en/wp-content/uploads/2014/03/cropped-pricederivatives-blog-logo-Copy3.png){kind=logo}
Here we’ll show how to value FX forwards (Foreign eXchange) on EURUSD exchange rate in excel with quantlib excel addin
in this example we’ll need following market data:
In this spreadsheet we first construct syntetic USD yield curve based on EURUSD forward points, so after we could use usual formulas for FX forward valuation
FX forward valuation excel:
FX forward valuation excel EURUSD example
Amortizing interest rate swap valuation excel with 2 curves example:
for online amortizing interest rate swap valuation with credit valuation adjustment see Online Amortizing Interest rate swap valuation with CVA and OIS discounting
for quantlib python version see Amortizing Interest rate swap valuation with python quantlib
In this example we value amortizing swap with 2 flat curves : discount curve and forwarding curve.
You can get curves in this format on Bloomberg or Reuters terminals.
to get real value of swap paste curves for your valuation date and adjust arguments in formulas.
set appropriate daycount conventions (usually actual/360 or 30/360) and calendars (usually TARGET in European contracts) from termsheet.
1) first install quantlib addin at http://quantlib.org/quantlibxl/ -> download
2) open file at QuantLibXL\xll\QuantLibXL-vc90-mt-s-1_2_0.xll
3) open spreadsheet ctrl-alt-f9 to recalculate all
interest rate swap valuation excel
for quantlib c++ valuation see http://www.pricederivatives.com/en/how-to-value-swap-with-2-curves-with-quantlib-quantlib-swap-example/
Here we show simplest method called current net exposure
for more elaborated Monte-Carlo method using python see CVA with monte carlo calculation
for online interest rate swaps and OTC derivatives valuation with CVA see Derivatives accounting calculators
CVA calculation online with current net exposure method online
lets say we have derivatives portfolio with the same counterparty which consist of 2 amortizing swaps
one approximate method for would be to calculate Net exposure at valuation time
lets say swap1 worth today 2M$
swap2 worth -1M$
if there’s a netting agreement then total today’s exposure is (1-R)*(2M-1M) = (1-R)*1M $
R is recovery normally supposed to be at 40%
then CVA would be price to hedge this exposure with CDS (fixed leg of CDS)
lets say counterparty Credit Defualt Swap spread is flat 300 pb (3%)
then the cost of this hedge would be approximately
CVA=3% * (maturity in years) * (1-R) *1M $
if we want to improve the quality of this approximation we could take average exposure of swaps until maturity (at maturity exposure to swap is 0) which equals exposure today /2
so final formula would be
CVA=0.5*3% * (maturity in years) * (1-R) *1M $
in case that two swaps have different maturity then we would have to weight the exposure by maturity factor of each instrument
if total exposure is negative with this method CVA=0 and we would have to take own CDS spread to calculate DVA (debit valuation adjustment)
example cva calculation in Excel (you can edit cells)
FAS 157 require derivatives CVA to be calculated for fair value of derivatives, as well as basel 3 for banks
to calculate value of annuity properly we can use quantlib addin from http://quantlib.org/quantlibxl/
1) first install quantlib addin at http://quantlib.org/quantlibxl/ -> download
2) open file at QuantLibXL\xll\QuantLibXL-vc90-mt-s-1_2_0.xll
3) open spreadsheet ctrl-alt-f9 to recalculate all
Here we calculate simplified derivatives CVA/DVA for a portfolio of 3 OTC derivatives with the same counterparty
if net current exposure is negative then we’ll have only DVA and we’ll use institution’s own CDS spread, else we’ll have only CVA and we’ll need to apply counterparty CDS spreads (both found on bloomberg or reuters terminals).
In this example we use sample flat curve for discounting. For proper use we’ll need to paste real curve for desired valuation date (also found on bloomberg or reuters)
the standard recovery of 40% is used.
to calculate annuity we’ll calculate swap value with 0 floating leg using quantlib addin functions for swap.
download excel:
cva calculation excel
references:
Stein_H_FeSeminar_Sp12.pdf
investopedia
credit value adjustement
Option Adjusted spread (OAS) is usually calculated for bonds with embedded options (call or put)
OAS is a constant spread over discounting curve you have to apply to back up market price of the bond
this spread is adjusted to embedded options
example
company A senior bond with embedded call feature is worth today 101$
if OAS spread is 5% it means that if you want to calculate senior bond of the same company A , which does not have this optionality you would have to shift the discounting curve up 5% (constant spread) to get market price of this simple bond
OAS spread could be used as CDS spread on this company A , if no other CDS quotes are available
it’s an option which give right (=option) to buyer to buy a stock at fixed price called strike
company A buys a european call option on Microsoft stock with strike 10 and maturity 1 jan 2015
it means that if on 1/jan/2015 MSFT costs 12$ A would buy from B MSFT stocks at 10$ each . making profit of 2$/option
if MSFT costs less than 10$ ,then A would not buy the stock and let the option expire
european option (exercicable only on maturity date) can be valued with Black-Scholes formula
payoff could be written as $$ (S-K)^+ $$ ‘+’ means that if expression inside is negative we take it as 0
formula de Black-Scholes tells us that call’s price is an approximation to payoff formula as equals
$$ Call= S Proba1 – DiscountFactor K Proba2 $$
where Proba2 is a probability that S> K at time T and Proba1 is the same , but “in another probability measure” (whoch makes a small difference between Proba1 y Proba2)
basically it’s a contract which price is based on price of another financial asset
company A buys a derivative form bank B by which
B will pay 1 million euros if Ibex 35 index is bigger than 20000 points in one year’s time (this on is called digital call option)
there’re derivatives which can be bought in organized markets , like stock exchanges , and there are other type which are negotiated in private
OTC derivatives (over the counter)
also, derivatives could be classified by underlying security:
example: dirivatives whose payout depends on level of Euribor , for example swaps
example: payout depens on EURUSD exchange rate
example: payout depends on Santander stock price
example: A pays B 1 million if company C goes broke
A pays B difference between precio del crudo Brent crude price and Ural crude price
another way to classify them is by exercise type:
option can be exercised at any date before maturity
option can be executed only at maturity
option can be executed at various time points
OTC derivatives market is huge: total outstanding notion at the end of 2011 was 10 time bigger than world GDP (700 trillion $)
it’s an adjustment to forward price of underlying if payoff’s currency is diferent from underlying’s currency
it’s a swap where we exchange fixed rate for variable rate calculated with dayly EONIA rate (Euro OverNight Index Average)
the payments are based on average EONIA rate , which corresponds to reinvest both notional and received interest at EONIA rate
for a Eonia – Fixed swap payout is calculated as:
$$ r_{efrective}=\frac{1}{\delta_{periodBegin->periodEnd} } (\prod_{i=1}^{N} [1+\delta_{i->i+1}r_{i} ] – 1 ) $$
so, to value floating leg we’ll need EONIA fixings from init of the period until valuation date
year fraction $$\delta$$ is based on Actual/360
for future fixings instead of calculating forwards every day we could calculate
$$ r_{effective}=\frac{1}{\delta_{periodBegin->periodEnd}} (\frac{df_{periodBegin}}{df_{periodEnd}}-1) $$
it’s a executive’s incentive plan whoch consists in giving employee a number of stock options which can be exercised only after certain period have passed (vesting period), normally few years
normally we could value it like an american call option.
Black -Scholes formula is not applicable here , because it hold only for european options (it could be applied if stock pays on dividends though).
one can use numerical methods like binary trees or approximative methods (like approximation of Barone-Adesi and Whaley , for example)
Vesting period generally lasts several years.Valuation of these options normally do not depend on this period as in the contracts there are additional clauses whoch stipulate that option could be executed before vesting period ends and there fore it would be generally hedged with american option.
vesting period plays role in accounting as the cost of option is generally divided between vesting years [from start of plan until vesting period ends]
to value these plans you can use following Accounting for stock options calculator
employee stock option plan calculator
it’s a numerical test in hedge accounting to determine if hedging financial instrument effectively hedges underlying loan.
In case when hedge is effective company can apply “hedge accounting” and would not be obliged to appy derivatives profit/loss to accounts , helping reduce accounts volatility.
if company has a 10 years loan of 1 million euros with floating rate euribor 6m
as a heding instrument company chooses fixed-floating interest rate swap with fixed rate 2% and loan and swap payment dates coincide
hedge effectivness test would consist in 2 parts:
1) retrospective test
which measures ratio (dollar offset) between change in value of hypothetical derivative (ideal hedge) and hedging instrument
if R(t) is real derivative fair value at time t (derivative bought for hedging)
and H(t) is Hypothetical derivative fair value at time t (derivative which would be an ideal hedge for the loan)
and T is valuation date for hedge effectiveness testing and t is inception date (when the hedge instrument was bought)
then dollar offset D would be
$$R=\frac{R(T)-R(t)}{H(T)-H(t)}$$
and this value normally supposed to be between 80% and 125%
2) prospective test
it would measure statistically correlation (R squared) and slope by changing current yield curve and calculating the ratio for various interest rate scenarios
in this hedge effectiveness testing we would take the yield curve for valuation date and then shift it for example 1% up , then recalculate change of value for real and hypothetical derivative , thus obtaining various points ,
then just feed these points to excel RSQR function and it will give the ratio and to excel SLOPE function to get the slope
