To calculate ROI in Excel over multiple years, subtract the investment’s total cost from its ending value, divide by the total cost, and format the result as a percentage. For an annualized rate that accounts for the length of the investment, use =(EndingValue/StartingCost)^(1/NumberOfYears)-1; when money is added or withdrawn at different times, use cash-flow measures such as IRR or XIRR instead.
What a multi-year ROI measures
Return on investment (ROI) compares a gain or loss with the amount invested. A basic cumulative ROI answers, “How much did this investment gain or lose in total?” It does not say how quickly the result happened. A 40% gain over three years and a 40% gain over eight years have the same cumulative ROI, but very different annualized rates.
Before building a worksheet, decide which question you need to answer. Use cumulative ROI to summarize the total change over a period. Use annualized ROI to compare investments held for different lengths of time, assuming the value grew at a steady compounded rate. Use a cash-flow return such as IRR when contributions or distributions occurred on a schedule. These measures are related, but they are not interchangeable.
ROI is also a simplified measure. Depending on what the calculation includes, it may omit fees, taxes, inflation, or the timing of cash flows. Label the result clearly and use consistent assumptions when comparing options. Spreadsheet calculations can organize figures, but they do not establish whether an investment is suitable.
Set up the worksheet
In Excel for Microsoft 365 on Windows, enter a label in column A and its amount in column B. For the simplest example, use the following layout:
- B2: initial investment, $10,000
- B3: value at the end of the period, $14,000
- B4: number of years, 3
Enter amounts as numbers, not text that merely looks like currency. Keep the original inputs in separate cells rather than typing them directly into formulas. That makes assumptions easy to review and lets you change an amount or holding period without rewriting the calculation. To format the amounts, select the cells and choose Home > Number > Currency. To show a result as a percentage, select its cell and choose Home > Number > Percent Style.
Calculate cumulative ROI
With the initial investment in B2 and ending value in B3 as shown above, enter this formula in B5:
=(B3-B2)/B2
The result is 0.4, or 40% when formatted as a percentage: ($14,000 − $10,000) ÷ $10,000 = 0.40. This is the total return across all three years, not a 40% return in each year. If the ending value were below the starting cost, the formula would return a negative percentage, indicating a loss under this simplified calculation.
Be precise about what “ending value” means. If the investment produces cash distributions that were not reinvested, those payments may need to be included in the gain. If they were reinvested and are already reflected in the ending value, adding them again would count the same value twice.
Convert the total return to an annualized rate
Annualized ROI expresses a multi-year result as a compounded yearly rate. With the initial amount in B2, ending amount in B3, and number of years in B4, enter this formula in B6:
=(B3/B2)^(1/B4)-1
For $10,000 growing to $14,000 over three years, the result is approximately 11.87% per year. This is the compound annual growth rate (CAGR): growing $10,000 by about 11.87% each year for three years would produce approximately $14,000. It is an equivalent rate, not proof that the investment actually earned that same percentage in every year.
The formula assumes a positive starting amount, a positive ending amount, and a holding period greater than zero. If B4 is zero or blank, Excel cannot calculate the intended annual rate. Check that the period is entered in years; if the dates cover months, convert the holding period to an appropriate fraction of a year or use dated cash-flow analysis for greater precision.
Show the return for each year
A yearly table helps reveal whether performance was steady or uneven. For example, put the investment value at the end of year 0 through year 3 in E2:E5: $10,000, $11,000, $12,500, and $14,000. Put “Annual return” in F2. With those values in E2:E5, enter this formula in F3 and copy it down through F5:
=E3/E2-1
The results are 10% for year 1, approximately 13.64% for year 2, and 12% for year 3. The calculation compares each year-end value with the previous year-end value. The total change from $10,000 to $14,000 is still 40%, while the annualized rate over the full three years is about 11.87%. The yearly rates differ because the investment did not grow by the same percentage each year.
This table assumes there were no additional contributions, withdrawals, or distributions during those intervals. If cash was added, a simple change in account value can mistake a deposit for investment performance. Use a cash-flow method or adjust the calculation to reflect the timing and amount of each external cash flow.
Include contributions and distributions
For a simple cumulative summary with more than one contribution, define the inputs explicitly. Suppose B2 is the initial contribution of $10,000, B3 is a later contribution of $2,000, B4 is $1,000 in distributions received and not reinvested, and B5 is the ending value of the remaining holding at $14,000. With those figures in B2:B5, enter:
=(B4+B5-SUM(B2:B3))/SUM(B2:B3)
The result is 25%: ($1,000 + $14,000 − $12,000) ÷ $12,000 = 0.25. This is a cumulative, money-in-versus-value-out summary. It treats all contributions as if their timing did not matter. A contribution made near the end of the period has been invested for less time than the initial contribution, so this percentage is not a time-weighted or annualized return.
Keep a clear record of what each figure represents. Do not include a distribution in B4 if it is already included in B5, and do not treat a transfer between accounts as a new investment if it does not represent new money entering the investment. When contribution dates differ substantially, a dated cash-flow calculation is usually more informative than this simple summary.
Use IRR or XIRR for timed cash flows
Excel’s IRR function estimates a periodic internal rate of return from a series of cash flows. For an example, place an initial outflow of -$10,000 followed by inflows of $3,000, $4,000, and $6,000 in B2:B5, one value per year. Enter this formula in B6:
=IRR(B2:B5)
The result is approximately 12.72% per period, or per year when the cash flows are one year apart. The initial investment is negative because it is money paid out; amounts received are positive. IRR assumes the values occur at regular intervals. If cash-flow amounts never change sign, Excel may not find a meaningful investment return; a conventional investment example generally has an outflow followed by one or more inflows.
When dates are irregular, use XIRR, which uses both cash-flow amounts and their dates. For example, enter 1/1/2023, 1/1/2024, 1/1/2025, and 1/1/2026 in A2:A5, and enter -$10,000, $0, $0, and $14,000 in B2:B5. With those dates in A2:A5 and cash flows in B2:B5, enter:
=XIRR(B2:B5,A2:A5)
The result is approximately 11.87% per year. The example has three years between the initial outflow and final inflow, so it gives the same annualized result as the earlier CAGR example. In a real dated cash-flow series, XIRR can differ from a simple annualized ROI because it accounts for when each contribution or distribution occurred. Excel requires valid dates and at least one positive and one negative cash flow for a typical investment calculation.
Compare ROI with NPV
ROI describes a percentage gain relative to an amount invested. Net present value (NPV) answers a different question: after discounting future cash flows at a chosen rate, how much value remains after accounting for the initial cost? This can help compare projected cash flows, but the discount rate is an assumption and should be stated rather than hidden.
For a simple annual example, put the initial outflow of -$10,000 in B2, then annual inflows of $3,000, $4,000, and $6,000 in B3:B5. Put a 10% discount rate in B6. With those entries, use:
=NPV(B6,B3:B5)+B2
The result is approximately $540.95: the present value of the three future inflows at 10% is about $10,540.95, and adding the initial -$10,000 leaves positive NPV of about $540.95. Excel’s NPV function treats the first value in its range as occurring one period in the future, so the initial investment at time zero is added separately. This timing detail is a frequent source of errors.
Common mistakes and checks
- Calling cumulative ROI annual ROI: A 40% total return over three years is not 40% per year. Use the annualized formula or a cash-flow return when appropriate.
- Ignoring contributions: Deposits increase account value but are not automatically investment gains. Record external cash flows separately.
- Mixing up signs: For IRR and XIRR, money invested is normally negative and money received is positive.
- Double-counting distributions: Add distributions only when they are not already part of the ending value or cash-flow total.
- Comparing different assumptions: Make sure the compared results use the same period, currency, fee treatment, and definition of return.
- Rounding too early: Keep full precision in the cells and format the display to two decimal places. Rounding intermediate values can slightly change the result.
As a quick check, confirm that the initial amount is not zero, the year count is positive, and the cash-flow dates are in chronological order. Review the result’s format as well: a decimal of 0.1187 represents 11.87%, not 0.1187%. If Excel displays an error, check for text-formatted numbers, missing dates, or an IRR/XIRR series without both positive and negative values.
Frequently Asked Questions
Is ROI the same as annualized ROI? No. Cumulative ROI reports the total gain or loss relative to the investment over the full period. Annualized ROI expresses that result as an equivalent compounded yearly rate, making it more useful for comparing different holding periods.
Can I calculate ROI if I add money each year? Yes, but a simple beginning-to-ending value formula can be misleading because it ignores when the contributions occurred. List each contribution as a negative cash flow and each distribution or final proceeds as a positive cash flow, then use IRR for regular intervals or XIRR for dated, irregular intervals.
Should I use IRR or XIRR in Excel? Use IRR when cash flows occur at regular intervals, such as once per year. Use XIRR when you have actual dates and the gaps between cash flows vary. Both produce a rate; their assumptions about timing differ.
Does ROI include taxes, fees, or inflation automatically? No. Excel’s basic ROI arithmetic includes only the values you provide. To reflect fees, taxes, or inflation, you would need to account for them in the inputs or use an appropriately defined calculation. For real financial, tax, investment, or legal decisions, check the actual terms with the relevant provider or a qualified professional.
Why is my ROI negative? A negative result means the value included in the calculation is less than the amount invested, or that net cash flows show a loss under the chosen method. Check that you entered the starting amount, ending value, contributions, distributions, and cash-flow signs consistently.
Related
- Bank Details Format in Excel: Fields, Setup and Safety
- Basic Cash Flow Template in Excel: Build and Use It
- Autofit Column Width in Excel: Steps, Shortcuts & Fixes
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Google Sheets IFS: How It Works and When to Use ItIn Google Sheets, IFS checks conditions in the order you list them and returns the result paired with…
- Excel Data Model on Mac: What Works and What Doesn’tExcel for Mac does not include Power Pivot, so you cannot create, manage, or refresh a full Excel…
- Google Sheets Duplicate Rows: Find and Remove ThemYes. In Google Sheets, select the data range and choose Data > Data cleanup > Remove duplicates to…
- Convert Hours to a Number in Excel: Time to DecimalTo convert an Excel time value to decimal hours, multiply it by 24. If the time is in…
- Excel Box and Whisker Plot Outliers: How They WorkIn an Excel box-and-whisker chart, an outlier is a data point below Q1 − 1.5 times the interquartile…
- Set Out of Office in Outlook for Mac: A Step-by-Step GuideTo set an out-of-office reply in Outlook for Mac, open Tools > Automatic Replies, choose your Exchange or…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.