After my corporation bought back some stock certificates, the method I used to calculate Item F, “Shareholder’s percentage of stock ownership for tax year,” became more complex. Neither Google nor WolframAlpha led me to an equation so I set about creating a spreadsheet to simplify the problem.
The IRS instructions for form 1120S Schedule K-1, Line F, describe the method to use thusly:
Each shareholder’s pro rata share items are figured separately for each period on a daily basis, based on the percentage of stock held by the shareholder on each day.
…..
If there was a change in shareholders or in the relative interest in stock the shareholders owned during the tax year, figure the percentage as follows.
• Each shareholder’s percentage of ownership is weighted for the number of days in the tax year that stock was owned. For example, A and B each held 50% for half the tax year and A, B, and C held 40%, 40%, and 20%, respectively, for the remaining half of the tax year. The percentage of ownership for the year for A, B, and C is figured as presented in the illustration and is then entered in item F.
I built myself a spreadsheet to calculate Item F correctly in the simple and complex cases. My spreadsheet implements the IRS described method without requiring the preparer to manually perform all the interim calculations. The only required inputs are the quantify of shares held by each shareholder and the number of days they were held.
Without any alteration, it supports up to 4 changes in shareholders and there’s no limit to the number of shareholders. I just plop in the raw numbers from the Stock Transfer Ledger and the spreadsheet does the rest.
I post it in Numbers and Excel formats, hoping that others will find it useful as well.