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]

I am afraid that you are most likely stuck with your first problem. If you go to Tools - Options - Calculations Tab will allow you to change the number of iterations (times the calculation is performed), but if you hit F9 (recalculate) it will perform that number of calcs again.

Why don't you let us know what you'd like to accomplish with the circ reference, someone here might be able to get you what you want without this problem?

Cheers,

 

[ryan87500]

So is this an issue with excel with handeling multiple circular calculations? What I am trying to do is somewhat confusing. It is to allocate patnership income for tac purposes based on this circular calc. Without walking someone through it over the phone I am not sure I can give it the right explanation to have someone even begin to help.

 

[shawnhet]

Basically, it is a fundamental issue to do with circular calculations. If I have an expression, in A2 such that A2=A2+1, then the value of A2 is dependent on what the original value of A2 is and the number of times you run the calculation. When the original value of A2 is 0 and the number of iterations is set to 100, then the final value of A2 is 100. If you hit F9 again it becomes 200 and so on. IOW, there is no value for A2 independent of the number of times you perform the calculation. This is different from a noncircular calculation, where the final value doesn't change if you keep performing the calculation.

I would be very much surprised if you couldn;t do what you are looking for without a circular reference, so I would try to describe your situation as best you could. You may also want to attach screen shots of your initial and desired outcomes(see the stickys on the top of the forum pages).

Cheers,

 

[ryan87500]

Is it possible to post actual excel files? I made an example of my issue but not sure if it helps to just do a screen print.

 

[shawnhet]

I don't believe it is. But I think if you posted a screen shot and the text of your formulae there is probably someone here who can help. It's probably worth a shot.

 

[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;">1</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;">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;;"> 2,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;;"> 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;">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;;"> (1,382,101)</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;;"> 100,617,899 </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;;"> 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;">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;;"> (16,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;;"> 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;">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;;"> 1,600,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;;"> 89,579,770 </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;;"> 100,617,899 </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;;"> 89,579,770 </td><td style="text-align: right;;"> 89,579,770 </td><td style="text-align: right;;"></td><td style="text-align: right;;"> (420,230)</td><td style="text-align: right;;"> 544,748 </td><td style="text-align: right;;"> 124,519 </td><td style="text-align: right;;">5.61%</td><td style="text-align: right;;">0.00%</td><td style="text-align: right;;"></td><td style="text-align: right;;"> 124,519 </td><td style="text-align: right;;"> - </td><td style="text-align: right;;"> - </td><td style="text-align: right;;"> 124,519 </td><td style="text-align: right;;"> 49,808 </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;;"> 837,352 </td><td style="text-align: right;;"> 1,875,481 </td><td style="text-align: right;;">93.77%</td><td style="text-align: right;;">0.00%</td><td style="text-align: right;;"></td><td style="text-align: right;;"> 1,875,481 </td><td style="text-align: right;;"> - </td><td style="text-align: right;;"> - </td><td style="text-align: right;;"> 1,875,481 </td><td style="text-align: right;;"> 750,192 </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;;"> 89,579,770 </td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"> 100,617,899 </td><td style="text-align: right;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"> 617,899 </td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DBE5F1;;"> 1,382,101 </td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"> 2,000,000 </td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">99.39%</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;;"> 2,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;;"> - </td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"> 2,000,000 </td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DBE5F1;;"> 800,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=";">Should be</td><td style="text-align: right;;"> 2,000,000 </td><td style="text-align: right;;"> 18,000,000 </td><td style="text-align: right;;"> (16,000,000)</td><td style="text-align: right;;"> 4,000,000 </td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:17.4em;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 w negative</p><br /><br />

 

[ryan87500]

Hopefully the screenshot is readable I know it is somewhat big.

To give a high level of my issue, I have to allocate income to partners in a partnership based on there change of 704(b) income which is essentially taxable income with a slight modificaiton.

The circular part is that the percentage allocated has to be after distributions are considered, so we have to gross up their income by distributions. The other fact is partner B is guaranteed a certain amount of income a year, in this example it is 1,038,129 but then you have to gross that up for taxes so it becomes more like 1.7 milion.

So right now if you look at the three distribution totals (highlighted in blue) I am getting different answers even though they are all linked. The one on top is supposed to be negative so that is ok.

Also it is not calculating my taxable income correct, so if you look at the bottom I have what the partners are getting versus what it "should be" for each column and you can see there is a difference. I can post my formulas if you think that will help as well.

Thanks!

 

[shawnhet]

Hi Ryan,

Can you post the formulas for the following columns?: Current Year Distributions 704(b) Net Income Allocation Positive Percentage Negative Percentage Gross Income Tier 1 Gross Income Tier 2 Gross Deduction Taxable Income Distribution

Also, I need a bit more info on how the amounts in the 704b column L27,L28 and L30 are calculated - can you describe this a bit more?

Cheers,

 

[ryan87500]

Hi Ryan,

Can you post the formulas for the following columns?: Current Year Distributions 704(b) Net Income Allocation Positive Percentage Negative Percentage Gross Income Tier 1 Gross Income Tier 2 Gross Deduction Taxable Income Distribution

Also, I need a bit more info on how the amounts in the 704b column L27,L28 and L30 are calculated - can you describe this a bit more?

Cheers,

For the 704(b) Net Income Allocation. What this represents is my 704(b) income prior to distributions, so it is working fine. It would equal cell J8 at the end of the day. What this is saying is that although my capital decreased by the net of 704(b) less distributions or in this case about 617k (cell J30), i need to allcoate income based on the grossed up number or 704(b) income pre distributions. Hopefully that makes sense.

If it helps I can increase my 704(b) income to a level where net after tax distributions it is enough to cover the preferred return and everything works nicely.

Here are the formulas.

Gross Income Tier 1 Formula: =IF($N$30=0,(M27*'704(b) allocations w negative'!$J$11),IF(N27=0,L27,0))

Gross Income Tier 2 Formula: =IF($N$30=0,0,(N27*('704(b) allocations w negative'!$J$11-'704(b) allocations w negative'!$P$30)))

Gross Deduction Formula: =IF($N$30=0,(M27*'704(b) allocations w negative'!$J$12),'704(b) allocations w negative'!N27*'704(b) allocations w negative'!$J$12)

Taxable Income Formula: =SUM(P27:R27)

Distributions Formula: =S27*0.4