Calculating Investment Return in Excel (Table of Contents)
Excel Calculating Investment Return
Every business needs investment to earn something out of the business and whatever is earned more than the investment is treated as “Return on Investment” (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 2 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.
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.
Example #1
Mr. A has 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 in excel worksheet to conduct the ROI calculation.
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 15th Jan 2019 has bought 150 shares for Rs. 20 each and on 31st 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 total cost occurred 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 traditional ROI formula but this can be overcome by using annualized ROI formula.
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 enter key to get the result.
So, ROI % for the time period from 15th Jan 2019 to 31st 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.
Recommended Articles
This has been a guide to calculating investment returns in excel. Here we discuss the calculation of Traditional and annualized Return on Investment (ROI) along with examples and explanation. You can learn more about excel from the following articles –
- Examples to Compare Two Columns for Matches
- Excel MINVERSE Function
- Formula of Percentage Change in Excel
- How to Forecast in Excel?
- How to Program in Excel
- List of Top Excel Hacks
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
