How do you use the ROI formula on Excel? (2024)

Staying on top of your company’s finances is absolutely critical. Regardless of industry or what sort of goods or services you’re selling, you need to know how much money is going into and out of the business because that’s going to tell you how worthwhile everything you’re doing is.

This is often called “working out your ROI” (Return on Investment), and it can be done using a fairly simple calculation. In this guide, we’ll explain the importance of ROI, how to use it in Excel, and most importantly how to supercharge those calculations and avoid wasted manual time with monday.com’s ROI formula template.

Get the template

What is an ROI formula in Excel?

Before we talk ROI formulas, let’s pump the brakes and cover what ROI is.

In its most basic form, ROI is a direct measure of return showing how much money you’ve made on a particular investment in relation to the cost of investment over a time period.

Return on investment (ROI) is a key business performance metric that companies and individuals use to calculate how efficient or profitable their investment is. It can also be used to compare different types of investments to figure out your net profit for each investment option.

Download Excel template

You can also compare your ROI over multiple time periods to calculate the growth rate at which your returns may have changed. To calculate your team’s ROI, you’d use a basic ROI formula. That calculation is just the return — or net income — your investment has generated divided by the cost of that investment. The resulting ratio or percentage will tell you how profitable that investment, project, or product really is.

It’s important to note that ROI doesn’t generally consider time value of money (TVM), which is the popular accounting theory that an amount of money you get today will be worth more than receiving the exact same amount at some point in the future. ROI treats every dollar the same — no matter when your business has earned it.

In Microsoft Office, you can use a built-in Excel calculator to figure out your ROI. All you have to do is complete a spreadsheet with the numbers you’d like to compare. The formula to calculate your true ROI is pretty simple. You just divide your returns in one cell by your cost in another cell.

(Image Source)

Why use an ROI formula in Excel?

ROI is one of the most fundamental business metrics out there. Why? Because it’s simple and super straightforward. You don’t have to be a professional financial analyst to work out your team’s ROI on a project, and it’s a decent gauge of an investment’s profitability that you can easily communicate to other stakeholders. More importantly, ROI can tell you a net gain or loss.

If your ROI is positive, it means your investment was probably worthwhile. If your ROI is negative, it means there’s likely some room for improvement. From there, your team can brainstorm what efficiencies could be made or where costs could be shaved.

A potential issue with calculating ROI is that it doesn’t totally eradicate all risk or uncertainty. But to make sure you’re getting all your calculations spot-on, you’re going to want to remove as much human error as possible.

That’s why it makes sense to use an ROI formula in Excel. Excel’s calculator doesn’t lie, and the formulas you enter automatically turn your data into ratios or percentages that will better inform your business choices.

What are some examples of ROI formulas in Excel?

Calculating ROI in Excel is simple. Deciding which ROI calculator to use is a little bit more difficult. Excel’s ROI formula calculator is essentially a system of formulas you can use to figure out a range of ROI calculations — from return on equity (ROE) and internal rate of return (IRR), to annualized ROI and return on assets.

To help give you an idea, let’s quickly break down a few ways you can work out your ROI using Microsoft Excel:

Net income

Using Excel’s net income formula, your ROI is going to be your net income divided by the cost of investment.

(Image Source)

This is one of the simplest and most useful ways to measure ROI in Excel.

Capital gain

This shows your ROI as a financial gain or loss generated by your investment. You can then use this percentage to compare against your original investment price — which means the formula is simply your capital gain divided by your cost of investment.

Total return

By calculating your total return in Excel, you’re looking at a percentage gain or loss just like your capital gain formula. But the key difference between total return and capital gain is that your total return formula looks at the original value versus your current value.

Written as a formula, that would be: ROI = (Ending value – Starting value) / Cost of investment.

Annualized return

The annualized return formula calculates your ROI as the average gain or loss you’ve made in a year on your initial investment.

This is displayed as a percentage, and the calculation would be: ROI = (Ending value / Starting value) ^ (1 / Number of years) -1. To figure out the number of years, you’d subtract your starting date from your ending date, then divide by 365.

(Image Source)

monday.com’s ROI formula template for Excel alternative

If you’re looking for the best ROI formula template Excel but are less than satisfied monday.com has got you covered. Our downloadable ROI formula template comes with loads of pre-built ROI formulas to suit just about every scenario. Every column is customizable, which means all you need to do is drag and drop your data and choose a formula to quickly get an accurate ROI calculation.

Our templates are supported by our all-in-one platform to automate calculations for you and even. Combine all of your financials into one flexible dashboard. This empowers you with a real-time view of key ROI metrics like net income, capital gains, total returns, and annual returns.

But more importantly, you benefit from automations that can keep a running tally of these ROI metrics as figures change and new project expenses are added. End result: you’ve always got a clear view of your ROI — without wasting hundreds of hours plotting in numbers and recalculating the formula every single time there’s a tiny change.

Sounding good so far? Try our financial statement template and start building your ROI formula space today.

Get the template

Related templates

Expense Tracking Template

Want to get more proactive about your finances? Check out our Expense Tracking Template. This dynamic template offers teams a flexible bird’s eye view of every aspect of their expenses and outgoings. You can also use it to forecast where your current financial path is going to take you in the future.

Budget Tracking Template

monday.com’s Budget Tracker Template offers you the chance to get a crystal-clear picture of how much your team is spending and then look at how that spending compares against what you’d originally budgeted. This template is 100% customizable, which means you can reorder line items, tag expenses, and create your own unique spending plan to keep things on track.

Get the template

FAQs about using ROI formulas on Excel

How do I calculate ROI in Excel?

Calculating ROI in Excel is pretty straightforward. All you’ve got to do is open a spreadsheet and then label the cells so you can keep track of the cost and return data you’re going to enter.

From there, just enter your data and select a blank column to enter in one of the Excel ROI formulas. If you’ve got your total returns and total cost in their own respective cells, it could be as easy as simply inputting “=A1/B1” to work out your ROI. Once you’ve got your result, you can just click the “%” icon.

This will change your ratio into an easy-to-understand percentage.

What is a good ROI percentage?

If you own or manage a business, you’ll want to take a look at your ROI pretty regularly. It’s a great way to understand how your business is going and where you might need to start improving things.

But in terms of what constitutes a good ROI, there isn’t a definitive answer. Each aspect of your business is different, and each will generate different percentages of ROI that might not be totally comparable. The best way to work out comparable figures is to tally up your ROI for a particular area regularly so that you can build a base of historical data to compare against.

If you’re talking stock investments, a good ROI is a bit more straightforward. Stock investments are where the investor buys the shares of a company, hoping that the company grows. It takes proper fundamental research of the company before purchasing the shares. Financial analysts say anywhere between 7% and 10% is a good ROI.