Fibonacci Ratio
Basics, Charts, Informative, Option Strategies, Options, Options strategies, Technical analysis, Technical Queries

Trading Using The Fibonacci Retracement Strategy

What Is Fibonacci Retracement?

Leonardo Piscano was an Italian mathematician from Pisa. He was nicknamed Fibonacci and he found an interesting pattern in a certain set of numbers. That pattern or sequence is known as the Fibonacci sequence. In the Fibonacci sequence of numbers, after 0 and 1, each number is the sum of the two prior numbers. Each number is around 1.618 times greater than the previous number. This value of 1.618 is called phi or the golden ratio. This ratio is visible in a lot of natural occurrences in the world. It has been observed in the Mona Lisa as well. The inverse of this number 0.618 is also used quite frequently in trading.
The numbers used in Fibonacci retracement are not the numbers from the sequence but they are derived from the mathematical relationship between the numbers in the sequence.

The basis of the “golden” Fibonacci ratio of 61.8% comes from dividing a number in the Fibonacci series by the number that follows it. For example – 89/144 = 0.618.

The 38.2 % ratio is obtained by dividing a number in the Fibonacci series by the number which is 2 places to the right. For example – 89/233 = 0.3819

The 23.6% ratio is derived from dividing a number in the Fibonacci series by the number three places to the right. For example: 89/377 = 0.2360.

The Fibonacci Retracement Strategy –

A retracement is a technical term used to identify a minor pullback or change in the direction of a financial instrument, such as a stock or index. Retracements are temporary in nature and do not indicate a shift in the larger trend. Fibonacci retracement means identifying levels using the Fibonacci ratios to determine where to take a position for trading.
The levels are placed using the Fibonacci retracement tool by dragging it across the lowest point of a price on the chart up to the highest point of the price if it’s an uptrend.
If it’s a downtrend, we drag the tool from the highest point to the lowest point of the chart to map the levels.
Fibonacci retracement levels are depicted by taking high and low points on a chart and marking the key Fibonacci ratios of 23.6%, 38.2%, and 61.8% horizontally to produce a grid. These horizontal lines are used to identify possible price reversal points. It is often used as a part of a trend trading strategy.
For example – if a certain stock is moving in a downtrend and hits the Fibonacci 0 level, traders assume that it will rally a bit up to the next Fibonacci level and hence they take a long position until the 0.382 level or the 0.5 level. When the stock reaches the desired level, traders either book profit or take a short position to earn along with the trend.

Fibonacci Retracement Strategy Chart

Here in the above image, you can see that the stock fell from point A(1 level) to point B
(0 levels). At point B a trader usually goes long till the price reaches the next Fibonacci level at 0.382 and then the price eventually settles down and continues to go along the trend.

There can also be a trend reversal if the price breaks the 0.5 mark. Traders love the 0.618 mark as it can be a strong indicator of trend reversal and hence they take suitable positions once the price nears the 61.8% mark.

Bottom Line –

Fibonacci retracement is about riding the trend. There is another strategy traders use called the Fibonacci extension which is used as an indicator of trend reversal levels.
Traders use the Fibonacci retracement strategy along with other technical indicators to make a decision. The Fibonacci retracement on its own is not a very good indicator of the price levels, but it can be very powerful once used with other indicators.
The last thing to note is that the time period can be very helpful while using this strategy, using it on a daily chart might not be as useful as using it on a weekly chart.

Track Fibonacci Retracement levels in your excel sheet with MarketXLS!

MakretXLS has an inbuilt Fibonacci retracement template to help you track the retracement levels of a certain stock in your excel sheet. Learn morehttps://marketxls.com/fibonacci-retracement-calculator

MarketXLS can bring you stock, options, ETF, mutual fund data, and much more in your excel sheet. You can sit back and analyze everything with our custom analysis templates and you can directly execute a trade from the excel sheet using tradier.
Book a Free demo to check out all our features – https://gmlnk.com/api/v1/track/link/click/622606eac52a798eae343bfe/1650880111421/?link=https%3A%2F%2Fmarketxls.com%2Fbook-demo

Read More
Charts, Portfolio analysis and optimization, Portfolio Management

Yahoo Portfolio – Export Portfolio In Excel (To Use With Marketxls)

Yahoo Portfolio is a great way to keep track of your investments and stock purchases online. Yahoo Finance portfolio has a great and intuitive way to add your transactions and then track your portfolio in real-time.

Yahoo Portfolio

And with the “Create New View” screen you can simply add many more parameters in the table and compare the detailed performance of each stock in your portfolio.

 

Yahoo finance portfolios provide a very convenient way to export your portfolio in Excel as shown below.

yahoo portfolio

With one click now, you have your portfolio in Excel.  The downloaded file looks something like shown below.

yahoo portfolio

At MarketXLS, we have created a utility and template which takes and this input and turns yahoo portfolio into a portfolio analytics dashboard in your Excel using MarketXLS’s portfolio analytics functions.

A portfolio is essentially a range of cells that has the stocks in your portfolio and the corresponding proportion of stocks as shown below.

The total of all the weights in the portfolio should need to be 100% for these functions to work. These portfolio functions should work for all US & Canadian stocks and ETFs.

The range B3: C11 which is highlighted in the image below is what is the portfolio input.

We have a category for portfolio functions. You should see this in Excel’s functions browser.

In all MarketXLS portfolio functions, a portfolio range is the first and the only required input. The default time period we use for all our calculations is 12 months and all the results are calculated with monthly returns. The number of periods can be modified by providing an optional integer value after the portfolio as shown below.

=monthlyReturns(“Portfolio Range”,18) will return monthly returns for 18 months.

We would recommend downloading the presentation for these functions and further details around calculations and inputs to these functions.

efficient frontier calculator

 

Read More
Charts, Portfolio analysis and optimization, Portfolio Management

Wealth Index Using Excel

The Wealth Index of portfolio with MarketXLS calculates the highest rate of return for the investment. The MarketXLS add-in enables it to apply to either financial instrument or business portfolios. The wealth index is a popular function to evaluate the investment and to calculate the returns.

What is Wealth Index

The wealth index is a data series that presents the value of your portfolio at historical time periods explaining the portfolio’s value at different points in time corresponding to the returns generated by it.

Calculation of Wealth Index

Wealth is referred to as earnings, and wealth index means profits over investments. It is a metric used to evaluate the investments and deciding whether to go on with the same investments or to include other assets in the portfolio.

The return on investment can be calculated by using total return. The total return is the total amount of yield plus capital earning form the portfolio.

Wealth Index using Excel

And if the investment does not pay the yield or capital gain, then the return can be calculated by using relative return.

Wealth Index using Excel

Both of the above formulas are used to calculate the return on individual investment.

Cumulative Wealth Index

The cumulative wealth index (CWI) is simply the return, expressed as a decimal multiple of the initial amount, earned by a certain initial amount of money over years. The calculation usually uses $1 as the initial investment, and the returns are compounded annually:

CWIn = WI0 × (1 + TR1) × (1 + TR2) × … × (1 + TRn)

Where:

WI0= Initial wealth

TR = Total Return

n = No. of years

Geometric Mean

The geometric mean is more accurate than the arithmetic mean because it accounts for compounding:

Geometric Mean = [(1+ TR1) (1+ TR2) … (1+ TRn)] 1/n – 1

where:

TR = Total Return

n = No. of years

The geometric mean will usually yield the correct, accurate result, but the example was off a little because of rounding errors in the data. Total returns can also be adjust for inflation by dividing the total return over a given period by the inflation rate over that same period, usually one year.

 

How MarketXLS Calculates Wealth Index of your Portfolio

MarketXLS® completely automates the process of calculating the wealth index using excel to get the optimal portfolios by calculating the wealth index.

The following inputs are required to be entered by the investor:

1. Symbol of the asset: Enter the ticker under which the security in your Portfolio is traded.

2. Weight of the asset in your Portfolio: Enter the percentage weight of each security, as shown below.

Input:

Optional Input:

Initial Investment: Enter the amount you invested initially to derive a data series that explains the present value of your portfolio.

Read More
Monte Carlo Simulation
Charts, Portfolio analysis and optimization, Valuation Models

Monte Carlo Simulation Excel

Monte Carlo Simulation Excel is an excellent tool for investors when assessing the potential risks associated with the portfolio and asset allocation. This article will discuss what it is, how it is works, how MarketXLS add-in calculates Monte Carlo simulation of your portfolio.

The Monte Carlo approach is a computer-based method that uses statistical sampling to build a model of a possible range of results (a probability distribution) for those factors that have an element of uncertainty.

Monte Carlo simulation involves creating random variables. These variables have similar properties to the risk factors which the simulation is attempting to simulate.

Monte-Carlo simulation simulates and produces several outcomes for a number of scenarios over a large number of time-steps. As a result, the technique provides a large number of possible outcomes of variables, along with their probabilities. From the possible outcomes of the values, an average value is chosen. The average measure is usually dependent or even mean calculated as to measure. The larger the number of scenarios, the higher the accuracy of the result.

How the Monte Carlo Simulation Works

The Monte Carlo simulation builds models of potential outcomes by substituting a range of values for every uncertain factor known as a probability distribution. The simulation then runs through all possible results, using a different set of random values every time.

During a Monte Carlo simulation, values are sampled at random from the input probability distributions. Each set of samples is defined as iteration. The resulting outcome from each sample is then recorded. At the end of the simulation, thousands or millions of “random trials” produce a distribution of outcomes that can be analyzed.

The Monte Carlo simulation determines the likelihood that stock trading asset price will change in a certain way. Additionally, this model can assess the risk that an entity or an asset will default. One of the most common ways to estimate returns and risk is using a Monte Carlo simulation (MCS).

Disadvantages of the Monte Carlo simulation

The Monte Carlo simulation in finance has its shortcomings as well because no one can predict the future. The simulations are particularly disadvantageous during a bear market. This is because the outcomes are based on constant volatility and can create a false sense of security for the investors.

Moreover, the simulation is unable to factor in the behavioral aspect of the stock market. Therefore the simulations only show an approximation of the true value and can sometimes show very large variances.

Monte Carlo Simulation Calculation

The investors can assess portfolios using Monte Carlo simulation. The MarketXLS® add in system calculates the standard deviation and annual returns for your portfolio based on set weights to give the result.

Step1: Calculate the Monthly Return of Every Asset in the Portfolio

Where:

Monte Carlo Simulation Excel

Note: To maintain consistency with theory in this regard, Close Price is a security’s Adjusted Close (Close prices adjusted for dividends and splits) of the last day of a given month, and the Open Price is the Adjusted Close price for the last day of the previous month.

Step2: Calculate Monthly Return of Portfolio

Portfolio return for a given month is calculated as follows:

Efficient Frontier

Where:

Efficient Frontier

This is repeated for all the months under consideration.

Step3: Calculate the STD_DEV of the Portfolio

The portfolio’s standard deviation is calculated below:

Monte Carlo Simulation

Where:

Monte Carlo Simulation

Step 4: Calculate Drift over a time period of the portfolio

Generating simulated asset paths of the portfolio:

Monte Carlo Simulation

Where:

Monte Carlo Simulation

Monte Carlo Simulation Excel with MarketXLS

Monte Carlo Simulation Excel with MarketXLS® formulae helps investors assess their portfolios and make investment decisions. MarketXLS® template has now made it easy to perform a Monte Carlo Simulation with just a few clicks. The investor needs to enter the stock symbol and the weight of the portfolio.

The following inputs are required to be entered by the investor:

  1. Symbol of the asset: Enter the ticker under which the security in your portfolio is traded.
  2. Weight of the asset in your Portfolio: Enter the percentage weight of each security, as shown below.

Input:

Output:

Stock price with 100 repeated simulations of random number with time steps on the graph would give this plot:

Optional Fields:

Months under observation:
To determine how many months you want to utilize retrospectively to arrive at your calculation, you may input the number of months you feel best, given that all the securities in your portfolio have existed long enough to have data for those periods. Default Value: In the absence of any user input, MarketXLS uses the data for the preceding 12 months from the date of the query.

Monte Carlo Simulation Excel is a great tool when assessing the potential risks associated with the portfolio and for asset allocation. The investor can use the Monte Carlo simulation with MarketXLS add in to get a combination of assets that attempt to predict the future many times over to estimate returns for a given level of risk of the portfolio. An investor can derive the expected returns with the Monte Carlo simulation excel, depending on his individual preferences and investment timeframe using MarketXLS.

Read More
Efficient Frontier
Charts, Portfolio analysis and optimization, Portfolio Management

Efficient Frontier Using Excel (With Marketxls)

efficient frontier calculator

The Efficient frontier using excel is one of the most important financial tools that help an investor compose an investment portfolio with the maximum returns and minimum amount of risk. This article will discuss what it is, how it is then calculated, and how MarketXLS calculates it of your Portfolio.

Contents:

  1. What is Efficient frontier
  2. Importance of Efficient frontier
  3. Optimal Portfolio by Efficient frontier
  4. How MarketXLS Calculates Efficient frontier of Portfolio

What is an Efficient frontier?

The Efficient frontier, also known as the portfolio frontier, represents the set of efficient portfolios that will give the highest return at each level of risk or the lowest risk for each level of return. The set of efficient portfolios is produced by the combination of different weights to get maximum expected return.

The Efficient frontier is a curve representing a combination of various securities to produce a different level of return.This frontier is formed by plotting the expected return on the y-axis and the standard deviation as a measure of risk on the x-axis.

Efficient frontier using Excel

For building the frontier, there are three crucial factors to be taken into consideration:

  1. Expected Return
  2. The covariance of one asset’s return to that of another asset.
  3. Variance/ Standard Deviation as a measure of the variability of returns known as risk.

The efficient frontier allows investors to understand how a portfolio’s expected returns vary with the amount of risk taken.

Importance of Efficient frontier

This tool helps investors get the most from their investment by analyzing the risk and returns associated with an investment portfolio and assisting them in adjusting their asset allocation accordingly.

It can also help determine if an investor should pull their funds from an investment with a certain amount of risk and return for a similar investment with less risk and the same return. The efficient frontier is important as it minimizes risk while maximizing rewards or returns.

The Optimal Portfolio by Efficient frontier

An optimal portfolio would offer a perfect balance between risk and return. The optimal portfolio contains:

  1. Securities with the highest potential returns with an acceptable degree of risk.
  2. It features securities with the lowest degree of risk for a certain level of return.
  3. Optimal returns tend to lie along the efficient frontier.

A risk-ready investor could choose securities right end of the efficient frontier. Those would likely have a high degree of risk coupled with high potential returns. Meanwhile, securities on the left end of the efficient frontier would be suitable for more cautious investors.

How MarketXLS Calculates Efficient frontier of your Portfolio

MarketXLS® completely automates the process of calculating the Efficient frontier using excel to get the optimal portfolios which tend to lie along the efficient frontier.

The following inputs are required to be entered by the investor:

  1. Symbol of the asset: Enter the ticker under which the security in your Portfolio is traded.
  2. Weight of the asset in your Portfolio: Enter the percentage weight of each security, as shown below.

Input:

Efficient frontier using Excel

Output:

Optional Fields:

Risk-free-rate(Rf):

To account for individuality in investor outlook, we make sure to give you the flexibility of determining what Mar/Rf you think is most viable. Default value: MarketXLS assumes a Mar/Rf of 0% by default to calculate the ratio.

Months under observation:

To determine how many months you want to utilize retrospectively to arrive at your calculation, you may input the number of months you feel best, given that all the securities in your Portfolio have existed long enough to have data for those periods. Default Value: In the absence of any user input, MarketXLS uses the data for the preceding 12 months from the date of the query.

Number of Points:

This refers to the number of points for which data is calculated for plotting the frontier. Default Value: MarketXLS calculates 100 points of data by default. 

The  GMV Portfolio:

The global minimum variance portfolio (GMV portfolio), is the portfolio with the lowest possible standard deviation (risk) out of all possible levels of expected return.

Calculation:

The following is how the MarketXLS add-in calculates the Efficient frontier for your Portfolio, one may note, that this also serves as an explanatory step-by-step guide for an n-asset portfolio as to how to calculate the Efficient frontier.

Method to calculate average monthly return is same for both types as follows:

  1. Monthly Return Calculation for Each Asset in Portfolio:

Efficient frontier using Excel

Where:

Efficient frontier using Excel

Note: To maintain consistency with theory in this regard, Close Price is a security’s Adjusted Close (Close prices adjusted for dividends and splits) of the last day of a given month, and the Open Price is the Adjusted Close price for the last day of the previous month.

  1. Portfolio Monthly Return Calculation:

Portfolio return for a given month is calculated as follows:

Efficient Frontier

Where:

Efficient Frontier

This is repeated for all the months under consideration.

  1. Determining Highest and Lowest Possible Expected Return on a Portfolio:

The individual return of every asset in the portfolio is determined by taking the mean of periodic returns for every asset and annualising the same to arrive at the expected return for every asset in the portfolio.

Efficient frontier using Excel

For MarketXLS as monthly data is utilized, the no. of periods in a year are taken as 12. 

The lowest value among this is the lowest possible return of the portfolio and similarly as the highest.

  1. Determining Weights for Every Level of Return in Between:

Quadratic Linear Programming is utilised to minimize portfolio volatility for all different levels of return subject to the constraint that sum of weights equals one. Herein, the optional input regarding the number of data points determines how many points in between the data is calculated for.

  1. Covariance of Portfolio is Calculated:

The covariance matrix is calculated for the portfolio:

Note: This is a two and three asset example and can be done for as many assets as a portfolio might have. 

  1. Standard Deviation of Portfolio: 

The portfolio’s standard deviation is calculated below:

  Efficient frontier using Excel

Where:

Efficient frontier using Excel

  1. Standard Deviation of Portfolio is Annualized:

To maintain consistency in periodicity of different parameters, standard deviation is annualised as follows:

Annualizing Factor = SQRT(Number of Periods a Year)

Note: Since MarketXLS uses monthly values, the annualizing factor is sqrt(12)

For instance, if taking daily data, seeing as a trading year has 252 days, multiply the ratio with the square root of 252.

Similarly,

  • for yearly returns, annualizing factor = SQRT(1)
  • for monthly returns, annualizing factor = SQRT(12)
  • for weekly returns, annualizing factor = SQRT(52)
  • for daily returns, annualizing factor = SQRT(252)
  1. Plotting the Efficient Frontier:

This results to derive the Efficient frontier plotted with standard deviation (risk) on x-axis and expected return on y-axis. 

The portfolio with lowest risk is plotted as the GMV portfolio. 

The portfolio with the highest Sharpe Ratio is used to plot the Capital Market line as a segment starting at the risk-free-rate of return on the y-axis and extending to the portfolio with the maximum sharpe ratio.  

The investor can use the Efficient frontier of Portfolio excel formula to get a combination of assets that has the optimal level of expected return for a given level of risk as all the portfolios on the line are efficient. An investor can choose a suitable portfolio anywhere on the Efficient Frontier depending on his individual preferences and investment timeframe using MarketXLS.

 

 

 

 

Read More
Binomial Option Model
Charts, Options, Options strategies

Binomial Option Pricing Model Excel

The Binomial Option Pricing Model Excel is available as a template with MarketXLS. The Binomial Option Pricing Model is a popular model for stock options evaluation, and to calculate the options premium.

The Binomial Options Pricing Model provides investors with a tool to help evaluate stock options. The model uses multiple periods to value the option.  For each period, the model simulates the options premium at two possibilities of price movement (up or down). The periods create a binomial tree — In the tree, each tree shows the two possible outcomes or the movement of the price.

Binomial Option Model

The model creates a binomial distribution of possible stock prices for the option. It creates possible paths that the stock price could go until the expiration date and the resulting impact on the options premium. Unlike the Black Scholes model of valuation of the option premium, the Binomial model gives you a view of an option contract at different prices at different periods until the expiration date.

Black-Scholes Vs Binomial Model

Black-Scholes model assumes that the option contract you are pricing is a European style option contract. A European style option contract is the one that can only be exercised at the date of the Expiry. The Americal style options contracts are the ones that can be exercised on any day until the expiry. Unlike, the Black Scholes model the Binomial option pricing model excel calculates the price of the option at various periods until the expiry. Since most of the exchange-traded options are American style options, the Black Scholes model seems to have a limitation.

If you were to assume that each period (days/weeks/months) until the expiry is the expiry date itself, you could also use the Black Scholes model to calculate a similar pay off table showing the value of the option for each period until expiry.

See the example below, where I use the Black Scholes model to generate a payoff for an option contract until the expiry date by assuming each day until the expiry is the expiry date. You can refer to our Options Profit Calculator template here.

Binomial Option Model vs Black Scholes Option Model

How do you calculate the Option Premiums using the Binomial Model?

The Binomial Option Pricing Model Excel takes the following as the Inputs. For example, I have taken a Call Option of American Airlines expiring on August 7th, 2020 and today is 29th of July 2020.  So, there are 10 days left until the expiry. The variable T as shown below in the days to expiry and n is the number of steps that we need in our Binomial tree. The current price of this option is 0.54 per contract. And the stock price is at 11.77. The following table shows other values and assumptions.

S = 11.77 #underlying pricek = 12 #Strike pricer = .04 #Riskfree ratev = .81 #VolatilityT = 10./365 #Time to maturityn = 10 #StepsUn= 1 #1 Unit is 100 stocksPC = 0 #Call option

The first step in the calculation is to create a binomial tree. This tree will have a specified amount of time that ends at the expiration date. Each point on the tree is a node. And each node is the price the stock can go at. The following image shows the binomial tree for the stock price movement(in table 1). So, for each period the table below shows the possible price movement on the underlying stock.

This chart below is the table for the price of the stock and the one below it is the table for the price of the option contract at corresponding prices (in table 2). And finally we have a table that shows the expected payoffs (in dollars) at these prices (in table 3) until the expiry when we buy 1 contract of this call option.

TABLE 1:

TABLE 2:

Binomial Option Model - Option Premium

TABLE 3:

Binomial Model PayOff Table

The binomial tree diagram represents the option payoff and probability at different nodes. Nodes outline the paths the price of the underlying asset may take over time. The following binomial tree represents the general one-period call option.

Binomial Option Pricing Model Excel

The option value using the one-period binomial option pricing model can be worked out using the following formula:

Binomial Option Pricing Model Excel

The put option uses the same formula as the call option:

Where:

C+ is the payoff of an up move;

C- is the payoff of the down move;

π is the probability of an up move;

1-π is the probability of the down move;

r is the discount rate.

Where π is the probability of an up move which is determined using the following equation:

Binomial Option Pricing Model Excel

Where:

t is the period multiplier (time to maturity);

r is the discount rate;

d is the down factor;

u is the up factor.

The binomial option pricing model excel is useful for options traders to help estimate the theoretical values of options. Price movements of the underlying stocks provide insight into the values of options premium. The model offers a calculation of what the price of an option contract could be worth today.

Read More