Allan wrote:
so I have a column of
negative numbers starting at-$7708 going to zero over 18 years. In the next
column I have the diference in the cash values that this stream of additional
premiums has created and I am trying toi measue the return each year of the
increaseed cash flow against the increasing cash value...
Annual Total by year
Add Premium Cash Value Increase
-7708 16099
-6609 20598
-6435 25892
-4876 31589
-3997 38792
-3100 48952
-2400 59000
So in year one an additional $7708 creates $16099 of additonal cash value.
In year two the $7708 and the $ 6609 create $20598, etc..

As Fred indicated, these two columns must be adjacent. But as Anthony
pointed out, IRR() will interpret the two columns as consecutive cash
flows, each in a different period, reading leff-to-right and
top-to-bottom.

So you would need to create a column of the __net__ cash flow per
period. Ostensibly, this might be =A3+B3. However, the data above
would result in all positive cash flows. IRR (and common sense)
requires that at least one cash flow is negative (an initial
investment).

My suspicion is: -7708 occurs at the beginning of the year, and 16099
occurs at the end of the year. Therefore, -7708 is the initial cash
flow, and -6609 and 16099 can be combined to create the net cash flow a
year later (close enough!). So you might set up the following
formulas, then copy C4 down:

C3: =A3
C4: =A4+B3

This will create the following net cash flow in C3:C10: -7708, 9490,
14163, 21016, 27592, 35692, 46552, 59000. Then the IRR can be
computed using IRR(C3:C10). Caveat: Sometimes you will need to add
the second "guess" argument.