You are here:

Excel/Excel formula to find ROI period

Advertisement


Question
Excel Question
Excel Question  
Dear Sir,
I am a sales engineer in a European company. First of all, I would like to appreciate and thank you for your such a devotion to help out people and I really salute you for sparing this valuable time to help people and spreading knowledge.
Here is my question:
I am using excel to make feasibility or Return on investment report. Where I have 3 columns as given below,
No. of Month   Month Name   Monthly income    ROI
=Total cost- added monthly earning
(Let total cost is 5000)
1   Jan   1000   4000
2   Feb   2000   2000
3   March   1000   1000
4   April   1500   -500
5   May   1000   -1500

Here as you can see, I have got my money back in 4th month. So I want the excel to automatically mention the ROI period or number of months for ROI.
ROI period for investment of 5000 is    Here I want to show ROI period / No of months taken

So in above cell what formula I should use. Please help me in this regard.
Thanks in advance

Answer
something like this

=INDIRECT("A"&MATCH(0,D1:D48,-1)+1)

would do it, assuming that column D has the ROI data you mentioned, and the month number is in column A

(question found in question pool, indicating original expert was unable or unwilling to answer the question)
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Aidan Heritage

Expertise

I have provided first hand support since `95 for Microsoft Office majoring in Word and Excel - support for all versions from 2 onwards. I'm based in the UK, so please allow for time differences when asking me questions from other parts of the world!

Experience

My background is in the insurance industry and call centre areas, but have been called upon to provide many varied solutions.

Education/Credentials
I'm educated to UK A level standard, but as I left school some 30 years ago that is rather irrelevent - university of life has provided more of a background!

©2016 About.com. All rights reserved.