EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Maths Function in Excel Calculating Investment Return In Excel
Secondary Sidebar
Excel Functions
  • Maths Function in Excel
    • Calculating Investment Return In Excel
    • Sumif Function Examples
    • MROUND in Excel
    • SUM Cells in Excel
    • MMULT in Excel
    • MINVERSE in Excel
    • Trunc in Excel
    • SUMIF with Text
    • COS Function in Excel
    • RANDBETWEEN in Excel
    • Excel SUMIF with OR
    • Excel DEGREES Function
    • Calculations in Excel
    • Sumif Between Two Dates
    • Calculate Income Tax in Excel
    • PRODUCT Function in Excel
    • How to Sum Multiple Rows in Excel
    • Matrix in Excel
    • ROUND Formula in Excel
    • How to SUM in Excel
    • SUMPRODUCT Formula in Excel
    • Excel Running Total
    • Excel Rounding
    • Excel Exponents
    • Excel SIN Function
    • Excel Square Root Function
    • Excel ROUNDUP Function
    • ROUND Excel Function
    • Excel ROUNDDOWN Function
    • ODD Excel Function
    • SUMPRODUCT Function in Excel
    • AGGREGATE Function in Excel
    • ABS Function in Excel
    • Excel TAN Function
    • COMBIN Function in Excel
    • CEILING Function in Excel
    • INT Excel Function
    • SUBTOTAL Function in Excel
    • LN Function in Excel
    • Excel RAND Function
    • TANH Function in Excel
    • SUMIF Function in Excel
    • SUM Function in Excel
    • POWER Function in Excel
    • EVEN Function in Excel
    • LOG Function in Excel
    • FLOOR Excel Function
  • Excel Functions (12+)
  • Excel Tools (114+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Lookup and Reference Functions in Excel (35+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (24+)
  • Statistical Functions in Excel (59+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • Online EXCEL ADVANCED Certification
  • Excel for HR Training

Calculating Investment Return In Excel

calculating return in excel 1

Calculating Investment Return In Excel

Investment return or majorly called Return on Investment (in short ROI) is the basic mathematical and financial calculation which we all have done in our earlier times in School. But we have not tried this on Excel. Calculating investment return is the way to find out how much amount or the percentage returns the investor will receive on the basis of the amount is being invested for the given time period. There are different ways to find the investment return or ROI depending upon the type of input we have. Which we will be seeing that in below examples and descriptions below as well.

How to Use Calculating Investment Return In Excel?

Considering the mathematical expression of calculating Return on Investment (ROI), we have different ways to find it. But most widely used method for finding ROI is shown below. Return on Investment can be the amount gained from the market or it can be the percentage by which we have gained the investment using the below formulas;

ROI = (Final Invested – Investment Amount)/ Investment Amount

ROI % = (Final Invested – Investment Amount)/ Investment Amount x 100

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

Where, Investment Amount – As clear the amount which is an investment into scheme or business

Final Invested – Amount being raised with a certain percentage

The first formula shown is the basic formula by which we can get the amount raised after the investment and another formula will give us the Return on Investment in percentage which is more precise.

Examples

Example #1 – Calculating Investment Return In Excel

In this example, we will see one of the simplest ways to calculate Return on Investment of Investment Return. For this, we have considered simple sets of columns where we will be calculating the ROI for the invested amount in 2017 to the investment return in 2018, 2019, and 2020 respectively as shown below.

All in One Excel VBA Bundle(120+ Courses, 30+ Projects)
Basic ExcelAdvanced ExcelVBA & MacrosExcel Dashboards
Pivot TablesStatistical FunctionsFinancial FunctionsCharts & Graphs
Price
View Courses
120+ Online Courses | 30+ Projects | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (64,768 ratings)

Calculating Investment Return In Excel 1-1

Let’s consider the invested amount in the year 2017 is Rs. 10,000/- for which we got the ending value or final return as shown below in respective year columns.

Calculating Investment Return In Excel 1-2

To calculate the ROI or Investment return of the investment in the year 2018, we need to follow the formula which we have seen above. Here are invested amount will be Rs. 10000/- and the final amount will be Rs. 11100/-. Now let’s put these values into the formula in cell C4 as shown below.

Calculating Investment Return In Excel 1-3

It is better to fix the cell of Current Value because this will be seen in all the other ROIs of 2018-2020 as shown above. Now we will press enter to exit, this will show the ROI.

c3

To convert this, into percentage click on the % symbol from the Home menu tab as shown below.

home

As we can see the final percentage rate of return is coming at 11% for the year 2018.

ROI 1

Now we will drag this formula to other side cells to calculate the ROI of 2019 and 2020. The ROI of 2019 is coming 12% and of 2020 is coming as -11%. Which means that investor is getting profit in the year 2018 and 2019 & loss in the year 2020.

example 1-1

Example #2 – Calculating Investment Return In Excel

In this example, we will see how to calculate ROI and also Annualized return using the below formula. To calculate the annualized return we will be using the below formula.

R= ((Invest Amount + Gain)/Invest Amount)^(365/Days)-1

We have a different set of data as shown below.

example 2-1

First, let’s find the gain in investment which is the difference in Investment and final investment.

example 2-2

We can then calculate the days invested using the TODAY function with Investment date there in cell A2.

today

Now using the formula which we have seen example-1, done as shown below. This would get us the %age ROI.

total return

total return 1

To calculate the Annualized Return which will be our final ROI for the whole year (considering 365 Days). Using the formula, first, add Invested amount and Gain and divide the sum with an Invested amount to calculate the whole ROI. And then give it the power of 365 days of year divide it using the days invested and subtract it with 1 to get the final value.

total return 2

total return 3

Pros of Calculating Investment Return In Excel

  • All the methods shown in the above examples are very easy to implement.
  • Better calculating ROI is from using the mathematical formula which we have seen in the above examples and then implementing them in Excel.

Things to Remember About Calculating Investment Return In Excel

  • ROI and ROI Percentage both are different. ROI is the value or the amount being gained or lost in the time interval and ROI Percentage shows the exact reference value by which change is being observed.
  • Annualized Return helps us to calculate the ROI through the year or better we say at the end of the year.
  • It is better to fix the cells when we are moving and pasting the formula into different cells. It helps in keeping the right formula value in the cell.
  • We have different methods too in statistics which are not bound to the methods which we have seen here in the above examples.

Recommended Articles

This is a guide to Calculating Investment Return In Excel. Here we discuss the definition, How to Use Calculating Investment Return In Excel? excel template. You may also have a look at the following articles to learn more –

  1. Excel Column Total
  2. Excel Shortcut to Edit Cell
  3. CSV Files into Excel
  4. Estimate Template in Excel
Popular Course in this category
Excel Advanced Training (16 Courses, 23+ Projects)
  16 Online Courses |  23 Hands-on Projects |  140+ Hours |  Verifiable Certificate of Completion
4.8
Price

View Course

Related Courses

Excel for HR Training (8 Courses, 10+ Projects)4.9
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more