
A ZUGFeRD invoice is rejected because of a sum difference. All amounts in Excel look correct. However, a cent is missing when calculating the amount. Often such deviations occur at the border between displayed numbers, stored values and the amounts actually taken into account in the invoice XML.
The number format and the calculation value must be considered separately. Position amounts are calculated according to a uniform procedure and rounded at the required point. The invoice amounts are based on exactly these amounts. VAT are calculated in a comprehensible manner per tax group. Afterwards Excel, PDF and structured data must contain the same values.
Two decimal places is not the same as round
A cell can contain the value 12,345 and appear as 12,35 Euro with a currency format. The following formulae can still count on the original value. If you add only the visible amounts, you may receive a different result from the workbook.
With =RUNDEN(A2;2) The results are calculated on two decimal places. Microsoft Documentation to ROUND describes the function and parameter for the number of places. The new formula must be used in the correct technical place: A round of all entries can eliminate, for example, required accuracy in quantities or individual prices.
A troubleshooting starts with an additional control column. There, more decimal places can be displayed temporarily. The formula bar and the data actually passed help in comparison. The decisive question is which value is used in the invoice as the position amount.
Example 1: Three positions yield 1,02 Euro – or 1,01 Euro
A fictitious invoice contains three items. Each item comprises one item at the net price of 0,335 Euro. There are no discounts, surcharges or different price base. For this example, the individual net position amounts are rounded to cents.
| Item | Quantity | Net price | Unrounded product | Net position amount |
|---|---|---|---|---|
| 1 | 1 | 0,335 € | 0,335 € | 0,34 € |
| 2 | 1 | 0,335 € | 0,335 € | 0,34 € |
| 3 | 1 | 0,335 € | 0,335 € | 0,34 € |
| Total | 1,005 € | 1,02 € |
Adding the unrounded products first and rounding only the total to cents turns EUR 1.005 into EUR 1.01. By contrast, the sum of the three individually rounded line-item amounts is EUR 1.02. The difference results from two different calculation methods.
The invoice shown contains EUR 0.34 for each line item. The total of the line items must therefore be EUR 1.02. The business relationship is defined in BR-CO-10 The separate rule BR-DEC-23 the net position amount limits the decimal places to two, which does not mean that all the accounting fields are limited to two digits.
A simple formula for Excel
For a template without discounts and supplements, the calculation may look like this: Column B contains the quantity, column C contains the net price per unit and column D the rounded net position amount. The formulas use the German Excel function names.
| Cell | Meaning | Formula | Result in the example |
|---|---|---|---|
| D2 | First position | =RUNDEN(B2*C2;2) | 0,34 € |
| D3 and D4 | Other items | Apply formula with matching line reference | Per 0,34 € |
| D6 | Total net | =SUMME(D2:D4) | 1,02 € |
| D7 | 19 % VAT on this group | =RUNDEN(D6*19%;2) | 0,19 € |
| D8 | Gross amount | =SUMME(D6:D7) | 1,21 € |
When assigning the field, the extension must use the same rounded position values on which the sum formulae are based. A rounded display column does not help if another unrounded column is still assigned as the position value for the XML output.
The area is also important: intermediate and final sums are not additional performance positions. If a subtotal is read again as position, the calculation can be wrong, although each round is correct. Therefore, before a formula change, it is always worth checking the actually assumed lines.
Example 2: Do not calculate total VAT by adding individually rounded tax lines
Three taxable positions are net 0,03 and belong to the same tax group with 19 percent. The basis of assessment of this group is 0,09 euros. This results in a calculation of 0,0171 euros VAT, rounded to cents 0,02 euros.
If each line item were taxed separately and rounded to cents, EUR 0.03 × 19% would give EUR 0.0057, rounded to EUR 0.01. Three such tax amounts would total EUR 0.03. This is one cent more than the tax calculated at group level.
| Calculating path | Arithmetic | Result |
|---|---|---|
| Common assessment basis: | Rounds of 0,09 € × 19 % | 0,02 € |
| Total of individually rounded tax values | 0,01 € + 0,01 € + 0,01 € | 0,03 € |
The Rule BR-CO-17 describes the tax calculation from the tax base and the rate of the respective VAT category. For several tax categories or tax rates, suitable groups are required. A flat-rate tax calculation from the entire net is not sufficient.
As a certain validator evaluates small differences, depends on the specific rules and their tolerances. A tolerated deviation does not make two different calculation paths a good template concept. The template should also remain comprehensible regardless of a possible test tolerance.
Do not quickly round single price, quantity and price base
A precise unit price may be necessary in a factual way, for example for consumption values or prices per hundred units. In the first example, the unit price deliberately contains three decimal places. A change to 0,34 Euro would be a change in the price base and not just a different representation of the position amount.
The price base must also be correct. If a material costs EUR 12.50 per 100 items, a quantity of 240 items has a net amount of EUR 30.00 before any additional charges or allowances: 240 × (12,50 ÷ 100) = 30,00. If the price base 100 is forgotten, the same input will falsely result in 3.000,00 Euro. This is not a rounding error.
For this case, a formula using quantity B2, price C2 and price-base quantity E2 could be =RUNDEN(B2*(C2/E2);2). E2 must contain a meaningful value greater than zero. Line-item allowances and charges require additional calculation steps. The Peppol rule R120 This is illustrated by this connection for Peppol-UBL; its concrete test tolerance must not be transferred to any ZUGFeRD profile.
What is the sum rule for which area?
A technical rule code can often be traced back to a small chain of data. The following assignment helps with the limitation. The linked rule texts are from the published UBL documentation; for a ZUGFeRD file a check is required that matches the CII format and profile.
| Rule | Control | Typical starting point |
|---|---|---|
| BR-CO-10 | Position sum | Position amounts, rounding points and line range expressed. |
| BR-CO-13 | Net of bills by discount and surcharge | Additional deductions and surcharges at document level. |
| BR-CO-17 | Tax payable per category and rate | The tax base and rounding of the tax group. |
| BR-CO-16 | Number of cases | Gross amount, amount already paid and, where applicable, rounding amount. |
An invoice may be contradictory in several places. Therefore, the examination should begin with the positions and then continue through net and tax until the open amount. If you change only the last amount, you may hide the actual cause.
Systematic correction of rounding differences
A freely entered cent correction, an invisible additional position or a subsequently modified PDF total amount should not cover any unexplained error. A separate amount of the amount of the payment rounding has a defined function in the data model; it does not replace coherent position or tax calculations.
After a correction, some deliberately selected test cases are useful: a simple invoice, an invoice with a precise individual price, several items with the same tax rate and, if necessary, an invoice with several tax groups. The expected results should be independently recalculated before the issue.
TabellenFaktura takes associated data from the Excel workbook and checks the generated e-invoice locally. The template must provide the technically intended values. Create ZUGFeRD statement from Excel. The other fault images are used to help Overview with seven typical Excel errorsThe contribution to the Interim and final invoices a complete example.
Technically checked on 23. September 2026. The basics are the above-linked Microsoft documentation and the published EN-16931 and Peppol rules. The examples were calculated specifically for this contribution. They deal with positive invoice amounts and the respective explicit assumptions. Credit, tax exemptions and complex price agreements require a suitable separate consideration.
Frequently asked questions
Why is the sum not correct, although Excel shows two decimal places everywhere?
The cell format changes the display first. Formulas can continue with additional saved decimal places. If individual amounts are rounded for the e-invoice, but the totals are calculated from unrounded values, differences can arise.
Which Excel formula is suitable for a simple net position amount?
For quantity in B2 and net unit price in C2, a simple formula is =ROUNDS(B2*C2;2) and requires a price base of one unit and does not yet contain any valent discounts or supplements related to valent to valentines.
May individual prices have more than two decimal places?
Single prices and net position amounts are different data fields. The two-digit rule for the net position amount is not a general restriction on all entries. The permissible accuracy and output must fit the used format and profile.
Is the VAT rounded individually for each position?
For the EN 16931 VAT breakdown considered in this article, the VAT amount is calculated from the taxable amount of each tax group and rounded to two decimal places. The sum of individually rounded tax values may differ.
Can a rounding amount fix each cent difference?
No. A rounding amount for the amount of the payment does not repair any wrong position sum and no contradictory tax calculation. First, it must be clarified at which point the difference arises.
Why can two validators evaluate the same calculation differently?
Format, profile, version and specific test rules may differ. Configuration, control level and reported tolerances should therefore be compared when evaluating the results. A suitable test setup remains crucial.