MTDC is a subtraction, and that is where budgets go wrong

Why a subtraction is harder than an addition

Grant budgets have two layers. Direct costs are the things you can point at — salaries, travel, supplies. Indirect costs are the share of keeping the lights on that this project ought to carry, and you recover them by applying a percentage rate.

A percentage of what, though? Not of everything. If it were, an organisation could inflate its indirect recovery just by buying an expensive machine or passing a large chunk of the work to somebody else — neither of which creates much administrative burden for them.

So the rules define a smaller base: total direct cost, minus the categories that would distort it. That is MTDC, and it is a subtraction.

Subtractions are harder to get right than additions, because every excluded category has its own rule, and none of them are obvious. A template that adds a budget up cannot tell you it got the base wrong — it will produce a confident, tidy, incorrect number.

Three errors, and what each one actually costs

On the seeded three-year award — $1,757,415 of total direct cost, a $1,370,415 MTDC base — here is what each of the three common errors does. Note that they do not all point the same way.

The first one is the dangerous one. It increases your recovery, which means nothing in your own review will flag it — you get more money and everything looks fine, until an audit takes it back.

The other two cost you money quietly. Twelve tablets at $680 each are an $8,160 line, but equipment is tested per unit against the capitalisation floor, and $680 is well under it — so they are supplies, and they stay in the base. Treating the line total as equipment removes $8,160 you were entitled to claim on.

Why the subaward cap is the hard one

Only the first slice of each subaward counts toward MTDC. The rest is excluded. The part people get wrong is when that slice is consumed.

The cap runs across the whole period of performance, not per year. It is consumed in the order the money goes out:

in base, year y = MAX(0, MIN(spend in year y,
                             cap - everything spent in earlier years))

So a subaward spending $80,000 in year one has already used the whole cap; nothing in years two or three counts. Apply the cap per year and you count it three times over.

The three subawards in the sample data cross the cap in three different years, deliberately, so the fill is genuinely exercised rather than accidentally correct:

How to build the MTDC base correctly

in base, year y = MAX(0, MIN(spend_y, cap - spent in earlier years))

% of DIRECT cost -> allowed = rate x direct
% of TOTAL cost  -> allowed = direct x rate / (1 - rate)

What is inside the file

Ten tabs — the most of anything here, because the exclusions each need their own working. Seeded with a three-year award whose three subawards cross the cap in three different years.

Opening it in Excel, Google Sheets or Numbers

It is one .xlsx file. There are no macros, no add-ins and nothing to install, which is what makes it portable — a macro-driven template would be Excel-only.

Every formula in this workbook uses ordinary functions — SUM, IF, INDEX, MATCH and their relatives. Nothing here is Excel-only.

The arithmetic, written out

MTDC = total direct cost
     - equipment, capital expenditure, patient care, rental,
       tuition remission, scholarships, participant support
     - the portion of EACH subaward above the cap

subaward in base, year y = MAX(0, MIN(spend_y, cap - spent in earlier years))
    # verified against the closed form: total = MIN(total spend, cap)

indirect computed = MTDC x rate
allowed, % of direct = rate x direct
allowed, % of TOTAL  = direct x rate / (1 - rate)
recovered = MIN(computed, allowed)

One more subtlety, in how the workbook prices an error. It computes

MIN(rate x (base + delta), allowed) - MIN(rate x base, allowed)

rather than rate x delta. That matters because if the cap is already binding, an error that moves your base correctly shows as worth nothing — the cap eats it. A naive calculation would tell you a mistake cost you money when it did not.

How I know the numbers are right

The clearest demonstration is the de minimis versus negotiated rate comparison on the seeded award, because it shows the cap doing its work.

The rate gap suggests negotiating is worth $178,154. It is actually worth $34,085, because the cap eats the rest. That is the kind of thing a budget template that only adds up cannot tell you, and it is a decision that costs real staff time to get wrong.

The seed data is tuned so the cap binds for the negotiated rate but not for de minimis, which means MTDC accuracy still drives money on the shipped figures — if the cap bound in both cases, the whole subtraction would be academic and the workbook would be untested.

Compared with the alternatives

Questions people ask before buying

Related spreadsheets