Build a cost-and-proceeds record before writing the percentage
An ROI worksheet should show what was paid, what was received and which costs are included. For this guide, use the same transaction definition as NumUtility's ROI Calculator: cost equals initial investment plus purchase costs, proceeds equal ending value plus separately received income minus exit costs, and profit is proceeds minus cost. Another definition needs its own labels and reconciliation.
The spreadsheet examples below use nonnegative amount fields with costs subtracted in the formulas rather than entered as negative expenses. This is different from a signed cash-flow table used for IRR. Decide which convention the sheet uses and keep it consistent. A formula cannot detect that a fee was both typed as negative and subtracted a second time.
Assign the transaction inputs to labeled cells
Use B2 for initial investment, B3 for purchase fees, B4 for ending value, B5 for separate cash income, B6 for exit costs and B7 for holding years. For a hypothetical example, enter $1,200, $30, $1,600, $80, $20 and 2 in those cells respectively. Keep all monetary amounts in one currency and the holding period in years.
Describe what ending value means for this transaction: for a sold asset, gross sale proceeds before the separately entered exit cost; for a retained asset, a stated valuation under the chosen boundary. Do not mix those meanings across rows. The worksheet does not obtain live prices, exchange currencies or determine an appropriate tax valuation automatically.
Calculate cost, proceeds and profit separately
Put =B2+B3 in B9 for cost, giving $1,230. Put =B4+B5-B6 in B10 for proceeds, giving $1,660. Put =B10-B9 in B11 for profit, giving $430. These intermediate cells make it possible to trace a discrepancy to a particular cost or receipt before interpreting the percentage.
For example, the $20 exit charge belongs in proceeds exactly once. If B4 already contains a net sale receipt after that charge, subtracting B6 again understates proceeds. Either record gross ending proceeds with a separate exit charge or reconcile a net amount under an explicitly consistent setup. The same rule applies to purchase costs already embedded in an initial total.
Return a decimal ratio and format the result as a percentage
For a positive cost denominator, the basic ROI formula is =B11/B9. In this example the underlying ratio is approximately 0.34959350, displayed as 34.96 percent. Do not multiply the formula by 100 and then apply percentage formatting; that would display approximately 3,495.93 percent. The numeric storage convention matters just as much as the formula's numerator.
A simple denominator guard is =IF(B9>0,B11/B9,NA()). It marks an unsupported denominator as unavailable instead of claiming zero ROI. Input validation is still needed: this guard does not by itself reject every text value, missing required cell or incorrectly signed expense. Do not wrap the whole calculation in an error handler that silently converts all problems to zero.
Copy a scenario table without changing the calculation boundary
If evaluating several transactions, give each row separate columns for the same six amounts or durations and use row-specific references consistently. Keep shared assumptions fixed only when they truly are shared. A formula accidentally pointing to the prior row's purchase cost can produce a plausible percentage that belongs to neither transaction.
Add a reconciliation column checking profit equals proceeds minus cost. Preserve a hand-worked row such as the $1,230-to-$1,660 example as a test case. Do not sum individual ROI percentages to get a portfolio return. Combining transactions requires an appropriate common cost and cash-flow method, especially when timing or capital reinvestment differs.
Guard endpoint annualization when cash income timing is unknown
With positive years, positive cost and nonnegative terminal proceeds, a pure endpoint annualization can be written as (proceeds/cost)^(1/years) − 1. To mirror NumUtility's narrower supported case, use =IF(AND(B5=0,B10>=0,B7>0,B9>0),(B10/B9)^(1/B7)-1,NA()). This deliberately withholds the result when separately entered cash income is positive, because its dates are not specified.
The example has $80 cash income, so this guarded annualization is unavailable rather than a guessed two-year CAGR. The aggregate 34.96 percent ROI remains available. For irregular dated receipts, use an appropriately constructed XIRR worksheet instead; do not move every cash distribution to the ending date merely to make an endpoint formula return a number.
Test losses, zero proceeds and expense signs
A second simple check with cost $1,000 and proceeds $800 gives −20 percent ROI. With the same cost and zero proceeds it gives −100 percent. If net proceeds become negative because exit costs exceed receipts, ROI can be below −100 percent under this defined cash calculation; the endpoint annualization is then not supported by the nonnegative-proceeds guard.
A zero initial-investment entry is not supported by NumUtility's transaction inputs even if a spreadsheet denominator could be made positive with other cells. Match the tool's full input rules when cross-checking, not just one IF condition. Keep fees positive in the amount columns and let the formulas apply subtraction, so that an expense does not accidentally become income.
Compare the sheet with the calculator and retain the assumptions
Enter the example's six values in the ROI Calculator and verify $1,230 cost, $1,660 proceeds, $430 profit and approximately 34.96 percent ROI, with annualization unavailable. Compare the underlying definition before troubleshooting small formatting differences. Display rounding should not replace the unrounded ratio unless a separate rounded reporting convention is expressly chosen. Keep a date or note beside a retained asset valuation, since replacing that input with a later price changes the measured result without changing the formula.
These English spreadsheet examples use common comma argument separators; local settings can differ. The worksheet is educational, not investment, tax or accounting advice, and does not produce a tax cost basis. Keep the cost boundary, valuation date, cash-income treatment and exclusions with any shared version. A well-labeled unavailable result is preferable to a precise-looking percentage built from missing information.