1

Dynamic methods for evaluating investment projects are based on the principle of discounting cash flows. The discounting operation is based on the discount rate. The discount rate is a measure of not only profitability, but also risk. The justification for the discount rate is largely determined by the calculation of the beta coefficient. The beta coefficient for calculating the discount rate in relation to investments in real assets is an indicator calculated for the planned type of operating activity of the enterprise that will arise as a result of the investment project. It is a measure of market risk, reflecting the variability of the profitability of the operating activities of an enterprise in relation to the average market profitability of this type of activity in the country or region.

assessment of investment projects

dynamic assessment methods

discount rate

beta coefficient of investment in real assets

1. Roche J. Company value: From desired to actual / Julian Roche; lane from English E.I. Nedbalskaya; scientific ed. P.V. Lebedev. – Minsk: “Grevtsov Publisher”, 2008 – 352 p.

2. What is the beta coefficient of a stock // URL: http://www.homearchive.ru/business/in0042.html.

3. Podkopaev O.A. On the issue of the shortcomings of dynamic methods for assessing investment projects // Advances in modern science. – 2014. – No. 7. – P. 144–147.

4. Sokolov D. Beta coefficient for a non-traded company. How to use peer companies? // URL: http://p2ib.ru/beta_koefficient.

As you know, investments are always characterized not only by a certain profitability, but also by a level of risk corresponding to this profitability. In this regard, the discount rate is a measure of not only profitability, but also risk. An approach that is based on the return on assets pricing model (CAPM) has become widespread in determining the discount rate. According to this model, the profitability of a financial asset will depend on the risk-free rate, “beta” and market returns, i.e. The required rate of return (discount rate, opportunity cost) for any type of investment depends on the risk associated with these investments and is determined by the expression:

Rtotal = R0 + R1 = R0 + Rm - R0) * β (1)

● R0 - return on risk-free assets;

● R1 - risk premium;

● Rm - average market rate of return;

● β - beta coefficient characterizing the level of systematic risk for an investment project (investment risk meter).

Let us recall that, based on the classical “portfolio” theory, financial assets have inherent risks that can be determined by quantitative methods. Firstly, this is the specific risk of the company's shares. In another way it is called unsystematic. This risk can be reduced by diversifying the assets in the portfolio. Secondly, by buying a share, an investor takes on the risk of the entire system. Systematic risk is a risk that cannot be radically reduced by increasing the number of assets in the portfolio, i.e. the diversification method does not “work”. Using the beta coefficient, such non-diversifiable risk is assessed. Beta describes the relationship between the performance of a specific asset and the market as a whole. The beta coefficient is needed to determine the discount rate in various fundamental analysis models, including when calculating the fair price of a stock using the discounted cash flow method.

Beta measures the sensitivity of one variable (such as the return of a particular stock) to another variable (the average market return or portfolio return). The beta factor in the CAPM model, used to calculate the discount rate for investments in securities, is a measure calculated for a security or a portfolio of securities. It is a measure of market risk, reflecting the variability of the return of a security (portfolio) in relation to the return of the portfolio (market) on average (the average market portfolio).

The beta coefficient shows the change in the price of a security in comparison with the dynamics of the entire stock market:

● for the Standard & Poor’s 500 Composite Index, the beta coefficient is 1;

● for more volatile stocks, the beta coefficient is greater than 1;

● For less volatile stocks, beta is less than 1.

The economic meaning of the beta coefficient: the higher the beta coefficient of an asset, the higher the risk of investing in that asset. If the beta is greater than one, it means that when the market is up, the security being analyzed is outperforming it. In conditions of decline, on the contrary, it “pulls” down faster. The higher the beta of an asset, the higher its volatility. So, for example, if the beta coefficient of an LTD stock is 1.5, then this means that this stock is 1.5 times more volatile than the “market”: if the “market” rises by 10%, then the stock of the company in question will rise by 15 %. Conversely, if the “market” falls by 10%, then the stock of that company will fall by 15%.

Cautious investors prefer stocks with low betas. So, for example, if the beta coefficient of RCM stock is 0.5, then this means that this stock is 50% less volatile than the “market”: if the “market” rises by 10%, then the stock of the company in question will rise by only 5%. Conversely, if the “market” falls 10%, the stock price will only fall 5%.

The value of the beta coefficient can change over time. Therefore, its calculation is based on at least 60 indicators of monthly income (weekly income is considered acceptable “only if the shares are liquid and traded every day”). However, this poses many problems. First, a closely held company may have difficulty finding comparable public companies, especially those with the same debt-to-equity ratio. And with different ratios of equity and debt capital, recalculation of the beta coefficient may be erroneous. Secondly, different sources give completely different beta values ​​for both the past and future periods. For example, IBM's 1999 BARRA beta was 1.18/1.39; according to Bloomberg - 1.16; according to S&P - 1.24; and according to ValueLine - 1.15.

Many sources offer information on beta coefficients; the problem is that they contradict each other. The same issues arise with regard to time frames: should betas be daily, weekly or monthly? For what period and with what statistical error? Should adjustments be made according to Bayes' theorem? Should special circumstances be taken into account? Do changes need to be made to reflect the lack of liquidity in certain stocks? How to deal with changes that occur over time? How should foreign branches be taken into account? Moreover, using beta to evaluate the performance of an investment or company in an acquisition is not always correct. Perhaps the bidder is acquiring a company with a different degree of risk. There may be benefits from a merger by reducing the level of fixed costs in the acquiring company and in the target company. There may be transactions involving debt instruments, such as leasing agreements or risk sharing agreements, or projects involving option terms. The theoretical justifications for choosing the beta coefficient study period are quite controversial. On the one hand, if you take data over a too short time period, the results obtained will be distorted by short-term market factors. For example, “the beta coefficient of Mosenergo shares would have been negative in May. After all, when the market fell, the company's securities, on the contrary, grew. It’s just that someone was actively buying them back then.” Thus, the beta coefficient can vary greatly depending on the selected period. The market is unpredictable over short periods of time, and, on the other hand, the horizon for calculating the beta coefficient should not be too long, since the Russian financial market is characterized by high volatility.

Investments in real assets are associated with the creation of new or development of existing operating activities of an enterprise. Let us remind you that the operating activities of a company mean its main activities. It is operating activities that are the main source of income (operating profit, EBIT) and cash for a normally functioning enterprise.

Investments in real assets, just like financial investments, have risks that can be determined by quantitative methods. These risks include: unsystematic (specific to a particular enterprise) and systematic risks (risks inherent in the entire market). Firstly, the specific risk of real investments is the risk of operational activities arising as a result of investments, inherent in a particular enterprise. This risk is also called unsystematic and is largely related to the internal environment of the enterprise. An investor whose interests, for example, are related to the production and sale of furniture, can diversify his capital by investing in different furniture business companies to reduce unsystematic risk. Secondly, by choosing an operating activity (for example, the production and sale of furniture), the investor takes on the risk of the entire market (the furniture market). Thus, systematic risk (non-diversifiable) is the risk inherent in the entire market. Systematic risks include interest rate risk, currency risk, inflation risk, and political risk. Systematic risks are associated with the economic situation in the country, rising prices for resources, rising inflation, changes in monetary and credit policies, etc. In this regard, a risk that cannot be radically reduced by increasing the number of assets (investments in different furniture business companies) in the real investment portfolio , is called systematic. It is precisely this non-diversifiable risk of real investment that is assessed using the beta coefficient. In this case, the beta coefficient describes the relationship between the behavior of a particular enterprise and the market as a whole. The beta coefficient adjusts the market premium equal to the difference between the average market and risk-free returns, depending on the degree of exposure of the investee to non-diversifiable risks.

Thus, the beta coefficient for calculating the discount rate in relation to investments in real assets is an indicator calculated for the planned type of operating activity of the enterprise that will arise as a result of the investment project. It is a measure of market risk, reflecting the variability of the profitability of the operating activities of an enterprise in relation to the average market profitability of this type of activity in the country or region.

If the beta of an operating activity is equal to one, then that business activity has the same amount of systematic risk as the market as a whole.

If the beta coefficient is greater than one, then the operating activities of the company in question are riskier than the same economic activities on average in the market. For example, due to the company's use of a larger share of borrowed funds in the structure of liabilities than the market average. However, the fundamental concept of the relationship between return and risk states: the higher the risk, the higher the required return. Indeed, an aggressive asset financing policy, which assumes a large share of borrowed funds in the structure of financing sources, indicates a high level of financial risk, but allows for a higher return on equity due to the effect of financial leverage. At the same time, if the economic situation in the country worsens, interest costs on attracting capital (WACC) will increase due to an increase in interest on loans and borrowings (CC), which will further reduce the company’s profitability (in particular, return on assets calculated based on net profit), than the market average.

If the coefficient is less than one, then the operating activities of the analyzed enterprise are less risky than the same economic activities on average in the market. For example, due to the firm using more equity capital and risk management tools than the market average. Application of conservative asset financing policies, i.e. the predominance of a large share of equity capital in the sources of asset financing reduces the ability to obtain greater profitability and limits the pace of development of the enterprise compared to the more risky aggressive model of financing the company’s assets, but increases its financial stability. The use of risk management tools (insurance, hedging, factoring, etc.) is associated with additional financial costs and also reduces the company’s ability to obtain high returns for the sake of the company’s economic stability. At the same time, if the economic situation in the country worsens, the profitability of this enterprise will decrease to a lesser extent than the market average.

The beta coefficient can be calculated using statistical methods based on observing changes in the average market return and the profitability of a specific asset over a sufficiently long period. The expert method for determining the value of the β-coefficient is based on an analysis of the degree of influence of various types of systematic risk on the investment object for subsequent weighted assessment. As profitability indicators, you can take return on assets calculated based on net profit. Finding a realistic overall risk value in relative terms is a labor-intensive and very difficult task for practical implementation using knowledge of probability theory and mathematical statistics. The calculation of the β-coefficient also requires the availability of statistical data on profitability and risks affecting the specific type of operating activity of the company. Therefore, the model can be applied by entrepreneurs already engaged in business and only for those types of operating activities that they intend to develop or expand. Finding the β-coefficient is not possible for novice entrepreneurs starting their own business. That is, this method cannot be applied by firms that do not have sufficient statistics to calculate their β-coefficient, as well as those that do not have the opportunity to find an analogous enterprise whose β-coefficient they could use in their own calculations.” To determine the discount rate, such companies should use other calculation methods or improve the methodology to suit their needs.

Beta coefficient is calculated as the ratio of the covariance of two variables to the variance of the second variable. Thus, the beta coefficient for the planned profitability of an enterprise’s operating activities relative to the average market profitability of a given type of activity is the ratio of the covariance of the values ​​under consideration to the market dispersion, respectively:

● ra - the estimated value for which the beta coefficient is calculated: the planned profitability of operating activities that will arise as a result of the implementation of the investment project;

● rp - the reference value with which comparison is made: the average market profitability of the type of activity planned for implementation in the country or region;

● Cov - covariance of the estimated and reference value;

● Var - dispersion of the reference value.

In practice, the beta coefficient calculation method is also used, based on comparison with the performance of peer companies. Such companies are firms from the same industry, whose business is as similar as possible to the business of the company being analyzed. When calculating the beta coefficient, it is necessary to make a number of adjustments, in particular, for the difference in the capital structure of the company planning to implement an investment project in real assets (or in the structure of the project’s sources of financing) and analogue companies (ratio of debt and share capital). While asset beta is the variability of the cash flows generated by those assets, equity beta depends on the level of debt in the ownership structure.

Accordingly, the beta coefficient of assets can be mathematically represented as follows:

bAct = bDebt∙wDebt + bAK∙wAK, (3)

● bAct - beta of the company's assets;

● bDebt - beta of the company's debt;

● bAK - beta of the company's share capital;

● wDebt - the share of debt in the ownership structure;

● wAK - share of share capital (share capital) in the ownership structure.

It's worth noting that the higher a company's debt levels, the higher its equity beta. If a company has high levels of debt, then a significant portion of its earnings will go to creditors, so the remaining cash flows to shareholders will fluctuate greatly—their variability will be significantly greater than the dispersion of earnings. If the debt level is low, then loan payments have virtually no effect on what goes to shareholders, i.e. the variability of net income and the variability of cash flow to shareholders will be approximately the same.

When calculating the weights of debt and equity, one important point must be taken into account - interest on loans is deducted from profits before calculating income taxes, so the debt level is adjusted by the amount (1-t), where t is the income tax rate. That is, the debt raised for financing “costs” slightly less than its nominal value.

As a result, the formula looks like:

where D and E are the amount of debt and equity capital, respectively.

It is standardly assumed that bDebt = 0, i.e. loan payments do not depend on general market factors. Although this is not always true (for example, the probability of bankruptcy increases during a crisis in the economy and the corresponding collapse in the market), but in practice this assumption is accepted in most cases.

Thus, scholars disagree on how accurate the CAPM model's risk-return prediction is; The practical calculation of the beta coefficient seems to be a complex and time-consuming process, but these facts in themselves do not prove the inconsistency of the theory in practice.

Bibliographic link

Podkopaev O.A. METHODS AND APPROACHES TO CALCULATING THE BETA COEFFICIENT FOR DETERMINING THE DISCOUNTING RATE OF FINANCIAL AND REAL INVESTMENTS // International Journal of Applied and Fundamental Research. – 2015. – No. 3-2. – pp. 245-249;
URL: https://applied-research.ru/ru/article/view?id=6523 (access date: 02.25.2020). We bring to your attention magazines published by the publishing house "Academy of Natural Sciences"

Let's analyze such an investment indicator as the beta coefficient, calculate it using a real example using Excel and consider various modern modifications.

Beta coefficient. Definition

Beta coefficient (EnglishBeta,β, beta coefficient) – determines the measure of risk of a stock (asset) in relation to the market and shows the sensitivity of changes in the stock’s profitability in relation to changes in market profitability. Beta can be calculated not only for an individual stock, but also for an investment portfolio. The coefficient is used as a measure of systematic risk, and is used in the W. Sharpe model - valuation of capital assets CAPM ( CapitalAssetsPriceModel). First, the beta coefficient was considered by G. Markowitz to assess the systematic risk of stocks, which was called the non-diversifiable risk index. The beta coefficient allows you to compare shares of different companies with each other based on their degree of risk.

Beta Calculation Formula

β – beta coefficient, a measure of systematic risk (market risk);

r i – profitability of the i-th acacia (investment portfolio);

r m – market return;

σ 2 m – dispersion of market returns.

★ (calculate your portfolio in 1 minute) + risk and return assessment

★

Analysis of the risk level by the value of the beta coefficient (β)

Beta measures the market risk of a stock and reflects the sensitivity of a stock's changes to changes in market returns. The table below shows the risk level estimate based on beta. Beta can have either a positive or negative sign, which shows a positive or negative correlation between a stock and the market. A positive sign reflects that the returns of stocks and the market are moving in the same direction, a negative sign – movement in different directions.

Indicator value	Share risk level	Direction of change in stock returns
	High	Unidirectional
	Moderate	Unidirectional
	Short	Unidirectional
-1 < β < 0	Short	Multidirectional
β = -1	Moderate	Multidirectional
	High	Multidirectional

Data for constructing beta coefficient by information companies

The beta coefficient is used by many information and investment companies to assess systematic risk: Bloomberg, Barra, Value Line, etc. To construct the beta coefficient, monthly/weekly data over several years is used. The table shows the main parameters for assessing the indicator by various information companies.

You may notice that Bloomberg uses a short-term assessment of the indicator, while Barra and Value Line use monthly data on stock and market returns over the past five years. Long-term assessment can be greatly distorted due to the influence of various crises and negative factors on the company's shares.

Beta coefficient in the capital asset pricing model –CAPM

Formula for calculating stock returns using the CAPM capital asset model ( CapitalAssetsPriceModel, model by W. Sharpe) has the following form:

Where:

r is the future expected return on the company's shares;

r f – return on a risk-free asset;

r m – market profitability;

β – beta coefficient (a measure of market risk), reflects the sensitivity of changes in the value of a company’s shares depending on changes in market profitability (index);

The CAPM model was created by W. Sharp (1964) and J. Linter (1965) and allows you to predict the future value of the return on a stock (asset) based on linear regression. The model reflects the linear relationship between the planned return and the level of market risk, expressed by the beta coefficient.

To calculate market returns use the return of an index or index futures (MICEX index, RTS index for Russia, S&P500 index for the USA).

Example of calculating beta coefficient inExcel

Let's calculate the beta coefficient in Excel for the domestic company OJSC Gazprom. This company has ordinary shares, the quotes of which can be viewed on the website finam.ru in the “Data Export” section. For the calculation, we took monthly quotes for the shares of OJSC Gazprom (GAZP) and the RTS index (RTSI) for the period from 01/31/2014 to 01/31/2015.

To calculate the beta coefficient, it is necessary to calculate the linear regression coefficient between the return on shares of OJSC Gazprom and the RTS index. Let's consider two options for calculating the beta coefficient using Excel.

Option #1. Calculation via formulaExcel

The calculation through Excel formulas looks like this:

INDEX(LINEST(D6:D17,E6:E17),1)

Option #2. Calculation via the Data Analysis add-on

The second option for calculating beta uses the Data Analysis Excel add-in. To do this, go to the “Data” section in the main menu of the program, select the “Data Analysis” option (if this add-in is enabled) and select “Regression” in the analysis tools. In the “Input interval Y” field, select the returns of the Gazprom OJSC shares, and in the “Output interval X” field, select the returns of the RTS index.

Next, we will receive a regression report on a separate sheet. Cell B18 shows the value of the linear regression coefficient, which is equal to beta = 0.46. We will also analyze other parameters of the model, for example, the R-squared indicator (determinism coefficient) shows the strength of the relationship between the profitability of the Gazprom share and the RTS index. The coefficient of determinism is 0.4, which is quite low for accurately predicting future profitability using the CAPM model. Multiple R is a correlation coefficient (0.6), which shows the existence of a relationship between the stock and the market.

A value of 0.46 beta coefficient for a stock indicates moderate risk and at the same time the co-directionality of changes in returns.

★ (calculation of Sharpe, Sortino, Treynor, Kalmar, Modiglanca beta, VaR) + forecasting course movements

Disadvantages of Using Beta in the CAPM Model

Let's consider a number of disadvantages inherent in this coefficient:

1. The difficulty of using beta to value low-liquid stocks. This situation is typical for developing capital markets, in particular: Russia, India, Brazil, etc.
2. It is not possible to evaluate small companies that do not issue ordinary shares. Most domestic companies have not gone through the IPO procedure.
3. Instability of beta coefficient forecast. Using linear regression to estimate market risk from historical data does not provide accurate risk forecasts. Generally, it is difficult to predict beta for more than 1 year.
4. It is not possible to take into account the company’s unsystematic risks: market capitalization, historical profitability, industry affiliation, P/E criteria, etc., which influence the expected profitability.

Since the coefficient proposed by U. Sharpov did not have proper stability and could not be used to predict future profitability in the CAPM model, various scientists proposed modifications and adjustments to this indicator ( Englishadjusted betamodifiedbeta).Let's look at the adjusted betas:

Modification of the beta coefficient from M. Blum (1971)

Marshall Bloom showed that over time, the beta coefficients of companies tend to 1. The formula for calculating the adjusted indicator is as follows:

Using these weights allows for a more accurate prediction of future systematic risk. This modification is used by many news agencies, such as Bloomberg, Value Line and Merrill Lynch.

Beta modification from Bava-Lindsberg (1977)

In his adjustment, Lindsberg proposed calculating a one-sided beta coefficient. The main postulate was that most investors do not consider changes in profitability above a certain level as a risk, and only what is below the level is considered a risk. The minimum level of risk in this model was the return on a risk-free asset.

Where:

r i – stock return; r m – market profitability; r f – return on a risk-free asset.

Beta modification from Scholes-Willims

β -1, β, β 1 – beta coefficients for the previous (-1) current and next (1) period;

ρ m – autocorrelation coefficient of market returns.

Beta modification from Harlow-Rao (1989)

The formula reflects one-sided beta, with the assumption that investors view risk only as a downward deviation from average market returns. In contrast to the Bava-Lindsberg model, the level of average market profitability was taken as the minimum level of risk.

where: μ i – average share return; μ m – average market profitability;

Summary

The beta coefficient is one of the classic measures of market risk for assessing the performance of stocks, investment portfolios and mutual funds. Despite the complexity of using this tool to evaluate domestic low-liquid stocks and the instability of its changes over time, the beta coefficient is a key indicator for assessing investment risks. The considered modifications of the coefficient allow us to adjust and give a more accurate assessment of the systematic risk. Ivan Zhdanov was with you, thank you for your attention.

The Sharpe model examines the relationship between the return of each security and the return of the market as a whole.

Basic assumptions of the Sharpe model:

As profitability security is accepted mathematical expectation of profitability;

There is a certain risk-free rate of return, i.e., the yield of a certain security, the risk of which Always minimal compared to other securities;

Relationship deviations return of a security from the risk-free rate of return(Further: security yield deviation) With deviations profitability of the market as a whole from the risk-free rate of return(Further: market return deviation) is described linear regression function ;

Security risk means degree of dependence changes in the yield of a security from changes in the yield of the market as a whole;

It is believed that the data past periods used in calculating profitability and risk fully reflect future profitability values.

According to the Sharpe model, deviations in security returns are associated with deviations in market returns using a linear regression function of the form:

where is the deviation of the security's yield from the risk-free one;

Deviation of market returns from risk-free ones;

Regression coefficients.

The main drawback of the model is the need to predict stock market returns and the risk-free rate of return. The model does not take into account fluctuations in risk-free returns. In addition, if the relationship between the risk-free return and the stock market return changes significantly, the model becomes distorted. Thus, the Sharpe model is applicable when considering a large number of securities that describe b O most of the relatively stable stock market.

41.Market risk premium and beta coefficient.

Market risk premium- the difference between the expected return of the market portfolio and the risk-free rate.

Beta coefficient(beta factor) - indicator calculated for securities or a portfolio of securities. Is a measure market risk, reflecting variability profitability security (portfolio) in relation to the portfolio return ( market) on average (average market portfolio). For companies that do not have publicly traded shares, a beta can be calculated based on a comparison with the performance of peer companies. Analogues are taken from the same industry, whose business is as similar as possible to the business of a non-public company. When calculating, it is necessary to make a number of adjustments, in particular, for the difference in the capital structure of the companies being compared (debt to equity ratio).

Beta coefficient for an asset in a securities portfolio, or an asset (portfolio) relative to the market is a relation covariances of the quantities under consideration to variances reference portfolio or market, respectively :

where is the estimated value for which the Beta coefficient is calculated: the return on the asset or portfolio being evaluated, - the reference value with which the comparison is made: the return on the securities portfolio or market, - covariance estimated and reference value, - dispersion reference value.

Beta coefficient is a unit of measurement that gives a quantitative relationship between the movement of the price of a given stock and the movement of the stock market as a whole. Not to be confused with variability.

Beta coefficient is an indicator of the degree of risk in relation to an investment portfolio or specific securities; reflects the degree of stability of the price of these shares in comparison with the rest of the stock market; establishes a quantitative relationship between fluctuations in the price of a given stock and the dynamics of market prices as a whole. If this ratio is greater than 1, then the stock is unstable; with a beta coefficient less than 1 – more stable; This is why conservative investors are primarily interested in this ratio and prefer stocks with a low level.

Beta is a measure of the risk of a security relative to the risk of the entire stock market. It reflects the variability of the return of a single security relative to the return of the market as a whole. Beta is one of the main indicators (along with price-to-earnings ratio, equity ratio, debt-to-equity ratio and others) that stock analysts consider when selecting securities for investment portfolios. This article explains how to find beta and use it to calculate a security's return.

Steps

Beta calculation. Simple formula

Find the risk-free rate. This is the rate of return that an investor can expect when investing in safe assets such as U.S. Treasury bills or German government bills. This figure is usually expressed as a percentage.

Determine the corresponding returns of the security and the market or index. These numbers are also expressed as percentages. Typically, returns are calculated over a period of several months.

• One or both of these values ​​can be negative; this means that an investment in the security or market (index) as a whole will result in losses. If one of the two indicators is negative, then the beta will be negative.

1. Subtract the risk-free rate from the security's yield. If the yield on a security is 7% and the risk-free rate is 2%, then the difference is 5%.

   Subtract the risk-free rate from the market (or index) return. If the market return is 8% and the risk-free rate is again 2%, then the difference is 6%.

   Divide the value of the first difference by the value of the second. This is beta, which is expressed as a decimal fraction. For the example above, beta = 5/6 = 0.833.

   Using beta to determine a security's return

   1. Find the risk-free rate (described in the "Calculating Beta" section above). In this section we will use the same value – 2%.

      Determine the return of a market or index. In this section we will use the same 8%.

      Multiply beta by the difference between the market return and the risk-free rate. In this section we will use a beta of 1.5. So: (8 – 2)*1.5 = 9%.

      Add the result and the risk-free rate. 9+2=11% - this is the expected return on the security.

      • The higher the beta value for a security, the higher its expected return. However, the higher the expected return, the higher the riskiness; Therefore, before making an investment decision, it is also necessary to analyze other critical indicators of the security.

   Using Excel Charts to Determine Beta

   1. Create three columns of numbers in Excel. The first column will contain dates. In the second – the price of the index (market). The third is the price of the security for which beta needs to be calculated.

      Enter data into the table. Start with one month intervals. Select a date - for example, the beginning or end of the month - and enter the corresponding price value for a stock market index (try using the S&P500) and then the price value for the security in question. Enter values ​​for 15 or 30 dates, possibly extending back a year or two.

      • The longer the time period you select, the more accurate the beta calculation will be.

   2. Create two columns to the right of the price columns. One column is for the index return, the other is for the security return. Use an Excel formula to determine your profitability.

      First, let's find the return on the stock index. In the second cell of the index return column, enter "=" (equal sign). Then click on second cell in the column with index prices, enter "-" (minus), click on first cell in the index price column, enter "/" (divide sign), and then click first cell in the column with index prices. Press "Return" or "Enter."

      • Nothing is calculated in the first cell, since you need at least two values ​​to calculate the yield; so you will start from the second cell.
      • To calculate your profitability, you subtract the old price from the new price, and then divide the result by the old price. This gives you the increase or decrease in price (in %) over a certain period of time.
      • Your formula in the yield column might look something like this: = (B3 -B2)/B2

   3. Copy the formula to repeat it in all other cells in the index return column. To do this, click on the lower right corner of the cell with the formula and drag it to the end of the column (to the last value). This way, Excel will repeat the same formula, but using the appropriate data.

      Repeat the same algorithm for calculating the yield of the security in question. After completing the calculations, you will receive two columns with the return (in %) for the stock index and the security.

      Construction schedule. Select all the data in the return columns and click on the chart icon in Excel. Select a scatter plot. Label the X axis as the index you are using (eg S&P500) and the Y axis as the security in question.

      Add a trendline to a scatter plot. You can do this by selecting Layout Trendline or by right-clicking on the chart and selecting Add Trendline. Make sure that the equation and R 2 value appear on the graph.

      • Make sure you select a linear trend rather than a polynomial or moving average.
      • The display of the equation and R2 value on the graph depends on the version of Excel you are using. In recent versions, click on Layout and find the R 2 display.
      • In older versions of Excel, this can be done by clicking on Layout - Trendline - More Trendline Options and checking the appropriate boxes.

   4. Find the coefficient of "x" in the trend line equation. Your trend equation will be written in the form: y = βx + a. The coefficient of x is the desired beta coefficient.

   Meaning of beta

   1. Learn to interpret beta coefficient. Beta measures the risk of a security (relative to the stock market as a whole) taken on by the investor who owns it. This is why you must compare the return of one security with the return of an index that is the benchmark. The default index risk is 1. A beta value less than 1 means the security is less risky than the index to which it is compared. A beta greater than 1 means the security is riskier than the index to which it is compared.

      • For example, the beta of the company GIN = 0.5. Compared to the S&P500 (the benchmark), the JIN security is half as risky. If the S&P falls 10%, GIN's stock price will only tend to fall 5%.
      • As another example, imagine that FRANK Company has a beta of 1.5 (compared to the S&P). If the S&P falls by 10%, then the price of FRANK securities is expected to fall by 15% (one and a half times more than the S&P).

Beta coefficient(beta factor)- an indicator calculated for a security or a portfolio of securities. It is a measure of market risk, reflecting the variability of the return of a security (portfolio) in relation to the return of the portfolio (market) on average (the average market portfolio).

If the security (the portfolio in the second case) is less risky than the portfolio (the market as a whole in the second case), then the beta coefficient is less than 1. Otherwise, the beta coefficient is greater than 1.

Beta coefficient (β) shows the sensitivity of the price of an individual security to the value of the index. For example, a beta of 2 means that if the index rises by 1 percent, the price of the security will rise by 2 percent. A negative beta indicates an inverse relationship between changes in the price of a security and the value of the index. A beta coefficient of zero indicates that there is no relationship between changes in the security's price and the index.

1. Here you can see the coefficients: beta (β), alpha (α) and volatility for different periods.