foreign-per-diem-calculator-for-usa-based-institutions

At a Glance

#

Anyone who has tried to file an international travel reimbursement at a U.S.-based institution (university, federal agency, federal contractor) knows the per diem math is more complicated than it should be. The traveler does not just claim the M&IE rate for each city. They claim the rate minus a 25% deduction for travel days, minus separate dollar deductions for any meal that was provided (a conference breakfast, a hosted lunch), and the meal deductions are not percentages but specific dollar amounts that vary by city.

This calculator does that math. The user enters travel dates, cities, and the M&IE rate from the State Department’s website, then checks boxes for any provided meals. The sheet computes the daily allowable per diem and the trip total, with the meal-deduction values pulled from a copy of the State Department’s Appendix B reference table embedded in a second sheet of the workbook.

The Problem

#

Foreign per diem looks like a single number until you actually try to compute it. Three things make it harder than it appears.

Travel days are not full days. Federal Travel Regulation specifies that the first and last day of any trip get 75% of the M&IE rate, not 100%. This is meant to reflect partial-day travel: you arrive in the evening, you leave in the morning, you do not need a full day’s meal allowance for either. The 25% reduction is mandatory and cannot be ignored.

Provided meals are deducted at fixed dollar amounts, not percentages. If the conference provides breakfast on day three, the traveler must subtract a specific dollar amount from that day’s allowance. The amount is not “one-third of the daily rate” or “$10.” It is determined by a 265-row table in the Federal Travel Regulation Appendix B, with separate values for breakfast, lunch, and dinner at every M&IE rate from $1 to $265. A $48 rate has a $7 breakfast deduction. A $98 rate has a $15 breakfast deduction. There is no formula to derive these; they are just published values.

Trip length is a variable. A two-day trip and a fifteen-day trip both need the same calculator. Hardcoding row count locks the tool to one trip length; leaving it unlimited produces dozens of empty rows that clutter the output.

Doing this math by hand requires looking up the State Department M&IE rate for each city, looking up the meal deduction values for that M&IE rate (separately for breakfast, lunch, and dinner), and then applying the 25% travel-day reduction on the right two days. Across a fifteen-day trip with multiple cities, this is several pages of arithmetic. The work is tedious enough that errors are common, and per diem reimbursement errors are exactly the kind of small-dollar problem that becomes a large-dollar audit finding when they happen at scale.

The Approach

#

A two-sheet Excel workbook. The main sheet (Per Diem Calculator) is the user-facing form. The second sheet (Meal Deductions) is a verbatim copy of the State Department’s Appendix B table, used as the lookup source for meal-deduction values.

The main sheet has nine user-facing columns and twelve hidden helper columns. The user sees a clean form: enter dates, cities, M&IE rates, and check boxes for any provided meals. The helper columns to the right do the actual math: looking up deduction values, applying conditionals, and summing the per-row total. Hiding the helpers keeps the visible form simple without sacrificing computational rigor.

The data flow looks like this:

flowchart TD
    A[User enters first and last travel dates] --> B[Trip length calculated]
    B --> C[Row activation: rows within trip length become live, others stay blank]
    D[User enters M&IE rate per city] --> E[VLOOKUP against Appendix B table]
    F[User checks Travel Day / Meal boxes] --> G[Conditional deduction: lookup value if checked, 0 if not]
    E --> G
    G --> H[Daily total = rate - sum of deductions]
    H --> I[Trip summary: sum across all live rows]

The two inputs that drive everything (dates and M&IE rate) are the only things the user has to manually research. Everything else is either a checkbox or an automatic calculation.

The Math

#

The daily total for any travel day is computed as:

$$\text{Daily Total} = \text{M&IE Rate} - D_{\text{travel}} - D_{\text{breakfast}} - D_{\text{lunch}} - D_{\text{dinner}}$$

Each deduction term is conditional on a checkbox. If the box is unchecked, the deduction is zero:

$$D_{\text{travel}} = \begin{cases} 0.25 \cdot \text{M&IE Rate} & \text{if first or last day of trip} \ 0 & \text{otherwise} \end{cases}$$

$$D_{\text{breakfast}} = \begin{cases} \text{Appendix B[M&IE Rate, Breakfast]} & \text{if breakfast provided} \ 0 & \text{otherwise} \end{cases}$$

with the same pattern for lunch and dinner. The breakfast, lunch, and dinner deductions are not formulas; they are looked up from the Appendix B table by the row’s M&IE rate.

The trip total is the simple sum of daily totals across all active rows in the table:

$$\text{Trip Total} = \sum_{i=1}^{N} \text{Daily Total}_i$$

where \(N\) is the trip length in days, computed automatically from the difference between the last and first travel dates.

Walking Through the Calculation

#

Trip Length and Row Activation

#

The user enters two dates: first day of travel and last day of travel. The cell labeled Total Number of Travel Days immediately computes their difference. This trip length value drives a chain of formulas down the date column:

M10: =IF(F3="", "", F3)                     ' First date or blank if input empty
M11: =IF(N11=TRUE, M10+1, "")               ' Next date if within trip, else blank
M12: =IF(N12=TRUE, M11+1, "")               ' Same pattern continues for all rows
N11: =L11<=N$5                              ' Trip-day index <= trip length?

The L column holds a static day index (L10=0, L11=1, L12=2, …) and the N column compares that index against the calculated trip length. As long as the day index is less than or equal to the trip length, the date column increments. As soon as the index exceeds trip length, the date column returns a blank string.

The visible date column (A) just mirrors the calculated date column (A11: =M11), so what the user sees is a clean date sequence that ends exactly when the trip ends. A two-day trip shows two dates and the rest is blank. A fifteen-day trip shows fifteen dates. The calculator handles any reasonable trip length without configuration.

M&IE Rate Lookup and Meal Deductions

#

The user enters the M&IE rate for each city in column C. This is the only piece of external information the calculator needs from the user; everything else flows from it.

For each meal checkbox, a corresponding helper column performs a VLOOKUP against the Appendix B table:

T10: =IF(P10=TRUE, VLOOKUP(C10, Allocation_of_M_IE_Rates_to_Be_Used_in_Making_Deductions_from_the_M_IE_Allowance[#All], 2, 0), 0)

The lookup key is the M&IE rate (C10). The lookup table is the named Appendix B table on the second sheet (Allocation_of_M_IE_Rates...), and the column index (2 for breakfast, 3 for lunch, 4 for dinner) selects which meal’s deduction to return. The 0 argument forces an exact match: a rate that does not appear in the table returns an error rather than a wrong value, which is the right default for a financial calculator.

The travel-day deduction has slightly different math because it is not in the Appendix B table:

S10: =IF(O10=TRUE, VLOOKUP(C10, Allocation_of_M_IE_Rates_to_Be_Used_in_Making_Deductions_from_the_M_IE_Allowance[#All], 1, 0)*0.25, 0)

Column 1 of the lookup is the M&IE rate itself, multiplied by 0.25 to get the 25% travel-day deduction. The same VLOOKUP could be replaced by a direct reference (C10*0.25), but using the lookup makes the formula structurally identical to the meal-deduction formulas, which is easier to read and audit.

Display vs Computation

#

The user-visible deduction columns (D, E, F, G) display each deduction as a negative number:

D10: =-S10
E10: =-T10
F10: =-U10
G10: =-V10

This is a small UX detail that pays off in clarity. The computed deductions (S, T, U, V) are stored as positive values because that is what the lookup returns. But on the user-facing side, the analyst sees -$7 instead of $7 for a breakfast deduction, which makes the daily-total subtraction visually obvious. Without the negation, the user would have to mentally subtract each value to verify the daily total; with it, they can read the daily total as a sum of the visible numbers.

Why The Lookup Table Approach Matters

#

A simpler implementation would hardcode the meal-deduction values directly into the formulas: a chain of IF statements like IF(C10>=80, IF(C10>=100, 19, 15), 12) to approximate the breakfast deduction by tier. This works for the most common rates but fails in two ways.

Updates require code changes. The State Department updates its Appendix B periodically. With a hardcoded formula, every update requires editing every formula in every cell that uses the lookup. With a separate reference table, only the table values need updating; every formula automatically picks up the new values on next recalculation.

Coverage is incomplete. Tiered IF formulas approximate the table but never match it exactly, because the actual table is not tiered. A $48 M&IE has a $7 breakfast deduction; a $49 M&IE has a $7 breakfast deduction; a $50 M&IE has a $8 breakfast deduction. The values are technically continuous in $1 steps, not bucketed into tiers, so any IF-based approximation introduces errors at boundary rates.

The Appendix B sheet captures every rate from $1 to $265 with its three deduction values, mirroring the State Department’s published table exactly. The calculator looks up the row that matches the rate the user entered and returns the precise published value. There is no approximation, no rounding, no boundary error.

Under The Hood

#

For the technically curious, three of the more interesting implementation pieces.

L10: 0                              ' Static day index, hardcoded
L11: 1
L12: 2
...
M10: =IF(F3="", "", F3)             ' First date, or blank if no input
M11: =IF(N11=TRUE, M10+1, "")       ' Next day if within trip
M12: =IF(N12=TRUE, M11+1, "")       ' Same pattern continues
N11: =L11<=N$5                      ' Is day index within trip length?
N12: =L12<=N$5

=VLOOKUP(C10, Allocation_of_M_IE_Rates_to_Be_Used_in_Making_Deductions_from_the_M_IE_Allowance[#All], 2, 0)

D10: =-S10                          ' Travel day deduction (visible, displayed as negative)
E10: =-T10                          ' Breakfast deduction
F10: =-U10                          ' Lunch deduction
G10: =-V10                          ' Dinner deduction
I10: =C10-W10                       ' Daily total = M&IE rate - total deductions
W10: =SUM(S10:V10)                  ' Total deductions for this row

Stack

#

• Format: Microsoft Excel .xlsx workbook with two sheets
• Features used: Structured tables (ListObject), VLOOKUP with structured references, conditional formatting, checkbox controls, dynamic row activation
• External data: State Department M&IE rates (entered manually per city) and Federal Travel Regulation Appendix B (embedded as a 265-row reference table on a separate sheet)
• Compatibility: Excel 2016 or later

Repo

#

github.com/hihipy/foreign-per-diem-calculator-for-usa-based-institutions