How do I calculate rate of return in Excel?

Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 for Mac Excel 2016 Excel 2016 for Mac Excel 2013 Excel 2010 Excel 2007 Excel for Mac 2011 Excel Starter 2010 More...Less

This article describes the formula syntax and usage of the IRR function in Microsoft Excel.

Description

Returns the internal rate of return for a series of cash flows represented by the numbers in values. These cash flows do not have to be even, as they would be for an annuity. However, the cash flows must occur at regular intervals, such as monthly or annually. The internal rate of return is the interest rate received for an investment consisting of payments (negative values) and income (positive values) that occur at regular periods.

Syntax

IRR(values, [guess])

The IRR function syntax has the following arguments:

Values Required. An array or a reference to cells that contain numbers for which you want to calculate the internal rate of return.
- Values must contain at least one positive value and one negative value to calculate the internal rate of return.
- IRR uses the order of values to interpret the order of cash flows. Be sure to enter your payment and income values in the sequence you want.
- If an array or reference argument contains text, logical values, or empty cells, those values are ignored.
Guess Optional. A number that you guess is close to the result of IRR.
- Microsoft Excel uses an iterative technique for calculating IRR. Starting with guess, IRR cycles through the calculation until the result is accurate within 0.00001 percent. If IRR can't find a result that works after 20 tries, the #NUM! error value is returned.
- In most cases you do not need to provide guess for the IRR calculation. If guess is omitted, it is assumed to be 0.1 (10 percent).
- If IRR gives the #NUM! error value, or if the result is not close to what you expected, try again with a different value for guess.

Remarks

IRR is closely related to NPV, the net present value function. The rate of return calculated by IRR is the interest rate corresponding to a 0 (zero) net present value. The following formula demonstrates how NPV and IRR are related:

NPV(IRR(A2:A7),A2:A7) equals 1.79E-09 [Within the accuracy of the IRR calculation, the value is effectively 0 (zero).]

Example

Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.

Data	Description
-$70,000	Initial cost of a business
$12,000	Net income for the first year
$15,000	Net income for the second year
$18,000	Net income for the third year
$21,000	Net income for the fourth year
$26,000	Net income for the fifth year
Formula	Description	Result
=IRR(A2:A6)	Investment's internal rate of return after four years	-2.1%
=IRR(A2:A7)	Internal rate of return after five years	8.7%
=IRR(A2:A4,-10%)	To calculate the internal rate of return after two years, you need to include a guess (in this example, -10%).	-44.4%

Need more help?

Microsoft tools like Excel make it easy to record, track, and manipulate financial data, such as benefits for any investments or assets. You can calculate your return on investment with Excel accurately without having to know complex math. You can also use Excel ROI formulas to review the success of your content marketing campaigns. In this article, we cover:

What Is Return On Investment?
Why Calculate ROI?
Formulas for Calculating Return on Investment with Excel
How To Calculate Net Income Return on Investment with Excel
Tips for Calculating Return on Investment with Microsoft Excel
ROI Calculator Excel Template
ROI Calculator Excel Example

What Is Return on Investment?

Image via Unsplash by @markuswinkler

Return on investment (ROI) is a financial ratio that calculates the benefits an investor gets compared to the cost spent on a project. Most often, you use it to measure the net income for that investment. The higher the ROI ratio, the more profitable the project. In marketing, you may use this calculation to understand if your campaigns are making money, or if they’re successful enough to extend beyond their original deadlines. ROI is just one of many types of marketing formulas you can use to understand the engagement health of your business.

Why Calculate Return On Investment?

Calculating ROI can help a business or investor decide whether to accept or reject an investment opportunity. For example, you may use ROI to determine if it’s a good financial decision to pay for branded design elements or syndication services. You can also calculate this number after you’ve made an investment to see if it was a smart business decision. You can calculate ROI for several projects to help determine which ones could be most successful. Doing so lets you prioritize resources, funding, and time for marketing activities.

The data you learn from performing these calculations can help you make similar investments and choices or change your strategy in the future to make your campaigns more effective. The marketing department can use ROI to show others within the organization, such as executive officers and stakeholders, the success of investments. Because it shows a clear figure of profit or loss, the ROI is easy to understand, even if others within the organization know little about the initial investment.

Want to learn more about how to get the best resource distribution for your investments, request your free content analysis from CopyPress today! We’ll pull together the content gaps you’re experiencing and give you a list of keywords you could create content around to rank higher in SERPs. If creating this content seems overwhelming, contact us to learn how we can help save you time and money.

WHAT SHOULD YOU FOCUS ON IN YOUR CONTENT MARKETING?

FREE Report: Feel Confident About
the Next Steps in Your Content Marketing

Get a customized content analysis for your business including
how you stack up against your competitors. See where you could
make the biggest impact in your content marketing!

"*" indicates required fields

We value your privacy. Period.

Formulas for Calculating Return on Investment with Excel

There are four common formulas to use when calculating ROI by hand or in Excel. They include:

Net income

With this formula, find the ROI using the net income of the original investment value and write it as a percentage. This is the simplest and most useful formula for measuring ROI. In mathematical notation, write the formula as:

ROI = Net income / Cost of investment

Capital gain

The capital gain formula displays the ROI as a percentage gain or loss made on one share or investment. It compares the current share price with the original investment price. Write the formula as:

ROI = Capital gain / Cost of investment

Total return

The total return formula also calculates the percentage gain or loss of one share or investment. The difference from capital gain is that this computation considers the original and current share prices and any dividends. Write the formula as:

ROI = [(Ending value – Beginning value) / Cost of investment]

Annualized return

In this formula, the ROI is the average annual gain or loss made on a share of investment since the initial investment. You display the answer as a percentage. Calculate this formula as:

ROI = [(Ending value / Beginning value) ^ (1 / Number of years)] – 1

To find the number of years for the second part of the calculation, use the following formula:

Number of years = (Ending date – Starting date) / 365

How To Calculate Net Income Return on Investment with Excel

Net income ROI is the easiest to understand and the easiest to calculate in Microsoft Excel. Use these steps to learn how to calculate this type of ROI within the spreadsheet formula:

1. Open Excel and Create a New Workbook

Open the Microsoft Excel program on your device or through Microsoft 365 online. If you don’t have Microsoft Excel, you can purchase and download the program from the company’s website. Microsoft offers a free trial for both business accounts and individuals. Once you’ve opened the program, open a blank workbook, then name and save the document.

2. Label the Cells

Label the cells across the top of the spreadsheet before adding other information. Doing this can help you find and keep track of the figures to use for ROI calculations. Use the following or similar labels for the corresponding cells:

A1: Content investment
B1: Sales from content
C1: Amount gained
D1: ROI

3. Enter the Content Investment

Enter the content investment figure in cell A2. This number can be the original cost of the campaign or the current worth of the investment. For content campaigns, you can use the first option. In the cell, type the dollar sign and the numerical figure. For example, if your campaign costs $5,000 to launch, then type $5,000 in cell A2.

4. Enter the Sales from Content

Enter the amount earned from the campaign or investment in cell B2. Type the dollar sign and the numeral, just as you did for the content investment. For example, if you made $7,000 from the campaign, type $7,000 in cell B2.

5. Calculate the Amount of Gain or Loss

Use a formula to calculate the gain or loss figure in cell C2. The formula for calculating profit or loss is subtracting the sales from content by the content investment. In Excel, type the formula =B2-A2 in cell C2. This allows the program to pull the numbers from the other cells to make automatic calculations for you.

6. Enter the ROI Formula

Like calculating the amount of gain or loss, use a formula to calculate the ROI in cell D2. The ROI formula divides the amount of gain or loss by the content investment. To show this in Excel, type =C2/A2 in cell D2.

7. Convert the ROI to a Percentage

Your initial ROI calculation in Excel appears as a decimal. It’s customary to display the ROI as a percentage. Highlight cell D2 and click the percentage icon under the “Home” tab. This makes the information in that cell display as a percentage.

8. Repeat the Steps

If there are multiple projects, campaigns, or investments to calculate, you can repeat steps three through seven in separate rows to display them all in one document. This can be beneficial if you’re trying to compare which projects are most useful within your greater marketing plan. You may also consider calculating the ROI of one project over time to find your peak return window.

Tips for Calculating Return on Investment with Microsoft Excel

Use these tips to make your ROI calculations easier:

Fix Inaccurate or Missing Calculations

Because Excel is a computer program, sometimes it may not read your formula correctly. There is also the chance that the program could miscalculate the data if the settings are wrong or the formula is mistyped. To troubleshoot some common formula errors, try:

Turning on automatic calculations: Check the “Formulas” tab to ensure the workbook settings are automatic. If you have it set to “Manual” make the change, so the program calculates on its own.
Deactivating “Show Formulas”: Under the “Formulas” tab, make sure the “Show Formulas” button is inactive. When checked, this option shows your formula in a cell rather than making a calculation.
Setting cell to currency: After highlighting a specific cell, click the “Home” tab to check that it sets the cell property to “Currency” so the information in the cell displays as money. After making the change, double-click the cell and press enter to recalculate and display the information correctly.
Removing spaces: Review the formula in the formula bar to make sure there is no space before the equals sign. Extra spaces may make the formula invalid and not calculate correctly or at all.
Removing apostrophes: Adding an apostrophe before the equals sign in a cell formula makes Excel store your formula as text rather than running the calculation. The apostrophe doesn’t appear in the cell on the spreadsheet, but it shows in the formula bar, so remove these where you want to see a calculation instead of a formula.

Download a Template

If you’re concerned with getting your formulas just right, or want to make sure the spreadsheet and calculations work properly, consider using a calculator template rather than recreating the document yourself. The Corporate Finance Institute (CFI) is just one organization that offers free downloads of ROI Excel Calculator templates. The pre-populated fields have the correct formulas and color-coded cells for your convenience. This particular template includes selections to calculate all four ROI formula types.

Consider an Alternative Formula

There are many alternatives to the basic and complex ROI formulas described above. If you find these four options don’t give you enough information, you can try a different type of calculation. The most detailed return measure is the Internal Rate of Return (IRR), which measures all the cash flow received over the life of an investment or campaign. The figure presents an annual percentage growth rate and accounts for the timing of cash flows, which some others do not.

Other alternatives include the Return on Equity (ROE) and Return on Assets (ROA), which do not account for the timing of cash flows and represent only the annual rate of return. They’re more specific than the net income ROI because they have a clearly defined denominator with equity and assets rather than the more general term of “investment.”

Try Google Sheets

If you’re unable to access Microsoft Excel, you still have options to use a program to calculate your ROI. Consider trying Google Sheets. You can use this program for free with a Google account to create, save, and share documents as you can with Excel. You can use the same formulas and design process to set up a workbook in Google Sheets as you do in Excel. The biggest difference is the location of some settings and the names of some menus.

Content Marketing Made EZ: Sign up for the CopyPress Newsletter Today!

ROI Calculator Excel Template

This Excel ROI template shows you what to enter in each cell to calculate the net income ROI:

	A	B	C	D
1	Content Investment	Sales from Content	Amount Gained	ROI
2	$ Numeral	$ Numeral	($) =B2-A2	=C2/A2 (%)

ROI Calculator Excel Example

This example Excel table shows you what the workbook looks like after the program calculates your figures. This is how your document should display if the calculations work correctly:

	A	B	C	D
1	Content Investment	Sales from Content	Amount Gained	ROI
2	$5,000	$7,000	$2,000	40%

Calculating the ROI of your campaign can help you understand which strategies work best for your marketing plan. Combining the data from these calculations and others, along with analytics and customer feedback, can give you a better picture of the success of your marketing efforts. You can use all this data as you plan future campaigns and tactics to help you reach your target audience and make a profit for your company using content marketing and advertising.

CopyPress

How do you do rate of return in excel?

Rate of Return = (Current Value – Original Value) * 100 / Original Value.

Rate of Return = (Current Value – Original Value) * 100 / Original Value..

Rate of Return Google = (2800 – 2000) * 100 / 2000..

Rate of Return Google = 800 * 100 / 2000..

Rate of Return Google = 40%.

What is the formula to calculate rate of return?

ROI is calculated by subtracting the initial cost of the investment from its final value, then dividing this new number by the cost of the investment, and finally, multiplying it by 100. ROI has a wide range of uses.