Calculation of weighted average cost of capital WACC in Excel


Formula for calculating the weighted average cost of capital

The essence of WACC is to estimate the cost (return) of a company's equity and debt capital. Own capital includes: authorized capital, reserve capital, additional capital and retained earnings. Authorized capital is the capital contributed by the founders. Reserve capital is money intended to cover losses and losses. Additional capital is money received as a result of the revaluation of property. Retained earnings are cash received after deducting all payments and taxes.

Take our proprietary course on choosing stocks on the stock market → training course

The formula for calculating the weighted average cost of capital WACC is as follows:

where: re – return on equity capital of the organization;

rd is the return on the organization's borrowed capital;

E/V, D/V – the share of equity and debt capital in the company’s capital structure. The sum of equity and debt capital forms the company's capital (V=E+D);

t – interest rate of income tax.

Business valuationFinancial analysis according to IFRSFinancial analysis according to RAS
Calculation of NPV, IRR in ExcelValuation of stocks and bonds

Estimation of the weighted average cost of capital of an investment project

In the process of any commercial activity, one often has to face the problem of finding sources of financing to fill the gap in working capital or implement any investment project.

Attracting any source to finance investments, including your own funds, is subject to a fee.

The amount of payment for attracting any source of investment financing is called the cost of capital (CC), which is expressed as a percentage.

For example, if an enterprise attracts any source to finance investments in the amount of 20 million rubles, and the price of this source is 15%, then the enterprise must pay an additional 3 million rubles. (20·15 = 3).

During the investment planning process, the price of capital must be determined due to the fact that:

1) the formation of an optimal structure for financing an investment project, as well as planning the capital investment budget are based on the use of the category “price of capital”;

2) the choice between various methods and sources of financing investment projects is based on a comparison of the foam of capital;

3) the price of capital is used as a discount rate when carrying out the economic feasibility study of investments.

Determining the price of capital is associated with calculating the price of its individual components (sources of financing): own, borrowed and borrowed funds.

The price of some sources of capital financing (investments) of an enterprise can be explicit and implicit (hidden). The explicit price is the one that is calculated by comparing the expenses generated by the source (interest, dividends, etc.) with the total amount of the source. The identification of the implicit cost of a source is associated with the concept of “opportunity cost” (opportunity costs). Opportunity costs are the opportunities lost as a result of using money in one way instead of other possible ways.

Let's consider existing approaches to determining the price of the main components of capital.

The price of the enterprise's own funds. There are two points of view regarding the price of equity capital. The first is that own funds are a free source. Proponents of the second point of view believe that although the use of own funds does not imply any explicit payments (such as interest or dividends), they still have their price.

In theory, the second point of view is the most common, but in practice they rarely resort to determining the cost of equity.

Let's consider what determines the price of equity.

Source price "retained earnings". There are two approaches to determining the price of retained earnings, based on opportunity costs.

The first approach is based on the following assumptions:

a) the goal of any enterprise is to maximize the welfare of its shareholders;

b) financial markets are perfect and economically efficient, and the actions of shareholders are rational;

c) the shares of the analyzed enterprise must be listed on the stock exchange.

Within the framework of this approach, the price of capital formed from retained earnings is the return that shareholders require from ordinary shares of an economic entity (rc).

The company can either pay out the profits as dividends or invest it in production development. If the profit is invested, then the opportunity costs of its use will be determined as follows: shareholders could receive this profit as dividends and then invest it in any securities, on a bank deposit, etc. Therefore, the return from using retained earnings for production development must, at a minimum, be equal to the return that shareholders can receive from alternative investments with equivalent risk; its value will correspond to rе. Therefore, if an enterprise cannot invest retained earnings in such a way as to obtain a return greater than re, then it should pay these income to its shareholders so that they themselves invest these funds in various assets that provide re.

The return that shareholders require from ordinary shares (ge) can be assessed using two models:

1) assessment of the return on financial assets (CAPM);

2) discounted cash flow.

These methods are quite well described in the educational literature.

The second approach is applicable to enterprises of any legal form. The value of retained earnings as a source of investment financing within the framework of this approach is established based on the profitability of alternative investments. To determine the price of retained earnings, it is necessary to analyze all alternative possibilities for its use. These opportunities may consist of purchasing securities, placing funds on deposit in a bank, etc. Next, from the existing alternatives, the one with the highest profitability is selected. The profitability of this alternative will be the value of retained earnings as a source of investment financing. It should be noted that the price of retained earnings (the return on an alternative investment) should not exceed the return on investment, otherwise the implementation of the analyzed investment project is inappropriate, but the implementation of an alternative investment would be advisable.

This approach is distinguished by its relative simplicity of application in practice. It can also be used to determine the price of the captain of such a source of investment as depreciation.

In general, the price of an individual source of investment financing is calculated using the formula

Determining the price of each specific source of investment financing has its own characteristics.

In a simplified version, the price of a long-term bank loan can be calculated using the following formula:

If the entire amount of interest paid on a bank loan is included in the reduction of taxable profit, then the price of a long-term bank loan can be found from the expression

where SP is the annual interest rate paid under the loan agreement; Нп — income tax rate, shares of units.

If the interest on a bank loan does not reduce the taxable profit of the enterprise, the price of the bank loan will be equal to the annual interest rate.

The price of capital for an additional issue of shares can be determined from the expression

In a similar way, you can calculate the price of investment resources mobilized through the issue of coupon bonds:

Typically, the implementation of a large investment project is carried out not from one, but from several sources of financing. In this case, it is necessary to determine the price of each of them, and then the weighted average price of capital from all sources.

Weighted average cost of capital

where CCi is the price of capital of the i-th source of investment financing; di is the share of the i-th source of investment financing in their total value.

Example. To implement the investment project, 80 million rubles are required, of which 20 million rubles. was financed from own funds, 32 million rubles. — by obtaining a long-term loan, the remaining amount (RUB 28 million) was obtained through an additional issue of shares. The price of attracted individual sources of investment financing is: equity capital - 12%; long-term loan - 18%; additional issue of shares - 15%.

Determine the weighted average cost of capital.

Solution. 1. Let's calculate the share of each source of investment financing in the total amount of funds required to implement the investment project.

Share of own funds

20 / 80 = 0,25.

Long-term loan share

32 / 80 = 0,4.

Share of funds received through additional issue of shares

28 / 80 = 0,35.

2. Calculate the weighted average price of capital, %:

SSav.vz = 0.25 · 12 + 0.4 · 18 + 0.35 · 15 = 3 + 7.2 + 5.25 = 15.45.

Determining the price of a separate source of financing and the weighted average price of capital is necessary, first of all, for the purposes of:

o formation of an optimal structure according to the sources of financing of the investment project;

o choosing the cheapest and most reliable source of investment financing;

o using the price of capital in some cases as a discount rate in the economic justification of an investment project;

o economic justification of the investment project.

The weighted average price of capital indicator performs a number of important functions.

Firstly, it can be used within an enterprise to evaluate the effectiveness of various proposed investment projects. This indicator shows the lower limit of the project's profitability. When assessing efficiency, all projects are ranked by level of profitability, and those that provide a return exceeding the weighted average cost of capital should be accepted for implementation.

Secondly, when making an economic justification for investment projects, it is necessary to compare the internal rate of return (IRR) with the weighted average price of capital (SSav.vzv).

Rule. If IRR > SSav.vzv - the project should be accepted for implementation. IRR < SSav.vzv - the project is not profitable. IRR (IRR) = SSav.vzv - the decision is made by the investor. Here IRR is the designation of the internal rate of return according to foreign methods.

Thirdly, the weighted average cost of capital as an indicator is used to assess the capital structure and its optimization.

The optimal capital structure is considered to be one that ensures the minimum cost of servicing the advanced capital and is determined according to the option with the minimum average cost of capital.

Areas of application of the weighted average cost of capital

The WACC model is used in investment analysis as a discount rate in calculating the performance indicators of an investment project: NPV, DPP, IP. (⊕ 6 methods for assessing the effectiveness of investments in Excel. Example of calculating NPV, PP, DPP, IRR, ARR, PI)

In strategic management to assess the dynamics of changes in the value of an organization. To do this, WACC is compared to return on assets (ROA). If WACC>ROA, then economic value added (EVA) decreases and the company “loses” value. If WACC

IndicatorExplanationCompany value
WACC>ROAThe company is developing and increasing its valueEVA ↑
WACCCapital costs exceed management efficiency, company value decreasesEVA ↓

In the evaluation of M&A mergers and acquisitions . To do this, the WACC of the companies after the merger is compared with the sum of the WACCs of all companies before the merger.

In business valuation, as a discount rate in assessing key indicators of a business plan.

The applications of the WACC model can be divided into two areas: for estimating the discount rate and for assessing the efficiency of a company's capital management. For more information about methods for calculating the discount rate, read the article: → 10 methods for calculating the discount rate.

Difficulties in applying the WACC method in practice

Let's consider the main problems of using the approach to estimating the weighted average cost of capital:

  • Difficulty in estimating expected return on equity (Re). Since there are many ways to evaluate (predict) it, the results can vary greatly.
  • Inability to calculate WACC values ​​for unprofitable companies or those in bankruptcy.
  • Difficulties in using the WACC method to estimate the cost of capital of startups and venture projects. Since the company does not yet have sustainable cash flows and profits, it is difficult to predict the return on equity. To solve this problem, expert and point assessment methods have been developed.

Methods for calculating return on equity

The most difficult part of calculating the WACC indicator is calculating the return on equity (Re). There are many different approaches to assessment. The table below discusses the key models for assessing the performance of equity capital and the areas of their application ↓

Methods and modelsApplication areas
Sharpe model (CAPM) and its modifications:
MCAPM

Fama and French model

Carhart model

Used to assess return on equity for companies that issue ordinary shares on the stock market
Gordon model (constant growth dividend model)Applies to companies that issue ordinary shares with dividend payments
Based on return on equityApplies to companies that do not issue shares on the stock market, but with open financial statements (for example, for a closed joint stock company)
Based on risk premiumUsed to assess the efficiency of equity capital of startups and venture businesses

Example No. 1. Calculation of WACC in Excel based on the CAPM model

To correctly calculate the return on equity in the WACC model using the capital asset pricing model (CAPM or Sharpe model), there must be an issue of ordinary shares on the stock market (MICEX or RTS), in other words, the shares must have fairly volatile quotes on the market. For more information about calculations using the CAPM model, read the article: → Capital asset valuation model - CAPM (W. Sharp) in Excel.

The cost of an organization's shareholder (equity) capital is calculated using the CAPM model using the formula:

Where:

r is the expected return on the company's equity;

rf – return on a risk-free asset;

rm – return of the market index;

β – beta coefficient (sensitivity of changes in stock returns to changes in market index returns);

σim is the standard deviation of the change in the stock’s return from the change in the return of the market index;

σ2m – dispersion of market index returns.

The return on the risk-free asset (Rf) can be taken as the yield on government OFZ bonds. Bond yield data can be viewed on the website rusbonds.ru. For the calculation we will use a coupon yield of 6.2%. The figure below shows the OFZ-PD bond card ⇓

OFZ bond yield

Average market return (Rm) is the average return of the RTS or MICEX market index (on the Moscow Exchange website → View index data archive). We took a yield of 7%.

The beta coefficient shows the sensitivity and direction of changes in stock returns to market returns. This indicator is calculated based on the returns of the index and the stock. For more information about calculating the beta coefficient, read the article: → Calculating the beta coefficient in Excel. In our example, the beta coefficient is 1.5, which means the stock is highly volatile relative to the market. The formula for calculating the value of equity (shareholder) capital is as follows:

Cost of equity = B7+B9*(B8-B7)

Cost of borrowed capital (Rd) – represents the fee for using borrowed funds. We can obtain this value based on the company’s balance sheet; an example of calculating these values ​​is discussed below. The interest rate for income tax is 20%. The income tax rate may vary depending on the type of activity of the company.

Different income tax rates

Profit tax percentage rate

The weight of equity and debt capital in the example was taken as 80 and 20%, respectively. The formula for calculating WACC is as follows:

WACC = B6*B12+(1-B11)*B13*B10

Example of calculating wacc in Excel

Redemption of shares by offer. What you need to know

In some situations, a company's shareholders may receive an offer to buy out their shares. The buyer may be the company itself or another major shareholder. Such an offer is usually called an offer, and the procedure itself is called a buyout.

The offer is published on the company’s website or on a special corporate information disclosure website. The repurchase offer is addressed to a wide range of shareholders; any of them can participate by presenting their shares and receiving cash or other securities for them. If the investor does not want to participate in the offer, then he can simply ignore this message. But in any case, the very fact of issuing an offer can affect market quotes.

For an ordinary private investor, a buyout can bring both profit and loss. To avoid unwanted risks or to spot profitable opportunities in time, it is worth understanding in more detail in what cases a buyout is possible and at what price it can take place.

In what cases can you apply for the repurchase of shares by the company?

Cases when shareholders have the right to demand the repurchase of shares by the company are listed in detail in Art. 75 of the law on joint stock companies.

Shareholders, owners of voting shares, have the right to repurchase after the general meeting of shareholders (GMS) of a public joint-stock company (PJSC) makes a decision on:

  • Reorganizations;
  • Approval of a major transaction with a volume of more than 50% of the book value of assets;
  • Introducing amendments and additions to the charter that limit the rights of shareholders;
  • Refusal of the status of a public company;
  • Delisting (company shares cease to be traded on the stock exchange).

In all these cases, shareholders have the right to demand the redemption of their shares if they voted against the decision or did not take part in the voting.

If an investor sees the prerequisites that any of the above issues may be brought to the General Meeting, then this is a reason to assess the likelihood of a buyout proposal and its impact on the return on investment. Price may be the determining factor here.

The buyout price is determined by the company's board of directors. In this case, the price should not be lower than:

  • The market value determined by the appraisal company without taking into account its changes due to the actions of the company, which resulted in the emergence of the right to demand appraisal and repurchase of shares.
  • The weighted average price of shares at organized trading for the 6 months preceding the date of the decision to hold the General Meeting, the voting at which gave rise to the right to repurchase.

The methodology of valuation companies remains a “black box” for minority shareholders, and in practice, to predict the lower limit of the repurchase, in practice, only the weighted average share price at trading for 6 months can be used.

It is worth noting that until April 26, 2021, the weighted average trading price was used as a benchmark only in the event of delisting or renunciation of public company status. In all other cases, companies could be guided solely by the opinion of the appraiser.

Example 1. In October 2021, the supervisory board of VTB Bank submitted for consideration to the General Assembly the issue of reorganization in the form of the merger of VTB Bank 24. The repurchase price, determined by an independent appraiser, was 3.8 kopecks. per share, which meant a discount of about 38% to the market price of 6.1 kopecks. If the current version of the law had been in force then, the repurchase price should have been no lower than the weighted average trading price for 6 months, which at that time was 6.36 kopecks per share.

Example 2. In August 2021, the Board of Directors of Lenzoloto submitted to the General Meeting for consideration the issue of selling 100% of its main asset ZDK Lenzoloto to the parent company Polyus. The deal affected more than 50% of the value of the company's assets, so shareholders who did not agree with it were sent a buyout offer. The redemption price, determined as the higher of two possible estimates, amounted to RUB 19,567. per ordinary share (premium to the market of about 10%). The company offered RUB 3,607 for a preferred share. (discount to the market about 33%).

The example of Lenzoloto clearly demonstrates a number of nuances. Firstly, according to paragraph 5 of Art. 76 of the Federal Law “On Joint-Stock Companies”, the total amount of funds allocated by the company for the repurchase of shares cannot exceed 10% of the value of its net assets as of the date of the decision that gave rise to such a right for shareholders.

Secondly, in general, the right to repurchase arises only from the holders of voting shares. In order to understand what owners of preferred shares can expect, it is necessary to understand the specific case in detail, as well as compare it with the intricacies of legislation and judicial practice, which is beyond the scope of this material.

In the case of Lenzoloto shares, holders of preferred shares were asked to sell or convert their securities based on a valuation suggesting a 7% discount to the weighted average trading price 6 months before the publication of the message.

In what cases can you apply for the repurchase of shares by other shareholders?

A minority shareholder can count on the repurchase of shares by another shareholder if:

1. A shareholder wishing to purchase more than 30% of the company's shares has submitted a voluntary buyout offer The redemption price is determined by the buyer. As a rule, the buyer offers a premium to the market price, since otherwise the remaining shareholders have no incentive to sell their shares to him.

Typically, such a buyout is interested in majority shareholders who plan to consolidate a large stake in the company up to full control with subsequent delisting. In practice, the offer usually comes from a subsidiary of the company, on whose balance sheet the shares will be repurchased.

Example 3. In 2021, TMK repurchased 230 million shares (22.3% of outstanding shares) as part of a voluntary offer. The shares were purchased on the balance of 100% of the Volzhsky Pipeline Plant subsidiary. Redemption price 61 rub. per share at the time of publication of the news included a premium of about 30% to the market price, so the quotes jumped sharply. In September, TMK made another offer to buy back shares at the same price. Within its framework, the remaining free-float volume can be purchased. The company has not announced plans to leave the Moscow Exchange, however, it can be assumed that if the share of free float falls below 5%, the remaining securities may be forcibly purchased and the company will become private.

2. The shareholder (including all affiliates) who, during the last transaction, became the owner of more than 30%, 50% or 75% of the company's shares is obliged to send a mandatory offer to the remaining shareholders at a price not lower than the highest price at which he purchased promotions within the last 6 months.

The redemption price also cannot be lower than the weighted average price at organized auctions over the last 6 months or the market price determined by the appraiser.

The described rule is intended to protect the interests of minority shareholders in cases where an individual large shareholder increases its influence on corporate decision-making. Those shareholders who believe that the potential return of the shares will be reduced because of this can exercise the right to sell the shares and receive fair compensation for them.

Example 4. In April 2021, Severgroup purchased 42% of Lenta from Luna inc and the EBRD at a price of $3.6 per GDR ($18 per share). Since Severgroup became the owner of more than 30%, it had an obligation to send a mandatory offer to other Lenta shareholders to buy out their shares at a similar price. The price implied a premium to the market of about 6.5%. As a result of this offer, Severgroup consolidated more than 78% of the retailer's shares.

However, in practice, this rule does not always allow to fully protect the interests of minority holders. In the simplest case, the buyer can deliberately increase its share only to the boundary values ​​of 29%, 49% or 74%.

Example 5. In February 2021, it became known that VTB would buy out most of the stake of Magnit founder Sergei Galitsky at a price of 4,661 rubles. per share (discount to the market about 3.9%). Galitsky's stake was about 32%, but VTB bought only 29%, and the remaining shareholders did not have the right to repurchase, despite the actual change of the strategic investor. If VTB crossed the 30% threshold, then shareholders would have the right to present their shares for redemption at a price no lower than the weighted average price for 6 months - about 6,675 rubles, or 37% higher than the market price at the time of publication.

The law also provides for some other restrictions on the application of this rule, which are worth remembering. The right to repurchase does not arise in cases where the threshold of 30%, 50% and 75% has been overcome by:

  • Acquisition of shares on the basis of a previously sent voluntary offer to acquire all shares of the company or a mandatory offer;
  • Purchasing shares from an affiliate;
  • Redemption of part of shares by a public company;
  • Acquisition of shares during the exercise of the pre-emptive right to buy out an additional issue;
  • In some cases, when we are talking about the property of state corporations, strategic enterprises, as well as federal and municipal property

More details about exceptions can be found in paragraph 8 of Art. 84.2 Federal Law “On Joint Stock Companies”.

Example 6. In February 2021, a bill was developed according to which the Government would buy a controlling stake in Sberbank from the Bank of Russia and place it on the balance sheet of the National Welfare Fund (NWF). Since the buyer and seller are essentially affiliates, no right of redemption arose. But as a friendly gesture, it was supposed to make an exception and make an offer to investors. Moreover, the offer should have been made not by the buyer, but by the seller. However, due to the coronavirus epidemic, Sberbank's stock quotes have fallen seriously, which has increased the likelihood that too many securities will be presented for repurchase. In March 2020, it was decided to replace the offer with a shareholders' agreement. 3. A shareholder (including all affiliates) who, during the last transaction, became the owner of more than 95% of the shares. In this case, the repurchase can be initiated both by minority shareholders and by the shareholder himself.

mandatory offer to repurchase to the remaining shareholders if the latter presented such a demand to him within 6 months from the date of receipt of notification of such right. The redemption price must not be lower than:

  • The prices at which such securities were purchased;
  • The highest price at which the purchaser (or its affiliates) purchased or committed to purchase those securities after the expiration of the voluntary or mandatory tender offer that resulted in the purchaser owning more than 95% of the company's shares.

A forced buyout can be carried out at the initiative of the buyer himself if he wants to consolidate 100% of the company in his hands. He has such a right if the 95% threshold has been overcome by purchasing at least 10% in the course of a voluntary or mandatory offer.

The demand for compulsory redemption must be sent to minority shareholders within 6 months from the expiration of the voluntary or mandatory offer, during which he became the owner of more than 95%.

The redemption price must not be lower than:

  • Market value determined by the appraiser;
  • The price at which the shares were acquired on the basis of a voluntary or mandatory offer, as a result of which the buyer became the owner of more than 95%;
  • The highest price at which the buyer (or its affiliates) purchased or committed to purchase these securities after the expiration of the voluntary or mandatory tender offer resulting in ownership of more than 95% of the company's shares.

In this case, payment for the repurchased securities can only be made in cash.

Example 7. In 2021, a forced repurchase of Uralkali shares took place. One of the companies, part of a group of persons that owned more than 95% of the company, sold in December 2021, and in June 2021 bought back a 10.18% stake from the Sberbank structure during a voluntary offer. The sale price was not disclosed, the purchase price was 89.3 rubles. per share. In August, a forced redemption was announced at a price of 120 rubles. per share (premium of about 20% to the market), which was determined by the appraiser.

Example 8. In April 2021, Megafon announced a forced buyout, after which the shares were delisted from the Moscow Exchange. The share price was set at RUB 659.26. per share, which corresponds to the price of the previous offer, during which 20.36% of shares were purchased on the balance sheet of the subsidiary Megafon-Finance.

BCS World of Investments

Calculation of WACC for CJSC companies

In one of the stages of calculating the weighted average price of capital, it is necessary to calculate the projected return on equity (Re), which is usually calculated using the CAPM model. For the correct application of this model, it is necessary to have ordinary shares traded on the market. Since CJSC companies do not have public issues of shares, it is impossible to assess the return on capital using a market method. Therefore, return on equity can be assessed on the basis of financial statements - the ROE (return on equity) ratio. This indicator reflects the rate of return created by the company's equity capital. As a result, Re = ROE

The WACC calculation formula will be modified.

Planwrld.ru

I put together a calculator for averaging stocks, calculating the average cost of orders (positions) and calculating possible profits. Using this calculator, you can add up all your orders to calculate the average price

  • Stock Averaging Calculator
  • How to use the calculator
  • An example of calculating the average price of an asset
  • An example of calculating possible profit on a transaction

Stock and Coin Cost Averaging Calculator

PriceQuantitySpentPriceProfitPercent

Rate for today

If you are interested in cryptocurrency, be sure to visit my Telegram channel. There I publish interesting scenarios of market behavior. Where and where is the best place to go, when to leave. Very useful, and most importantly free!

If you don’t understand how the calculator works or if you want some additional functionality, write in the comments. The calculator itself can be divided into two parts. The first calculates the average order value (the average price of a coin or share on the exchange). The second is an additional part of the calculator; it calculates profits in money and percentages.

Average order cost

Here we are interested in the first three columns. I have added space for 5 orders; it is not necessary to use them all at once.

Price - in this column we indicate the prices of coins or shares (any asset) at the time of their purchase on the exchange. And against each price we indicate their quantity.

Spent - the calculator calculates how much you spent by multiplying the number of coins by their value (at the time of purchase). Everything is calculated line by line, and the last one (“bold”) displays the total.

Calculation of profit by coins

This part of the calculator will work correctly as long as you have entered "Current Rate" in the field below the table.

Value - shows the current value of your coins.

Profit - shows the difference between cost and value for today. Either profit or loss.

Percentage is the same profit, only as a percentage.

An example of calculating the average price of an asset

Do you use portfolio services in your trading? I use Cryptocompare.com and suggest you consider the following situation there, perhaps you have already gone through something similar. Let's take XRP as an example, let's say we decided to take it at a supposed correction. The price has dropped to $0.61 and we buy 10,000 coins. Then the price drops to $0.6 and we decide to take another 8,000 coins. But the market does not calm down and our orders begin to bring us losses. After which we try to purchase again at a price of $0.55. On the graph it will look like this:

As a result, we have three processed orders, two of which are in drawdown, which is what the Cryptocompare.com service shows us.

Visually, of course, we see that the situation is generally positive and in terms of our overall cap we are in profit. For those who do not use portfolio services and only navigate orders on Binance, if there are a large number of them, the picture may not be clear. But how can we calculate the average price for all orders and understand whether we are in profit? This is where the calculator comes to our aid.

An example of calculating possible profit on a transaction

I'll add a little later... + I’ll make a video to make it clear, in which I’ll give more interesting examples. For example, how can you use this calculator in a completely different area.

If you are interested in cryptocurrency, be sure to visit my Telegram channel. There I publish interesting scenarios of market behavior. Where and where is the best place to go, when to leave. Very useful, and most importantly free!

Example No. 2. Calculation of WACC by balance in Excel

Let's look at an example of calculating WACC on an organization's balance sheet. This approach is used when a company does not issue ordinary shares on the stock market or they are low-volatile, which does not allow the return (efficiency) of the company’s capital to be assessed on the basis of a market approach.

We will conduct the assessment based on the balance sheet of KAMAZ OJSC. Despite the fact that this company has ordinary shares, their market volatility is too weak to adequately assess the return on equity using the CAPM model.

The organization's balance sheet can be downloaded from the official website or →. The first parameter of the formula is the cost of equity capital, which will be calculated as the organization’s return on equity. The calculation formula is as follows:

Net profit is reflected in line 2400 in the income statement, the amount of equity capital in line 1300 of the balance sheet. Entering data into Excel.

Cost of equity = B6/B7

At the next stage, it is necessary to calculate the cost of borrowed capital, which is a fee for the use of borrowed funds, in other words, the percentage that the organization pays for borrowed funds. Interest paid at the end of the reporting year is presented in line 2330 of the balance sheet, the amount of borrowed capital is the sum of long-term and short-term liabilities (line 1400 + lines 1500) in the income statement. The formula for calculating the cost of borrowed capital is as follows:

Cost of borrowed capital =B9/B10

At the next stage, we enter the values ​​of the tax percentage rate. The income tax rate is 20%. To calculate the shares of equity and borrowed capital, it is necessary to apply existing data and formulas:

Equity weight = B7/(B7+B10)

Debt capital weight = B10/(B7+B10)

WACC = B5*B12+(1-B11)*B13*B8

Calculation of the weighted average cost of capital on the balance sheet in Excel

Guidelines for disclosing information on earnings per share

Approved by order of the Ministry of Finance of the Russian Federation dated March 21, 2000 No. 29n.

I. General provisions

  1. The accounting regulations “Accounting statements of an organization” (PBU 4/99), approved by Order of the Ministry of Finance of the Russian Federation dated July 6, 1999 No. 43n (according to the conclusion of the Ministry of Justice of the Russian Federation dated August 6, 1999 No. 6417-PK, the specified document does not require state registration), provides for disclosure in the financial statements information about profit per share.

These Methodological Recommendations are used to generate information on profit per share of a joint stock company.

  1. A joint stock company discloses information about profit per share in two amounts: basic profit (loss) per share, which reflects the part of the profit (loss) of the reporting period attributable to shareholders - owners of ordinary shares, and profit (loss) per share, which reflects a possible decrease in the level of basic earnings (increase in loss) per share in the subsequent reporting period (hereinafter referred to as diluted earnings (loss) per share).

II. Basic earnings (loss) per share

  1. Basic earnings (loss) per share is determined as the ratio of basic earnings (loss) for the reporting period to the weighted average number of ordinary shares outstanding during the reporting period.
  2. The basic profit (loss) of the reporting period is determined by reducing (increasing) the profit (loss) of the reporting period remaining at the disposal of the organization after taxation and other obligatory payments to the budget and extra-budgetary funds by the amount of dividends on preferred shares accrued to their owners for the reporting period.

When calculating the basic profit (loss) of the reporting period, dividends on preferred shares, including cumulative ones, for previous reporting periods that were paid or declared during the reporting period are not taken into account.

  1. The weighted average number of ordinary shares outstanding during the reporting period is determined by summing the number of ordinary shares outstanding on the first day of each calendar month of the reporting period and dividing the resulting amount by the number of calendar months in the reporting period.

Ordinary shares are included in the calculation of their weighted average number from the moment the rights to ordinary shares arise from their first owners, except for the cases provided for in paragraph 7 of these Methodological Recommendations.

To calculate the weighted average number of ordinary shares in circulation, data from the register of shareholders of the company as of the first day of each calendar month of the reporting period is used.

Example 1.

In 2000, the following movement of ordinary shares took place in joint stock company “X”:

date Placement (number of additional shares paid for in cash) Redemption (acquisition) (number of shares repurchased (acquired) from shareholders) Common shares outstanding (number)
01.01. 1000
01.04. 800 1800
01.10. 400 1400
Total 12/31. 800 400 1400

Weighted average number of ordinary shares outstanding:

(1000 x 3 + 1800 x 6 + 1400 x 3) : 12 = 1500, or (1000 x 12 + 800 x 9 – 400 x 3) : 12 = 1500

  1. Data on the weighted average number of ordinary shares outstanding are adjusted in the following cases:
      a) placement by the joint-stock company of ordinary shares without payment, which does not affect the distribution of profits between shareholders, in accordance with paragraph 7 of these Methodological Recommendations;
  2. b) placement of additional ordinary shares at a price below market value in accordance with paragraph 8 of these Methodological Recommendations.

For the purposes of these Methodological Recommendations, the market value of securities is determined in accordance with the Federal Law of November 26, 1995 No. 208-FZ “On Joint Stock Companies” (Collected Legislation of the Russian Federation, 1996, No. 1, Art. 1).

  1. When a joint stock company places ordinary shares without payment by distributing them among the company's shareholders, each shareholder who owns ordinary shares is distributed an integer number of ordinary shares proportional to the number of ordinary shares owned by him. This type of placement includes splitting and consolidation of ordinary shares, including the issue of additional shares within the limits of the revaluation of fixed assets aimed at increasing the authorized capital.

For the purpose of ensuring comparability of the weighted average number of ordinary shares outstanding at the beginning and end of the reporting period, ordinary shares are considered issued at the beginning of the reporting period. In this case, the number of ordinary shares in circulation before the date of the specified placement, when calculating their weighted average number, increases (decreases) in the same proportion in which they were increased (decreased) as a result of the specified placement.

Example 2.

In 2000, joint stock company “X” placed additional ordinary shares by distributing them among shareholders at the rate of one additional share per one share outstanding.

1999 2000 Common shares outstanding (number)
01.01.2000 1400
06/01/2000 Additional issue of shares without payment 1400 2800
Weighted average number of ordinary shares outstanding 1500
Weighted average number of ordinary shares outstanding, adjusted 3000 2800
  1. When placing ordinary shares at a price below their market value in cases provided for by the Federal Law “On Joint Stock Companies”, for the purposes of calculating basic profit (loss) per share, all ordinary shares outstanding before the specified placement are assumed to have been paid at a price below market value with a corresponding increase in their number.

The number of ordinary shares outstanding prior to the said offering is adjusted depending on the ratio of the market value on the date of completion of the said offering and the average estimated value of the ordinary shares outstanding.

RS ——— SRS

Where

RS is the market value of an ordinary share as of the end date of the placement;

CPC is the average estimated cost of an ordinary share on the next date after the end of the placement.

The average estimated value of common shares outstanding is determined by dividing the total value of common shares outstanding on the date following the closing date of the offering by their number.

In this case, the total value of ordinary shares consists of:

    • the market value of ordinary shares outstanding prior to the offering;
  1. funds received from the placement of ordinary shares at a price below market value.

SRS = (D1 + D2) : KA

Where

D1 - the market value of ordinary shares outstanding before the placement, which is determined as the product of the market value of an ordinary share on the end date of the placement (RS) by the number of ordinary shares outstanding before the start of the specified placement;

D2 - funds received from the placement of ordinary shares at a price below market value;

KA - the number of ordinary shares outstanding on the date following the end of the placement.

Example 3.

In 2000, joint-stock company "X" carried out a placement of ordinary shares with a preemptive right to purchase by shareholders at a price below their market value at the rate of one additional share for every 4 shares outstanding. The right must be exercised no later than 06/01/2000 at a price of 9 rubles. per share. The market value as of the end date of placement is 10 rubles. per share.

1999 2000 Common shares outstanding (number)
01.01.2000 2800
06/01/2000 Additional issue of shares 700 3500
Weighted average number of ordinary shares outstanding 2800
Average Estimated Cost (RS) (SRS) (10 x 2800 + 9 x 700): 3500 = 9.8 rub.
Correction factor (RS/SRS) 10 : 9,8 = 1,02 10 : 9,8 = 1,02
Weighted average number of ordinary shares outstanding, adjusted 2800 x 1.02 = 2856 (2800 x 1.02 x 5 + 3500 x 7) : 12 = 3232

III. Diluted earnings (loss) per share

  1. The amount of diluted earnings (loss) per share shows the maximum possible degree of decrease in profit (increase in loss) per one ordinary share of a joint-stock company in the following cases:
      conversion of all convertible securities of a joint stock company into ordinary shares (hereinafter referred to as convertible securities);
  2. upon execution of all contracts for the purchase and sale of ordinary shares from the issuer at a price below their market value.

Convertible securities include preferred shares of certain types or other securities that give their owners the right to demand their conversion into ordinary shares within the period established by the terms of issue.

Profit dilution means its decrease (increase in loss) per one ordinary share as a result of a possible future issue of additional ordinary shares without a corresponding increase in the company’s assets, except for the cases provided for in paragraph 7 of these Methodological Recommendations.

  1. When determining diluted earnings (loss) per share, the values ​​of basic earnings and the weighted average number of ordinary shares outstanding, used in the reporting period when calculating basic earnings per share, are adjusted by the corresponding amounts of the possible increase in these values ​​in connection with the conversion of all convertible shares into ordinary shares. securities of the joint-stock company and execution of contracts specified in paragraph 9 of these Methodological Recommendations.

The adjustment is made by increasing the numerator and denominator used in calculating basic earnings per share by the amounts of the possible increase, respectively, in basic earnings and the weighted average number of ordinary shares in circulation in the event of conversion of securities and execution of contracts specified in paragraph 9 of these Methodological Recommendations.

The possible increase in profit and the possible increase in the weighted average number of ordinary shares in circulation are calculated:

    • for each type and issue of convertible securities;
  1. for each agreement specified in paragraph 9 of these Methodological Recommendations, or several agreements, if they provide for the same conditions for the placement of ordinary shares.
  2. When determining a possible increase in profit, all expenses (income) related to the above-mentioned convertible securities and agreements are taken into account, which the joint-stock company will cease to carry out (receive) in the event of conversion of all convertible securities into ordinary shares and execution of the agreements specified in paragraph 9 of these Methodological recommendations.

Expenses related to convertible securities may include: dividends due on preferred shares, which, in accordance with the terms of their issue, can be converted into ordinary shares; interest paid on its own convertible bonds; the amount of write-off of the difference between the placement price of convertible securities and the par value, if they were placed at a price below the par value; other similar expenses.

Income attributable to convertible securities may include:

    • the amount of write-off of the difference between the placement price of convertible securities and the par value, if they were placed at a price higher than the par value;
  1. other similar income.

When calculating the possible increase in profit for the purpose of determining diluted profit (loss), the amount of the above expenses is reduced by the amount of the above income.

  1. When determining the possible increase in the weighted average number of ordinary shares outstanding in cases of conversion of securities, all additional ordinary shares that will be placed as a result of such conversion are taken into account.

In case of execution of the contracts specified in paragraph 9 of these Methodological Recommendations, additional ordinary shares are placed at a price below their market value. Accordingly, for purposes of calculating diluted earnings (loss), it is assumed that a portion of the common shares issued under such an agreement will be paid at market value and the remainder will be issued without payment. Thus, when calculating the possible increase in the weighted average number of ordinary shares outstanding, only those that will be placed without payment are taken into account.

A possible increase in the number of ordinary shares in circulation without a corresponding increase in the company's assets is determined as follows:

(RS* – CR) x KA* ———————— RS*

Where

РС* is the market value of one ordinary share, determined as the weighted average market value during the reporting period;

CR - the placement price of one ordinary share in accordance with the conditions specified in the agreement;

KA* is the total number of ordinary shares under the acquisition agreement.

A possible increase in the number of ordinary shares is taken into account in calculating the weighted average number of ordinary shares in circulation:

    • from the beginning of the reporting period;
  1. from the date of issue of convertible securities or conclusion of an acquisition agreement, if these events occurred during the reporting period.

If during the reporting period there is a termination of the contracts specified in paragraph 9 of these Methodological Recommendations, or the cancellation of convertible securities, as well as their conversion into ordinary shares, the possible increase in the weighted average number of ordinary shares in circulation is calculated for the period during which the convertible securities (the agreements specified in paragraph 9 of these Methodological Recommendations) were in circulation (in force).

If the agreement specified in paragraph 9 of these Methodological Recommendations is executed during the reporting period, the calculation of the possible increase in the weighted average number of ordinary shares in circulation is made for the period from the beginning of the reporting period (from the date of conclusion of the specified agreement) to the date of placement of securities, that is, the emergence of rights for ordinary shares from their first owners.

  1. Based on the data calculated in accordance with paragraphs 11 and 12 of these Methodological Recommendations, the ratio of the possible increase in profit to the possible increase in the weighted average number of ordinary shares in circulation for each type and issue of convertible securities and contracts specified in paragraph 9 of these Methodological Recommendations is determined.

The obtained values ​​should be arranged in ascending order: from the smallest value to the largest.

Basic earnings (numerator), in accordance with the specified sequence, increases by the amount of the possible increase in earnings, and the weighted average number of ordinary shares outstanding (denominator) increases by the amount of the possible increase in the weighted average number of ordinary shares outstanding.

  1. Diluted earnings (loss) per share is the ratio of basic earnings (loss), adjusted for the amount of its possible increase, to the weighted average number of ordinary shares outstanding, adjusted for the amount of possible increase in their number as a result of the conversion of securities into ordinary shares and exercise contracts specified in paragraph 9 of these Methodological Recommendations.

To calculate the diluted earnings per share indicator, those convertible securities (contracts specified in paragraph 9 of these Guidelines) are selected, the conversion of which into ordinary shares (execution) leads to a decrease in basic earnings (increase in loss) per share. For these purposes, it is necessary to analyze the values ​​obtained as a result of calculations made in accordance with paragraph 13 of these Guidelines. If any of these values ​​is greater than the previous one, i.e. leads to an increase in earnings per share of common stock outstanding, the relevant type (issue) of convertible securities or agreement has an anti-dilutive effect and is not included in the calculation of diluted earnings per share. If the obtained values ​​are arranged in descending order, this means that all convertible securities and contracts available to the company, specified in paragraph 9 of these Methodological Recommendations, have a dilutive effect.

  1. For the purposes of calculating diluted earnings per share, the numerator and denominator values ​​in the calculation of basic earnings per share are increased by the corresponding amounts of the possible increase in earnings and the weighted average number of ordinary shares outstanding and securities and execution of contracts specified in paragraph 9 of these Guidelines that have a dilutive effect. The result obtained is an indicator of the maximum possible degree of dilution of earnings per share and is reflected in the financial statements of the joint-stock company.

Example 4.

Calculation of diluted earnings per share

Net profit of joint stock company "X" for 2000, reduced by the amount of dividends on preferred shares 64640 rub.
Weighted average number of common shares outstanding during 2000 3232 pcs.
Basic earnings per share 64640 : 3232 = 20 rub.
Weighted average market value of one ordinary share 10 rub.
The joint stock company placed before the reporting period: convertible preferred shares with dividends in the amount of 4 rubles. per share, each of which is convertible into 2 ordinary shares 1000 pcs.
20% bonds convertible into common shares, par value 500, each convertible into 5 common shares 1000 pcs.
The joint stock company entered into an agreement giving the right to purchase ordinary shares from the joint stock company at a price of 9 rubles. 100 pieces.

Calculation of the possible increase in profit and the possible increase in the weighted average number of shares in circulation

I. Convertible preferred shares
Possible profit increase 4 x 1000 = 4000 rub.
Additional number of shares 2 x 1000 = 2000 pcs.
Possible increase in earnings per additional share 4000 : 2000 = 2
II. Convertible bonds
Possible increase in profit due to savings on interest paid on bonds 500,000 x 0.2 = 100,000 rub.
Possible increase in income tax expenses if the amount of interest paid reduces the tax base (rate 30%) 100,000 x 0.3 = 30,000 rub.
Possible increase in profit minus possible increase in income tax amounts 100,000 - 30,000 = 70,000 rub.
Additional number of shares 5 x 1000 = 5000
Possible increase in earnings per additional share 70000 : 5000 = 14
III. When executing the contract
Possible profit increase 0
Additional shares without a corresponding increase in assets (10 - 9) x 100: 10 = 10
Possible increase in earnings per additional share 0

Calculation of diluted earnings per share

Name Numerator Denominator Earnings per share
Execution of the contract 64640 + 0 = 64640 3232 + 10 = 3242 64640 : 3242 = 19.94 has a dilutive effect
Convertible preferred shares 64640 + 4000 = 68640 3242 + 2000 = 5242 68640 : 5242 = 13.09 has a dilutive effect
Convertible bonds 68640 + 70000 = 138640 5242 + 5000 = 10242 138640 : 10242 = 13.54 has an anti-dilution effect
Diluted earnings per share 13,09

IV. Disclosure of information in financial statements

  1. The financial statements of a joint stock company reflect:
      a) basic profit (loss) per share, as well as the amount of basic profit (loss) and the weighted average number of ordinary shares in circulation, used in its calculation;
  2. b) diluted earnings (loss) per share, as well as the amounts of adjusted basic earnings (loss) and the weighted average number of ordinary shares outstanding used in its calculation.

Basic and diluted earnings (loss) per share must be presented for the reporting year, as well as for at least one previous reporting year, unless the relevant information is presented by the joint-stock company for the first time.

If a joint stock company does not have convertible securities or contracts specified in paragraph 9 of these Methodological Recommendations, then only basic profit (loss) per share is reflected in the financial statements with mandatory disclosure of relevant information in the explanatory note.

  1. In order to present comparable information, the amounts of basic and diluted earnings (loss) per share for the previous reporting periods presented in the financial statements are subject to adjustment in the following cases:
      a) changes in accounting policies;
  2. b) placement of ordinary shares on the terms provided for in paragraphs 7 and 8 of these Methodological Recommendations.

In the case provided for in subparagraph b, data on the weighted average number of ordinary shares outstanding for previous reporting periods are adjusted as if these events had occurred at the beginning of the earliest period presented in the financial statements. The explanatory note discloses the following information:

    • what event resulted in the issuance of additional ordinary shares;
  1. date of issue of additional ordinary shares;
  2. the main terms and conditions of the issue of additional ordinary shares;
  3. number of additional ordinary shares issued;
  4. the amount of funds received from the placement of additional shares (when shareholders exercise their rights to acquire additional ordinary shares with their partial payment).

If after the reporting date, but before the date of signing the financial statements, ordinary shares are placed under the conditions provided for in paragraphs 7 and 8 of these Methodological Recommendations, then the amounts of basic and diluted earnings (loss) per share for the reporting and previous reporting periods presented in the financial statements are also subject to appropriate adjustment.

Information about events that occurred after the reporting date must be disclosed in the explanatory note.

  1. If, after the reporting date, transactions took place with ordinary shares, convertible securities and contracts specified in paragraph 9 of these Methodological Recommendations, which are significant for users of financial statements, information about these transactions is disclosed in the explanatory note. Such transactions include:
      significant issues of ordinary shares;
  2. significant transactions involving the repurchase of ordinary shares by the company;
  3. conclusion by the company of an agreement by virtue of which the company, if certain conditions are met, is obliged to issue additional ordinary shares;
  4. fulfillment of the conditions necessary for a significant placement of ordinary shares (if such placement was conditional on their fulfillment);
  5. other similar operations.
  6. Any information that is material for users of financial statements regarding basic and diluted earnings (loss) per share is subject to additional disclosure.
Rating
( 2 ratings, average 4.5 out of 5 )
Did you like the article? Share with friends:
For any suggestions regarding the site: [email protected]
Для любых предложений по сайту: [email protected]