One unlocked four-tab Excel workbook covering IRC Section 1502 CTI assembly, post-2017 SRLY absorption, 1.1502-13 intercompany gain matching, and 1.1502-32 stock basis / ELA rollforwards.
Enter your common parent and subsidiary separate taxable incomes, pre-consolidation SRLY NOL, intercompany asset sale gain, and subsidiary distribution to see the federal tax and stock basis impact at once. In the pre-loaded example, P ($2,500,000), Sub A ($3,200,000), and Sub B (-$1,400,000) produce $4,300,000 pre-NOL CTI, absorb $720,000 of a $1,000,000 SRLY NOL (leaving $3,580,000 final CTI and $751,800 federal tax at 21%), match $100,000 of a $500,000 intercompany gain, and push Sub B into a $646,000 Excess Loss Account.
One unlocked Excel workbook, zero macros, zero circular references. Free: enter your email address and the download opens at once, along with the other free tax and accounting models.
Consolidated_Tax_Return_Calculator.xlsx (14 KB)
You join the reader list: one email when a new free model or book comes out. Unsubscribe in one click. Already subscribed? Enter the same address. How your address is used.
This calculator is a clean standalone extract of the ten-tab workbook in The Consolidated Tax Return Handbook, which walks a complete corporate group across three years (2026 to 2028) through IRC Sections 1501 to 1504, P.L. 119-21 charitable floors and Section 163(j) pooling, 1.1502-13 matching and acceleration, 1.1502-32 stock basis and ELAs, SRLY vs. Section 382 overlap, all three tiers of the Unified Loss Rule (1.1502-36), short-year allocations (1.1502-76), and Section 1552 tax-sharing agreements.
Paperback PDF edition, $19.99 Book page and full 10-tab workbook
| Measure | Value |
|---|---|
| Pre-NOL Consolidated Taxable Income (P + Sub A + Sub B) | $4,300,000 |
| Post-2017 SRLY NOL absorbed (80% of $900,000 member CTI) | $720,000 |
| Final Consolidated Taxable Income (CTI) | $3,580,000 |
| Consolidated Federal Tax at 21% | $751,800 |
| Year 1 Intercompany Gain Matched (5-yr step-up on $500,000) | $100,000 |
| Sub B Ending Stock Basis / Excess Loss Account (ELA) | -$646,000 ($646,000 ELA) |
Every figure in the calculator is an illustrative teaching example, independent of the book's Alderbrook case. Starting from Sub B's $2,000,000 initial stock basis, absorbing its -$1,400,000 loss reduces basis by $1,400,000, the 21 percent tax-sharing reimbursement adds back $294,000, nondeductible expenses subtract $40,000, and a $1,500,000 intercompany distribution pushes ending basis to -$646,000, creating a $646,000 Excess Loss Account.
Rules described as read in November 2026 (IRC Sections 1501 to 1504, Treas. Reg. 1.1502-13, -19, -21, -32, and TD 9927). Education only, not tax or legal advice.
It combines member separate taxable incomes into Consolidated Taxable Income (CTI), applies the post-2017 80 percent SRLY limitation under TD 9927, computes annual intercompany deferred gain matching on depreciable property, and rolls a loss subsidiary's outside stock basis down into an Excess Loss Account (ELA).
Yes. Under Treas. Reg. 1.1502-21(c) as amended by TD 9927, the calculator caps annual post-2017 SRLY NOL absorption at 80 percent of the member's positive cumulative register and tracks remaining SRLY carryforwards automatically.
Under Treas. Reg. 1.1502-32(b)(3)(iv)(D), when the group absorbs a subsidiary's loss and reimburses that subsidiary for the 21 percent federal tax benefit under a 100 percent percentage tax-sharing method, the reimbursement offsets the negative loss adjustment. In the pre-loaded example, Sub B's -$1,400,000 loss is paired with a $294,000 tax reimbursement.
No. Every input cell is blue, every formula cell is black and unlocked, there are zero macros or circular references, and the 3_Checks tab verifies all four reconciliation checks equal 0.