Free Excel Templates

Consolidated Tax Return Calculator (Excel)

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.

Get the Excel template

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.

The handbook behind this calculator

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

What is inside the workbook

Start_Here
Quick orientation, color conventions (blue inputs, black formulas), and links to the four core consolidated return schedules.
1_Group_Inputs
Editable inputs for P, Sub A, and Sub B separate taxable incomes, federal tax rate (21%), starting SRLY NOL and member CTI, intercompany asset gain and step-up recovery life, and Sub B starting basis, nondeductible expenses, and intercompany distribution.
2_Consolidated_Calc
Live calculation of pre-NOL CTI, 80 percent SRLY absorption and ending carryover, final CTI and 21 percent federal tax, Year 1 matched intercompany gain and remaining deferred balance, and Sub B ending stock basis / Excess Loss Account.
3_Checks
Four automated reconciliation checks (tax rate proof, SRLY conservation, intercompany gain conservation, and ELA definition) whose failure count must equal 0.

What the pre-loaded example returns

MeasureValue
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.

Questions tax directors ask

What does the Consolidated Tax Return Calculator compute?

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).

Does the calculator handle the post-2017 SRLY 80 percent cap?

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.

How are tax-sharing reimbursements reflected in stock basis?

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.

Are any cells locked or macros required?

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.