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.



[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Cash flow[/TD]
[TD]XIRR[/TD]
[/TR]
[TR]
[TD]31/01/2016[/TD]
[TD]-10,000,000[/TD]
[TD]#N/A[/TD]
[/TR]
[TR]
[TD]29/02/2016[/TD]
[TD][/TD]
[TD]#NUM[/TD]
[/TR]
[TR]
[TD]31/03/2016[/TD]
[TD][/TD]
[TD]#NUM[/TD]
[/TR]
[TR]
[TD]30/04/2014[/TD]
[TD]2,00,000[/TD]
[TD]-100%[/TD]
[/TR]
[TR]
[TD]31/05/2016[/TD]
[TD][/TD]
[TD]-100%[/TD]
[/TR]
[TR]
[TD]30/06/2016[/TD]
[TD]5,000,000[/TD]
[TD]-62%[/TD]
[/TR]
[TR]
[TD]31/07/2016[/TD]
[TD][/TD]
[TD]-62%[/TD]
[/TR]
[TR]
[TD]31/08/2016[/TD]
[TD]1,000,000[/TD]
[TD]-43%[/TD]
[/TR]
[TR]
[TD]30/09/2016[/TD]
[TD][/TD]
[TD]-43%[/TD]
[/TR]
[TR]
[TD]31/10/2016[/TD]
[TD]5,000,000[/TD]
[TD]65%[/TD]
[/TR]
[TR]
[TD]30/11/2016[/TD]
[TD][/TD]
[TD]65%[/TD]
[/TR]
[TR]
[TD]31/12/2016[/TD]
[TD][/TD]
[TD]65%[/TD]
[/TR]
[TR]
[TD]31/01/2017[/TD]
[TD]5,000,000[/TD]
[TD]155%[/TD]
[/TR]
[TR]
[TD]28/02/2017[/TD]
[TD][/TD]
[TD]155%[/TD]
[/TR]
[TR]
[TD]31/03/2017[/TD]
[TD][/TD]
[TD]155%[/TD]
[/TR]
</tbody>[/TABLE]

 

[shg]

Welcome to the board.

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td][/td][td="bgcolor:#F3F3F3"]

Goal​

[/td][td]

15%​

[/td][td="bgcolor:#909090"][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td="bgcolor:#F3F3F3"]

Date​

[/td][td="bgcolor:#F3F3F3"]

Amt​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

31 Jan 2016​

[/td][td]

(10,000,000)​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

30 Apr 2016​

[/td][td]

200,000​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

30 Jun 2016​

[/td][td]

5,000,000​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]

31 Aug 2016​

[/td][td]

1,000,000​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]

31 Oct 2016​

[/td][td="bgcolor:#E5E5E5"]

4,626,832​

[/td][td][/td][td]B7: =-XNPV(C1, B3:B6, A3:A6) *(1+C1)^((A7-A3)/365)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td][/td][td="bgcolor:#E5E5E5"]

15.000%​

[/td][td][/td][td]B8: =XIRR(B$3:B7, A$3:A7)[/td][/tr]
[/table]

 

[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.