In this post, we are going to walk you through an example of calculating the weighted average cost of capital (WACC) using Excel.
For illustration purposes, we are going to calculate the WACC of Barrick Gold, a major Canadian mining company. [caption id="attachment_613" align="aligncenter" width="628"] Barrick Gold as at January 25 2019. Source: Stockchart.com[/caption] The WACC is calculated as follows,
where To calculate the
Table below shows the calculation for the cost of equity as at September 30, 2018, As for the Finally, we proceed to calculate the WACC. Table below shows the calculation steps and results, Click on the link below to download the Excel workbook which contains detailed calculation steps and references. Originally Published Here: Weighted Average Cost of Capital (WACC)-Business Valuation Calculator in Excel
0 Comments
A lot of research has been devoted to answering the question: do options price in the volatility risks correctly? The most noteworthy phenomenon (or bias) is called the volatility risk premium, i.e. options implied volatilities tend to overestimate future realized volatilities. Much less attention is paid, however, to the underlying asset dynamics, i.e. to answering the question: do options price in the asset dynamics correctly? Note that within the usual BSM framework, the underlying asset is assumed to follow a GBM process. So to answer the above question, it’d be useful to use a different process to model the asset price. We found an interesting article on this subject [1]. Instead of using GBM, the authors used a process where the asset returns are auto-correlated and then developed a closed-form formula to price the options. Specifically, they assumed that the underlying asset follows an MA(1) process, where β represents the impact of past shocks and After applying some standard pricing techniques, a closed-form option pricing formula is derived which is similar to BSM except that the variance (and volatility) contains the autocorrelation coefficient, From the above equation, it can be seen that - When the underlying asset is mean reverting, i.e. β<0, which is often the case for equity indices, the MA(1) volatility becomes smaller. Therefore if we use BSM with σ as input for volatility, it will overestimate the option price.
- Conversely, when the asset is trending, i.e. β>0, BSM underestimates the option price.
- Time to maturity, τ, also affects the degree of over- underpricing. Longer-dated options will be affected more by the autocorrelation factor.
[1] Liao, S.L. and Chen, C.C. (2006), Original Post Here: Is Asset Dynamics Priced In Correctly by Black-Scholes-Merton Model? Debt instruments are an important part of the capital market. In this post, we are going to provide an example of pricing a fixed-rate bond.
We are going to price a hypothetical bond as at October 31, 2018. We first build a zero coupon curve. Picture below shows the market rates as at the valuation date. [caption id="attachment_595" align="aligncenter" width="628"] US swap curve as at Oct 31 2018[/caption] We utilize the deposit rates (leftmost column) to construct the zero curve up to 12-month maturity. We then use this zero curve to price the following hypothetical fixed rate bond:
We use Python [1] to build a bond pricer. Picture below shows the result returned by the Python program. The price is $99.94 (per $100 notional). Click on the link below to download the python code.
[1] Post Source Here: Valuing a Fixed Rate Bond-Derivative Pricing in Python In a previous post entitled Credit Risk Management Using Merton Model we provided a brief theoretical description of the Merton structural credit risk model. Note that,
In this installment, we are going to present a case study based upon the Merton credit risk model. [caption id="attachment_588" align="aligncenter" width="628"] Junior Gold Miners ETF as at Nov 28 2018. Source: stockcharts.com[/caption] Our Client is a junior gold miner. They are looking to raise additional capital in order to finance the production of gold. The client currently has an outstanding liability in the form of future discount for the payment of deliverable gold. According to a contingency clause, the Buyer can exercise a certain amount of cash into a secured obligation. This conversion will increase the leverage of the Company, thus leading to higher credit risks. We determined the increase in the credit risks by using the Merton structural credit model. We estimated that if the Buyer exercises the cash conversion clause, the credit spread of the Client will increase by approximately 20%. We thus helped the Client better negotiate the deal with their counterparty. Originally Published Here: Merton Credit Risk Model, a Case Study 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,
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
The values of the fixed, floating legs and the IRS are calculated using an Excel spreadsheet. Table below presents their values Click on the link below to download the Excel spreadsheet. Article Source Here: Interest Rate Swap-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,
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. [caption id="attachment_561" align="aligncenter" width="335"] American option valuation in Excel using Binomial Tree[/caption] Click on the link below to download the Excel Workbook. Post Source Here: Valuing an American Option Using Binomial Tree-Derivative Pricing in Excel 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, where
is the asset volatility,
We note that both asset ( where denotes the volatility of equity. These 2 equations can be solved simultaneously in order to obtain 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). [caption id="attachment_541" align="aligncenter" width="564"] Term structure of credit spread[/caption] 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
[1] Merton, R. C. 1974, Article Source Here: Credit Risk Management Using Merton Model 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.
*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 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].
[caption id="attachment_518" align="aligncenter" width="621"] Government of Canada Benchmark Bond Yield. Source: Bank of Canada[/caption] Recall that the important inputs are:
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.
The stock price is also obtained from Yahoo finance. It is 13.5 as of the valuation date (Aug 22 2018).
The dividend yield is obtained from Yahoo finance. It is 1.2%. Note that for illustration purposes we use continuous instead of discrete dividend.
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. [caption id="attachment_519" align="aligncenter" width="336"] American option valuation in Excel[/caption] Click on the link below to download the Excel Workbook.
Barone-Adesi, G. and Whaley, R.E. (1987 Originally Published Here: Valuing an American Option-Derivative Pricing in Excel 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,
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. [caption id="attachment_484" align="aligncenter" width="540"] Barrick Gold mining financial data as at Aug 23 2018[/caption] The important input parameters are:
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.
The stock price is also obtained from Yahoo finance. It is 13.5 as at the valuation date.
The dividend yield is obtained from Yahoo finance. It is 1.2%. Note that for illustration purposes we use continuous instead of discrete dividend.
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. [caption id="attachment_482" align="aligncenter" width="306"] European option valuation in Excel[/caption] In the next installment, we’re going to present an example for American option. Follow the link below to download the Excel Workbook.
[1] Hull, John C. (2005),
Originally Published Here: VALUING A EUROPEAN OPTION 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
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,
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, [caption id="attachment_350" align="aligncenter" width="485"] Average trade PnL vs. days in trade[/caption] 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.
[1] Gopikrishnan P, Plerou V, Nunes Amaral LA, Meyer M, Stanley HE, Read Full Article Here: A Simple Hedging System with Time Exit |