J
JamesG
I am trying to create a simple way of calculating the required fina
payment needed to set a stream of uneven cash flows equal to a
inputted IRR. I have created a long manual formula for doing this, bu
it is limited to 8 or so payments before Excel is unable to calculat
the result. For example, let's assume I receive (pay) the followin
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
given IRR. Let's say 15% in this scenario. If I put those dates i
order across row 1 (so that 9/30/04 corresponds to A1 and 12/31/07 t
G1) and put the cash flows in order across row 2, and the discoun
factor (1+15%) in A3, I can solve this problem with the followin
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. Exce
will also stop calculating a result once I get above 8 or 9 payments.
Does anyone have suggestion for a simpler formula (or built i
function) that will solve this? In particular, one that can easily ad
additional payments and can accomodate an unlimited number of payment
(or rather, limited by the cells in Excel).
I notice that this equation can be rewritten as a geometic series, bu
I don't know any way to express this in Excel. I am trying to avoi
using a built-in formula or macros, as this file must be sent aroun
and I do not want to deal with security setting issues.
Any help is much appreciated. Thanks in advance
payment needed to set a stream of uneven cash flows equal to a
inputted IRR. I have created a long manual formula for doing this, bu
it is limited to 8 or so payments before Excel is unable to calculat
the result. For example, let's assume I receive (pay) the followin
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
given IRR. Let's say 15% in this scenario. If I put those dates i
order across row 1 (so that 9/30/04 corresponds to A1 and 12/31/07 t
G1) and put the cash flows in order across row 2, and the discoun
factor (1+15%) in A3, I can solve this problem with the followin
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. Exce
will also stop calculating a result once I get above 8 or 9 payments.
Does anyone have suggestion for a simpler formula (or built i
function) that will solve this? In particular, one that can easily ad
additional payments and can accomodate an unlimited number of payment
(or rather, limited by the cells in Excel).
I notice that this equation can be rewritten as a geometic series, bu
I don't know any way to express this in Excel. I am trying to avoi
using a built-in formula or macros, as this file must be sent aroun
and I do not want to deal with security setting issues.
Any help is much appreciated. Thanks in advance