How to Use Excel to Calculate Your ROI

ROI is a measure of the profitability of an investment.
Image Credit: Alexander Khripunov/Hemera/Getty Images

The basic calculation for return on investment -- ROI, for short -- is net gains divided by the cost of the investment. Although you can calculate ROI by hand, you can easily create a spreadsheet in Microsoft Excel to calculate it. As long as you enter the formulas, ROI will automatically populate after you enter your investment data.

Step 1

Starting in column B, designate a column for each year of your investment and label the cells accordingly. After the last year, create a column for investment totals. For example, say that you originally purchased your investment in 2013 and sold it in 2015. Type "2013" in cell B1, "2014" in C1 and "2015" in D1. In cell E1, type "total."

Step 2

Beginning in row 2, designate three rows to figure your cash flows and net gain. In cell A2, type "cash inflows." In cell A3, type "cash outflows." In cell A4, type "net gain." In cell B4 -- the cell adjacent to "net gain" -- type the formula "=B2-B3." This formula will subtract annual cash outflows from annual cash inflows to calculate net gain. Copy the formula and paste it into every cell in row 4 for which you have a year.

Step 3

In the three cells directly below "total," create a sum formula. In this example, it would be cells E2, E3 and E4. Type the formula "=sum(B2:D2)" into E2. Copy and paste the formula into cells E3 and E4. This will calculate the total cash inflows, outflows and net gain for the life of the investment.

Step 4

Fill in the Excel spreadsheet with the relevant data from your investment. List all cash you received from the investment -- like dividends, interest and the price you sold the investment for -- in the "cash inflows" row. Detail the original purchase price of the investment and any additional costs incurred in the "cash outflows" row. As long as you entered the formulas correctly, the "net gains" row and "total" column should automatically populate as you enter information.

Step 5

Type "ROI" in the cell directly below "net gains." Divide total net gains by the total investment cost to find the ROI for the investment. Total net gains is the intersection of the "net gains" row and the "total" column. In this example, it's cell E4. Total investment cost is the sum of all cash outflows over the life of the investment. In this example, it's cell E3. Type "=E4/E3" in the cell to the right of "ROI." This is the ROI for your investment.

references