Calculating Investment Return In Excel

What Is Calculating Investment Return In Excel?

Calculating Investment Returns in Excel evaluates the business performance’s efficiency or profitability. It is used to compare different types of investments from net profit for each investment.

For example, the image depicts the investment flow, and we will calculate the Return on investment (ROI) using the Formula. In the table,

  • Columns A and B shows the particulars and the amount, respectively.
  • Cells B4 and B5 displays the ROI Output and the ROI% Output.
Calculating Investment Return in Excel

The steps to evaluate the cashflows by the Return on investment (ROI) are as follows:

  1. First, select the cell where we will enter the formula and calculate the result. The selected cell, in this case, is cells B4 & B5.

  2. Next, we will enter the Return on investment (ROI) formula in cell B4.

  3. So, the complete formula is =B3-B2 in cell B4.

  4. Next, press the Enter key. We can see the result in cell B4.


    Calculating Investment Return in Excel - Step 4

  5. Next, we will enter the Return on investment (ROI)% formula in cell B5.

  6. Therefore, the complete formula is =(B3-B2)/B2 in cell B5.

  7. Finally, press the Enter key. We can see the results in cell B4.


    Calculating Investment Return in Excel - Step 7

Key Takeaways
  • Return on investment (ROI) calculates the return on an asset or investment over a specific period.
  • The return on investment (ROI) is typically expressed as a percentage. It is a measure of an investment’s profitability, indicating the return relative to the initial investment. 
  • The Return on Investment (ROI) formula is a crucial tool for measuring the profitability of an investment. To calculate ROI, subtract the initial investment value from the current value and divide the result by the initial value.
  • Return on investment (ROI) is a metric used to compare the total return of different investments, considering any potential downsides. It is a crucial tool for investors to evaluate the profitability of their investments and make informed decisions.

What Is The Return On Investment (ROI)?

Return On Investment is calculated by dividing the investment’s benefit or profit by the cost of the investment. Return on investment (ROI) is ubiquitous in the finance industry, representing the gains realized from an investment. ROI is a measure of profitability that compares the return to the initial investment.

The ROI Formula

ROI = Total Return – Initial Investment

ROI% = Total Return – Initial Investment / Initial Investment * 100

Examples

Example #1

The succeeding example depicts the investment and sold value of the property, and we will calculate the Return on investment (ROI) using the Formula.

In the table,

  • Columns A and B shows the particulars and the amount.
  • Cells B4 and B5 shows the ROI Output and the ROI% Output.
Calculating Investment Return in Excel - Example 1

The steps to evaluate the cashflows by the Return on investment (ROI) are as follows:

  • Step 1: Select the cell where we will enter the formula and calculate the result. The selected cell, in this case, is cells B4 & B5.
  • Step 2:  Next, we will enter the Return on investment (ROI) formula in cell B4.
  • Step 3: The complete formula is =B3-B2 in cell B4.
  • Step 4: Next, press the Enter key. We can see the result in cell B4.
Example 1 - Step 4
  • Step 5:  Next, we will enter the Return on investment (ROI)% formula in cell B5.
  • Step 6: The complete formula is =(B3-B2)/B2 in cell B5.
  • Step 7: Press Enter key. We can see the result in cell B5.
 Example 1 - Step 7

Example #2

The succeeding example depicts the investment flow on shares, and we will calculate the Return on investment (ROI) using the Formula.

In the table,

  • Columns A and B contains the particulars and the amount.
  • Cells B6 and B7 contains the ROI output and the ROI% Output.
Calculating Investment Return in Excel - Example 2

The steps to evaluate the cashflows by the Return on investment (ROI) are as follows:

  • Step 1: Select the cell where we will enter the formula and calculate the result. The selected cell, in this case, is cells B6 & B7.
  • Step 2:  Next, we will enter the Return on investment (ROI) formula in cell B6.
  • Step 3: The complete formula is =B5-B3 in cell B4.
  • Step 4: Next, press the Enter key. Cell B6 shows the result.
Example 2 - Step 4
  • Step 5:  Next, we will enter the Return on investment (ROI)% formula in cell B7.
  • Step 6: The complete formula is =(B5-B3)/B3 in cell B7.
  • Step 7: Press the Enter key. We can see the result in cell B7.
Example 2 - Step 7

Example #3 – Calculating Annualized Return On Investment

The succeeding example depicts the investment flow and earning profit annually, and we will calculate the Annualized Return on investment (ROI) using the Formulas.

In the table,

  • Columns A and B contains the particulars and the amount.
  • Cells B5, B6, B7 and B8 shows the ROI Output, the Days Invested Output, the ROI% Output and the Annualized Return Output, respectively.
Calculating Investment Return in Excel - Example 3

The steps to evaluate the cashflows by the Return on investment (ROI) are as follows:

  • Step 1: Select the cell where we will enter the formula and calculate the result. The selected cell, in this case, is cells B5, B6, B7 & B8.
  • Step 2:  Next, we will enter the Return on investment (ROI) formula in cell B5.
  • Step 3: The complete formula is =B4 – B3 in cell B5.
  • Step 4: Next, press the Enter key. The result is obtained in cell B5 as shown in the image below.
Example 3 - Step 4
  • Step 5:  Next, we will enter the formula calculating Days Invested in cell B6.
  • Step 6: The complete formula is =TODAY () – B2 in cell B6.
  • Step 7: Press Enter key. The result is returned in cell B6 as 117.
Example 3 - Step 7
  • Step 8:  Next, we will enter the Return on investment (ROI)% formula in cell B7.
  • Step 9: The complete formula is = (B4 – B3) / B3 in cell B7.
  • Step 10: Next, press Enter key. The result is returned in cell B7 as shown in the image below.
Calculating Investment Return in Excel - Example 3 - Step 10
  • Step 11:  Next, we will enter the Annualized Return on Investment (ROI)% formula in cell B8.
  • Step 12: The complete formula is = ((B3 + B5) / B3) ^ (365 / B6) -1 in cell B8.
  • Step 13: Next, press the Enter key. The result is obtained in cell B8 as shown in the image below.
Calculating Investment Return in Excel - Example 3 - Step 13

Important Things To Note

  • Calculating Investment Returns (ROI) is the traditional method in Excel.
  • The annual return on investment (ROI) is calculated based on the investment’s starting and end dates. This metric is used to determine the profitability of an investment over a specific period.
  • It is a crucial tool for investors to evaluate the performance of their investments and make informed decisions about future investments. 
  • Return on Investment value can be calculated from many different statistical methods.

Frequently Asked Questions (FAQs)

1. What are the benefits of calculating Return on investment (ROI)?

The benefits of calculating Return on investment (ROI) are;
Make Investment decisions
Evaluate Investment efficiency
Determine an investment’s profitability
Compare the total result of different investments
Make easy comparisons
Simplify interpretation

2. What are the limitations of calculating Return on investment (ROI)?

The limitations of calculating Return on investment (ROI) are;
• Disregards Time – The investment yields a high Return on investment (ROI), like we have two investments with the same ROI. The first created yields in two years, while the other took five years to yield the same profit. On evaluating the same ROI, it looks like the investments have the same values, but the first investment was more efficient.
• Susceptible to manipulation – If different people evaluate ROI, they might come to a different conclusion as they use different formulas for calculating ROI. To calculate a more accurate ROI, we can use the true ROI that includes every cost as the investment rises.

3. What information is needed to calculate Return on investment (ROI)?

Information needed to calculate the Return on investment (ROI) is;
Initial date and Investment balance of that date.
The values and date of deposits and withdrawals.
The date of the end of the period and the balance on that date.

Download Template

This article must help understand the Calculating Investment Return in Excel formula and examples. You can download the template here to use it instantly.

This has been a guide to Calculating Investment Return in Excel. Here we calculate Return on Investment, annual ROI & ROI %, examples & downloadable template. You can learn more from the following articles –

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *