Interest Rate Swap-Derivative Pricing in Excel

An interest rate swap (IRS) is a financial derivative instrument that involves an exchange of a fixed interest rate for a floating interest rate.  More specifically,

An interest rate swap’s (IRS’s) effective description is a derivative contract, agreed between two counterparties, which specifies the nature of an exchange of payments benchmarked against an interest rate index. The most common IRS is a fixed for floating swap, whereby one party will make payments to the other based on an initially agreed fixed rate of interest, to receive back payments based on a floating interest rate index. Each of these series of payments is termed a ‘leg’, so a typical IRS has both a fixed and a floating leg. The floating index is commonly an interbank offered rate (IBOR) of specific tenor in the appropriate currency of the IRS, for example LIBOR in USD, GBP, EURIBOR in EUR or STIBOR in SEK. To completely determine any IRS a number of parameters must be specified for each leg; the notional principal amount (or varying notional schedule), the start and end dates and date scheduling, the fixed rate, the chosen floating interest rate index tenor, and day count conventions for interest calculations. Read more

The above description refers to a plain vanilla IRS. However, interest rate swaps can come in many different flavors. These include, (but are not limited to)

  • Amortizing notional IRS
  • Cross-currency swap
  • Float-for-float (basis) swap
  • Overnight index swap
  • Inflation swap etc.

Interest rate swaps are often used to hedge the fluctuation in the interest rate. To value an IRS, fixed and floating legs are priced separately using the discounted cash flow approach.

Below is an example of a hypothetical plain vanilla IRS

Maturity: 5 years

Notional: 10 Million EUR

Fixed rate: 3.5%

Floating rate:  Euribor

The values of the fixed, floating legs and the IRS are calculated using an Excel spreadsheet. Table below presents their values

Interest rate swap derivative in Excel

Click on the link below to download the Excel spreadsheet.

Originally Published Here: Interest Rate Swap-Derivative Pricing in Excel

Advertisements
Posted in Uncategorized | Leave a comment

Valuing an American Option Using Binomial Tree-Derivative Pricing in Excel

In a previous post, we provided an example of pricing American options using an analytical approximation. Such a pricing model is fast and accurate enough for risk management purposes. However, sometimes more accurate results are required. For this purpose, the binomial (lattice) model can be used. Wikipedia describes the binomial tree model as follows,

In finance, the binomial options pricing model (BOPM) provides a generalizable numerical method for the valuation of options. The binomial model was first proposed by Cox, Ross and Rubinstein in 1979. Essentially, the model uses a “discrete-time” (lattice based) model of the varying price over time of the underlying financial instrument…

The binomial pricing model traces the evolution of the option’s key underlying variables in discrete-time. This is done by means of a binomial lattice (tree), for a number of time steps between the valuation and expiration dates. Each node in the lattice represents a possible price of the underlying at a given point in time.

Valuation is performed iteratively, starting at each of the final nodes (those that may be reached at the time of expiration), and then working backwards through the tree towards the first node (valuation date). The value computed at each stage is the value of the option at that point in time.

Derivative Pricing in Excel Binomial Tree Lattice

We utilized the lattice model previously to price convertible bonds. In this post, we’re going to use it to value an American equity option. We use the same input parameters as in the previous example. Using our Excel workbook, we obtain a price of $3.30, which is smaller than the price determined by the analytical approximation (Barone-Andesi-Whaley) approach.

Derivative Pricing in Excel Binomial Tree Workbook

American option valuation in Excel using Binomial Tree

Click on the link below to download the Excel Workbook.

Originally Published Here: Valuing an American Option Using Binomial Tree-Derivative Pricing in Excel

Posted in Uncategorized | Leave a comment

Credit Risk Management Using Merton Model

R. Merton published a seminal paper [1] that laid the foundation for the development of structural credit risk models. In this post, we’re going to provide an example of how it can be used for managing credit risks.

Within the Merton model, equity of a firm is considered a call option on its asset, and it is expressed as follows,

credit risk management structural model

where    E denotes the equity of the firm,

               V is the firm’s asset,

                is the asset volatility,

                B is the notional amount of the debt,

               r is the risk-free interest rate, and

We note that both asset (V) and its volatility are not observable. However, the asset volatility can be related to equity and its volatility through the following equation,

credit risk management structural modelwhere denotes the volatility of equity.

These 2 equations can be solved simultaneously in order to obtain V and its volatility which are then used to determine the credit spread

credit risk management structural model

Having the credit spread, we will be able to calculate the probability of default (PD).  Loss given default (LGD) can also be derived under Merton framework.

Graph below shows the term structures of credit spread under various scenarios for the leverage ratio (B/V).

credit risk management structural model

Term structure of credit spread

It’s worth mentioning that the Merton model usually underestimates credit spreads. This is due to several factors such as the volatility risk premium, firm’s idiosyncratic risks and the assumptions embedded in the Merton model.  This phenomenon is called the credit spread puzzle.  Research is being conducted actively in order to improve the model.

References

[1] Merton, R. C. 1974, On the Pricing of Corporate Debt: The Risk Structure of Interest Rates, Journal of Finance, Vol. 29, pp. 449–470.

Article Source Here: Credit Risk Management Using Merton Model

Posted in Uncategorized | Leave a comment

Valuing an American Option-Derivative Pricing in Excel

In the previous installment, we presented a concrete example of pricing a European option. In this follow-up post we are going to provide an example of valuing American options.

The key difference between American and European options relates to when the options can be exercised:

  • A European option may be exercised only at the expiration date of the option, i.e. at a single pre-defined point in time.
  • An American option on the other hand may be exercised at any time before the expiration date. Read more

An exact analytical solution exists for European options.  For American options, however, we have to use numerical methods such as Binomial Tree (i.e. Lattice) or approximations.  The post entitled How to Price a Convertible Bond provides an example of the Binomial Tree approach.

The Binomial Tree model is an accurate one. However, its main drawback is that it’s slow. Consequently, several researchers have developed approximate solutions that are faster. In this example we’re going to use the Barone-Andesi-Whaley approximation [1].

The Barone-Adesi and Whaley Model has the advantages of being fast, accurate and inexpensive to use. It is most accurate for options that will expire in less than one year.

The Black-Scholes Model is appropriate for European options, that is, options that may be exercised only on the expiration date. The Barone-Adesi and Whaley Model is designed for American options, which are options that may be exercised at any time before they expire. The Barone-Adesi and Whaley Model takes the value computed by the Black-Scholes Model and adds the value of the early exercise option that is available on American option.. Read more

Financial derivative pricing interest rate derivative

Government of Canada Benchmark Bond Yield. Source: Bank of Canada

Recall that the important inputs are:

Volatility

In this example we are going to use historical volatility. We retrieve the historical stock data from Yahoo finance.  We then proceed to calculate the daily returns and use them to determine the annual volatility. The resulting volatility is 43%. Detailed calculation is provided in the accompanying Excel workbook.

Stock price

The stock price is also obtained from Yahoo finance. It is 13.5 as of the valuation date (Aug 22 2018).

Dividend

The dividend yield is obtained from Yahoo finance. It is 1.2%. Note that for illustration purposes we use continuous instead of discrete dividend.

Interest rate

The risk-free interest rate is retrieved from Bank of Canada website. Since the tenor of the option is 3 years, we’re going to use the 3-year benchmark yield. It is 2.13% as at the valuation date.

We use the Excel calculator again and obtain a price of $3.32 for the American put option.

Derivative Pricing in Excel - Valuing an American Option

American option valuation in Excel

Click on the link below to download the Excel Workbook.

 

References

Barone-Adesi, G. and Whaley, R.E. (1987) Efficient Analytic Approximation of American Option Values The Journal  of Finance, 42, 301-320.

Originally Published Here: Valuing an American Option-Derivative Pricing in Excel

Posted in Uncategorized | Leave a comment

VALUING A EUROPEAN OPTION

An option is a financial contract that gives you a right, but not an obligation to buy or sell an underlying at a future time and at a pre-determined price.  Specifically,

…  an option is a contract which gives the buyer (the owner or holder of the option) the right, but not the obligation, to buy or sell an underlying asset or instrument at a specified strike price on a specified date, depending on the form of the option. The strike price may be set by reference to the spot price (market price) of the underlying security or commodity on the day an option is taken out, or it may be fixed at a discount or at a premium. The seller has the corresponding obligation to fulfill the transaction – to sell or buy – if the buyer (owner) “exercises” the option. An option that conveys to the owner the right to buy at a specific price is referred to as a call; an option that conveys the right of the owner to sell at a specific price is referred to as a put. Read more

Excellent textbooks and papers have been written on options pricing theory; see for example Reference [1]. In this post we are going to deal with practical aspects of pricing a European option. We do so through a concrete example.

We’re going to price a put option on Barrick Gold, a Canadian mining company publicly traded on the Toronto Stock Exchange under the symbol ABX.TO.  For this exercise, we assume that the option is of European style with a strike price of $13. (American style option will be dealt with in the next installment). The option expires in 3 years, and the valuation date is August 22, 2018.

Barrick Gold pricing stock option

Barrick Gold mining financial data as at Aug 23 2018

The important input parameters are:

Volatility

In this example we are going to use historical volatility. We retrieve the historical stock data from Yahoo finance.  We then proceed to calculate the daily returns and use them to determine the annual volatility. The resulting volatility is 43%. Detailed calculation is provided in the accompanying Excel workbook.

Stock price

The stock price is also obtained from Yahoo finance. It is 13.5 as at the valuation date.

Dividend

The dividend yield is obtained from Yahoo finance. It is 1.2%. Note that for illustration purposes we use continuous instead of discrete dividend.

Interest rate

The risk-free interest rate is retrieved from Bank of Canada website. Since the tenor of the option is 3 years, we’re going to use the 3-year benchmark yield. It is 2.13% as at the valuation date.

After obtaining all the required input data, we use QuantlibXL to calculate the price of the option. The calculator returns a price of $3.21. The picture below presents a summary of the valuation inputs and results.

Financial derivative in Excel pricing a European option

European option valuation in Excel

In the next installment, we’re going to present an example for American option.

Follow the link below to download the Excel Workbook.

 

References

[1] Hull, John C. (2005), Options, Futures and Other Derivatives (6th Ed.), Prentice-Hall

 

Originally Published Here: VALUING A EUROPEAN OPTION

Posted in Uncategorized | Leave a comment

A Simple Hedging System with Time Exit

This post is a follow-up to the previous one on a simple system for hedging long exposure during a market downturn. It was inspired by H. Krishnan’s book The Second Leg Down, in which he referred to an interesting research paper [1] on the power-law behaviour of the equity indices.  The paper states,

We find that the distributions for ∆t ≤4 days (1560 mins) are consistent with a power-law asymptotic behavior, characterized by an exponent α≈ 3, well outside the stable Levy regime 0 < α <2. .. For time scales longer than ∆t ≈4 days, our results are consistent with slow convergence to Gaussian behavior.

Basically, the paper says that the equity indices exhibit fatter tails in shorter time frames, from 1 to 4 days. We apply this idea to our breakout system.  We’d like to see whether the 4-day rule manifests itself in this simple strategy. To do so, we use the same entry rule as before, but with a different exit rule.   The entry and exit rules are as follows,

Short at the close when Close of today < lowest Close of the last 10 days

Cover at the close T days after entry (T=1,2,… 10)

The system was backtested on SPY from 1993 to the present. Graph below shows the average trade PnL as a function of number of days in the trade,

Hedging system for protecting stock portfolios

Average trade PnL vs. days in trade

We observe that if we exit this trade within 4 days of entry, the average loss (i.e. the cost of hedging) is in the range of -0.2% to -0.4%, i.e. an average of -0.29% per trade. From day 5, the loss becomes much larger (more than double), in the range of -0.6% to -0.85%. The smaller average loss incurred during the first 4 days might be a result of the fat-tail behaviour.

This test shows that there is some evidence that the scaling behaviour demonstrated in Ref [1] still holds true today, and it manifested itself in this system.  More rigorous research should be conducted to confirm this.

 References

[1] Gopikrishnan P, Plerou V, Nunes Amaral  LA, Meyer M, Stanley HE, Scaling of the distribution of fluctuations of financial market indices, Phys Rev E, 60, 5305 (1999).

See Full Article Here: A Simple Hedging System with Time Exit

Posted in Uncategorized | Leave a comment

Historical Default Rates Do Not Predict Future Defaults

Yesterday, Bloomberg published an article arguing that the current credit risk is low because the default rate is low,

Insulated by cheap money from the QE era and bolstered by cash on their balance sheets, it remains rare for companies in Europe and the U.S. to miss debt payments. Among higher-risk speculative-grade firms the default rate fell to 2.9 percent last quarter, and may drop further to 2.1 percent by year-end, according to Moody’s Investors Service. And only one investment-grade firm has defaulted since 2012, data from Standard & Poor’s Global Ratings show.

“Default rates are on the floor,” said Fraser Lundie, co-head of credit at Hermes Investment Management. “Fundamentals still broadly stack up.” Read more

However, note that the default rate they talked about is historical default rate. It does not predict future defaults. In fact, historical default rate to future probability of default is what historical volatility to implied volatility. Just because the recent historical volatility is low it does not mean that the volatility risk is low. This applies to the credit market too.

 

But default rates aren’t the only thing credit investors care about. Spreads have widened to levels not seen for more than a year as concerns grow of overheating in the U.S. market, trade disputes, rising rates, inflation and the end of the European Central Bank’s bond-buying program.

… The credit market may also be downplaying the potential impact of tariffs, analysts at UBS Group AG wrote in a July 24 report. They say investors should be cautious about sectors including tech, industrials, metals and mining. Higher corporate leverage may also lead to an increase in stress among non-cyclical industries such as consumer staples and healthcare, the analysts including Bhanu Baweja wrote.

…The end of loose monetary policies may also boost defaults in emerging markets next year, according to Abdul Kadir Hussain, the head of fixed income at Arqaam Capital, a Dubai-based investment bank.

ByMarketNews

Published via http://harbourfronttechnologies.blogspot.com/

Posted in Uncategorized | Leave a comment