Calculating Investment Return In Excel

Every business needs an investment amount to earn something out of it. And any amount earned more than the investment is the profit, i.e., the Return on Investment or ROI. The key factor in investing is to know if the return on investment is good to take calculated risks on future investments.

For example, assume you bought shares worth ₹1.5 million. Then, after two months, you sold it for ₹2 million. In this case, ROI is 0.5 million for an investment of ₹1.5 million, and the return on investment percentage is 33.33%.

ROI is the most popular concept in the finance industry. It is the returns gained from the initial investment made. If the ROI is more than the initial investment, then that indicates that the company or the concern has produced a profit.

Like this, we can calculate the investment return (ROI) in Excel based on the numbers given.

Excel doesn’t have an inbuilt formula to calculate ROI.

• The formula to calculate the ROI is shown below.

“ROI = Total Return – Initial Investment”.

• The formula to calculate the ROI percentage is shown below.

“ROI% = Total Return – Initial Investment / Initial Investment * 100”.

So, using the above two formulas, we can calculate the ROI.

We will consider examples to Calculating Investment Returns in Excel using the above-mentioned formulas.

Example #1

Mr. A bought the property in Jan 2015 for ₹3,50,000. And after 3 years, in Jan 2018, he sold the same property for ₹6,00,000. So, calculate the ROI for Mr. A from this investment.

From this info, first, enter all these things into the Excel worksheet to conduct the ROI calculation.

We will apply the formula mentioned above to calculate investment return in Excel. But first, we will calculate the ROI value.

First, select cell B2, and enter the formula =B3, i.e., the “Sold Value”.

Now, select the investment value cell B2. So, the formula is =B3-B2.

Finally, press “Enter”. Therefore, the ROI for Mr. A is ₹2.5 lakhs, as shown below.

Similarly, to calculate the ROI %, select cell B5, enter the formula =(B3-B2)/B2, and press “Enter”.

The output is shown above. Mr. A has got 71.43% as ROI after 3 years for investing ₹3.5 lakhs.

Example #2

Mr. A, on 15th Jan 2019, bought 150 shares for ₹20 each. On 31st Aug 2019, he sold all 150 shares for ₹30 each. So, calculate his ROI.

From this detail, first, we need to calculate the total cost incurred to buy the 150 shares, so find this value by multiplying the per-share value by the number of shares.

Similarly, we will calculate the sold value by multiplying the no. of shares by the selling price per share.

Now we have “Investment Value” and “Investment Sold Value” from these two pieces of information. Let us calculate ROI.

ROI will be: -

ROI% will be: -

So, Mr. A has earned a 50% ROI.

Example #3 - Calculating Annualized Return on Investment

In the above example, we have seen how to calculate investment return in Excel. Still, one of the problems is that it does not consider the period for the investment.

For example, an ROI percentage of 50% earned in 50 days is the same as making the same in 15 days, but 15 days is a short period, so this is a better option. It is one of the limitations of the traditional ROI formula, but we can overcome this by using the annualized ROI formula.

The Annualized ROI formula is,

“Annualized ROI = – 1”

We will calculate the number of yearsby considering the “Investment Date” deducted by the “Sold Date” and dividing the number of days by 365.

Let us take the “Example 2” scenario only for this example.

We will apply the formula to get the annualized ROI percentage as shown below:

Then, we will press the “Enter” key to get the result.

So, the ROI % for the period from 15th Jan 2019 to 31st Aug 2019 is worth 91.38% when considering the period involved in the investment.

• It is the common Excel method to calculate the investment returns (ROI).
• We use the annualized ROI by considering the periods involved or time taken from the start to the end of investment.
• In statistics, there are different methods to measure the ROI value.