Need help re: tax calculations

[ianholds]

I'm working on a template for a client who wants to see a calculation every month of the tax that would have to be paid on death as well as a calculation on how to fund it. Part of the funding calculation is simple as it relates to hard assets (cash, life insurance proceeds, etc.), however after that the funding must come from liquidating stocks / mutual funds held in their corporations. Ideally, what I would like to be able to do is run some sort of calculation that would look at the unfunded tax liability prior to the equities held in their corporations being sold and determine what equities should be sold at that point to eliminate the unfunded liability, given the corresponding tax consequences of these equities being sold and getting the cash to the estate. If anyone could help, it would be phenomenal.

[JDI]

Can you provide some dummy data, and expected results?

[ianholds]

Hey, thanks for replying. I've attached a workbook with a little bit of background on everything. Please reply or PM with whatever questions you may have.

[JDI]

I will take this home and study it, it is doable if I can understand what is going on...

[ianholds]

You're a gem. Again, if you have any questions or want clarification about how the tax works, let me know. I'm happy to work with you (or anyone else) to try and fill in whatever gaps there may be.

[JDI]

So we know the value of O, this is given and we wish to find P by minimizing the units disposed. If this is correct... can you clarify what variables need to be minimized to find units disposed. I'm not sure what needs to be minimized. I'm an engineer not an accountant haha :p

I have reduced the formulas in C36, C40, C45, C50 to dependencies on D and E only.

Also in the sample holdings A= E-D but on sheet 1 tax formuals A=D-E

Does P (sheet1) equal C53*C54 or C53-C54 or something else?