
A e-invoice may contain items with different tax rates. However, for ZUGFeRD and XRechnung the associated tax groups must be calculated separately and shown in a structured manner, especially if a discount on the entire invoice has been agreed.
EUR 200.00 excluding VAT at each of the 7% and 19% rates gives an initial net total of EUR 400.00. A uniform discount of 10% reduces each group to EUR 180.00. This produces VAT of EUR 12.60 and EUR 34.20, giving a total of EUR 406.80.
Several rates mean separate calculations
The net total amount alone is not sufficient to determine the VAT of a mixed invoice. For each combination of tax category and tax rate, a separate tax base is created. Only after that the tax amounts are added.
The following example deliberately presupposes the tax classification: Category A is taxed here with 7 percent, Category B with 19 percent. The designations are examples of calculations and no classification of concrete goods. Which rate in the actual case applies depends on the performance and in particular § 12 UStG.
Both groups are, for example, taxable transactions of category S. "Reduced tax rate, does not automatically mean a category other than S. Tax exemption, zero tax rate and reverse tax liability are separate cases and cannot be replicated by any change in percentage.
Example calculation with 10 percentage discount
The fictitious invoice contains two items. The same discount of 10 per cent is granted on both net amounts. Advance payments and additional costs do not exist.
| Item | Quantity | Net price | Line sum | Rate: |
|---|---|---|---|---|
| Category A | 10 | 20,00 € | 200,00 € | 7 % |
| Category B | 5 | 40,00 € | 200,00 € | 19 % |
| Total items | 400,00 € | two groups | ||
The total discount of 40,00 Euro is distributed here with 20,00 Euro each between the two groups. This breakdown is based on the same net sum and the percentage agreed for both groups.
| Calculation step | Group 7 % | Group 19 % | Total |
|---|---|---|---|
| Total net | 200,00 € | 200,00 € | 400,00 € |
| 10 % Invoice discount | −20,00 € | −20,00 € | −40,00 € |
| Tax base after discount | 180,00 € | 180,00 € | 360,00 € |
| VAT | 12,60 € | 34,20 € | 46,80 € |
| Gross amount | 192,60 € | 214,20 € | 406,80 € |
A discounted total of EUR 360,00 net with a flat rate of EUR 19 per cent tax would be wrong. Likewise, it would be wrong to assign the total discount of EUR 40,00 to the 19 percentage group only, although the agreement concerns both groups equally.
Excel formulas for multiple VAT rates
In the example, C12:C13 contains the amounts, D12:D13 the net prices and E12:E13 the percentages stored. In F12 stands =RUNDEN(C12*D12;2); F13 uses the same formula for the second line item.
- Position sum of the 7 percentage group:
=SUMMEWENN(E12:E13;→ 200,00 Euro7%; F12:F13) - Position sum of the 19 percentage group:
=SUMMEWENN(E12:E13;→ 200,00 Euro19%; F12:F13) - Discount per group: respective position sum × 10 %, rounded to two digits → each 20,00 Euro
- Tax base per group: position sum minus group discount → each 180,00 Euro
- Tax 7 %:
=RUNDEN(180*7%;2)→ 12,60 Euro - Tax 19 %:
=RUNDEN(180*19%;2)→ 34,20 Euro
The numbers 180 in the last two formulas are used for explanation. In a reusable template, the respective result cells should be added. For further positions, the evaluated area must grow. Otherwise, a position outside the sum range is missing in the tax base, even if it is visible in the pressure range.
For this example, the grouping is sufficient by percentage, because both items have the same tax category. If a template contains different tax categories, the grouping must take into account both criteria. SUMMEWENNS Page 3
What is the control data of the e-invoice?
The structured invoice contains the tax classification of each item and additionally the summary of each tax group. A document discount is also recorded with its tax category and its rate. In the example, two discount shares are generated at 20,00 Euro each.
| Field of expertise | First group | Second group |
|---|---|---|
| Tax category BT-118 | S | S |
| Rate BT-119 | 7 | 19 |
| Tax base BT-116 | 180,00 € | 180,00 € |
| VAT amount BT-117 | 12,60 € | 34,20 € |
The EN-16931-Rule BR-S-08 the tax base with the appropriate billing lines, surcharges and discounts. Rule BR-CO-17 describes the relationship between the tax base, rate and the rounded amount of tax.
Both groups are subject to the tax of EUR 360,00 without tax, EUR 46,80 tax and EUR 406,80 with tax. Because no payment was made in the example, the amount due is the gross amount. A prepayment would be taken into account separately; it is not an additional negative item.
These errors often only occur when exporting
- A tax formula for everything: The total net sum is multiplied by only one sentence.
- Discount without tax allocation: The visible trigger is not considered in the XML control groups.
- Percentages as text: "7 %, is text in a cell, while the formula expects the numerical value 0,07.
- Grouped only by 0 %: Different tax-free or non-taxable situations are mixed.
- Double discount: Already reduced lines are shortened again by the same document discount.
- Different rounding: PDF and XML use different intermediate values.
In the case of unequal groups and a fixed total discount, the distribution of the actual agreement must follow. Any rounding rest must be assigned to a group in a comprehensible way, so that the sum of the partial discounts corresponds exactly to the total discount. Rounding errors at ZUGFeRD deals with the cent vote.
Checklist for the finished file
- For each item, the tax category and rate have been determined in a technical way.
- The group totals contain all associated positions.
- Discounts and supplements are taken into account exactly once and in the right group.
- The XML key includes both tax bases and both tax amounts.
- 360,00 Euro net plus 46,80 Euro tax is calculated in the example 406,80 Euro brutto.
- PDF presentation, structured values and test report were jointly controlled.
The article Discount and discount in the e-invoice deepens the types of estate. The complete creation of an existing template describes Creating ZUGFeRD invoices with Excel.
Frequently asked questions
May contain an e-invoice 7 and 19 percent VAT?
Yes, if the benefits are to be classified in tax terms, the items and the tax summary must correctly represent the different groups.
Which tax category is applicable in the example for 7 percent?
In the example shown, both the items with 7 and those with 19 percent taxable use category S. The tax rate distinguishes the two groups.
How is a discount distributed between both tax groups?
In the example, both groups receive the same discount of 10 percent on each of the nets of 200,00 euros. Therefore, the discount of 20,00 euros is due to the 7 and 19 percentage groups.
Can the VAT be calculated on the total net sum?
For different rates, not with a single rate. First, the tax is calculated and rounded per group, then the tax amounts are added.
Is a grouping enough by percentage?
Only if the tax category within each group is also consistent. For different tax categories, category and rate must be taken into account together.