IRR Assistance

[hardgrafting]

Hi all,

This is my first thread and a query which I hope you can assist me with. I am trying to calculate the exact point in monetary terms that my IRR becomes a certain percentage%. So for example see below, on the 31/10/2016, the payment of 5,000,000 gives me an IRR of 65%, I want to understand how much was needed (proportion of the 5,000,000) that would make the IRR 10% or 15%. I need the way in which this works to be dynamic, so for example if I input a specific IRR rate between -43% and 65% I want to understand how much of the 5,000,000 gets me to that IRR.

Date	Cash flow	XIRR
31/01/2016	-10,000,000	#N/A
29/02/2016		#NUM
31/03/2016		#NUM
30/04/2014	2,00,000	-100%
31/05/2016		-100%
30/06/2016	5,000,000	-62%
31/07/2016		-62%
31/08/2016	1,000,000	-43%
30/09/2016		-43%
31/10/2016	5,000,000	65%
30/11/2016		65%
31/12/2016		65%
31/01/2017	5,000,000	155%
28/02/2017		155%
31/03/2017		155%

<tbody>
</tbody>

 

[shg]

Welcome to the board.

	A​	B​	C​	D​
1​		Goal​	15%​
2​	Date​	Amt​	​	​
3​	31 Jan 2016​	(10,000,000)​
4​	30 Apr 2016​	200,000​
5​	30 Jun 2016​	5,000,000​
6​	31 Aug 2016​	1,000,000​
7​	31 Oct 2016​	4,626,832​		B7: =-XNPV(C1, B3:B6, A3:A6) *(1+C1)^((A7-A3)/365)
8​		15.000%​		B8: =XIRR(B$3:B7, A$3:A7)

 

[hardgrafting]

Thank you very much, this is most helpful.

May I ask if there is way to tweak the formula in order to make it work when the dates in column A are all present and we have no cash flows in a period. For example I want to show that in February 2016 there is no payment, but when I introduce this, the formula does not work.

 

[hardgrafting]

Had a think about this, and I guess I can just put 0 in these cells which have a 0 cash flow.

Thanks again, much appreciated

 

[shg]

You're welcome.