"Fred Smith" wrote:

=IRR(L6:L7,P7) will not work. Excel needs all your cash flows in one range.
Although the range can span more than one column, it must be contiguous. It
looks like you will have to reformat your data or copy it to another column to
get your IRR.

I also don't see what value you're getting out of calculating the IRR of the
*difference* in premiums. Why not calculate the IRR of each policy separately?

--
Regards,
Fred


"Allan" wrote in message
...


"Anthony D" wrote:

Hi Allan,

IRR can use two columns e.g. IRR(a1:b6) with outgoings in column a and
income in column b.
The easiest case is for the data to be on only one row at a time (outflow or
inflow but not both at the same time).
If there are two values on the same row, it seems that the IRR function
assumes that they occur in two different periods (with the left column first)
rather than as a net flow that occurs in the same period.
If the data actually does have two flows that occur at the same time and are
in the same row (which would be the usual interpretation for two flows on the
same row), the correct result can be obtained by constructing a third column
for the net flows per period (e.g. in c1 = a1 + b1, etc.) and using that new
column for the calculation instead.

Hth
Anthony

"Allan" wrote:

How to calculate the IRR when the outgos are in one column & the income in
another?

In my case I have two life insurance polices, one costing more than the
other so there is an additional cost for the second policy. With this
additional cost there is an increase in the income (cash value) each year .
My premium is in column L and shows the annual difference in the premiums. My
cash value is in column P.

For example,in year one the difference in premium is $7708 and the
difference in the cash value is $16966. In year two the difference in
premiums is $6530 and the difference in cash value is $20699.

I think my formula should be =IRR(L6:L7,P7) where the two outflows are in
column L and the inflow is in column P.

Does this make any sense?


Thank you for your response. I am trying to make a case financailly for spending additional money on a similar life insurance poilcy that has higher cash values and higher premiums. IN order to do so, I am attempting to evaluate teh additional premium paid each year againxt teh increase in eth cash value from one year to the other.

Also, because I am using dividends to reduce the premium ,the annual
difference in cash flow is going down each year...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..