Calculating Investment Return In Excel

Article byJeevan A Y
Edited bySheeba M
Reviewed byDheeraj Vaidya, CFA, FRM

What Is 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%.

Key Takeaways

  • Investment Return in Excel helps us find the difference between initial investment and future returns.
  • It helps us analyze if the concern is running in profit or loss. Therefore, every business or investment motive is to return on investment and determine the percentage.
  • 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.
  • We can calculate the ROI using the ROI formulas, and with the help of the Annualized ROI formula, we find the ROI w.r.t the investment and return period.

What Is The Return On Investment (ROI)?

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.

ROI Formula

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.

Excel-Calculating-Investment-Return

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

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Examples

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

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

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.

Calculating Investment Return in Excel Example 1

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

Example 1.1

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

Calculating Investment Return in Excel Example 1.2

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

Example 1.3

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

Calculating Investment Return in Excel Example 1.4

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.

Example 2

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.

Calculating Investment Return in Excel Example 2.1

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

Example 1.5

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

Calculating Investment Return in Excel Example 1.6

ROI will be: –

Example 1.7

ROI% will be: –

Calculating Investment Return in Excel Example 1.8

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 = [(Selling Value / Investment Value) ^ (1 / Number of Years)] – 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:

Calculating Investment Return in Excel Example 3

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

Example 3.1

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.

Important Things to Note

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

Frequently Asked Questions (FAQs)

1. What is the purpose of ROI?

ROI, or Return on Investment, is the concept where we use the initial investment amount to start a project or a company to get future profits. The purpose is to calculate the profit, i.e., when we get our invested amount and the extra amount as profit in a specific period, that is the Return on Investment.

2. How to calculate Return on Investment?

We can calculate the Return on Investment and the Return on Investment Percentage using the following formulas,
The formula to calculate the ROI is,
“ROI = Total Return – Initial Investment”.

The formula to calculate the ROI percentage is,
“ROI% = Total Return – Initial Investment / Initial Investment * 100”.

3. What is annualized ROI?

We Calculate Investment Return In Excel traditionally, but it does not consider the period for the investment. To overcome this limitation, we use the Annualized ROI formula.

The Annualized ROI formula is,
“Annualized ROI = [(Selling Value / Investment Value) ^ (1 / Number of Years)] – 1”

This article is a guide to Calculating Investment Return in Excel. Here, we find investment returns from initial amt & profit, examples & downloadable template. You can learn more about Excel from the following articles: –