Black scholes model
Calculators, Option Strategies, Options, Options strategies, Others, Using MarketXLS

How Are Options Priced?

MarketXLS offers you simple commands to fetch historical and live option prices, but have you wondered how options contracts are priced?

Any option’s premium consists of two components: the contract’s intrinsic and extrinsic value. The intrinsic value signifies the difference between the strike price of an option and the current market price of the underlying instrument. For a call option, the intrinsic value is the current price – the strike price, and for a put option, it is the strike price – the current price.

The extrinsic value consists of several factors such as time to expiration, implied volatility, interest rates, and dividend yields, among others. In other words, any premium over intrinsic value is said to form the extrinsic value. The premium is what an investor is willing to pay above the intrinsic value in the hope that the value of the contract increases due to changing market conditions.

Because of the involvement of several factors in the option premium, calculating the premium is a challenging task and is accomplished with the help of mathematical models. Several models are used in practice, like the Black-Scholes model, Heston Model, Morton Framework, etc. However, one of the most commonly used models is the Black-Scholes model.

The Black Scholes Model –

The Black Scholes model assumes no-arbitrage pricing for the options contracts and uses five key inputs to derive the option price. These inputs are namely:

  1. Strike price,
  2. Current price of underlying,
  3. Interest Rate,
  4. Implied Volatility, and
  5. Time to expiry

 

For a Call option, the option price is derived using.Black Scholes ModelC = call option price

N = normal distribution

St = Current price of the asset

K = strike price

r = risk-free interest rate

t = time to maturity

𝜎 = implied volatility of the asset

Fortunately, you don’t have to do this calculation yourself. Instead, MarketXLS give you predefined functions that you can directly run in excel to get the output.

But does it mean that actual market prices are always equal to the value derived from the BSM? Sadly, No. The BSM model is based on certain assumptions which may or may not hold in the real world, leading to a mismatch between the values derived from the BSM and market values.

These assumptions are as follows:

  1. Short term interest rates and volatility are constant
  2. There are no transaction costs associated with buying or selling options
  3. The options in consideration are European
  4. The returns of the underlying stock are normally distributed
  5. The markets are perfectly liquid

The Bottom Line –

Due to the impractical nature of these assumptions, market participants often notice huge differences between the values derived from BSM and actual market values. These differences are often driven by changing interest rate and volatility assumptions and supply and demand equations. However, the value derived from the BSM model can be used as a starting point while analyzing options.

Options trading gives you great advantages over trading any other kind of financial instrument. However, with the leverage that the options provide also comes with risks. Use MarketXLS Option Templates along with your own Excel calculations and real-time options data to get an advantage in the markets.
The Black Scholes Option model tries to calculate the fair value of the Option Contract.
In MarketXLS you can calculate the model value in a very simple way.
=BlackScholesOptionModelValue(“Option Symbol”) this function will return the value as per the model based on the dividend yield on the underlying asset, historical 7 trading day volatility, and an expected rate of return of 5%.
In most cases, you would notice that the value this function returns for an option contract will be pretty close to the last price of the option in the market.If you would like to use your assumptions of volatility and rate of return the function below will allow you to get the calculation Black Scholes Options Model Value.
Check out MarketXLS Plans here.
Read More
Altman Z Score
Calculators, Guru functions, Guru Strategies, Stock investments, Templates

Altman Z Score – Meaning And Application In Excel

What is The Altman Z Score?

Altman Z-Score is a score that indicates the solvency and debt repaying capacity of a company listed on a public exchange. This technique/template is usually used for publicly traded companies in the manufacturing sector. It is a barometer of the credit strength of a particular company.
New York University’s Finance professor Edward Altman defined and named the score in 1967. Since then, there were various evolutions in the score, and it has been a decisive measure of profitability and liquidity of a company.
Investors look at the Altman Z score to gauge the possibility of a firm going bankrupt in the near future. This model is a valuable predictor of the financial distress of firms. It has helped various investors of the Wall Street in deciding whether to invest in a company or not.

Past Performance of the Score

The score has evolved and developed over the years, which has resulted in an increase in its accuracy. From 1969 to 1975, Altman found 86 companies on the verge of bankruptcy using this score. In the 1976 to 1995 period, he found 110 companies and 120 companies in the upcoming three years. We can see that the number of financially distressed companies kept on increasing over the years. Majority of these companies were manufacturing companies. The accuracy in these years ranged from 82% – 94%
In 2012, he refined the score and came out with Altman Z Score Plus, which helped measure the credit risk of all types of companies.

Parameters of the Score

The five financial ratios used for calculating the Z Score are:
• Working Capital/ Total Assets
Negative working capital would indicate a low degree of liquidity and solvency, and positive working capital would indicate a high degree of the same.
• Retained Earnings/ Total Assets
This ratio shows the total amount of retained earnings out of the total assets.
• Earnings before Interest and Tax/ Total Assets
The EBIT ratio depicts a company’s ability to produce enough earnings to finance the expenses and operations and still be in profit.
• Market Value of Equity/ Total Assets
This ratio can be interpreted in terms of investor reliability in the company’s performance.
• Sales/ Total Assets
This ratio depicts the usage of assets to generate revenues.

Altman Z Score in MarketXLS

With the help of the Z score, investors can gauge the financial strength of the company. It highlights the possibility of the firm going bankrupt in the near future. MarketXLS provides its template for the calculation of the Z score. It also highlights the criteria and the formula using which the score has been calculated.
This score is significant for all those investors who are willing to invest in growth stocks. The primary reason behind this is that the growth stocks are the stocks of companies who usually have a good amount of leverage and debt in their balance sheet. This score helps assess whether this debt will affect the firm’s financial performance in the long term and there are any chances of bankruptcy in the future.
Let us have a look at the ready-to-use template provided by MarketXLS:

‘Index’ Sheet

Inputs in the Altman Z Score
https://www.marketxlswp.com/template/altman-z-score/

How to use the Template?
Enter the following information in the ‘Index’ Sheet to get the result summary:
• Stock Ticker in the cell E6
• Year of consideration in the cell E8
This sheet gives the Altman Z Score for the company and the template’s interpretation based on the score.
We have taken the example of Marvell Technology Group Ltd and the base year as 2017.
For detailed analysis, switch to the Calculations worksheet.

‘Calculations’ Sheet

Altman Z Score

Here comes the main question; how is Z score calculated? Z score is calculated using the five financial ratios and adding their weighted products as per the below formula:

Z score = 1.2*A + 1.4*B +3.3*C + 0.6*D + 1*E

Where, A = Working Capital / Total Assets
B = Retained Earnings / Total Assets
C = EBIT / Total Assets
D = Market Value of Equity / Total Assets
E = Sales / Total Assets

How to interpret Z score?

The financial soundness of the firm can be gauged based on the below legend:
Financially sound if greater than 2.99
Caution required if between 2.77 – 2.99
Likely to go bankrupt within two years if between 1.8 – 2.7
Likelihood of bankruptcy is high if below 1.8
Average for non-bankrupt companies – 5.02
Average for bankrupt companies – (-0.29)
In this case, the Altman Z Score for MRVL stock is 11.87. This means that the company seems financially stable as it meets the criteria of the score.
(These templates should not be considered as an advertisement or advice, professional or otherwise. You are requested to consider all the risk factors, including your financial condition, suitability to risk-return profile, and other similar conditions. These templates do not account for any professional advice but are merely some guidelines to explain the concept.)

The Bottom Line

The Altman Z Score has to be used with other templates/ models/ techniques for deciding on investment in a firm. This score tells about the solvency and the credit strength of the firm. For other measures like good financial strength and indicators, Piotroski Score and The Zweig Screen can be used along with this score. For gauging the dividend yield capacity of a blue-chip firm (or a firm that has a vast market capitalization), this score can be used along with The Weiss Blue Chip Dividend Yield Screen.
The link for these templates in MarketXLS are:
Piotroski F Score – https://www.marketxlswp.com/marketplace/product/piotroski-f-score-template/
The Zweig Screen – https://www.marketxlswp.com/template/zweig-screen/
The Weiss Blue Chip Dividend Yield Screen – https://www.marketxlswp.com/template/weiss-blue-chip-div-yield-screen/
For more such interesting information, visit https://www.marketxlswp.com/blog/

Disclaimer

None of the content published on marketxls.com constitutes a recommendation that any particular security, portfolio of securities, transaction, or investment strategy is suitable for any specific person.
The author is not offering any professional advice of any kind. The reader should consult a professional financial advisor to determine their suitability for any strategies discussed herein.
The article is written to help users collect the required information from various sources deemed to be an authority in their content. The trademarks, if any, are the property of their owners, and no representations are made. All trademarks referenced are the property of their respective owners. Other trademarks and trade names may be used in this document to refer to either the entity claiming the marks and names or their products. MarketXLS disclaims any proprietary interest in trademarks and trade names other than its own or affiliation with the trademark owner.

Reference

https://www.investopedia.com/terms/a/altman.asp

Read More
Leverage ratios
Basics, Calculators, Stock Comparisons, Stock investments, Templates, Valuation Models

Leverage Ratios – Comparing Companies In Excel Template ( With Marketxls )

A leverage ratio is a financial measurement to check out what proportion of capital comes within in the kind of debt (loans) and assess the ability of a corporation to satisfy its financial obligations. The leverage ratio category is vital because companies rely on a mix of equity and debt to finance their operations, and knowing the quantity of debt held by a corporation is beneficial in evaluating whether it pays off its debts as they come due.

The financial leverage ratios measure the general debt load of a corporation and compare it with the assets or equity. This shows what proportion of the corporate assets belong to the shareholders instead of creditors. When shareholders own a majority of the assets, the corporate is claimed to be less leveraged. When creditors own a majority of the assets, the corporate is considered highly leveraged. All of those measurements are important for investors to know how risky the capital structure of a corporation is and if it’s worth investing in.

Leverage ratios

Leverage Types

1.) Operating leverage: Operating leverage is a formula that measures the degree to which a firm or project can increase operating income by increasing revenue. A business that generates sales with a higher gross margin and low variable costs has high operating leverage.
Operating leverage= Total contribution / Operating income

2.) Financial leverage: it is also called trading on equity. Financial leverage means the use of preference share capital, equity share capital along with fixed interest bearing securities or debentures i.e. debt. Financial leverage assumes that the firm is capable of earning more on assets than that acquired by use of funds, on which fixed rate of dividend/interest is paid.
Financial leverage= total company debt / shareholder’s equity

3.) Combined leverage: This leverage shows the relationship between alteration in sales and the corresponding variation in taxable income. If the management feels that a certain percentage of change in sales would result in percentage change to taxable income they would like to know the level or degree of change.
Combined leverage= Operating leverage * Financial leverage

How is Leverage Created?

• A corporation takes on debt to buy specific assets. This is referred to as “asset-backed lending” and is very common in real estate and purchases of fixed assets like property, plant, and equipment (PP&E).
• A company borrows money based on the overall creditworthiness of the business. This is usually a type of “cash flow loan” and is generally only available to larger companies.
• When a company borrows money to finance an acquisition (learn more about the mergers and acquisitions process).
• When a private equity firm (or other company) does a leveraged buyout (LBO).
• A business increases its fixed costs to leverage its operations. Fixed costs do not change the capital structure of the business, but they do increase operating leverage which will disproportionately increase/decrease profits relative to sales.

Common leverage ratios

There are several different leverage ratios that may be considered by market analysts, investors, or lenders. Some accounts that are considered to have significant comparability to debt are total assets, total equity, operating expenses, and incomes.
Here is a list of 5 important leverage ratios:

Debt-to-Assets Ratio = Total Debt / Total Assets
The Debt to Asset Ratio, also known as the debt ratio, is a leverage ratio that shows the percentage of assets financed with debt. The higher the ratio, the greater the level of leverage and financial risk.
The debt to asset ratio is commonly used by suppliers to determine the amount of debt in a company, the ability to repay its debt, and whether additional loans will be extended to the company. On the other hand, investors use the ratio to make sure the company is solvent, is able to meet current and future payments, and can generate a return on their invested capital.

Debt-to-Equity Ratio = Total Debt / Total Equity
The Debt to Equity ratio is a leverage ratio that measures the weight of total debt (long term and short term) against total shareholders’ equity. Unlike the debt-assets ratio which uses total assets as a denominator, the D/E Ratio uses total equity. This ratio points out how a company’s capital structure is slanted either towards debt or equity financing.

In short, Debt Equity ratio helps us see the proportion of debt and equity in the capital structure of the company. For example, if a company is too dependent on debt, then the company is too risky to invest in. On the other hand, if a company doesn’t take debt at all, it may lose out on the benefits of leverage.

Debt-to-Capital Ratio = Today Debt / (Total Debt + Total Equity)
This leverage ratio calculation is the extension of the previous ratio. Instead of doing a comparison between debt and equity, this ratio would help us see at entire capital structure. This leverage ratio will help us explain the exact proportion of debt in the capital structure. Through this ratio, we will get to know whether a company has taken a higher risk of supplying its capital with more loans or not.

The debt-to-capital ratio gives analysts and investors an enhanced idea of a company’s financial structure and whether or not the company is a suitable investment. All else being equal, the higher the debt-to-capital ratio, the riskier the company. This is because a higher ratio, the more the company is financed by debt than equity, which means a higher liability to repay the interest and principal payments and a greater risk of defaulting on the loan if the debt cannot be paid timely.

Debt-to-EBITDA Ratio = Total Debt / Earnings Before Interest Taxes Depreciation & Amortization (EBITDA)
Debt/EBITDA—earnings before interest, taxes, depreciation, and amortization—is a ratio showing the amount of income generated and available to pay debt before accounting for interest, taxes, depreciation, and amortization expenses. Debt/EBITDA measures a company’s ability to pay off its funded debt. A high ratio result could indicate a company has quite a heavy debt burden.

This leverage ratio is the ultimate ratio that finds out how much impact debt has on the revenues of a company. And since a company needs to pay interests (cost of debt), this ratio will have a huge impact on the company’s revenues. For example, if the debt is more, the interests would be more (possibly, if the cost of debt is higher) and as a result, the taxes would be less and vice versa.

Asset-to-Equity Ratio = Total Assets / Total Equity
The Assets to Equity Ratio shows the relationship between the Total Assets of the Firm and the portion owned by stakeholders. This is measured using the most recent balance sheet available, whether interim or end of year.

There is no standard Asset to Equity ratio but it is valuable in comparing to similar businesses and industries. A relatively high ratio (indicating more of assets and very little equity) may indicate the company has taken on substantial debt merely to remain in business but a high Asset to Equity ratio can also mean the return on borrowed capital exceeds the cost of that capital.

At some higher levels, however, the ratio can reach unsustainable levels, as the additional debt intensifies up interest costs and the deteriorating financial position puts the firm in difficulty.

How to Find Important Leverage Ratios Using MarketXLS Template?

Step 1: Enter year in the cell C3. Then Enter up to 5 stocks (Ticker symbols) to be compared in the cell range (C5:G5). Here I have taken the stocks of Netflix, Amazon, Visa, Microsoft and Apple each from consumer cyclical, financial and technology sectors for the year 2020. You can also compare stocks from the same sectors to get their respective leverage ratios.

leverage ratios

 

Step 2: As soon as you press enter, stocks’ names and sectors will appear. Further, the calculated important leverage ratios can be seen below it.

leverage ratios

Link to this Template: https://www.marketxlswp.com/template/leverages-analysis-low-debt-companies-ratio-analysis/

Step 3: To further understand the calculation of leverage ratios, please switch to the CALCULATION sheet. The calculation sheet contains all the related data and information about the company’s earnings, total assets, debt and equity structure. It will appear like this:

 

leverage ratios template

With the help of MarketXLS, you can stream market data for stocks, ETFs, options, mutual funds, currencies refreshed, or refresh on-demand. You can also get all the historical data (EOD, Intraday) you may need with MarketXLS functions in a few clicks. Update the Excel tables dynamically and save your time in formatting those tables. Also get access to variety of templates like this and compare your portfolio stocks to get better analysis of your investments.

Risks of High Operating Leverage and High Financial Leverage

If leverage can increase earnings, it can also increase risk. Having both high operating and financial leverage ratios can be quite problematic for a business. A high operating leverage ratio explains that a company is generating few sales, still has high costs or margins that need to be covered. This may either result in a lower profit target or insufficient operating income to cover other expenses and will result in negative earnings for the company. On the other hand, high financial leverage ratios occur when the return on investment (ROI) does not surpass the interest paid on loans. This will significantly decrease the company’s profitability and earnings per share.

Bottom Line

As investors, you need to look at everything. Leverage ratios will help you how a company has structured its capital. Many companies don’t like to take loans from outside. They believe that they should fund all their expansion or new projects through equity.

But to take advantage of leverage, it’s important to structure the capital with a proprotion of the debt. It helps reduce the cost of capital (by reducing the cost of equity, it is huge). Plus, it also helps in paying less tax since the interest payments are tax deductible (i.e. the cost of debt).

If the company has taken too much debt, it’s too risky to invest in the company. At the same time, if a company doesn’t have any debt, it may pay off too much in cost of capital and actually reduce their earnings in the long run.

But only leverage ratios won’t help. You need to look at all the financial statements (especially four – cash flow statement, income statement, balance sheet, and shareholders’ equity statement) and all other ratios to get a concrete idea about how a company is doing. However, it surely does help investors in deciding whether a company is taking advantage of the leverage or not.

Disclaimer
All trademarks referenced are he property of their respective owners. Other trademarks and trade names may be used in this document to refer to either the entity claiming the marks and names or their products. MarketXLS disclaims any proprietary interest in trademarks and trade names other than its own, or affiliation with the trademark owner.
None of the content published on marketxls.com constitutes a recommendation that any particular security, portfolio of securities, transaction, or investment strategy is suitable for any specific person. The author is not offering any professional advice of any kind. The reader should consult a professional financial advisor to determine their suitability for any strategies discussed herein. The article is written for helping users collect the required information from various sources deemed to be an authority in their content. The trademarks if any are the property of their owners and no representations are made.

Reference
www.corporatefinanceinstitute.com
https://www.wallstreetmojo.com/leverage-ratios/
www.investopedia.com

Image Source
https://www.google.com/url?sa=i&url=https%3A%2F%2Fwww.warriortrading.com%2Fleverage-ratio%2F&psig=AOvVaw2qWL4XzQaYZBlF_a2IM1yd&ust=1618996373844000&source=images&cd=vfe&ved=0CAIQjRxqFwoTCOC4_9K9jPACFQAAAAAdAAAAABAK

For related articles, click here.

Read More
dividend yield formula
Basics, Calculators, Informative, Stock investments, Stocks

Dividend Yield- Tracking Dividend Related Information (With Marketxls)

Introduction

The dividend yield, expressed as a percentage, is a financial ratio (dividend/price) that shows how much a company pays out in dividends each year relative to its stock price. The dividend yield is a way to measure how much cash flow you are getting for each dollar invested in an equity position. In the absence of any capital gains, dividends are treated as the return on stock investment. Investors can be classified as growth-oriented and value-oriented investors, where the former invests in growing companies and expects returns in the form of capital gains as the company grows over time. Value-oriented investors, on the other hand, expect stable returns in the form of dividends along with capital gains over the long term. The dividend yield ratio is highly useful for value-oriented investors.

Formula

The dividend yield formula is as follows:
Dividend Yield = Annual Dividend per share / Market value per share
Where:

▪Dividend per share is the company’s total annual dividend payment, divided by the total number of shares outstanding

▪ Market value per share is the current share price of the company

dividend yield formula

Example

Company A trades at a price of $60. Over the course of one year, the company paid consistent quarterly dividends of $0.50 per share. The dividend yield ratio for Company A is calculated as follows:

Dividend Yield Ratio = $0.50 + $0.50 + $0.50 + $0.50 / $60 = 0.03333 = 3.33%

The dividend yield ratio for Company A is 3.33%. Therefore, an investor would earn 3.33% on shares of Company A in the form of dividends.

Dividend Yield Ratio Across Industries

The comparison of dividend yield ratios should only be done for companies operating in the same industry. Average yields vary significantly between industries. Yields can also vary for different companies in the same industry. The average dividend yield for several industries is as follows:

  • Basic materials industry: 4.92%
  • Financial services industry: 4.17%
  • Healthcare industry: 2.28%
  • Industrial industry: 1.76%
  • Services industry: 2.37%
  • Technology industry: 3.2%
  • Utility industry: 3.96%

Analysis

As we already know, dividends are essential for investors who wish to make a continuous passive income from their investments. The dividend yield ratio will give you the productivity of your investments. Dividends are also considered as a sign of a stable company since only profit-making companies pay dividends. Historically, companies that pay dividends mostly continue to do so as a dividend cut is received adversely by the markets. The companies that offer regular dividends are considered as mature companies since it shows the control over liquidity position. Since value investors prefer this, such companies may well even in the time of market lows.

A high or low yield depends on factors such as the industry and the business life cycle of the company. For example, it may be in the best interest of a fast-growing company to not pay any dividends. The money might be better used by reinvesting into the company to grow the business.

On the other hand, a mature company may report a high yield due to a relative lack of future high growth potential. Therefore, the yield ratio does not necessarily indicate a good or bad company. Rather, the ratio is used by investors to determine which stocks align with their investment strategy.

Explanation

Dividend yield ratio is one of the several parameters that are used to evaluate a company before purchasing its stocks. Investors also use the forward dividend yield ratio and trailing dividend yield ratio to get a better understanding of the company’s dividend yield. The dividend yield ratio is significantly different across several industries. Industries such as IT or electronics are known to have a negligible dividend yield. On the contrary, public sector units (PSU) and fast-moving consumer goods (FMCG) can have a stable dividend yield. It is essential to understand that not all companies with a high dividend yield ratio are worth investing. If the market price of the share is falling, the dividend yield ratio becomes more attractive. In such an instance, the company might not be a good buy. Finally, it is also vital to understand that the stability in dividend yield and strong fundamentals are a few of the positive indicators for purchasing a stock. One should also consider other macroeconomic factors such as government policies before investing.

Advantages

Historical evidence suggests that a focus on dividends may amplify returns rather than slow them down. For example, according to analysts at Hartford Funds, since 1970, 78% of the total returns from the S&P 500 are from dividends. This assumption is based on the fact that investors are likely to reinvest their dividends back into the S&P 500, which then compounds their ability to earn more dividends in the future.

Disadvantages

While high dividend yields are attractive, it’s possible they may be at the expense of the potential growth of the company. It can be assumed that every dollar a company is paying in dividends to its shareholders is a dollar that the company is not reinvesting to grow and generate more capital gains. Even without earning any dividends, shareholders have the potential to earn higher returns if the value of their stock increases while they hold it as a result of company growth.

It’s not recommended that investors evaluate a stock based on its dividend yield alone. Dividend data can be old or based on erroneous information. Many companies have a very high yield as their stock is falling.

Forward Dividend Yield

A forward dividend yield is an estimation of a year’s dividend expressed as a percentage of the current stock price. The year’s projected dividend is measured by taking a stock’s most recent actual dividend payment and annualizing it. The forward dividend yield is calculated by dividing a year’s worth of future dividend payments by a stock’s current share price.

For example, if a company pays a Q1 dividend of 25 cents, and you assume the company’s dividend will be consistent, the firm will be expected to pay $1.00 in dividends over the course of the year. If the stock price is $20, the forward dividend yield is 5%. If not, trailing yields, which indicate the same value over the previous 12 months, are used.

How To Track Dividend Yield Using MarketXLS Software?

Step 1: Go to the MarketXLS tab in the ribbon and hover over search & help option. Type dividend yield in the search panel. You will notice a list containing an ample amount of functions related to the dividend yield along with their short descriptions like dividend yield, forward annual dividend yield, 5-year average dividend yield, dividend record date, etc. Search for the one you need.

Dividend Yield

Step 2: Now go to the cell, type the function chosen and select the ticker symbol. Press enter. Use flash fill to perform the same function for all the stocks. You will see the list of dividend yields of different stocks.

dividend yield function

Step 3: Similarly, you can also find out all the other dividend information related to stocks. Here I have also used the function of dividend payout ratio, according to my analysis requirement before investing in a stock.

dividend payout

With the help of MarketXLS, you can stream market data for stocks, ETFs, options, mutual funds, currencies refreshed, or refresh on-demand. You can find all the important fundamental ratios and analyses in a few clicks. You can also get all the historical data (EOD, Intraday) you may need with MarketXLS functions. Update the Excel tables dynamically and save your time in formatting those tables.

Bottom Line

Some investors, such as retirees, are heavily reliant on dividends for their income. For these investors, the dividend yield of their portfolio could have a meaningful effect on their personal finances, making it very important for these investors to select dividend-paying companies with long track records and clear financial strength. For other investors, dividend yield may be less significant, such as for younger investors who are more interested in growth companies that can retain their earnings and use them to finance their growth.

Disclaimer

All trademarks referenced are the property of their respective owners. Other trademarks and trade names may be used in this document to refer to either the entity claiming the marks and names or their products. MarketXLS disclaims any proprietary interest in trademarks and trade names other than its own, or affiliation with the trademark owner.
None of the content published on marketxls.com constitutes a recommendation that any particular security, portfolio of securities, transaction, or investment strategy is suitable for any specific person. The author is not offering any professional advice of any kind. The reader should consult a professional financial advisor to determine their suitability for any strategies discussed herein. The article is written for helping users collect the required information from various sources deemed to be an authority in their content. The trademarks if any are the property of their owners and no representations are made.

Reference

https://www.investopedia.com/terms/d/dividendyield.asp

https://corporatefinanceinstitute.com/resources/knowledge/finance/dividend-yield-formula/

www.cleartax.in

Image Source

https://cdn.wallstreetmojo.com/wp-content/uploads/2018/03/Dividend-Yield-Stock-Formula.jpg

 

Read More
Calculators, Fundamental analysis, Others, Screeners, Stock investments, Templates, Valuation Models

Discounted Cash Flow Calculator (Stock Valuation Using Marketxls Discounted Cash Flow Template)

Discounted cash flow or DCF is the method for estimating the current value of an investment by taking into account its future cash flows. It can be used to determine the estimated investment required to be made in order to receive predetermined returns. In short, DCF analysis attempts to figure out the value of an investment today, based on projections of how much money it will generate in the future.
For example, $50 will be worth more currently than 1 year later owing to interest accrual and inflation. If a person is seeking to invest $50 now, he or she will want to know its return on investment and what its future valuation will be, which can be calculated through DCF.

Where Can the Discounted Cash Flow Method Be Used?
DCF can be used to estimate the valuation of –
• A business
• Real estate
• Stocks
• Bonds
• Long-term Assets
• Equipment

Assumptions in DCF:
The underlying assumption in Discounted Cash Flow is that a company or an asset is expected to make money (generate cash flow) over time. The second assumption, which is also a fundamental theory, is that the value of money today is worth more than it will be tomorrow.
Based on the two principles, the method derives its model and also its name – Discounted Cash Flow
▪ Discounted is adjusting for the diminishing value of money
▪ Cash Flow is the money generated by a business/asset

The Method:
Here’s the equation:
Discounted Cash Flow = CF1 / (1+dr)1 + CF2/ (1+dr)2 +…..+ CFn/ (1+dr)n, where
▪ Discounted Cash Flow – It is the sum of all future discounted cash flows that an asset/company is expected to produce. This sum is the fair value that we are solving for.
▪ CF – It is the total cash flow for a given year. CF1 is for the first year, CF2 is for the second year and so on.
▪ dr – It is the discount rate which is nothing but the target rate of return that you are looking for from the investment. It is the weight average cost of capital (WACC).

DCF formula

 

Application in Real Life:
When you are looking to buy shares of a company, you need to project and discount the expected cash flows.
If you find that an investment that is priced below the sum of discounted cash flows, it depicts undervaluation and, therefore, a potentially rewarding investment.
On the other hand, if the price is higher than the sum of discounted cash flows, the asset may be overvalued.

Pros of DCF Analysis:
• Incredibly detailed and includes all significant assumptions regarding the business

• It helps determine the “intrinsic” value of a business

• It does not require any comparable companies

• Model is suitable for analyzing mergers and acquisition

• Used to compute the internal rate of return IRR of an investment which is critical for making investing decisions

Cons of DCF Analysis:
• The fundamental drawbacks of the model are that significant time is required to project the variables that are involved. While some parameters such as operating cost and revenue may be easy to anticipate in advance, but getting the right understanding of capital expenditure, other investments and funding mix remain an area of concern. Thus, even a minor deviation in any of the metrics leads to a vast change in the company’s valuation.

• The future cash flows would rely on a variety of factors, such as market demand, the status of the economy, technology, competition, and unforeseen threats or opportunities.

• Estimating future cash flows too high could result in choosing an investment that might not pay off in the future, hurting profits. Estimating cash flows too low, making an investment appear costly, could result in missed opportunities. Choosing a discount rate for the model is also an assumption and would have to be estimated correctly for the model to be worthwhile.

How to Apply the Discounted Cash Flow Calculator Using MarketXLS Template:

The output of valuation is the intrinsic value of a stock. The intrinsic value is compared with the current market value.
1) If Intrinsic value < Market value –> The stock is Overvalued
2) If Intrinsic value > Market value –> The stock is Undervalued

Step 1: Enter the stock ticker in cell B15 of the sheet to get the result. Here I have taken stock of NVIDIA Corporation as an example.

STEP1.jpg”>STEP1-300×108.jpg” alt=”DCF TEMPLATE” width=”300″ height=”108″ />

Link: https://www.marketxlswp.com/template/dcf-model/

Step 2: Other useful parameters will be calculated as well. Scroll down below to check them.

STEP2.jpg”>STEP2-300×117.jpg” alt=”DCF CALCULATION” width=”300″ height=”117″ />

Step 3: For deeper analysis, switch to the calculation sheet and look at the detailed working of a DCF model.

DCF CALCUALTION

Since the calculated intrinsic value per share comes out to be lower than the market value per share, the stock is overvalued by 90.41% according to the DCF model of valuation.

Why Use This Template?
The DCF valuation can be used to assess how the market has Overvalued/Undervalued a company and by what percentage.
The model also estimates and calculates loads of important data about a company such as Future cash flows, Firm’s value, Beta, Cost of equity, Cost of debt, WACC, etc.

Disclaimer:
All trademarks referenced are the property of their respective owners. Other trademarks and trade names may be used in this document to refer to either the entity claiming the marks and names or their products. MarketXLS disclaims any proprietary interest in trademarks and trade names other than its own, or affiliation with the trademark owner.
None of the content published on marketxls.com constitutes a recommendation that any particular security, portfolio of securities, transaction, or investment strategy is suitable for any specific person. The author is not offering any professional advice of any kind. The reader should consult a professional financial advisor to determine their suitability for any strategies discussed herein. The article is written for helping users collect the required information from various sources deemed to be an authority in their content. The trademarks if any are the property of their owners and no representations are made.

Reference:
https://www.investopedia.com/terms/d/dcf.asp
https://groww.in/blog/what-is-dcf-discounted-cash-flow-and-why-is-it-important/

Image Source:
https://www.zilculator.com/real-estate-analysis/calculate-discounted-cash-flow-formula-excel-example

Read More
stockvolatility
Calculators, Fundamental analysis, Stocks

Stock Volatility Calculator

In this article, we will look at how we estimate the volatility based on the history of a stock price movement. With MarketXLS, you can simply use the stock volatility function to turn Excel into the stock volatility calculator.

Volatility comes in various forms and what MarketXLS volatility functions return is the simplest form of volatility. It is calculated by first calculated by the daily return on the stock and then taking the standard deviation on those daily returns. It can serve you well when you are comparing multiple stocks in your portfolio for its volatility.

The example below shows you a simple volatility comparison table of the stocks in a hypothetical portfolio.

stockvolatility

MarketXLS Stock Volatility Functions

MarketXLS provides you with the following functions to quickly calculate the stock volatility without you having to calculate the returns, or download historical stock prices. We perform all those calculations on the MarketXLS server and all you have to do is use this function to get the value.

stock volatility calculator

Some functions have a pre-defined period like seven days, 15 days, nine months, and so on. But you can also use the Stock Volatility Custom dates function to calculate the volatility on the stock returns over a custom historical period as shown below.

MSFT.png” alt=”stock volatility formula” width=”451″ height=”162″ />

Just like, the volatility functions MarketXLS has other functions to calculate the cumulative stock returns using the functions like =StockReturnSevenDays(“MSFT“)

MarketXLS provides you with hundreds of custom functions to help you quickly analyze the stock data. Use these functions as your stock volatility calculator in Excel. Stock volatility simply refers to the severity with which the price of the stock fluctuates on a daily basis. And when comparing various investments this measure could be very useful in making investment decisions.

Beta vs Volatility

Both beta and volatility are measures of risk, sometimes these two may be confused. For example, a stock may have a higher beta and lower volatility when compared to other stock. The main difference between the two is that volatility is the total risk (Market Risk + Firm-Specific risk) whereas the Beta is just the market risk. So, the Beta will tell us the percentage change in the stock return given a one percent change in the return of the market.

Similar to the stock volatility functions you can also use MarketXLS’s CustomBeta functions to calculate the Beta on the stocks.

Stock Beta Functions

Read More
Calculators, Portfolio analysis and optimization, Portfolio Management

Portfolio Beta Calculator

In this article, we will look at how you can use MarketXLS’s portfolio beta calculator template to easily calculator Beta of your portfolios. MarketXLS has a very strong portfolio analytics engine that can get you all kind of analysis of your portfolio with easy to use Excel functions

What is Beta?

Beta is a way to measure the Systematic Risk. Let’s say you are considering investing in an Amazon INC. Stock AMZN and you want to find out the total risk in that investment. You can also think about “risk” as the volatility of the movement in the price. Now, the overall risk is a combination of up of Systematic Risk and Unsystematic risk.

Unsystematic risk comes from company-specific events. For example, the company’s profits were not up to expectations. Or if there was some news in the market that was just about the company.

The company, however, can also be affected by the systematic risk, which is the risk that comes from the market-wide conditions, for example, change in interest rates, etc.

In most cases, if you have a portfolio of multiple stocks, the unsystematic risk is diversified away as you have various stocks in your portfolio of stocks. As the number of stocks in your portfolio increases, the volatility decreases. So, you look at systematic risk when investing and figuring out what kind of risk premium you would want to pay as an investor.

So, to measure the systematic risk, you use the Beta. In most simple terms, the Beta of a stock is the percentage change in the stock’s return, given a 1% change in the market. So, a Beta of 1.5 that if the market portfolio moves by 1%, the stock could likely move by 1.5%.

Generally, you would use the Index to which the stock belongs to, to calculate the Beta. In MarketXLS, the default =StockBeta uses the ETF SPY to calculate the Beta.

So, the Beta tells us how tied in are the returns of one stock to the overall market conditions. You can also think about the systematic risk as to the “Market Risk” of a stock.

Calculate the Beta of an Entire portfolio

To calculate the Beta of the entire portfolio, you will take the weighted average of the Beta of individual stocks in the portfolio. The weights will be the proportion of the amount you invested in each stock vs. the total invested amount in your portfolio.

Use MarketXLS’s Portfolio Beta Calculator

In MarketXLS, there are many stock related analytical functions that are available, along side many Beta functions. The Beta functions as shown below can be used to instantly return the Beta of a stock over various number of periods of time.

portfolio beta calculator

Assume that your portfolio looks like as shown in the image below. The column D shows the invested amount on each of these stocks. The column F uses the MarketXLS formula =CustomBetaOneYear(stock) to return the Beta value.  Then we simply take the weighted average of the beta with weights in the portfolio.

portfolio beta calculator

As you can see our Portfolio Beta is 1.1107.

With our new functions you can calculate the portfolio Beta for stocks and ETFs portfolio by simply using a function =PortfolioBeta(Portfolio, Period) as shown below.

Portfolio Beta
Read More
Calculators, Fundamental analysis, Technical analysis

On Balance Volume (Formula In Excel)

On Balance Volume in Excel

MarketXLS.com offers On Balance Volume excel based utilities to enhance your personal spreadsheets. Utilizing the security identifier and a date rage, we can generate OBV trend lines allowing us to enhance our charting capabilities by provide a powerful analytical tool. Historically the On Balance Volume indicator has been a valuable and reliable tool for many traders.

What is On Balance Volume:

On-Balance Volume (OBV) Excel analytics utilizes volume and price to help predict future price movement. The on balance volume indicator is one of the few indicators that truly precedes the movement of price. Other Indicators, MACD and RSI to name a few popular tools are lagging indicators. The benefit of the OBV is that it is a leading indicator and can help identify trends.

On Balance Volume Formula:

OBV totals of the volume both additions and subtractions and forms and OBV value. Cumulating the values we can draw a trend line that can then be compared with the price chart of the underlying stock. Allowing us to identify divergences or confirmations of trends.

The method for OBV is as follows:

If closing price Today(n) is greater than (>) the closing price of yesterday (n-1), then OBV of Today(n) = OBV of day Yesterday(n-1) + Todays(n) trading volume

If closing price of day Today(n) is less(<) then close price of Yesterday(n-1), then OBV of Today(n) = OBV of day Yesterday(n-1) – Todays trading volume

If closing price of Today(n) equals(=) the closing price of Yesterday(n-1), then OBV of Today(n) = OBV of Yesterday(n-1) – or no change

On Balance Volume Excel Formula

Excel coupled with VBA scripting can enhance your charting capability. Utilizing the tools we can trend the On Balance Volume indicator. To calculate the OBV you will need the securities historical trading volume and closing prices. These can be source from Yahoo Finance or another open source forum with historical data.

Note: The OBV of day 1 is the trading volume of day 1 – Or first day of reporting period.
Continued until last day of reporting date range

On Balance Volume Benefits

OBV helps predict trends and trend reversals. When large volume occurs with minimal price change, we should plan for a bullish trend. On the contrary, a significant decrease in volume with minimal price change are a bearish signal. Bullish divergences form when the OBV trends higher creating higher lows even as prices move lower. Bearish divergences form when the OBV indicator trends lower and forms lower lows, while prices trend higher and forge higher highs. As with any indicator, the OBV should not be the only too utilize. Paired with other tools like price action can have large benefits to your trading strategy.

Read More
Time Value of Money Formula Excel
Basics, Calculators, Fundamental analysis

Time Value Of Money (Excel Formula – Calculator)

Time value of money is the change in value or purchasing power of money with the time.

In the period of inflation purchasing power of money is going down day by day. If we invest or deposit some money in the bank, then we receive a return or interest on such money. Such return or interest is the compensation for the loss of value of money for such length of the period. The money received today is more valuable than money received in the future. It is vice versa in the environment of deflation.

Time Value of Money Formula Excel
Time Value of Money Formula Excel

Types of Time Value of Money

1) The present value of money

Present value is the value today of an amount that is receivable in the future with the investment rate for the period of time. The investment rate is the discounting rate or the hurdle rate. We can calculate it by using the technique of discounting.

2) Future value of money

Future value is the compounded amount of money after a period of time with the interest rate. It is calculated by the technique of compounding.

Difference between simple and compound interest

Simple interest is due periodically and paid periodically. It is not accumulated with the principal amount.
Amount (A) = P + I
Simple interest (I) = (P x R x N)/100
A = Amount
P = Principal
R = rate of interest
N = No. of years

Even though, the compound interest is due periodically it’s not paid regularly there it is accumulated with the principal.
A = P(1+r/100)^n
Compound interest (I) = A – P
A = Amount
P = Principal
r = Rate of interest
n = No. of years

Compound interest is interest on (principal + interest)

Frequency
Compounded Semi-annually : r = Annual rate/2, n=no of yrs. X 2
Compounded Quarterly : r = Annual rate/4, n=no of yrs. X 4
Compounded  Monthly : r = Annual rate/12, n=no of yrs. X 12

How to calculate the effective interest rate
Nominal interest = 9 %
If compounded quarterly
A=100[1+(9/4)/100]^1*4
A=100(1.0225)^4
A=109.30
Effective interest (I) = A – P
Which is  = (9.30/100)*100
Therefor, the effective interest rate is 9.30%

Annuity : is the regular payment or receipt at fixed intervals.

The types of annuities

1) The regular annuity is the amount we get or pay at the end point of each period.
For example, we deposit USD 500 in the bank at end of every month.
2) The immediate annuity is money we pay or receive at the start point of each period.
For example, we pay the rent on the 1st day of every month.

When in doubt, its generally assumed to be the regular annuity.

Type Future value Present value
Regular P[(1+i)^n-1]/i P{[(1+i)^n-1]/i(1+i)}
Immediate P(1+i)[(1+i)^n-1]/i P(1+i){[(1+i)^n-1]/i(1+i)}

We derive the formulas above using the geometric progression.

To summarize, the change in the value of money with the period of time in both inflation and deflation period can be thought of as the Time value of money.

Read More