Iterations Issues

[ryan87500]

I am running a complex circular calcuation for some allocations I have to do. I have to have a circular reference based on the agreement, and there is no way around it. What I am finding though is I get one answer but when I hit F9 I get another answer, I hit it again and it goes back to the first answer.

The other issue is that if I want A2 to equal A1 it wont it will give me some other answer.

Thanks for any help!

 

[shawnhet]

Hi Ryan,

I'm having a hard time seeing where the specific 704b numbers for A & B come from?

It seems to me that 704b=Change in Assets+Distribution, where total 704b=2million and Change in Asset B is 1038129.

Unless there is some constraints on both the 704b column and the A columns that I am not seeing, there are many different ways to solve these equations which may be why you are running into issues that you seem to be.

For instance, if we take 704 for A to be 125K, then from what I could see: Change in A's assets could be 0 in which case distribution of A would be 125K or the Change in A's assets could be -400K in which case the distribution would be -525K. I can't see which of these situations is preferable and why.

Cheers,

 

[ryan87500]

Hi Ryan,

I'm having a hard time seeing where the specific 704b numbers for A & B come from?

It seems to me that 704b=Change in Assets+Distribution, where total 704b=2million and Change in Asset B is 1038129.

Unless there is some constraints on both the 704b column and the A columns that I am not seeing, there are many different ways to solve these equations which may be why you are running into issues that you seem to be.

For instance, if we take 704 for A to be 125K, then from what I could see: Change in A's assets could be 0 in which case distribution of A would be 125K or the Change in A's assets could be -400K in which case the distribution would be -525K. I can't see which of these situations is preferable and why.

Cheers,

The Class A preferred of 1,038,129 is just a formula =FV(0.025,4,0,-C15)-C15, they get a 10% return on their investment each year no matter what the income is.

So what hapens is my 704(b) income of 2 million less the distribution is less than the 1,038,129 so what happens is Partner B gets their 1,038,129 and Partner A gets a decrease in their class B units, so now they have a negative change in capital. The negative change in capital is what I believe is throwing the entire calculation off.

Sorry i don't follow your example above. I am going to post in a separate reply what it looks like if I have enough 704(b) income after distribution to cover my preferred return.

 

[ryan87500]

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th><th>P</th><th>Q</th><th>R</th><th>S</th><th>T</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="border-bottom: 1px solid black;;">Capital Available for Distribution</td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;;"></td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;border-left: 1px solid black;;">Assumptions</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Beginning Capital</td><td style="text-align: right;;"></td><td style="text-align: right;;"> 100,000,000 </td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Net Income</td><td style="text-align: right;;"></td><td style="text-align: right;;"> 8,000,000 </td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="border-left: 1px solid black;;">704(b) Income Before Special Allocations</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"> 10,000,000 </td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">Current Year Distributions</td><td style="text-align: right;;"></td><td style="text-align: right;font-style: italic;background-color: #DBE5F1;;"> (4,000,000)</td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="border-left: 1px solid black;;">Amortization</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"> (2,000,000)</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">Ending Capital</td><td style="text-align: right;;"></td><td style="text-align: right;;"> 104,000,000 </td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="border-left: 1px solid black;;">704(b) Income After Special Allocations</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"> 8,000,000 </td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="border-left: 1px solid black;;">Gross Income</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"> 20,000,000 </td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="border-bottom: 1px solid black;;">Hypothetical Liquidation</td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="border-left: 1px solid black;;">Gross Deduction</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"> (10,000,000)</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="border-left: 1px solid black;;">Taxable Income</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"> 10,000,000 </td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style=";">Class A Preferred</td><td style="text-align: right;;"></td><td style="text-align: right;;"> 1,038,129 </td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style=";">Class A</td><td style="text-align: right;;"></td><td style="text-align: right;;"> 10,000,000 </td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="border-left: 1px solid black;;">Estimated Distribution (40%)</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"> 4,000,000 </td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style=";">Class B</td><td style="text-align: right;;"></td><td style="text-align: right;;"> 92,961,871 </td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style=";">Total Capital</td><td style="text-align: right;;"></td><td style="text-align: right;;"> 104,000,000 </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">If no one is negative all income is allocated based on positive.  If someone is negative tier 1 represents the positive peoples 704(b) net income</td><td style="text-align: center;;">If someone is negative, the negative people get allocated the remainder of gross income based on their respective percentage</td><td style="text-align: center;;">If someone is negative, the negative people get allocated  gross deductions based on their respective percentage</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="border-bottom: 1px solid black;;">Income Allocation to Partners</td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style=";">Partners</td><td style="text-align: right;;"></td><td style="text-align: center;;">Beginning Capital</td><td style="text-align: center;;"></td><td style="text-align: center;;">Class A Preferred</td><td style="text-align: center;;">Class A</td><td style="text-align: center;;">Class B</td><td style="text-align: center;;">Ending Capital</td><td style="text-align: center;;"></td><td style="text-align: center;;">Change in capital</td><td style=";">Current Year Distributions</td><td style=";">704(b) Net Income Allocation</td><td style=";">Positive Percentage</td><td style=";">Negative Percentage</td><td style="text-align: right;;"></td><td style="text-align: center;;">Gross Income Tier 1</td><td style="text-align: center;;">Gross Income Tier 2</td><td style="text-align: center;;">Gross Deduction</td><td style="text-align: center;;">Taxable Income</td><td style="text-align: center;;">Distribution</td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"> 100,000,000 </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style=";">Partner A</td><td style="text-align: right;;"></td><td style="text-align: right;;"> 90,000,000 </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"> 92,961,871 </td><td style="text-align: right;;"> 92,961,871 </td><td style="text-align: right;;"></td><td style="text-align: right;;"> 2,961,871 </td><td style="text-align: right;;"> 2,961,871 </td><td style="text-align: right;;"> 5,923,742 </td><td style="text-align: right;;">74.05%</td><td style="text-align: right;;">0.00%</td><td style="text-align: right;;"></td><td style="text-align: right;;"> 14,809,355 </td><td style="text-align: right;;"> - </td><td style="text-align: right;;"> (7,404,678)</td><td style="text-align: right;;"> 7,404,678 </td><td style="text-align: right;;"> 2,961,871 </td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style=";">Partner B</td><td style="text-align: right;;"></td><td style="text-align: right;;"> 10,000,000 </td><td style="text-align: right;;"></td><td style="text-align: right;;"> 1,038,129 </td><td style="text-align: right;;"> 10,000,000 </td><td style="text-align: right;;"></td><td style="text-align: right;;"> 11,038,129 </td><td style="text-align: right;;"></td><td style="text-align: right;;"> 1,038,129 </td><td style="text-align: right;;"> 1,038,129 </td><td style="text-align: right;;"> 2,076,258 </td><td style="text-align: right;;">25.95%</td><td style="text-align: right;;">0.00%</td><td style="text-align: right;;"></td><td style="text-align: right;;"> 5,190,645 </td><td style="text-align: right;;"> - </td><td style="text-align: right;;"> (2,595,322)</td><td style="text-align: right;;"> 2,595,322 </td><td style="text-align: right;;"> 1,038,129 </td></tr><tr ><td style="color: #161120;text-align: center;">29</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">30</td><td style=";">Total</td><td style="text-align: right;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"> 100,000,000 </td><td style="text-align: right;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"> 1,038,129 </td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"> 10,000,000 </td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"> 92,961,871 </td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"> 104,000,000 </td><td style="text-align: right;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"> 4,000,000 </td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DBE5F1;;"> 4,000,000 </td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"> 8,000,000 </td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">100.00%</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">0.00%</td><td style="text-align: right;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"> 20,000,000 </td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"> - </td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"> (10,000,000)</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"> 10,000,000 </td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DBE5F1;;"> 4,000,000 </td></tr><tr ><td style="color: #161120;text-align: center;">31</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">32</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:18em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">704(b) allocations No Negative</p><br /><br />

 

[shawnhet]

I guess I am not understanding why the totals in 704b are what they are instead of some other numbers. Could A not just say their 704b in your above example is 4000000 and this would change what his change in assets and distribution were? Could he keep 704 the same and increase his distribution to 4000000? If not, why not?

I guess, ultimately, I am curious as to what the problem is with the answers you are currently generating. It seems to me that your problem is set up so that there are many acceptable solutions, so if you are looking for a single correct answer that may be your problem.

Cheers,

 

[ryan87500]

I guess I am not understanding why the totals in 704b are what they are instead of some other numbers. Could A not just say their 704b in your above example is 4000000 and this would change what his change in assets and distribution were? Could he keep 704 the same and increase his distribution to 4000000? If not, why not?

I guess, ultimately, I am curious as to what the problem is with the answers you are currently generating. It seems to me that your problem is set up so that there are many acceptable solutions, so if you are looking for a single correct answer that may be your problem.

Cheers,

Sorry wen you say 704(b) which column or cell are you referring to?

So the reason it is setup this way is the capital account or asset value is what the company is worth and what each partner would get upon liquidiation. The agreement was written in a circular manner, which is the issue, that says each persons share of taxable income is there change in 704(b) plus their share of distributions.

You can't get distributions without knowing taxable income which is differnet then 704(b) income. You can't get their share of taxable income without knowing their "704(b) net income allocation" which takes in account distribution which in turn causes the circular error.

What confuses me is that this only happens when I have a negative change in capital, then all the formulas seem to just fail.

 

[shawnhet]

Ok, I think that that makes things a bit clearer. Correct me if I am wrong here:

So you would have some sort of (independent) measure of the company's hypothetical value, which would establish the total change in assets, correct? Given that, can the value of Class A assets(C15) change? I assume not, but if it can, then can you provide me with details as to how it can change (would this be another plug number or is it calculated somehow).

You say that you cannot get 704b NI alloc without Taxable income, but what does this mean? Your formula posted refers to another sheet so I don't know how it is calculated. Can you describe it for me in words?

Cheers,

 

[ryan87500]

Ok, I think that that makes things a bit clearer. Correct me if I am wrong here:

So you would have some sort of (independent) measure of the company's hypothetical value, which would establish the total change in assets, correct? Given that, can the value of Class A assets(C15) change? I assume not, but if it can, then can you provide me with details as to how it can change (would this be another plug number or is it calculated somehow).

You say that you cannot get 704b NI alloc without Taxable income, but what does this mean? Your formula posted refers to another sheet so I don't know how it is calculated. Can you describe it for me in words?

Cheers,

Sorry for formula that references another sheet is the same sheet. Not sure how that happened must have been clicking around. I updated them to make it easier for you to see.

Gross Income Tier 1: =IF($N$30=0,(M27*$J$11),IF(N27=0,L27,0))
Gross Income Tier 2: =IF($N$30=0,0,(N27*($J$11-$P$30)))
Gross Deduction: =IF($N$30=0,(M27*$J$12),N27*$J$12)

You are correct the hypothetical liquidation value was established on day 1 so lets say this is the first year, and the value was 100,000,000, then we would say the value of the company increases or decreases by the 704(b) income less distributions of the company as that is the cash they earned. So C15 is a constant as Partner A put in 10,000,000 and has to get 10,000,000 back per the agreement PLUS they get their preferred return. Partner B put in 90,000,000 and only gets back what is left.

Does that help at all?

 

[shawnhet]

Thanks for sending me the workbook.

Because of the circularity issue, I wasn't able to figure out how most of the amount were actually calculated so I went to the Calculation tab and change the maximum number of calculations to 1. By pressing F9 repeatedly, I was able to see that the solutions cycle through a set of 5 different solutions before returning to the one you posted. IOW, your spreadsheet as currently constructed cycles btw 5 different solutions for the negative case. There is no one correct answer for the negative case.

For whatever reason, OTOH, your no negative case seems to have produced a stable solution(only one). Given the nature of the problem, my suggestion is to leave the no negative alone and choose one of the 5 possibles for the negative one. If that works for you, you may as well leave it there.

I do believe that it would be possible to get what you want without circularity, but unfortunately, I am not understanding enough of what if anything might be used to constrain your solution set anymore.

Cheers,

 

[ryan87500]

Thanks for looking at it. What is weird is I see the 5 different solutions but in all but 1 the taxable income through the calculation doesn't even equal the taxable income.

Does this mean that excel can't handle the circular calc based on the fact pattern?

 

[ryan87500]

It seems like each circular reference cell is one iteration behind the other.

So C7 will display the amount in T30 after you hit F9, but then T30 changes.