ROI Calculator Excel
Excel is business’s universal language. Create ROI calculations with professional spreadsheet techniques.
How the ROI Calculator Excel works
Build Excel ROI models with formulas, functions, and formatting. Learn XNPV, XIRR, and advanced techniques for financial modeling.
Excel ROI skills are essential. This calculator teaches while calculating, building valuable spreadsheet expertise.
How it works
Tutorial
Excel ROI calculations provide flexibility, reusability, and professional presentation that simple calculators cannot match. Building ROI models in spreadsheets enables scenario analysis, sensitivity testing, and customization for specific business situations. Mastering Excel formulas for ROI calculations—including understanding functions like XNPV, XIRR, and proper absolute vs relative cell references—is an essential business skill.
You have two options: use the calculator above to see Excel-formatted ROI calculations with formulas, or follow this guide to build your own Excel ROI model from scratch.
The Formula
| Calculation Type | Excel Formula |
|---|---|
| Simple ROI | =((Revenue-Investment)/Investment)*100 |
| NPV | =NPV(rate, value1, value2, …) – initial_investment |
| IRR | =IRR(values, [guess]) |
| XNPV (with dates) | =XNPV(rate, values, dates) |
| XIRR (with dates) | =XIRR(values, dates, [guess]) |
Step-by-Step Calculation
Let’s build a comprehensive Excel ROI model with proper formulas.
Step 1: Set Up Input Section
Create clearly labeled input cells:
| Cell | Label | Value | Formula |
|---|---|---|---|
| B2 | Initial Investment | $100,000 | =100000 |
| B3 | Annual Revenue | $35,000 | =35000 |
| B4 | Annual Costs | $8,000 | =8000 |
| B5 | Project Duration (years) | 5 | =5 |
| B6 | Discount Rate | 8% | =0.08 |
Best Practice: Use named ranges (Formulas > Define Name) to make formulas readable.
Step 2: Build Annual Net Cash Flow Table
Create year-by-year cash flow calculations:
| Row | Year (Col A) | Revenue (Col B) | Costs (Col C) | Net Cash Flow (Col D) |
|---|---|---|---|---|
| 10 | 0 | $0 | $100,000 | =-$100,000 |
| 11 | 1 | $35,000 | $8,000 | =$27,000 |
| 12 | 2 | $35,000 | $8,000 | =$27,000 |
| 13 | 3 | $35,000 | $8,000 | =$27,000 |
| 14 | 4 | $35,000 | $8,000 | =$27,000 |
| 15 | 5 | $35,000 | $8,000 | =$27,000 |
Formula for D10: =-B$2 (initial investment as negative cash flow)
Formula for D11: =B11-C11 (drag down to D15)
Step 3: Calculate Simple ROI
Build the basic ROI calculation:
| Cell | Label | Formula | Result |
|---|---|---|---|
| B18 | Total Revenue | =SUM(B11:B15) | $175,000 |
| B19 | Total Costs | =B2+SUM(C11:C15) | $140,000 |
| B20 | Total Profit | =B18-B19 | $35,000 |
| B21 | ROI Percentage | =(B20/B2)*100 | 35.0% |
Calculation: =($35,000/$100,000)*100 = 35.0%
Step 4: Calculate Net Present Value (NPV)
Account for time value of money:
| Cell | Label | Formula | Result |
|---|---|---|---|
| B24 | NPV | =NPV(B6,D11:D15)+D10 | $7,816 |
Calculation: =NPV(0.08, D11:D15) + D10 = $7,816
Important: Add initial investment separately since NPV assumes end-of-period cash flows.
Step 5: Calculate Internal Rate of Return (IRR)
Find the discount rate where NPV = 0:
| Cell | Label | Formula | Result |
|---|---|---|---|
| B26 | IRR | =IRR(D10:D15) | 10.97% |
Calculation: =IRR(D10:D15) = 10.97%
Reasoning: IRR of 10.97% exceeds the 8% discount rate, indicating a good investment.
Step 6: Calculate Payback Period
Determine when cumulative cash flow turns positive:
| Row | Year | Cash Flow | Cumulative (Col E) | Formula |
|---|---|---|---|---|
| 10 | 0 | -$100,000 | -$100,000 | =D10 |
| 11 | 1 | $27,000 | -$73,000 | =E10+D11 |
| 12 | 2 | $27,000 | -$46,000 | =E11+D12 |
| 13 | 3 | $27,000 | -$19,000 | =E12+D13 |
| 14 | 4 | $27,000 | $8,000 | =E13+D14 |
Payback occurs in Year 4
Precise calculation in cell B28: =MATCH(TRUE,E10:E15>0,0)-1+ABS(INDEX(E10:E15,MATCH(TRUE,E10:E15>0,0)-1))/D14
Result: 3.70 years
Step 7: Build Scenario Analysis
Create sensitivity table for different assumptions:
| Pessimistic | Base Case | Optimistic | |
|---|---|---|---|
| Annual Revenue | $30,000 | $35,000 | $42,000 |
| Annual Costs | $10,000 | $8,000 | $7,000 |
| ROI | 0% | 35% | 75% |
| NPV | -$20,091 | $7,816 | $39,853 |
| IRR | 7.93% | 10.97% | 14.87% |
Formula for scenario ROI: =((Revenue*Years-Costs*Years-Investment)/Investment)*100
Step 8: Add Professional Formatting
Format for presentation:
| Element | Format | How To |
|---|---|---|
| Currency | $#,##0 | Home > Number Format > Currency |
| Percentages | 0.00% | Home > Number Format > Percentage |
| Conditional Formatting | Positive = Green | Home > Conditional Formatting > Color Scales |
| Input Cells | Yellow fill | Home > Fill Color |
Final Answer: The Excel ROI model shows 35% simple ROI, 10.97% IRR, $7,816 NPV, with 3.7-year payback
What This Means
Building ROI calculations in Excel transforms static analysis into dynamic decision-making tools. With proper formulas, you can instantly see how changing assumptions affects returns, test multiple scenarios, and present professional analysis to stakeholders. The IRR of 10.97% exceeding the 8% discount rate confirms this investment creates value, while the positive NPV quantifies that value at $7,816 in today’s dollars.
Meet the fastest voice-to-text for professionals
WriteVoice turns your voice into clean, punctuated text that works in any app. Create and ship faster without typing. Your first step was ROI Calculator Excel; your next step is instant dictation with WriteVoice.
A blazing-fast voice dictation
Press a hotkey and talk. WriteVoice inserts accurate, formatted text into any app, no context switching
Works in any app
Press one hotkey and speak; your words appear as clean, punctuated text in Slack, Gmail, Docs, Jira, Notion, and VS Code—no context switching, just speed with writevoice
Accurate, multilingual, and smart
97%+ recognition, smart punctuation, and 99+ languages so your ideas land first try, built for teams and pros.
Private by default
Zero retention, audio and text are discarded instantly, with on-device controls so you can dictate sensitive work confidently.