Calculateur Excel TVA collectée/déductible par taux et repérer les risques de pro-rata

Ce guide explique comment organiser vos données, faire les calculs par taux, appliquer un pro-rata (provisoire ou définitif) et sécuriser vos déclarations. Il s’appuie sur un modèle Excel simple (Ventes, Achats, Résumé) mais les principes valent pour tout outil.

1) Les fondamentaux à garder en tête

Deux flux :
TVA collectée (sur les ventes taxées) et TVA déductible (sur les achats/frais).
Par taux : 20 % • 10 % • 5,5 % • 2,1 % • 0 % (exonéré ou hors champ).
Droit à déduction :
- OD (ouvre droit) → déductible à 100 %
- NOD (n’ouvre pas droit) → non déductible
- MIXTE (servant à des opérations taxées et exonérées) → soumis au pro-rata
Pro-rata = part d’activité taxable :

Il peut être provisoire (en cours d’année) puis régularisé avec un pro-rata définitif.

2) Structurer les données : la clé de la fiabilité

Ventes (TVA collectée)

Colonnes : Date • Pièce • Client • Catégorie (OD, NOD, HC) • Taux • Base HT • TVA
Règles :
- OD → TVA = Base × Taux
- NOD (exonéré) → Base compte pour le pro-rata (au dénominateur)
- HC (hors champ) → hors pro-rata (ni numérateur, ni dénominateur)

Achats (TVA déductible)

Colonnes : Date • Pièce • Fournisseur • Nature (OD, MIXTE, NOD) • Taux • Base HT • TVA facture
Déduction :
- OD → déductible 100 %
- MIXTE → déductible × pro-rata
- NOD → non déductible (à suivre pour contrôle interne)

3) Les formules utiles (Excel FR ; adaptez `;`/`,` selon vos paramètres)

3.1. TVA collectée (ligne de vente OD)

=ARRONDI(Base_HT * Taux_% ; 2)

3.2. TVA déductible sur achat

Brute :

=ARRONDI(Base_HT * Taux_% ; 2)

Après pro-rata (si Nature = MIXTE) :

=SI(Nature="MIXTE" ; ARRONDI(TVA_Facture * Prorata ; 2) ;
   SI(Nature="OD" ; TVA_Facture ; 0))

3.3. Pro-rata auto (dans l’onglet Résumé)

CA_OD   = SOMME.SI.ENS(Ventes[Base_HT] ; Ventes[Catégorie] ; "OD")
CA_NOD  = SOMME.SI.ENS(Ventes[Base_HT] ; Ventes[Catégorie] ; "NOD")
Prorata = SI(CA_OD+CA_NOD>0 ; CA_OD / (CA_OD + CA_NOD) ; 1)

Si vous connaissez le pro-rata définitif, saisissez-le dans une cellule « Pro-rata utilisé » qui écrase l’auto :
Prorata_utilisé = SI(Prorata_saisi>0 ; Prorata_saisi ; Prorata_auto)

3.4. Synthèse par taux

TVA_coll_taux = SOMME.SI.ENS(Ventes[TVA] ; Ventes[Taux] ; 20 ; Ventes[Catégorie] ; "OD")
TVA_ded_taux  = SOMME.SI.ENS(Achats[TVA_Apres_ProRata] ; Achats[Taux] ; 20)

Répétez pour 10 %, 5,5 %, 2,1 %, 0 %.

3.5. Totaux et net

TVA_collectee_totale   = SOMME(plage_TVA_coll_par_taux)
TVA_deductible_totale  = SOMME(plage_TVA_ded_par_taux)
NET_A_PAYER            = TVA_collectee_totale - TVA_deductible_totale

4) Pro-rata : bonnes pratiques et cas particuliers

Provisoire vs définitif
- Au fil de l’année, appliquez un pro-rata provisoire (ex. celui de N-1).
- En fin d’exercice, calculez le définitif à partir du CA réel ; régularisez l’écart (écriture d’ajustement).
Seuil de 10 %
- Si la part de CA exonéré ≥ 10 %, le risque de mixité est élevé → attendez-vous à un pro-rata < 100 %.
Immobilisations
- Leur TVA suit des règles spécifiques de régularisation pluriannuelle ; si vous en avez, traitez-les séparément (suivi poste à poste).
Opérations hors champ (HC)
- Ni au numérateur, ni au dénominateur. À ne pas confondre avec NOD (exonéré mais dans la fraction).
Sectorisation
- Si plusieurs activités distinctes, vous pouvez tenir un pro-rata par secteur (plus fin, mais exige une comptabilité analytique).

5) Contrôles et “filets de sécurité”

Cohérence arithmétique
- TVA déductible brute ≥ TVA déd. après pro-rata
- TVA déd. après pro-rata + Non déductible = TVA facture
- Net = Collectée − Déductible
Couverture par taux : toutes les ventes/achats ont un taux reconnu (20/10/5,5/2,1/0).
Alerte mixité : CA_NOD / (CA_OD + CA_NOD) ≥ 10 % → ⚠ pro-rata probable.
Zéros “fantômes” : si tout reste à 0 alors qu’il y a des lignes, vérifier Calcul automatique (ou Ctrl+Alt+F9).
Données “texte” : nettoyer espaces insécables et % (SUBSTITUE( ;CAR(160);"" )).
Journal de régularisation : tracer le passage provisoire → définitif (cellule de prorata saisi + écart).

6) Exemple compact

Ventes
- OD 20 % : 80 000 € → TVA coll. 16 000 €
- OD 10 % : 10 000 € → TVA coll. 1 000 €
- NOD 0 % : 10 000 €
Achats (TVA facture)
- OD : 6 000 € (déductible 6 000 €)
- MIXTE : 5 000 € (déductible × pro-rata)
- NOD : 800 € (non déductible)

Pro-rata auto = 80 000+10 000 / (80 000+10 000+10 000) = 90 %
Déduction MIXTE = 5 000 × 90 % = 4 500 €
TVA déductible totale = 6 000 + 4 500 = 10 500 €
TVA collectée totale = 16 000 + 1 000 = 17 000 €
Net = 17 000 − 10 500 = 6 500 € à payer.

7) Écritures comptables (schéma usuel)

Constat TVA collectée :
- Débit 707/compte de produits (HT)
- Crédit 44571 TVA collectée
Constat TVA déductible :
- Débit 44566 TVA déductible
- Crédit 401/Compte fournisseur
Déclaration / paiement :
- Si TVA à payer : Débit 44571 – Crédit 44551 (ou 512 au paiement)
- Si crédit de TVA : Débit 44567 – Crédit 44566 (report/crédit)

Pour la régularisation du pro-rata (provisoire ↔ définitif), passez l’écart en 44566/44567.

Checklist de mise en production

Listes de valeurs : OD/NOD/HC (ventes), OD/MIXTE/NOD (achats).
Taux standardisés : 20 ; 10 ; 5,5 ; 2,1 ; 0.
Helpers numériques (taux %, base, TVA) pour éliminer #VALEUR!.
Pro-rata auto + cellule de prorata utilisé (prioritaire).
Tableau par taux + totaux + net.
Indicateurs : part exonérée, présence d’achats MIXTE, achats NOD avec TVA > 0, taux “non standard”.
Aide au recalcul : message si lignes > 0 mais totaux = 0.
Archivage : figez un PDF à chaque fin de mois + export des écritures.

FAQ rapide

Ventilation des subventions ? Selon le cas, elles peuvent majorer le dénominateur (NOD), ce qui baisse le pro-rata.
Base 360/365 ? Sans enjeu ici (pas d’intérêts), mais gardez une unité sur vos taux et arrondis.
Immobilisations ? Faites un suivi séparé (régularisation pluriannuelle).
Plusieurs activités ? Songez à une sectorisation du pro-rata.

TVA_ProRata Download

Modèle Excel “TVA – Pro-rata

Un classeur prêt à l’emploi pour :

calculer la TVA collectée (par taux) et la TVA déductible (brute / après pro-rata),
appliquer un pro-rata provisoire ou définitif,
sortir un net à payer / crédit de TVA,
signaler les risques de pro-rata et les incohérences.

Architecture du fichier

Onglet Ventes
Saisie des ventes : Date – Pièce – Client – Catégorie (OD/NOD/HC) – Taux – Base HT.
Calcul : TVA collectée.
Aides cachées (colonnes H:I:J) : Taux % (numérique), Base (numérique), TVA (numérique) pour des totaux fiables même si vous collez du texte (« 20% », « 5,5 », espaces, etc.).
Onglet Achats
Saisie des achats/frais : Date – Pièce – Fournisseur – Nature (OD/MIXTE/NOD) – Taux – Base HT.
Calculs : TVA facture, TVA déductible brute, TVA déductible après pro-rata, Non déductible.
Aide cachée (col. L) : TVA après pro-rata (numérique) pour les totaux.
Onglet Resume (page résultats)
- Pro-rata AUTO = CA OD / (CA OD + NOD).
- Pro-rata UTILISÉ (B5) : si vous entrez une valeur (ex. 80 %), elle remplace l’auto.
- Tableau par taux : TVA collectée (OD), TVA déductible après pro-rata, Non déductible.
- Totals : Collectée, Déductible, Net à payer / Créance.
- Indicateurs & Alerte recalcul : part exonérée ≥10 %, présence d’achats MIXTE, et message si des lignes existent mais que les totaux restent à 0 (pense-bête « Calcul automatique »).

Logique de calcul

Ventes
- OD → TVA = Base × Taux.
- NOD (exonéré) → compte au dénominateur du pro-rata (pas de TVA).
- HC → hors fraction de pro-rata.
Achats
- OD → déductible 100 %.
- MIXTE → déductible TVA facture × PRORATA.
- NOD → non déductible.
Par taux : 20 % • 10 % • 5,5 % • 2,1 % • 0 % (modifiable).
Net = TVA collectée totale − TVA déductible (après pro-rata).

Robustesse technique (anti-erreurs)

Coercitions automatiques : les colonnes d’aide convertissent les saisies « texte » en nombres (VALUE + SUBSTITUTE), ce qui élimine #VALEUR!.
Nom défini PRORATA (lié à Resume!A8) pour éviter les soucis de références inter-feuilles.
Totaux via SUMIFS sur les colonnes numériques (fiables même si les cellules visibles affichent 0,00 €).
Mises en forme :
- Titres colorés (bleu), blocs résultats en jaune, messages/alertes en rouge/vert.

Utilisation en 3 étapes

Renseigner Ventes (OD/NOD/HC, taux et bases).
Renseigner Achats (OD/MIXTE/NOD, taux et bases).
Lire Resume : ajuster B5 si vous avez un pro-rata définitif, vérifier les indicateurs, récupérer le Net à payer/crédit.

Contrôles intégrés

TVA déd. après pro-rata ≤ TVA brute ; Non déductible = TVA facture − TVA après pro-rata.
Net = Collectée − Déductible.
Alerte si part exonérée ≥10 % (risque de pro-rata) et si achats MIXTE avec pro-rata = 0 % ou 100 %.