The 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 a company generates with its shareholders’ equity. ROE may be used to compare the profitability of one company to 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 the calculation for ROE:
- In Excel, get started by right-clicking on column A. Next, move the cursor down and left-click on column width. Then, change the column width value to 30 default units and click OK. Repeat this procedure for columns B and C.
- Next, enter the name of a company in cell B1 and the name of another company into cell C1.
- Then, enter “Net Income” into cell A2, “Shareholders’ Equity” into cell A3, and “Return on Equity” into cell A4.
- Put the formula for “Return on Equity” =B2/B3 into cell B4 and enter the formula =C2/C3 into cell C4.
- Once that is completed, enter the corresponding values for “Net Income” and “Shareholders’ Equity” in cells B2, B3, C2, and C3.
Suppose that Facebook (FB) had a net income of $15.920 billion and shareholders’ equity of $74.347 billion as of Dec. 31, 2017. Its competitor, Twitter (TWTR), had a net income of -$108.063 million and shareholders’ equity of $5.047 billion.
Let’s set up the calculation for this example in Excel:
- Enter =15920000000 into cell B2 and =74347000000 into cell B3.
- The resulting return on equity of Facebook is 21.41%, according to the formula in B4, =B2/B3.
- Then, enter =-108063000 into cell C2 and =5047218000 into cell C3 for Twitter.
- The resulting ROE of Twitter is -2.14%, according to the formula in C4, =C2/C3.
Twitter is thus less profitable and operating at a loss, while Facebook is highly profitable.
Time Saving Tips for Advanced Users
There are some ways to save time when using the ROE formula in Excel repeatedly.
- Left-click on column A. Then, press and hold the shift key while left-clicking on column C. Columns A, B, and C should now be selected simultaneously. When right-clicking on the selected area, it should be possible to adjust the width for all the columns at the same time.
- It is also possible to calculate the 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 of =B2/B3 in cell B4 can be copied by pressing Ctrl+C and then pasted into C4, D4, and other cells with Ctrl+V. The values in the formula will automatically adjust to C2/C3, D2/D3, or other appropriate cells.
- The names of companies and values for “Net Income” and “Shareholders’ Equity” can be changed without reentering the formulas.
- It is also possible to create a template. Simply save a spreadsheet called “ROE template” with blank values for Net Income, Shareholders’ Equity, and the names of companies.