Batch calculation to test for optimal pension split

I’ve been playing around with batch calculations in Client Manager, trying to find a way to run one that would allow me to verify that pension splitting was correctly optimized in our T1s. It’s a bit of a long story, but I identified a few returns in April whose split was sub optimal, so thought this would be a good verification step to make sure what we filed was best.

My initial idea was changing the response to whether the taxpayer is the pensioner/pension transferee, i.e.:


and then calculating the family balance due either way in the batch calculation report. The third column in the batch calc report would be the family balance due as filed, and then I was planning to run some logic in excel to identify anytime where the filed amount did not equal the lowest family balance due possible.

From the tinkering I’ve done, I haven’t found a way to run this type of “what if” calculation (as it requires a change to a cell response in the tax return). I’m wondering if I’m missing something though? Has anyone found a way to accomplish something similar?

That’s a great idea but I just don’t think you can extract enough information from the Client Manager Batch to determine whether the pension splitting amount calculated by TaxCycle is the optimum amount. I did a similar Excel application for Profile that physically went into the data file and ran through a series of loops that would test the net family balance with different pension splitting amounts. The test amounts would get narrowed down to where the pension splitting amount was pretty much guaranteed to be optimal (lowest overall tax). Without the ability to access the data file and do that looping, I don’t think the Batch calculation can give you the answer. I hope I’m wrong, though, and would welcome a way to test this.