How to calculate return in Excel

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.

Table of Contents Show

What is the Return on Investment (ROI)?
Examples of Calculating Return on Investment (ROI)
Things to Remember About Excel Calculating Investment Returns
Recommended Articles
How do I calculate rate of return in Excel?
How do you calculate the return?
How do I calculate percentage return?

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 the 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 your website, templates, etc, Please provide us with an attribution linkArticle Link to be Hyperlinked
For eg:
Source: Calculating Investment Return in Excel (wallstreetmojo.com)

Examples of Calculating Return on Investment (ROI)

Below are examples of calculating investment return 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 the 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 with 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 yearsby considering “Investment Date” deducted by “Sold Date” and divide 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 we consider the period involved in the investment.

Things to Remember About Excel Calculating Investment Returns

It is the traditional method of calculating investment returns(ROI) in Excel.
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.

How do I calculate rate of return in Excel?

Rate of Return = (Current Value – Original Value) * 100 / Original Value.

Rate of Return = (Current Value – Original Value) * 100 / Original Value..

Rate of Return Google = (2800 – 2000) * 100 / 2000..

Rate of Return Google = 800 * 100 / 2000..

Rate of Return Google = 40%.

How do you calculate the return?

Key Takeaways. Return on investment (ROI) is an approximate measure of an investment's profitability. ROI is calculated by subtracting the initial cost of the investment from its final value, then dividing this new number by the cost of the investment, and, finally, multiplying it by 100.

How do I calculate percentage return?

Take the selling price and subtract the initial purchase price. The result is the gain or loss. Take the gain or loss from the investment and divide it by the original amount or purchase price of the investment. Finally, multiply the result by 100 to arrive at the percentage change in the investment.