Year or ROI

[caroline]

hello,
I am doing a cash flow and NPV calculation. so far so good....
However I also want to find out the year of my return of investment (i.e.
when the cash flow turns from a Negative to a Positive). Any idea how I could
do this easily?
thanks

 

[Fred Smith]

Just do a running balance or your cash flows. That will tell you when it
turns from -ve to +ve.

Regards,
Fred.

 

[Niek Otten]

Hi Caroline,

Look in HELP for the NPER() function
Or, even better, look at this article, whicht explains all these related
functions:

http://office.microsoft.com/en-us/excel/HA011117451033.aspx

 

[caroline]

Thanks but this is not what I am looking for.
My cash flow changes all the time according to the scenario I run, so I need
a formula to give me the year of ROI

 

[joeu2004]

Thanks but this is not what I am looking for.
My cash flow changes all the time according to
the scenario I run, so I need a formula to give
me the year of ROI

It might not be what you are "looking for", but if your cash flow
"changes all the time", I cannot imagine any other way to do it.

Perhaps if you offered some specifics, we could be more helpful. For
example, if your cash flow simulation is driven by a mathematical
relationship, there might be a solution. But you would need to let us
know what that mathematical relationship is.

NPER is useful only if your cash flow is the same amount at regular
intervals, or if you have only one date for investment and one date
for redemption. But in that case, you could use PV instead of NPV in
the first place.


----- original posting -----

 

[caroline]

Thanks, but this is not exactly what I am looking for
My NPV is calculated as follow:
=NPV($H$115,$H$109:OFFSET($H$109,0,H108-$H$108))
where $H$115 is the rate
line 109 is the cash flow for each year and 108 are the years
and this formula is dragged for 20 years. I need to see the NPV for 20 years
in case the project stops.

But I also need to display the year of ROI.
I hope that I am clear, not sure
thanks any way

 

[Fred Smith]

So what you want is when the sum of H109:?109 is greater than zero, right?
You want to know at which column this sum turns positive.

I would post again with exactly that question. Don't talk about ROI or NPV,
because that will just confuse people. In this case, it's a straight sum,
and has nothing to do with interest rate calculations. You want to know when
you get your return *of* investment, not the return on the investment.

I'm sure there's an array formula which will give you an answer, but that's
outside my area of expertise.

Regards,
Fred.

Thanks, but this is not exactly what I am looking for
My NPV is calculated as follow:
=NPV($H$115,$H$109:OFFSET($H$109,0,H108-$H$108))
where $H$115 is the rate
line 109 is the cash flow for each year and 108 are the years
and this formula is dragged for 20 years. I need to see the NPV for 20
years
in case the project stops.

But I also need to display the year of ROI.
I hope that I am clear, not sure
thanks any way

 

[joeu2004]

My NPV is calculated as follow:
=NPV($H$115,$H$109:OFFSET($H$109,0,H108-$H$108))
where $H$115 is the rate line 109 is the cash flow
for each year and 108 are the years and this formula
is dragged for 20 years.
[....]
But I also need to display the year of ROI.

So I think you are interested in the first year in which the NPV
changes sign, as Fred said. In your case, that would be:

=offset(H108,0,sumproduct(--(sign(H110)=sign(H110:AA110))))

The SUMPRODUCT expression counts the number of NPVs with the same
sign. I assume the NPVs are in H110:AA110; substitute your own range,
if different. The OFFSET expression returns the reference to the cell
with year in row 108.

PS ....

I believe you can simplify your NPV formula as follows:

=NPV($H$115,$H$109:H109)

The H109 will change automagically as you copy the formula to the
right.

Also, are you aware that Excel NPV discounts the first cash flow
(H109)? If that's what you want, fine. Otherwise, you might want:

H110: =H109
I110: =$H$109+NPV($H$115,$I$109:I109)

You can copy I110 to the right. (Again, substitute your own range for
my H110, I110, etc.)

HTH.


----- original posting -----

 

[joeu2004]

PPS....

Also, are you aware that Excel NPV discounts the first cash flow
(H109)?  If that's what you want, fine.  Otherwise, you might want:
H110:  =H109
I110:  =$H$109+NPV($H$115,$I$109:I109)
You can copy I110 to the right.  (Again, substitute your own range
for my H110, I110, etc.)

Or dispense with Excel NPV altogether and, instead, use the following
formula in H110 and copy to the right:

=sumproduct($H$109:H109/(1+$H$115)^(column($H$109:H109)-column($H
$109)))

For an explanation, see the NPV formula on the Excel Help page for the
NPV function.