how to calculate irr on a life insurance policy (due in 40 minutes)

Sorry, looks like this missed your deadline (homework?). Just type in your cash flows as they occur for each year 1 through 25 then use that range for your input to IRR. I got 9.59%

Originally Posted by Pauleyb

Just type in your cash flows as they occur for each year 1 through 25 then use that range for your input to IRR. I got 9.59%

I am curious to see exactly how you got 9.59%. Even if it is incorrect, I want to see how you derived the number, just for my edification.

For 25 cash flows, I get about 1.1059% using Excel IRR. See the cash flow table below.

[ERRATA] Removed misleading complications due to my misreading of the ending balance specification.

Interesting note.... When using Excel IRR, it is important to write zero for years with no cash flow. If we leave those cells empty, Excel IRR does not count them as cash flows. Thus, Excel IRR would interpret them as 11 equally-spaced cash flows, not 25 or 26. Even then, the (incorrect) IRR is about 2.9135%, still not even close to 9.59% [sic].

[ERRATA] Simplified presentation below.

	A	B	C	D
1	Yr	CF	IRR
2	1	-200000	1.1059%	=IRR(B2:B26)
3	2	-200000
4	3	-200000
5	4	-50000
6	5	-50000
7	6	0
8	7	0
9	8	0
10	9	0
11	10	0
12	11	0
13	12	0
14	13	0
15	14	0
16	15	50000
17	16	50000
18	17	50000
19	18	50000
20	19	50000
21	20	0
22	21	0
23	22	0
24	23	0
25	24	0
26	25	625000

I'm no financial wizard but this is how I see the problem after reading the help file. I think that there is a very large area left open for interpretation....13 or 14 years of unknown activity.

	A	B	C
1	-200000
2	-200000
3	-200000
4	-50000		1.0298%
5	-50000
6	0
7	0
8	0
9	0
10	0
11	0
12	0
13	0
14	0
15	0
16	0
17	0
18	0
19	0
20	50000
21	50000
22	50000
23	50000
24	50000
25	625000

or something like this:

	A	B	C
1	-200000
2	0
3	-200000
4	0		1.0974%
5	-200000
6	0
7	-50000
8	0
9	-50000
10	0
11	0
12	0
13	0
14	0
15	0
16	0
17	0
18	0
19	0
20	50000
21	50000
22	50000
23	50000
24	50000
25	625000

The whole thing didn't make complete sense to me the way it was stated. Why you an investment have years of investment followed by a period of "no action" followed by payments followed by "no action". That year 15 seemed out of place. I couldn't make sense of it so what I posted was the best sense that I could make of the problem. Maybe I did something wrong when I placed the 5 payments at year 15 because I ended up with an error and when I put 0s between the payments. I just re-did it and it worked out to 1%. I must have entered an O instead of a zero.

	A	B	C
1	-200000		1%
2	-200000
3	-200000
4	-50000
5	-50000
6	0
7	0
8	0
9	0
10	0
11	0
12	0
13	0
14	0
15	50000
16	0
17	50000
18	0
19	50000
20	0
21	50000
22	0
23	50000
24	0
25	650000

Lumping the 50000s at row 15 on didn't change the outcome.
As it stands, something tells me that there is an error somewhere...either in the problem statement or the interpretation of it.

Originally Posted by joeu2004

I am curious to see exactly how you got 9.59%. Even if it is incorrect, I want to see how you derived the number, just for my edification.

For 25 cash flows, I get about 1.1059% using Excel IRR. See the cash flow table below.

How did I get 9.59%? By mistyping the initial 3 year $200K deposits as $20k deposits. Whoops, sorry about that. Now that does not look like a great investment.