Calculating Investment Return in Excel (Step by Step Examples)

Excel Calculating Investment Return

Every business needs investment to earn something out of business, and whatever is earned more than the investment is treated as “ROI.” Every business or every investment motive is to return on investment, and find out what the return on investment percentage is; the key factor in making the investment is to know if the return on investment is good to take calculated risks on future investments. In this article, we will take you through how to perform the calculation of investment return in the excel model.

What is the Return on Investment (ROI)?

ROI is the most popular concept in the finance industry; ROI is the returns gained from the investment made. For example, assume you bought shares worth Rs. 1.5 million, and after two months, you sold it for Rs. 2 million, and in this case, ROI is 0.5 million for the investment of Rs. 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.

Examples of Calculating Return on Investment (ROI)

Below are the examples of calculating investment return in excel.

You can download this Calculating Investment Return Excel Template here – Calculating Investment Return Excel Template

Example #1

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

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

Popular Course in this category

All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion

View Course

Apply the above-mentioned formula to calculate investment return in excel. First, we will calculate the ROI value.

First, select the “Sold Value” by selecting the cell B3.

Now select the investment value cell B2.

So, the ROI for Mr. A is 2.5 L.

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

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

Example #2

Mr. A, on 15^th Jan 2019, has bought 150 shares for Rs. 20 each, and on 31^st Aug 2019, he has sold all the 150 shares for Rs. 30 each. So, calculate his ROI.

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

Now similarly calculate the sold value by multiplying no., of shares with selling price per share.

Ok, now we have “Investment Value” and “Investment Sold Value” from these two pieces of information’s let’s 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, but one of the problems is it doesn’t take into consideration of time period for the investment.

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

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

The number of Years will be calculated by taking into consideration “Investment Date” deducted by “Sold Date” and divide the number of days by 365.

Let’s take the “Example 2” scenario only for this example as well.

Apply the formula as shown below to get the annualized ROI percentage.

Hit the enter key to get the result.

So, ROI % for the time period from 15^th Jan 2019 to 31^st Aug 2019 is worth 91.38% when we take into consideration of time period involved in the investment.

Things to Remember About Excel Calculating Investment Returns

This is the traditional method of calculating investment returns(ROI) in excel.
Annualized ROI was taken into consideration of time periods involved from starting date to end date of investment.
In statistics, there are different methods to measures the ROI value.

19th/20th September 2020	Saturday - Sunday	9 am IST to 5 pm IST
26th/27th September 2020	Saturday - Sunday	9 am IST to 5 pm IST

Calculating Investment Return in Excel