Return on equity, or ROE, is used in fundamental analysis to measure a company’s profitability. The ROE formula shows the amount of net income generated by a company with its shareholders’ equity. ROE can be used to compare the profitability of one company to that of another firm in the same industry.
Calculating ROE in Excel
The formula to calculate a company’s ROE is its net income divided by shareholders’ equity. Here’s how to use Microsoft Excel to set up calculations for ROE:
- In Excel, start by right-clicking on column A. Next, move the cursor down and left-click on Column Width. Then, change the Column Width value to the default 30 units and click OK. Repeat this process for columns B and C.
- Next, enter a company name in cell B1 and another company name in cell C1.
- Then, enter “Net Income” in cell A2, “Shareholders’ Equity” in cell A3, and “Return on Equity” in cell A4.
- Place the formula for “Return on Equity” =B2/B3 in cell B4 and enter the formula =C2/C3 in cell C4.
- Once this is done, enter the corresponding values for “Net Income” and “Shareholders’ Equity” in cells B2, B3, C2, and C3.
Assume that Facebook (FB) had net income of $15.920 billion and shareholders’ equity of $74.347 billion as of December 31, 2017. Its competitor, Twitter (TWTR), had net income of -$108.063 million and shareholders’ equity of $5.047. Arab.
Let’s set up the calculation for this example in Excel:
- Enter =15920000000 in cell B2 and =74347000000 in cell B3.
- According to the formula in B4, =B2/B3, Facebook’s resulting return on equity is 21.41%.
- Then, enter =-108063000 in cell C2 and =5047218000 in cell C3 for Twitter.
- According to the formula in C4, =C2/C3, Twitter’s resulting ROE is -2.14%.
Thus Twitter is less profitable and making losses, while Facebook is highly profitable.
Time Saving Tips for Advanced Users
There are a few ways to save time when repeatedly using the ROE formula in Excel.
- Left-click on column A. Then, hold down the Shift key while left-clicking on column C. Columns A, B and C should now be selected together. When right-clicking on the selected area, it should be possible to adjust the width of all columns at once.
- It is also possible to calculate ROE for more than two firms by selecting more columns. D will work for three firms, E for four, F for five, and so on.
- The formula for =B2/B3 in cell B4 can be copied by pressing Ctrl+C and then pasted with Ctrl+V into cells C4, D4, and other cells. The values in the formula will be automatically adjusted to C2/C3, D2/D3, or other appropriate cells.
- The names of companies and the values of “net income” and “shareholders’ equity” can be changed without re-entering the formulas.
- It is also possible to create templates. Simply save a spreadsheet called “ROE Template” with blank values for net income, shareholders’ equity, and companies’ names.