Return on Equity Formula in Excel

Return on equity reflects the ratio of net profit from sales to the average amount of own funds.

The data for the calculation are taken from the balance sheet. The profitability ratio is denoted by "ROE".

The economic meaning of the financial indicator "ROE"

The profitability ratio shows how effectively the invested money was used in the reporting period. It is clear that this indicator is extremely important for investors and business owners.

There are several rates of return. We will be interested in return on equity. That is, those assets that are owned by the company.

How to evaluate the result of calculations:

1. The higher the ratio, the more efficiently the invested funds were used. Investments are more profitable.
2. Too high an indicator - the financial stability of the organization "suffers".
3. The coefficient is below zero - the feasibility of investing in this enterprise is doubtful.

The return on equity ratio is compared with other options for investing free money in assets and securities of other firms. Or with bank interest on deposits, in extreme cases.

The value of return on equity.

Formula for calculating ROE in Excel

The rate of return on equity is calculated as a ratio of net profit to the average size of own capital investments. The data is taken for a certain time interval: month, quarter, year.

The formula for calculating the return on equity ratio:

ROE = (Net Profit / Average Equity) * 100%.

Figures for calculations should be taken from the statement of financial results (final figure) and the balance sheet liability (final figure).

Average equity capital - calculation formula:

SC \u003d (SC of the beginning of the period + SC of the end of the period) / 2.

Return on equity - balance sheet formula:

ROE = (p. 2110 + p. 2320 + p. 2310 + p. 2340) / ((p. 1300 ng + p. 1300 kg + p. 1530 ng + p. 1530 kg) / 2) * 100%.

In the numerator - data from the statement of financial results (form 2). In the denominator - from the final balance (form 1).

To calculate the profitability using Excel, we will enter the data for the financial statements of company "X":

And the income statement ("in the old way": profit and loss):

The tables highlight the values ​​that will be needed to calculate the return on equity ratio.

• Profitability ratio for 2015: = (6695 / 75000) * 100% = 8.9%.
• Profitability ratio for 2014: = (2990 / 65000) * 100% = 4.6%.

We automate the calculation using Excel formulas. In general, you can make a separate table with important economic indicators. Enter formulas with links to values ​​in relevant reports - and quickly get data for statistical analysis, comparison and management decision-making.

Excel formulas for calculating return on equity:

Conclusions:

1. There is an increase in the return on equity from 4.6 percent to 8.9 percent.
2. It is not profitable to invest free funds in the shares of company X. The same bank rate on deposits in 2015 was 9.5%.
3. It is advisable to consider other offers from enterprises or put money on a deposit at interest (as a last resort).

The investment attractiveness of the project is not evaluated only by the return on investment. When making a decision, the investor looks at the return on assets, sales and other criteria for the efficiency of the enterprise.