Are you doing manual calculations on paper or even in Google Sheets itself to calculate the ROI in Google Sheets?
If so, this needs to stop.
In this tutorial, I am going to show you can calculate the return on investment in Google Sheets using 3 different methods, namely the Capital Gain Method, the Net Income Method, and the Total Return Method.
How to Calculate the ROI in Google Sheets
To calculate the ROI in Google Sheets:
- Identify your revenue and total costs for a particular day or sale.
- Input your data in Google Sheets with the revenue in ‘Cell1’ and the total costs in ‘Cell2’.
- Select a cell where you want the ROI to be. (eg. ‘Cell1’ and ‘Cell2’)
- Type in this formula without the quotes: “=TO_PERCENT((Cell1-Cell2)/Cell2)”
Calculate the Return On Investment (ROI) in Google Sheets Video
3 Different Methods of Calculating ROI in Google Sheets
The Return on Investment (or ROI) is determined by deducting the primary spending for the investment from its final value, then dividing the result by the cost of the investment, and finally, multiplying it by 100 to get a percentage result.
As powerful as Google Sheets can be, it still depends on how you utilize it to its fullest.
The definition of the formula for calculating ROI in Google Sheets is extremely simple but when used properly, it can solve your most complex problems.
Below are some sample methods that you can take advantage of to calculate ROI in Google Sheets depending on how your investment is set up.
1. Capital Gain Method
The Capital Gain Method is a return that you receive from an increase in the value of a capital asset (stocks, investment, or real estate). It is the measure of the investment gain for the investor, compared to the expense when an asset was acquired.
It is computed by defining your profits by deducting your expenses from your revenue, dividing the result by the expenses, and multiplying everything by 100 to get a percentage numerical value.
Alternatively, we can also use the TO_PERCENT function to automatically convert a number to a percentage value.
=TO_PERCENT((Revenue-Cost)/Cost)
In the example, we can see that the computation went as follows:
=TO_PERCENT((Current Share Price – Original Share Price) / Original Share Price)
The profit was calculated by subtracting the original share price from the current share price, which can also be referred to as the ‘Net Income’.
In the next sample dataset, we can see how practical it is to calculate the ROI in Google Sheets in situations where you have multiple computations.
By simply re-aligning your data to a transpose position and adjusting your formula, you can just simply copy your formula to the rest of your dataset and you’ll be able to complete your tasks easily.
2. Net Income Method
Return on investment (ROI) is a ratio utilized to financially compute the gains an investor will obtain relative to their investment cost. As with the Net Income Method, it is popularly measured by simply dividing the net income by the original capital cost of the investment. The greater the ratio, the higher the gains acquired.
This method is applicable to simpler situations particularly when you already have a value for the actual Net Income.
The formula is as follows:
=TO_PERCENT(Net Income/Original Investment Value)
This is fairly straightforward and is the fastest method to calculate the ROI in Google Sheets.
The strength of this method is fully applied with array datasets as you’ll simply have to copy the formula and you’ll be able to obtain the ROI in Google Sheets for all your entries.
Even though this can be the simplest in terms of getting the ROI in Google Sheets, you can make this more presentable visually by using Sparklines and other media.
3. Total Return Method
The last method is an opportunity to modify the Capital Gain Method. In the Total Return Method, you can calculate the ROI in Google Sheets even if you have multiple variables for either the revenue or the total cost.
The standard formula for this is:
=TO_PERCENT((Revenue-Cost)/Cost)
That said, if you have multiple revenue sources as well as different expenses such as production, marketing, storage, and more – you can modify the Capital Gain Method to include everything.
=TO_PERCENT(((Revenue1+Revenue2+…)-(Cost1+Cost2+…))/(Cost1+Cost2+…))
As an example, the dataset below has two values for revenue which are the share price increase (original share price-current share price) and the total dividends received. Dividing all of that with the original share price which is the sole cost for this dataset, we get to the ROI of 31.60%.
Similar to the previous methods, this is best applied to array datasets. For more variables, just add more columns and adjust the formula to reflect your additional values.
Keep in mind that these values are reflected in percentages and will not show the actual amount of profit. The formula for profit is “REVENUE – COST”.
Frequently Asked Questions about How to Calculate the ROI in Google Sheets
How can I convert the number for ROI in Google Sheets to a percentage?
You can convert a number to a percentage in Google Sheets by using the TO_PERCENT function or by selecting the number, cell, or range that you wish to update, go to ‘Format’, ‘Number’, and select ‘Percent’.
Is there a function to calculate the ROI in Google Sheets?
There is no straight function to calculate the ROI in Google Sheets at this time but it’s easily done by using the basic formula “=TO_PERCENT(((Revenue1+Revenue2+…)-(Cost1+Cost2+…))/(Cost1+Cost2+…))”.
Conclusion on How to Calculate the ROI in Google Sheets
To compute the ROI in Google Sheets, identify your ‘revenue’ and ‘total costs’. Afterward, input your data in Google Sheets with the ‘revenue’ in ‘Cell1’ and the ‘total costs’ in ‘Cell2’. Then select a cell where you want the ROI to be and type in this formula without the quotes: “=TO_PERCENT((Cell1-Cell2)/Cell2)”.
