Complicated IRR problem

I am trying to create a simple way of calculating the required final payment needed to set a stream of uneven cash flows equal to an inputted IRR. I have created a long manual formula for doing this, but it is limited to 8 or so payments before Excel is unable to calculate the result. For example, let's assume I receive (pay) the following cash flows at the following dates:

9/30/04 ($100)
3/31/05 $10
4/23/05 $10
5/20/06 $10
8/25/06 $10
9/30/07 $10
12/31/07 ????

I want to create a formula that will give me the value of ???? at a given IRR. Let's say 15% in this scenario. If I put those dates in order across row 1 (so that 9/30/04 corresponds to A1 and 12/31/07 to G1) and put the cash flows in order across row 2, and the discount factor (1+15%) in A3, I can solve this problem with the following equation:

=(-A2-(B2/A3^((B1-A1)/365))-(C2/A3^((C1-A1)/365))-(D2/A3^((D1-A1)/365))-(E2/A3^((E1-A1)/365))-(F2/A3^((F1-A1)/365)))*A3^((G1-A1)/365)

The result here is $93.3, which gives that stream of cash flows a 15% IRR (you can check with the XIRR function). This gets the job done, but, as I mentioned, it is not possible to easily add payments. Excel will also stop calculating a result once I get above 8 or 9 payments. Does anyone have suggestion for a simpler formula (or built in function) that will solve this? In particular, one that can easily add additional payments and can accomodate an unlimited number of payments (or rather, limited by the cells in Excel).

I notice that this equation can be rewritten as a geometic series, but I don't know any way to express this in Excel. I am trying to avoid using a built-in formula or macros, as this file must be sent around and I do not want to deal with security setting issues.

Any help is much appreciated. Thanks in advance.

In B3, enter this formula:

=-(B2/$A$3^((B1-$A$1)/365))

Copy that to the right.

In A4, enter this formula:

=(-A2+SUM(B3:F3))*$A$3^((G1-$A$1)/365)

This gives the same result as your formula.

I think you can now easily extend you range by copying the formula in B3 and
adapting the formula in A4.

--
Kind regards,

Niek Otten

"JamesG" <[email protected]> wrote in
message news:[email protected]...
>
> I am trying to create a simple way of calculating the required final
> payment needed to set a stream of uneven cash flows equal to an
> inputted IRR. I have created a long manual formula for doing this, but
> it is limited to 8 or so payments before Excel is unable to calculate
> the result. For example, let's assume I receive (pay) the following
> cash flows at the following dates:
>
> 9/30/04 ($100)
> 3/31/05 $10
> 4/23/05 $10
> 5/20/06 $10
> 8/25/06 $10
> 9/30/07 $10
> 12/31/07 ????
>
> I want to create a formula that will give me the value of ???? at a
> given IRR. Let's say 15% in this scenario. If I put those dates in
> order across row 1 (so that 9/30/04 corresponds to A1 and 12/31/07 to
> G1) and put the cash flows in order across row 2, and the discount
> factor (1+15%) in A3, I can solve this problem with the following
> equation:
>
> =(-A2-(B2/A3^((B1-A1)/365))-(C2/A3^((C1-A1)/365))-(D2/A3^((D1-A1)/365))-(E2/A3^((E1-A1)/365))-(F2/A3^((F1-A1)/365)))*A3^((G1-A1)/365)
>
> The result here is $93.3, which gives that stream of cash flows a 15%
> IRR (you can check with the XIRR function). This gets the job done,
> but, as I mentioned, it is not possible to easily add payments. Excel
> will also stop calculating a result once I get above 8 or 9 payments.
> Does anyone have suggestion for a simpler formula (or built in
> function) that will solve this? In particular, one that can easily add
> additional payments and can accomodate an unlimited number of payments
> (or rather, limited by the cells in Excel).
>
> I notice that this equation can be rewritten as a geometic series, but
> I don't know any way to express this in Excel. I am trying to avoid
> using a built-in formula or macros, as this file must be sent around
> and I do not want to deal with security setting issues.
>
> Any help is much appreciated. Thanks in advance.
>
>
> --
> JamesG
> ------------------------------------------------------------------------
> JamesG's Profile:
> http://www.excelforum.com/member.php...o&userid=23804
> View this thread: http://www.excelforum.com/showthread...hreadid=516423
>

That was a nice work around. Thank you.