How to Calculate a Return on an Investment in Excel
A smart business person knows the importance of doing regular return on investment (ROI) calculations. It is a way to measure the overall success of your efforts. Knowing your ROI percentages can help you make better decisions about where to put your investment money and show you which business initiatives should be dropped immediately. An easy way to track your ROI calculations is with a simple Excel spreadsheet.
Label your columns so that you can easily identify the information for future reference. Label the first column (A1) "Name of Investment," then "Investment Amount," "Profit," and "ROI" for the subsequent column headings (B, C, and D). Then press "Return" to go to the next line to start entering in information about your investments.
Type in the information per your headings on the second line of your spreadsheet. Describe the investment that you made briefly in cell A2. Include all costs related to the investment, including taxes, fees, and upfront payments under the "Investment Amount" column (cell B2). Total up the profit that you made as a direct result of that investment in the "Profit" column (cell C2). The profit is the amount that you made above your total investment amount. You can calculate your return on investment on a weekly, monthly, or yearly basis.
Calculate your ROI by dividing the profit by the investment amount. Type this formula in the "ROI" column (cell D2) to do the calculation automatically: "=C1/B1" minus the quotes.
When you finish the first ROI calculation, enter your next investment on the following line. One of the main purposes of doing a return on investment calculations is to compare your investments to see which ones perform the best. When you get to D3, select cell D2, click and hold the tiny square at the bottom right of the cell, and drag it down with your mouse. This will copy the formula from your first entry into the next entry. Do this for every ROI entry you make to the spreadsheet.
Format your spreadsheet so that your results are easier to read and digest. Make your headings bold, turn your ROI into a percentage, and give your investment amount a dollar sign and decimals. You can do this by right clicking the cell or column and selecting "Format Cells." Make your "Name of Investment" column wider to comfortably fit all of your words. A little formatting will go a long way when you are analyzing your spreadsheet.