Free Excel templates

A free PFIC excess distribution calculator in Excel

The 125 percent test, the excess and nonexcess parts, the allocation by day over the holding period, the deferred tax at the highest rate of each earlier year and the interest, for one fund, with the same holding run under a QEF election and a mark-to-market election.

An investor holds one foreign fund in three lots and receives a large distribution in 2026, then sells everything in December. Of the $2,340 received, $1,441 is an excess distribution and $899 is not; the $6,610 of gains on the sale is treated as an excess distribution too. Spread back over the holding periods, the earlier-year parts bring $2,404 of deferred tax and $523 of interest, for a 2026 tax effect of $3,713. The calculator lays the chain out for one fund, ready for your own lots.

Get the calculator

One Excel file, no macros, nothing locked. Free: enter your email address and the download opens at once, along with the other free models.

PFIC_Excess_Distribution_Calculator.xlsx (64 KB)

You join the reader list: one email when a new free model or a new book comes out, and a few notes on using the models. Leave in one click. Already on the list? Enter the same address. How your address is used.

The book behind this calculator

This calculator is a simplified version of the companion workbook of The PFIC Handbook, which follows one couple and four foreign holdings through a full return: the income and asset tests with look-through, an indirect holding through an operating company, a fund in four lots with two distributions in one year and one lot sold, a pedigreed QEF, a late mark-to-market election, a purge, and four Forms 8621 line by line.

Paperback PDF edition, $19.99

What is in the file

Inputs are in blue; everything else is a formula; the checks count the failures. Unused lot rows stay empty and change nothing.

1. Start here
What the calculator does, the rules it applies with the date they were read, what it leaves out, and a note that every example figure is a teaching assumption, not a market figure.
2. Inputs
The fund's first PFIC year; up to three lots with purchase date, shares and price; the distribution per share and payment date of each year; the sale date and price; the highest rate of each year and the quarterly underpayment rates as declared hypotheses; and the ordinary and capital gain rates used for the comparison.
3. Calculator
For each lot: the holding period, the three prior years, the 125 percent test, the excess and nonexcess parts, the allocation by day of each excess distribution and of the gain, the current-year and earlier-year parts, the deferred tax and the interest year by year; then the same holding under a QEF election and under a mark-to-market election, year by year.
4. Checks
The controls of the calculator, each showing a gap that must read 0, and a count of the failures.

What the worked example returns

MeasureValue
Distributions received in 2026$2,340
Excess distribution / nonexcess distribution$1,441 / $899
Gains on the sale of the three lots, treated as excess distributions$6,610
Deferred tax on the earlier-year parts$2,404
Interest to the due date of the 2026 return$523
Total 2026 tax effect$3,713
2020 to 2026, default regime / QEF election / mark-to-market election$4,701 / $2,803 / $3,854
Control blockEvery check at 0

The example holding is fictional and every input is a teaching assumption: one fund, a PFIC since 2010; lots of 400 shares at $22.50 on May 11, 2020; 300 shares at $25.10 on September 6, 2022; 200 shares at $27.40 on February 20, 2024; distributions per share paid on October 20 of each year: 2020 $0.70, 2021 $0.72, 2022 $0.75, 2023 $0.77, 2024 $0.80, 2025 $0.82, 2026 $2.60; all three lots sold on December 15, 2026 at $31.80 a share. Every check of the control block reads 0. The calculator is separate from the book's case: read its figures against each other, not against the book's couple.

How to use it on your own holding

  1. Enter one row per purchase lot, with the trade date, not the settlement date, and the shares actually bought.
  2. Enter every distribution with its payment date, back at least three years before the year you are computing.
  3. Enter the first year the fund was a PFIC, from its reports or your adviser.
  4. Check the highest rate of each year and the quarterly interest rates against the current IRS tables, and state any rate not yet published.
  5. Read the Checks tab before any figure: a count above zero means a cell to fix.

Rules described as read in November 2026 (26 U.S.C. 1291, 1293, 1296 and 6621 as applied by the IRS Instructions for Form 8621 (Rev. December 2025) and the IRS table of quarterly interest rates). Education only, not tax advice: the facts of your own return govern, and your tax adviser decides.

Questions people ask

What does the PFIC excess distribution calculator do?

For one fund held in up to three lots, it runs the 125 percent test on each year's distributions, splits them into excess and nonexcess parts, spreads each excess distribution and the gain on a sale by day over the holding period, applies the highest rate of each earlier year and the quarterly interest rates, and compares the result with a QEF election and a mark-to-market election on the same holding.

Which rules does it apply?

As read in November 2026: section 1291 with the 125 percent test over the three preceding years, allocation by day, the highest section 1 rate of each year from the table in the instructions for Form 8621, and interest at the section 6621 underpayment rates compounded daily to the due date of the return. Rates not yet published are entered as declared assumptions.

What does it leave out?

The PFIC tests themselves, indirect ownership, foreign currency, foreign tax credits, purging elections, section 1294, the qualified dividend question and state rules. The book's full workbook covers most of them on one case.

Can I rely on it to fill Form 8621?

No. It is built for a first pass and for learning the mechanics. The return needs its own figures, statements and lot records, and the conclusions belong to your tax adviser.

Is this calculator really free?

Yes. You enter an email address, the download opens at once, and you can leave the reader list in one click.

Go further

The free workbook of The PFIC Handbook goes further: the PFIC tests with look-through, direct and indirect holders, four lots and two distributions in one year, the QEF and mark-to-market tabs, one fund under the three regimes over ten years, a purge, Form 8621 line by line for four PFICs, and sensitivities.

See the book and its free files →

Related reading