Calculate Payment to achieve IRR of 10% - No what-if calcs allowed

[malcolmrdj]

I have an entity in a project that receives quarterly but inconsistent cashflows until the entity's IRR = 10%.

The distribution amount is subject to available cash so one quarter's distribution could be as low as 200k or as high as whatever is available subject to 10% irr cap. Entity will always receive 200k at a minimum. Distributions will keep being made until the 10% irr has been achieved.

For example, let's say I am at 11/15/16 in table below. The payments made prior are locked in, the payments made in 2017 are guaranteed. Without using what-if, how can I back into the payment made on 11/15/16 which would create an overall irr of 10%?

Date Amount
11/15/15 ($10,000,000)
02/15/16 $200,000
05/15/16 $700,000
08/15/16 $2,500,000
11/15/16 ?
02/15/17 $200,000
05/15/17 $200,000

 

[joeu2004]

For example, let's say I am at 11/15/16 in table below. The payments made prior are locked in, the payments made in 2017 are guaranteed. Without using what-if, how can I back into the payment made on 11/15/16 which would create an overall irr of 10%?

For your example, the easiest structure might be:

[TABLE="class: grid, width: 265"]
<tbody>[TR]
[TD="align: right"]
[/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD="align: right"]IRR
[/TD]
[TD="align: right"]10.00%
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD="align: right"]11/15/2015[/TD]
[TD="align: right"]-$10,000,000[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD="align: right"]2/15/2016
[/TD]
[TD="align: right"]$200,000[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD="align: right"]5/15/2016[/TD]
[TD="align: right"]$700,000[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD="align: right"]8/15/2016[/TD]
[TD="align: right"]$2,500,000[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD="align: right"]11/15/2016[/TD]
[TD="align: right"]$0
[/TD]
[TD="align: right"]$7,106,779.52
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD="align: right"]2/15/2017[/TD]
[TD="align: right"]$200,000[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD="align: right"]5/15/2017[/TD]
[TD="align: right"]$200,000[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]

The $0 in B7 is a placeholder; it allows us to simplify the formula.

The actual cash flow is calculated in C7 with the following formula:

=-XNPV(B1,B3:B9,A3:A9) * (1+B1)^((A7-A3)/365)

Caveat: Excel XNPV does not allow B1 (IRR) to be negative, for no good reason. If that might be an issue, we can use SUMPRODUCT instead of XNPV.

This paradigm is based on the definition of NPV; see the XNPV help page. We are solving for the missing cash flow.

If there are multiple missing cash flows, we must resort to a what-if method, generally.

 

[shg]

[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:#E5E5E5"]

-70.5663%​

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

10.0000%​

[/td][td]B1 and across: =XIRR(B4:B10, $A4:$A10)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

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

(6,459,022)​

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

0​

[/td][td]B2 and across: =XNPV(10%, B4:B10, $A4:$A10)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

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

Date​

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

Amt​

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

Amt​

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

4​

[/td][td]

11/15/2015​

[/td][td]

(10,000,000)​

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

(10,000,000)​

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

5​

[/td][td]

2/15/2016​

[/td][td]

200,000​

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

200,000​

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

6​

[/td][td]

5/15/2016​

[/td][td]

700,000​

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

700,000​

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

7​

[/td][td]

8/15/2016​

[/td][td]

2,500,000​

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

2,500,000​

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

8​

[/td][td]

11/15/2016​

[/td][td]

0​

[/td][td="bgcolor:#92D050"]

7,106,780​

[/td][td]C8: =-B2*(1+10%)^((A8-A4)/365)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td]

2/15/2017​

[/td][td]

200,000​

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

200,000​

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

10​

[/td][td]

5/15/2017​

[/td][td]

200,000​

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

200,000​

[/td][td][/td][/tr]
[/table]

The formula in C8 is all you need; the rest just demonstrates that it works.

 

[malcolmrdj]

Joeu/shg - many thanks on the quick replies. That did exactly what i needed..I will have to brush up on my financial theory as it has been a while.

thanks,
malcolmrdj

 

[shg]

You're welcome.