IRR Calculation

[rmatenchuk]

HI,

I am trying to calculate the last value in an IRR casfhflow to make the total cash flow = 15% IRR

I have a string of numbers, -100, -10 , 10, 10, 15, X. I am trying to find the value of X that makes the cashlow = 15% IRR

Can anyone help with a formula?

Thanks,

Ryan

 

[Andrew Poulsom]

I think you would need Goal Seek for that.

 

[rmatenchuk]

I imagine that one could use an NPV/ IRR forumal for it, which is what I am hoping for someone to suggest.

I want to keep it dynamic, where a goal seek would make it static.

Ryan

 

[Richard Schollar]

Yep GoalSeek would be my suggestion because as far as I can see you will end up with a circular reference otherwise.

 

[Richard Schollar]

OK, I was wrong - here's one way:

Excel Workbook
	A	B	C	D
1	Net Cash Flow	Period	Discounted @ IRR	IRR
2	-100	0	-100.0000	15%
3	-10	1	-8.6957
4	10	2	7.5614
5	10	3	6.5752
6	15	4	8.5763
7	172.942	5	85.9828	0
8
9
10
11	A6 needs to be:	172.942
Sheet1

Excel 2002

Cell Formulas
Range	Formula
C2	=A2/(1+$D$2)^B2
B11	=-SUMPRODUCT($A$2:$A$6/(1+$D$2)^$B$2:$B$6)*(1+$D$2)^$B$7
D7	=SUM(C2:C7)

 

[brettdj]

Or working backwards using NPV
=(100-NPV(15%,-10,10,10,15))*(1+15%)^5
=172.94

Cheers

Dave

 

[rmatenchuk]

Thank you both very much!!

Ryan

 

[BenJG]

Does anyone have any suggestions as to how this can be done when the cash flows do not occur annually? I have a series of cash flows that occur at random intervals over a number of years. I need to determine what the value of the final cash flow needs to be to deliver an IRR of 8%. I can do this using XIRR and goal seek, however this is a static solution, which is what I have been trying to avoid.

Any suggestions?

Thanks

 

[brettdj]

Use my construct above but use XNPV (which accepts dates) rather than NPV

Cheers

Dave

 

[Richard Schollar]

And here's mine suitably modified:

Excel Workbook
	A	B	C	D	E
1	Net Cash Flow	Date	Days from day 0	Discounted @ IRR	IRR
2	-100	01/01/2005	0	-100	8%
3	-10	25/09/2007	997	-8.1041
4	10	13/06/2008	1259	7.66851
5	10	14/08/2008	1321	7.56891
6	15	02/03/2009	1521	10.8845
7	126.818	01/09/2010	2069	81.9821	0
8
9
10
11	A7needs to be:	126.8181279
Sheet2

Excel 2010

Cell Formulas
Range	Formula
C2	=B2-$B$2
D2	=A2/(1+$E$2)^(C2/365)
B11	=-SUMPRODUCT($A$2:$A$6/(1+$E$2)^($C$2:$C$6/365))*(1+$E$2)^($C$7/365)
E7	=SUM(D2:D7)