Free Excel templates

A free installment sale calculator in Excel, with a worked example

The gross profit percentage, the gain on the down payment and on each installment, the unrecaptured section 1250 gain taken first, the interest on the seller note, an assumed federal tax by year and the section 453A test, for one property sold on installments.

The example is a small commercial building sold for $1,500,000, with an adjusted basis of $600,000 after $300,000 of straight-line depreciation and $60,000 of selling expenses. The buyer pays $300,000 down and gives a note of $1,200,000. The calculator returns a gross profit of $840,000, a gross profit percentage of 56.00%, $168,000 of gain on the down payment and $67,200 on each annual installment. Replace the inputs with your own sale.

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.

Installment_Sale_Calculator.xlsx (13 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 file is the simplified version of the model in The Installment Sale Handbook. The book works a full business sale on installments: the price allocated among five assets, inventory and recapture taxed in the year of sale, the per-asset schedule of Form 6252, the interest test, the section 453A charge, a pledge, an earnout, a related buyer, a note sold or settled for less, and the decision to elect out.

DRM-free PDF, $19.99 → · Paperback, $32.99, shipped worldwide

What is in the file

Four tabs. Blue cells are inputs; every other cell is a formula.

Inputs
Selling price, debt assumed, cost, depreciation, selling expenses, recapture, down payment, number of installments, stated rate, assumed rates and the section 453A thresholds; then the results: installment sale basis, gross profit, contract price, gross profit percentage, note, gain on the down payment and on each installment, unrecaptured section 1250 gain.
Schedule
One row per year: principal received, note balance, installment sale income, the unrecaptured layer taken first, other long-term gain, interest, an assumed federal tax and the section 453A interest when it applies.
Section 453A
The face amount and sales price tests, the applicable percentage, the deferred tax liability at the end of the year of sale and the interest for that year.
Checks
Internal ties (total gain equals gross profit, principal adds up, the unrecaptured layer is used up) and a comparison with the figures printed in the book.

Three things to try

Change the down payment. The gain of the year of sale follows the cash received, not the price: a larger down payment brings more of the gain, and more of the unrecaptured layer, into the first year.

Shorten the note. With fewer installments, each one carries more gain, and the unrecaptured section 1250 gain of $300,000 is used up sooner. In the example it takes the year of sale and 2 more years.

Make the note larger than $5,000,000. The Section 453A tab switches on and shows the applicable percentage and the interest on the deferred tax.

Questions

What does the calculator return for the example?

A gross profit of $840,000, a gross profit percentage of 56.00%, $168,000 of gain on the down payment, $67,200 on each annual principal payment, and $78,000 of interest in the first year at the note's stated rate.

Does it use macros?

No macros and no circular reference. It recalculates in Excel, LibreOffice or Google Sheets.

Can it handle the sale of a whole business?

No. It works on one property. A business sale needs the price allocated among the assets, the inventory and recapture taken out of the deferral and a schedule per asset attached to Form 6252; the book works through all of it on a full case.

Related reading