IRR Calculations

[Allan]

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

 

[Anthony D]

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]

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

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?

 

[Fred Smith]

=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?

 

[Allan]

=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





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..

 

[joeu2004]

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.