Calculate Annual ROI in Excel: Formula and Examples

Formulas & functions · Updated November 21, 2026

To calculate annualized ROI in Excel, divide the ending value by the initial investment, raise the result to the power of one divided by the number of years, then subtract 1. If the initial amount is in B2, the ending amount in B3, and years held in B4, enter =(B3/B2)^(1/B4)-1 in another cell and format it as a percentage. This gives the compounded equivalent annual return; a simple annual ROI uses a different calculation.

What annual ROI means

Return on investment, or ROI, compares a gain or loss with the amount invested. A basic ROI calculation gives the total return over the entire period. Annual ROI expresses a return in relation to a year, but the phrase can mean either a simple average per year or an annualized, compounded rate. Those are not interchangeable, so decide which measure answers your question before building a spreadsheet.

Annualized ROI is often useful when comparing investments held for different lengths of time. It answers: what steady yearly growth rate would turn the initial value into the ending value over this period, assuming growth compounds? It does not mean the investment actually gained that same percentage in every year. Actual returns can rise and fall, and annualizing a result does not remove that variation or predict future performance.

There is no single universal ROI convention for every business or investment. For a useful calculation, define what counts as the initial cost, what counts as the final value, and how to handle money received or added along the way. Keep those choices consistent when comparing results.

Set up the inputs in Excel

In Excel for Microsoft 365 on Windows, create a small input table so the assumptions are visible and easy to update. For a basic investment held for a known number of years, use this layout:

  • A2: Initial investment
  • B2: 10000
  • A3: Ending value
  • B3: 13000
  • A4: Years held
  • B4: 3
  • A5: Annualized ROI
  • B5: formula result

Enter amounts as positive numbers in the input table. The initial investment is the starting value, while the ending value should represent what the investment is worth at the end of the period. If you are measuring a business project rather than an asset, use the project cost as the initial amount and the clearly defined value or proceeds at the end as the ending amount.

Use years as a number, such as 3 for three years or 2.5 for two and a half years. Do not enter a text description such as three years in the formula cell. For a period shorter than a year, enter a fraction of a year if that matches your chosen convention; for exact dates and irregular cash flows, use XIRR instead, as explained below.

Calculate annualized ROI step by step

  1. Enter the initial investment, ending value, and holding period in separate cells.
  2. Click the cell where you want the annualized result to appear.
  3. Use the annualized formula shown below, changing the cell references if your layout differs.
  4. Select the result and choose Home > Number > Percent Style. Increase or decrease the displayed decimal places as needed.

With the initial investment in B2, the ending value in B3, and the holding period in years in B4, enter =(B3/B2)^(1/B4)-1 in B5. For the example values of 10,000, 13,000, and 3 years, the result is about 9.14% per year. The arithmetic is the cube root of 1.3, minus 1: 1.3 raised to one-third is about 1.0914, so the annualized rate is about 0.0914.

The parentheses matter. Excel first calculates the ending-to-initial value ratio, raises that ratio to the reciprocal of the number of years, then subtracts 1. The final subtraction converts a growth factor into a rate. Format the result as a percentage to show 9.14% rather than 0.0914. Percentage formatting changes how the number is displayed, not the underlying calculation.

Worked example: simple versus compounded results

For the 10,000-to-13,000 example, total gain is 3,000 and total ROI is 30%. Someone might divide 30% by three and report 10% per year. That is the simple average annual ROI, which spreads the total return evenly across the years without compounding. It can be a useful rough measure in some project comparisons, but it is not the annualized compounded rate.

With the initial investment in B2, the ending value in B3, and the number of years in B4, enter =((B3-B2)/B2)/B4 to calculate simple annual ROI. For the example, the calculation is (13,000 − 10,000) ÷ 10,000 ÷ 3, which equals 0.10, or 10% per year. By contrast, the annualized formula returns about 9.14% because earning 9.14% in each of three compounded years produces approximately 30% total growth.

Neither figure is automatically the right answer in every context. Use the compounded annualized rate when you need a consistent annual growth rate for a beginning and ending value over time. Use simple annual ROI only when that is the convention required for a report or comparison, and label it clearly so readers do not mistake it for a compounded return.

Include costs and define the ending value

A formula can be mathematically correct and still produce a misleading ROI if its inputs leave out relevant costs. For an asset, consider whether the starting amount includes purchase fees, setup costs, or other expenses directly tied to the investment. At the end, the value might be a sale price, an estimated market value, or net proceeds after selling expenses. Choose a consistent basis and record what you included.

For a business project, the initial cost might include equipment, implementation, and other project spending. The final benefit could be cash savings, revenue, or a measurable value at a stated date. Avoid combining unlike measures, such as comparing a purchase price before fees with sale proceeds after fees, without explaining the difference. If tax treatment affects a real decision, check the applicable facts with a qualified professional; this spreadsheet calculation is not tax or investment advice.

To include a known cost in a simple one-time investment calculation, you can incorporate it into the initial amount before calculating ROI. Keep the source numbers in separate rows when possible rather than burying adjustments in a complex formula. That makes it easier to audit the inputs, explain the result, and revise it if a cost estimate changes.

When cash flows happen at different times

The basic annualized formula assumes one starting amount and one ending amount, with no additional contributions or withdrawals in between. If you add money during the period, receive distributions, or make several project payments on different dates, treating everything as if it happened at the start or end can distort the annual rate. Excel’s XIRR function can calculate an annualized rate for dated cash flows.

Put actual dates in A2:A3 and corresponding cash flows in B2:B3, with the investment recorded as a negative amount and the money received recorded as a positive amount. For example, use January 1, 2023 and January 1, 2024 in A2:A3, and -10000 and 11000 in B2:B3. Enter =XIRR(B2:B3,A2:A3) in another cell. With those dates exactly 365 days apart, the result is 10% annualized: an outflow of 10,000 followed by an inflow of 11,000 one year later represents a 10% return.

For several cash flows, extend both ranges to include every date and matching amount. The first investment is usually negative because it is money paid out; later contributions are also negative, while proceeds and distributions received are positive. XIRR uses the dates to account for when those cash flows occurred. Make sure the date and value ranges are the same length and include at least one negative and one positive amount. If Excel returns an error such as #NUM!, check the signs, dates, and cash-flow pattern; some patterns may not produce a single meaningful rate.

Check the result and avoid common errors

  • Zero or negative holding period: The annualized formula divides by the number of years. Enter a positive period; use a date-based method if the duration is not known reliably.
  • Wrong cell references: Confirm that the starting and ending values have not been reversed. A ratio below 1 can correctly produce a negative annualized return when value fell.
  • Percent entered as a whole number: Enter 10% as 10% or 0.10, not 10, if the cell represents a rate. The initial and ending investment amounts are entered as amounts, not percentages.
  • Uncounted deposits or withdrawals: Use XIRR for dated intermediate cash flows rather than relying on the one-start, one-end formula.
  • Misleading decimal display: A result of 0.0914 is 9.14% when formatted as a percentage. Applying percentage formatting to a value already multiplied by 100 would display an incorrect scale.

As a quick reasonableness check, compare the result with the total change and time period. If an investment grew from 10,000 to 13,000 over three years, the annualized answer should be positive and below the 30% total gain. If the value fell from 10,000 to 8,000, the annualized answer should be negative. Check your definitions and inputs before concluding that an unexpected result is an Excel error.

Make the worksheet easier to interpret

Label the measure explicitly as Annualized ROI or Simple annual ROI, not merely Return. Add a note stating the holding period and the costs or cash flows included. If the result will be shared, show the initial amount, ending amount, dates or years, and formula output together. This lets another reader trace the calculation instead of treating the percentage as a standalone fact.

For an ongoing model, keep raw amounts and dates in input cells and put formulas in separate result cells. Use number formatting rather than typing a percent sign into a text value. You can also add a short note beside the result explaining whether cash flows between the start and end dates were included. Excel for Mac and Excel for the web use the same formulas; ribbon placement and labels can vary slightly by platform, but the calculation itself does not change.

Frequently Asked Questions

Is annual ROI the same as annualized ROI?
Not necessarily. Annual ROI may refer to a simple total ROI divided by years, while annualized ROI usually means the compounded yearly rate between beginning and ending values. State which definition you use.

How do I calculate annual ROI for less than one year?
For a single starting value and ending value, the annualized formula can use a fraction of a year, such as 0.5 for six months. For precise dates or multiple cash flows, use XIRR with the actual dates and amounts instead of estimating the period.

Can annualized ROI be negative?
Yes. If the ending value is less than the initial value, the annualized result is negative. A loss over time is not an Excel problem; check that the values and period are entered correctly.

Should I use ROI or XIRR?
Use the basic annualized ROI formula when there is one initial amount, one ending amount, and no intervening cash flows. Use XIRR when cash flows occur on different dates, such as additional investments, withdrawals, or distributions.

Why does Excel show a decimal instead of a percentage?
Excel stores rates as decimal values. Select the result cell and choose Home > Number > Percent Style to display 0.0914 as 9.14%. The underlying value remains the same.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.