Excel Calculating Investment Return

Every business needs investment to earn something out of business; whatever is made more than the investment is treated as ROI. Therefore, every business or investment motive is to return on investment and determine the return on investment percentage. The key factor in investing is to know if the return on investment is good to take calculated risks on future investments. This article will take you through calculating investment return in the Excel model.

For example, suppose you need to calculate an accurate rate of return without any complex Maths. Then, Excel Return on Investment(ROI) can be beneficial for calculating the benefits you may get as an investor compared to the cost spent on investment.

What is the Return on Investment (ROI)?

ROI is the most popular concept in the finance industry. It is the returns gained from the investment made. 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%.

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

To calculate the ROI, below is the formula.

ROI = Total Return – Initial Investment

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

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

You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: Calculating Investment Return in Excel (wallstreetmojo.com)

Examples of Calculating Return on Investment (ROI)

Below are examples of calculating investment returns in Excel.

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, we must select the “Sold Value” by selecting cell B3.

Now, we will select the investment value cell B2.

So, the ROI for Mr. A is ₹2.5 lakhs.

Similarly, to calculate the ROI %, we can apply the following formula.

So, Mr. A, for investing ₹3.5 lakhs, has got 71.43% as ROI after 3 years.

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 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.

Annualized ROI = [(Selling Value / Investment Value) ^ (1 / Number of Years)] – 1

We will calculate the number of years by 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, ROI % for the period from 15th Jan 2019 to 31st Aug 2019 is worth 91.38% when considering the period involved in the investment.

Things to Remember About Excel Calculating Investment Returns

  • It is the traditional Excel method of calculating investment returns (ROI).We took annualized ROI into consideration of periods involved from starting date to the end date of investment.In statistics, there are different methods to measure the ROI value.

This article is a guide to Calculating Investment Return in Excel. Here, we discuss how to calculate investment return in Excel, along with examples. You can learn more about Excel from the following articles: –

  • Investment CalculatorBrownfield InvestmentFormula for Abnormal ReturnExamples to Compare Two Columns for MatchesTANH Function in Excel