TRUNC

In journal entry testing you look at the manual MEM entries in the XAF auditfile export. Round amounts are a fraud indicator, but the debit column has cents. You want a helper column with the whole euros, so you can see which entries have no cents at all.

Formula in C2

=TRUNC(B2)

=TRUNC(B2)

Result: €12,480.00€ 12.480,00

Watch out: A whole-euro column is only a helper. An amount of 5000.45 and one of 5000.00 give the same whole euros, so always compare against the original column before you flag an entry as round.

=INT(B2)

=INT(B2)

At year end the client revalues its open supplier invoices in dollars at the closing rate of 31 December 2025, and its system cuts the euro amount to whole cents, never rounded. You recalculate the creditors specification to see if the balance agrees.

The rate sits in E1 and the formula is copied down. One line is a credit note with a negative amount, and it is cut toward zero as well.

Formula in C2

=TRUNC(B2 * $E$1, 2)

=TRUNC(B2 * $E$1; 2)

Result: €4,197.71€ 4.197,71

Watch out: Without the $ signs, row 3 multiplies by E2, which is empty, and returns 0 without an error: a credit note that silently disappears from your recalculation.

=ROUND(B2 * $E$1, 2)

=ROUND(B2 * $E$1; 2)

You reconcile general ledger balances to the client's specifications (debtors list, creditors list, VAT return). For the working paper you want the difference per account as readable text: -649.75 should read -649 euro 75 cent, and a difference of -0.25 must keep its minus sign. The formula builds it in three parts:

• IF adds the minus sign
• TRUNC takes the whole euros of the absolute difference
• the rest is turned into cents

Formula in D2

=IF(B2 - C2 < 0, "-", "") & TRUNC(ABS(B2 - C2)) & " euro " & ROUND(ABS(B2 - C2 - TRUNC(B2 - C2)) * 100, 0) & " cent"

=IF(B2 - C2 < 0; "-"; "") & TRUNC(ABS(B2 - C2)) & " euro " & ROUND(ABS(B2 - C2 - TRUNC(B2 - C2)) * 100; 0) & " cent"

Result: -649 euro 75 cent

Watch out: The shorter formula =TRUNC(B2 - C2) & " euro " & ROUND(ABS(B2 - C2 - TRUNC(B2 - C2)) * 100, 0) & " cent" works for -649.75 but fails for account 1800: the difference is -0.25, TRUNC(-0.25) is 0, and zero has no minus sign, so it shows 0 euro 25 cent and a shortfall reads as an excess.

=TRUNC(B2 - C2) & " euro " & ROUND(ABS(B2 - C2 - TRUNC(B2 - C2)) * 100, 0) & " cent"

=TRUNC(B2 - C2) & " euro " & ROUND(ABS(B2 - C2 - TRUNC(B2 - C2)) * 100; 0) & " cent"

You recalculate the VAT on sales invoices from the XAF export (21% and 9%). The client's system cuts VAT per line to whole cents, and credit notes are cut toward zero too.

Some products of net amount and rate are stored a hair below the cent they display, and a bare TRUNC would take a cent off those. The formula uses LET to clean the product before cutting it.

Formula in D2

=LET(
  net, B2,
  rate, C2,
  raw, net * rate,
  clean, ROUND(raw, 6),
  TRUNC(clean, 2)
)

=LET(
  net; B2;
  rate; C2;
  raw; net * rate;
  clean; ROUND(raw; 6);
  TRUNC(clean; 2)
)

Result: €264.60€ 264,60

Watch out: The mirror-image mistake is INT(raw * 100) / 100. For positive values it matches TRUNC, and both share the same floating-point risk, so it passes a test on sales, but on credit note 25-0414 it goes down to -100.87 and shows a false difference of one cent.

=TRUNC(ROUND(B2 * C2, 6), 2)

=TRUNC(ROUND(B2 * C2; 6); 2)

=TRUNC(B2, -2)

=TRUNC(B2; -2)