R
RobertH
I'm trying to create a function that calculates the relative payback
period based on an investment's annual net revenue. Here's some
sample values for the net revenue of an investment each year:
Year Net Revenue Cumulative Net Rev.
1 -6 -6
2 -4 -10
3 -3 -13
4 -1 -14
5 1 -13
6 3 -10
7 3 -7
8 3 -4
9 3 -1
10 3 2
11 3 5
So, I know that I could do a simple function to return year in which
Cum Net Rev is greater than 0 (such as
SUM(countif(CumNetRevRange,"<0"),1), but what I'd prefer is to be
able to calculate the fraction of the year in which I break even. That
is, I know that in I break even once I realize 1/3 of my net revenue in
year 10. Assuming that my revenue's accumulate at a constant rate
over the course of the year, how can I write a function that, given the
data above, will yield 10.33 as the payback time?
I'm using Excel 2003, and tried to use the solution posted here
(http://groups.google.com/group/micr...&q=FAME_Payback&rnum=1&hl=en#4f0da517f9e562b4)
but have been unable to get it to work.
All help is appreciated!
Thanks,
Robert
period based on an investment's annual net revenue. Here's some
sample values for the net revenue of an investment each year:
Year Net Revenue Cumulative Net Rev.
1 -6 -6
2 -4 -10
3 -3 -13
4 -1 -14
5 1 -13
6 3 -10
7 3 -7
8 3 -4
9 3 -1
10 3 2
11 3 5
So, I know that I could do a simple function to return year in which
Cum Net Rev is greater than 0 (such as
SUM(countif(CumNetRevRange,"<0"),1), but what I'd prefer is to be
able to calculate the fraction of the year in which I break even. That
is, I know that in I break even once I realize 1/3 of my net revenue in
year 10. Assuming that my revenue's accumulate at a constant rate
over the course of the year, how can I write a function that, given the
data above, will yield 10.33 as the payback time?
I'm using Excel 2003, and tried to use the solution posted here
(http://groups.google.com/group/micr...&q=FAME_Payback&rnum=1&hl=en#4f0da517f9e562b4)
but have been unable to get it to work.
All help is appreciated!
Thanks,
Robert