Solving for a given monthly IRR by using the XNPV

[mkw4949]

So here is what I'm trying to do. I'm working on a waterfall income allocation and the allocation scheme changes once one of the partners reaches a target IRR which is calculated on a monthly basis. What this means is that income allocation scheme will generally change during a period meaning, for example, that the first $100 will be allocated differently then the last $100 in a given month. This means making sure that income is allocated, according to the first scheme, only until a given IRR threshold is reached (say 20% IRR minimum return). What it requires is that you solve for a given IRR. In other words, based on past cash flows I need to do solve for the additional distribution that will get a given partner to hit their minimum return of 20%. Not a penny more or less. I’ve actually solved this problem but only with a monthly IRR calculation that is suspect at best. In the past I have calculated the annualized monthly IRR by simply multiplying the standard IRR by 12 i.e.

=IRR(A1:A120)*12

. Then I would solve for the necessary distribution in a given period by backing into it using the NPV calc. I used the formula:
=NPV(X/12,$A1:A40)*(1+X/12)^N where:
X = The Target IRR
N = the period number

The above formula worked however it is based on a flawed IRR calculation. I’m trying to get to the same place but by using the XIRR and XNPV functions. I’m getting close but not close enough. Does anyone out there have any suggestions. If you’d like to help but cant make sense of my crypt writing please feel free to e-mail me. Thank.