Editor’s Note – This is a great and useful post. Most importantly, these formulae work perfectly – your editor actually tried them out in his own spreadsheet. For your next Debtor case, use this for your budgeting and there should be no surprises for US Trustee fees.
Recently I had to project quarterly U.S. Trustee fees in several jointly administered chapter 11 bankruptcy cases under the U.S. Trustee’s current fee guidelines (posted to effectuate 28 U.S.C. § 1930(a)(6)). Fees are calculated based on each debtor’s “disbursements” in a given quarter.[1] The calculation of multiple U.S. Trustee fees for multiple debtors can be challenging, particularly since the U.S. Trustee fees are calculated based on eight different brackets. So, I created an Excel spreadsheet to simplify things, and created “cut and paste” formulae that match up to the eight brackets (and even the bracket-busting “Trustee’s Fee Surcharge” under 28 U.S.C. § 1930(a)(6)(B)). These formulae also work for your “zero disbursement debtors,” in which $0 in quarterly disbursements still yields a minimum quarterly fee of $325.
In short, when you use this spreadsheet, your only inputs are estimated or actual disbursements, and then actual payments; the formulae do the rest of the work for you.
The spreadsheet format.
-
First, create an Excel spreadsheet that looks like the below. Do not cut and paste the header row or the left side border from the spreadsheet below – those are shown here because I am telling you where the formulae go in a professional fashion (such as “cell B3,” instead of “count over two boxes and down three boxes;” let’s keep it legit, folks).
-
For this example, I have three debtors, but you can do this with two, or twenty, or any number of debtors. You will just need to create more columns – a column for each debtor.
-
Plug in the actual / projected disbursements for each month, for each debtor.[2] As with the number of debtors, this spreadsheet can have more months; as many months as your debtor(s) can possibly afford.
-
Insert your formulae, listed below, and they work their magic from there. Insert the formulae below in the first cell indicated, and then once it is in Excel, cut and paste it into the next cell – each time you cut and paste from Excel, Excel will update the formula and pull from the correct cells above, below, or to the left, automatically.
With demonstrative disbursement and fee payments plugged in, the spreadsheet looks like this – and it is correct:
--> Scroll to see full table data
|
|
A |
B |
C |
D |
E |
F |
G |
|
1 |
Month / Quarter |
[Debtor 1] |
[Debtor 2] |
[Debtor 3] |
Amount Accrued |
Amounts Paid |
Running Balance |
|
2 |
Month 1 |
$110,000 |
$10,000,000 |
$20,000 |
$0 |
$0 |
$0 |
|
3 |
Year 1, Q3 |
$975 |
$100,000 |
$650 |
$101,625 |
|
$101,625 |
|
4 |
Month 2 |
$500,000 |
$11,111,111 |
$50,000 |
|
$100,000 |
$1,625 |
|
5 |
Month 3 |
$400,000 |
$11,111,555 |
$5,000 |
|
|
$1,625 |
|
6 |
Month 4 |
$800,000 |
$11,111,999 |
$25,000 |
|
|
$1,625 |
|
7 |
Year 1, Q4 |
$17,000 |
$250,000 |
$975 |
$267,975 |
|
$269,600 |
--> Scroll to see full table data
The spreadsheet formulae. Here are the formulae:
-
The fee per-quarter, per-debtor formula (e.g., cell B7, and then cut and paste it from that cell (B7) into cell C7, and then into cell D7) is as follows:[3]
=IF(SUM(B4:B6)>25000000,250000,
IF(SUM(B4:B6)>1000000, SUM(B4:B6)*0.01,
IF(SUM(B4:B6)>300000,4875,
IF(SUM(B4:B6)>225000,1950,IF(SUM(B4:B6)>150000,1625,
IF(SUM(B4:B6)>75000,975,IF(SUM(B4:B6)>15000,650,325)))))))
-
Modified for a one month quarter (e.g., Cell B3, and then cut and paste it from that cell B3 into cell C3, and then into cell D3), this formula is as follows:[4]
=IF(SUM(B2:B2)>25000000,250000,
IF(SUM(B2:B2)>1000000,SUM(B2:B2)*0.01,
IF(SUM(B2:B2)>300000,4875,IF(SUM(B2:B2)>225000,1950,
IF(SUM(B2:B2)>150000,1625,
IF(SUM(B2:B2)>75000,975,
IF(SUM(B2:B2)>15000,650,325)))))))
-
The fee per-quarter for all debtors formula (the “Amount Accrued” column) uses a simple “SUM” function:[5]
=SUM(B3:D3)
Again, place this in cell E3, and then cut and paste it from there into cell E7. Repeat for each quarter for however long you expect the case(s) to proceed.
-
The last formula, for the “Running Balance,” is also pretty simple:[6]
=SUM(E$2:E7)-SUM(F$2:F7)
Again, place this in cell G7, and then cut and paste it from there into cells G6, G5, G4, and G3 (it doesn’t work the other way around).
-
As your debtor(s) pays its U.S. Trustee fees, fill in Column F with the actual amount paid (the “Amounts Paid”).
-
The length of the cases can be extended for additional quarters by selecting the entire last quarter from the sample you’ve created (cells B4:G7) and dragging that down. All of the formulae should drag down accurately.
That’s it. If you have any questions or want some help building the spreadsheet, or encounter any difficulties, feel free to reach out to me.
[1] There is a world of confusion regarding what a “disbursement” is, whose disbursements are at issue, and who is liable; this confusion only gets worse post-confirmation. But this post assumes you’ve already mastered these issues and know what dollars are counted as “disbursements” and when.
[2] Obviously, projections are usually based off of the 13 week cash flow and / or any other budgets prepared in the cases.
[3] Formula for cell B7 shown.
[4] Formula for cell B3 shown.
[5] Formula for cell E3 shown.
[6] Formula for cell G7 shown.
[View source.]
