Créer sa simulation d'assurance vie sur Excel : guide pas-à-pas avec formules

Un tableur Excel permet de simuler son assurance vie avec une précision supérieure aux outils en ligne en intégrant ses frais réels, des versements irréguliers et la fiscalité applicable. La fonction VC() calcule le capital futur par capitalisation composée, tandis que la Recherche d'objectif (Goal Seek) détermine le versement mensuel nécessaire pour atteindre un objectif. Après 8 ans, les gains bénéficient d'un abattement annuel de 4 600 € (9 200 € pour un couple) selon service-public.fr.

Une simulation d'assurance vie sur Excel vous donne le contrôle qu'aucun outil en ligne n'offre : intégrer vos frais réels, modéliser des versements irréguliers et anticiper l'impact fiscal rachat par rachat. Ce guide détaille colonne par colonne la construction du tableur, des formules de base jusqu'à la couche fiscale complète (abattement des 8 ans, PFU, prélèvements sociaux), avec un scénario chiffré reproductible.

Pourquoi modéliser son assurance vie dans un tableur plutôt qu'un simulateur en ligne

Les simulateurs en ligne gratuits : Boursorama, Crédit Agricole, ou les outils des courtiers : donnent une première estimation. Mais ils restent enfermés dans les paramètres que leur concepteur a choisis. Un fichier Excel que vous construisez vous-même échappe à ces limites.

La valeur d'un tableur personnel tient à quatre choses. D'abord, vous maîtrisez chaque hypothèse : le taux net de frais de gestion de votre contrat réel, pas une moyenne de marché. Ensuite, vous pouvez modéliser des versements irréguliers : un bonus annuel placé en décembre, une pause d'épargne pendant six mois. Aucun simulateur grand public ne le fait proprement.

Troisièmement, vous conservez l'historique de vos scénarios. Un onglet par hypothèse de rendement, un autre par durée : vous comparez sans perdre le fil. Quatrièmement, la couche fiscale que vous programmez dans Excel reflète votre situation personnelle : abattement de 4 600 € ou 9 200 € selon votre foyer fiscal, tranche marginale d'imposition si vous optez pour le barème progressif plutôt que le PFU.

Les limites des simulateurs en ligne grand public

Les simulateurs en ligne partagent trois défauts majeurs. Le premier : ils utilisent des frais de gestion moyens : souvent autour de 0,6 % à 1 % par an : qui ne correspondent pas forcément à votre contrat. Un contrat ancien peut prélever 0,5 % sur le fonds euros quand un contrat récent en prélève 0,8 %. L'écart sur 15 ans atteint plusieurs centaines d'euros.

Deuxième limite : la fiscalité est traitée de façon simpliste. Beaucoup d'outils appliquent le PFU de 12,8 % sans proposer l'option barème progressif, pourtant plus favorable pour les contribuables faiblement imposés. Troisième lacune : les versements irréguliers. Un simulateur classique raisonne en mensualités constantes. Impossible d'y programmer un versement exceptionnel de 5 000 € en année 3 suivi d'une année blanche.

Ce qu'un tableur bien construit permet que les outils clé-en-main ne font pas

Un tableur personnel ouvre trois possibilités absentes des outils clé-en-main. Vous pouvez simuler un rachat partiel programmé : par exemple 8 000 € par an pendant 5 ans : et visualiser l'impact sur le capital restant et la fiscalité due, année après année. La plupart des simulateurs en ligne ne sortent qu'un résultat final.

Vous pouvez aussi comparer directement deux stratégies : assurance vie versus compte-titres ordinaire, fonds euros versus unités de compte, versement mensuel unique versus arbitrage progressif. Un onglet par scénario, une cellule de synthèse qui fait le delta. Enfin, la Recherche d'objectif (Goal Seek) d'Excel répond à la question « combien dois-je épargner par mois pour atteindre 100 000 € dans 15 ans ? » sans tâtonnement manuel.

Les variables indispensables à renseigner dans votre simulation

Avant de poser la première formule, il faut lister les variables qui piloteront toute la simulation. Chaque paramètre doit occuper une cellule dédiée en haut du fichier, avec un libellé clair. Cette pratique : séparer les hypothèses des calculs : est la règle d'or du tableur financier.

Les variables se répartissent en quatre catégories. Les montants (capital initial, versement mensuel), la durée (en mois ou en années, à convertir dans les formules), le rendement (taux annuel net ou brut selon ce que vous voulez modéliser), et la fiscalité (taux et abattements applicables). Documenter chaque hypothèse dans une cellule adjacente évite de se perdre quand on rouvre le fichier six mois plus tard.

Capital de départ et versements programmés

Trois cellules forment la base : le capital de départ, le versement mensuel récurrent, et la durée. Le capital initial (ex. 5 000 €) est placé dès le mois zéro. Le versement mensuel (ex. 200 €) est ajouté chaque mois, en début ou en fin de période selon votre convention. La durée, exprimée en années, doit être convertie en nombre de mois pour le calcul ligne à ligne : une simple multiplication par 12.

Pour les versements ponctuels, ajoutez une colonne « Versement exceptionnel » que vous remplissez uniquement les mois concernés. La formule de solde viendra y piocher mécaniquement. Cette approche capture la réalité de l'épargne : personne n'épargne 200 € le 1er de chaque mois pendant 15 ans sans variation.

Rendement : fonds euros vs unités de compte

Le taux de rendement est la variable la plus sensible. Un écart de 0,5 point sur 20 ans change radicalement le résultat. Deux cas de figure : le fonds euros, dont le rendement est publié chaque année par l'assureur (net de frais de gestion mais brut de prélèvements sociaux), et les unités de compte, dont la performance est par nature incertaine et non linéaire.

Pour le fonds euros, utilisez le dernier taux publié par votre assureur comme hypothèse prudente. Pour les UC, mieux vaut tester plusieurs scénarios : un taux bas (2 %), un taux médian (4 %), un taux haut (6 %). Trois onglets, un résultat à chaque fois. Le taux à saisir dans les formules est le taux périodique : divisez le taux annuel par 12 pour du mensuel.

Frais à ne surtout pas oublier dans le calcul

Quatre catégories de frais grèvent le rendement réel. Les frais d'entrée (0 % à 5 % du versement) sont prélevés à chaque dépôt : un versement de 200 € avec 3 % de frais d'entrée ne place que 194 €. Les frais de gestion annuels sur le fonds euros (0,4 % à 1 %) sont intégrés dans le taux net publié par l'assureur : ne les déduisez pas une seconde fois.

Les frais de gestion sur UC (0,5 % à 1,2 % par an) sont prélevés sur la valeur de rachat, donc à intégrer dans votre taux net d'hypothèse. Les frais d'arbitrage (0 % à 1 % du montant arbitré, parfois forfaitaires) s'appliquent quand vous réallouez entre supports. Enfin, les prélèvements sociaux à 17,2 % sur les intérêts du fonds euros sont prélevés annuellement par l'assureur : votre tableur doit les déduire chaque année, pas uniquement en fin de simulation.

L'essentiel

• Un fichier Excel personnel capture vos frais réels et vos versements irréguliers, là où les simulateurs en ligne appliquent des moyennes standardisées
• La fonction VC() combinée à la Recherche d'objectif permet de modéliser à la fois la projection et l'effort d'épargne nécessaire
• Ne pas oublier les prélèvements sociaux annuels de 17,2 % sur les intérêts du fonds euros : cet oubli surestime le capital final de plusieurs milliers d'euros sur 10 ans

Construire le tableau Excel colonne par colonne

La structure du tableau repose sur une ligne par mois. Chaque ligne calcule l'état du contrat à la fin de ce mois, en repartant du solde final du mois précédent. Cette approche ligne à ligne, qu'on appelle aussi « tableau d'amortissement de l'épargne », donne une vision complète de la trajectoire.

Placez vos variables d'hypothèse (capital initial, versement, taux, etc.) dans des cellules nommées en haut du fichier. Utilisez des références absolues (avec le signe $) pour que les formules pointent toujours vers ces cellules quand vous les tirez vers le bas sur 120, 180 ou 240 lignes. Une erreur de référence relative est la première cause de résultat aberrant dans un fichier d'épargne.

Structure des colonnes et formules de base

Voici les sept colonnes recommandées, avec leur formule type en ligne 2 (la ligne 1 porte les en-têtes).

• Mois : =LIGNE()-1 : numérote automatiquement de 1 à la durée.
• Solde initial : =M1 pour la ligne 2 (le capital de départ), puis =F1 (solde final du mois précédent) pour les lignes suivantes.
• Versement : =$B$2 (référence absolue à la cellule du versement mensuel), recopié sur toutes les lignes.
• Versement exceptionnel : colonne manuelle, remplie uniquement les mois concernés.
• Intérêts bruts du mois : =(B2+C2+D2)*($E$2/12) : applique le taux mensuel (taux annuel/12) sur le solde initial + versements du mois.
• Prélèvements sociaux : =E2*17,2% pour le fonds euros (prélevés chaque année sur les intérêts de l'année).
• Solde final : =B2+C2+D2+E2-F2.

Cette structure à sept colonnes couvre l'essentiel. Ajoutez une colonne « Frais d'entrée » si votre contrat en comporte, avec la formule =C2*$Frais_entree déduite du montant net investi.

Utiliser la Recherche d'objectif (Goal Seek) pour trouver le bon versement

La Recherche d'objectif (Données > Analyse de scénarios > Recherche d'objectif) est la fonction qui transforme un tableur de projection en outil de décision. Elle inverse le calcul : au lieu de partir d'un versement pour trouver un capital final, elle part du capital souhaité pour déterminer le versement nécessaire.

La manipulation : dans la cellule du solde final (dernière ligne, colonne F), vous définissez la valeur cible (ex. 100 000 €). Dans « cellule à modifier », vous pointez la cellule du versement mensuel (B2). Excel ajuste automatiquement le montant jusqu'à ce que le solde final atteigne l'objectif. Le résultat est immédiat.

Deux précautions. D'abord, vérifiez que votre modèle est cohérent avant de lancer la recherche : une erreur de référence produit un résultat aberrant. Ensuite, pour un objectif combinant capital final et retraits programmés, il faut passer par le Solveur (complément à activer dans les options Excel), qui gère plusieurs contraintes simultanées.

Automatiser le calcul des intérêts composés

La fonction VC() d'Excel donne une estimation rapide sans construire le tableau ligne à ligne. Sa syntaxe : =VC(taux_périodique; nombre_de_périodes; -versement; -capital_initial). Le signe négatif indique une sortie de trésorerie (vous payez).

Exemple : =VC(2,5%/12; 120; -200; -5000) calcule le capital après 10 ans avec 2,5 % annuel, 200 € mensuels et 5 000 € de départ. Résultat : environ 33 400 €.

Deux limites à connaître. VC() suppose des versements constants et un taux fixe : irréaliste pour les UC. La fonction ne gère pas non plus la fiscalité ni les prélèvements sociaux annuels. Pour ces raisons, VC() sert de contrôle rapide ou de point d'entrée dans le modèle, mais le tableau ligne à ligne reste indispensable pour la version finale intégrant la couche fiscale.

Pour un taux variable dans le temps (baisse progressive du rendement du fonds euros par exemple), vous pouvez utiliser une colonne dédiée au taux mensuel que vous faites évoluer manuellement. La formule d'intérêts devient alors =(Solde_initial+Versements)*Taux_mensuel_ligne, ce qui donne une projection plus réaliste qu'un taux constant sur 20 ans.

Intégrer la fiscalité de l'assurance vie dans votre simulateur Excel

La couche fiscale est ce qui distingue un tableur amateur d'un outil de pilotage patrimonial. L'assurance vie n'est pas un compte d'épargne ordinaire : la fiscalité dépend de la date des versements, de la durée du contrat, et du type de support. Intégrer ces règles dans des formules SI() transforme la projection brute en résultat net actionnable.

On distingue trois strates fiscales. Les prélèvements sociaux (17,2 % selon service-public.fr), prélevés annuellement sur les intérêts du fonds euros ou au rachat pour les UC. L'impôt sur le revenu (PFU à 12,8 % ou barème progressif, selon l'option choisie). Et l'abattement annuel de 4 600 € pour une personne seule (9 200 € pour un couple) applicable sur les gains après 8 ans de détention, comme le précise le barème fiscal du rachat assurance vie 2026.

La règle des 8 ans et ses abattements

Le seuil des 8 ans est le pivot fiscal de l'assurance vie. Avant 8 ans, les gains sont soumis au PFU de 12,8 % (ou barème progressif sur option) pour les versements postérieurs à septembre 2017. Après 8 ans, l'abattement annuel de 4 600 € (personne seule) ou 9 200 € (couple marié ou pacsé) s'applique sur la part de gain incluse dans le rachat.

Concrètement, un épargnant seul qui rachète 20 000 € incluant 5 000 € de plus-values après 8 ans ne paie d'impôt que sur 400 € (5 000 − 4 600), soit 51,20 € au PFU. Sans l'abattement, l'impôt serait de 640 €. Le tableur doit refléter cette mécanique pour ne pas surestimer la charge fiscale.

Pour les versements antérieurs au 27 septembre 2017, le régime d'imposition diffère : le PFU n'est pas applicable de plein droit, l'option pour le barème progressif reste possible. Si votre contrat comporte des versements anciens, ajoutez une colonne « millésime du versement » et appliquez la règle correspondante dans vos formules de rachat.

Modéliser le PFU et les prélèvements sociaux dans une formule SI()

La formule SI() permet d'automatiser le calcul de l'impôt dans une feuille dédiée au rachat. Voici une structure simplifiée pour un rachat partiel post-8 ans avec PFU :

• Gain taxable : =MAX(0; Plus_value_rachat - Abattement) : l'abattement ne peut pas créer de crédit d'impôt.
• Impôt : =Gain_taxable * 12,8% : PFU sur la part excédant l'abattement.
• Prélèvements sociaux : =Plus_value_rachat * 17,2% : pour les UC, car ils n'ont pas été prélevés annuellement.

Pour un rachat total, la formule calcule la plus-value totale depuis l'origine. Pour un rachat partiel, la part de gain se calcule au prorata : Plus_value_totale * (Montant_rachat / Valeur_rachat_totale). Cette règle du prorata est essentielle : un rachat de 10 000 € sur un contrat de 50 000 € contenant 20 000 € de plus-values génère 4 000 € de gain taxable.

Cas pratique chiffré : simuler 200 € par mois pendant 10 ans

Prenons un scénario réaliste. Un épargnant de 35 ans place 5 000 € à l'ouverture, puis 200 € par mois pendant 10 ans. L'hypothèse de rendement est fixée à 2,5 % net de frais de gestion pour un contrat 100 % fonds euros : un taux prudent, inférieur aux rendements servis par les meilleurs contrats en 2025, mais cohérent avec une projection conservatrice sur une décennie.

L'objectif de cette simulation n'est pas de prédire le rendement futur du fonds euros, mais d'illustrer la mécanique du calcul et l'impact de chaque paramètre. Les résultats chiffrés ci-dessous sont purement illustratifs et ne constituent en aucune façon une promesse de gain. Le guide complet sur la fiscalité de l'assurance vie détaille les règles applicables à tous les types de contrats.

Hypothèses du scénario et paramétrage du tableur

Paramétrage du fichier :

• Capital initial : 5 000 € (cellule B1)
• Versement mensuel : 200 € (cellule B2)
• Durée : 120 mois (10 ans)
• Rendement annuel net : 2,5 % (cellule B3)
• Taux mensuel : =B3/12 = 0,2083 %
• Prélèvements sociaux annuels : 17,2 % sur les intérêts du fonds euros
• Frais d'entrée : 0 % (contrat en ligne sans frais d'entrée)

Le tableau comporte 120 lignes (une par mois), avec les sept colonnes décrites précédemment. Les intérêts sont calculés chaque mois sur le solde initial + versement. Les prélèvements sociaux sont déduits mensuellement des intérêts générés. L'abattement fiscal post-8 ans est modélisé dans une feuille séparée de calcul de rachat.

Résultat brut, résultat net de fiscalité : le tableau comparatif

Résultats après 120 mois (10 ans) :

L'impôt résiduel est quasi nul grâce à l'abattement de 4 600 € qui couvre la quasi-totalité du gain. Sans cet abattement (rachat avant 8 ans), l'impôt serait d'environ 619 € (12,8 % × 4 834 €), ramenant le capital net à environ 32 384 €. La différence : près de 600 €.

Quel écart avec un compte-titres ordinaire sur la même durée ?

Comparons ce même scénario avec un compte-titres ordinaire (CTO) sur 10 ans. Hypothèses identiques : 5 000 € + 200 €/mois, rendement 2,5 % avant fiscalité.

Sur un CTO, les intérêts (dividendes, coupons) sont imposés chaque année au PFU de 12,8 % + prélèvements sociaux 17,2 %, soit un total de 30 % dès la première année. Le capital net après 10 ans atteint environ 32 000 €, contre près de 32 973 € en assurance vie. L'écart : environ 973 € : provient de deux mécanismes : l'abattement post-8 ans et l'absence de taxation annuelle des intérêts capitalisés en assurance vie.

Pour un couple marié, l'écart se creuse davantage : l'abattement de 9 200 € annule toute imposition sur les gains, portant le capital net à près de 33 003 € (seuls les prélèvements sociaux sont dus). Sur un CTO, le couple subit la même flat tax de 30 %. L'écart dépasse alors 1 000 €. Ces chiffres illustrent l'effet de la fiscalité sur une durée longue, sans constituer une prévision. Le comparatif assurance vie vs PEA approfondit l'arbitrage entre ces deux enveloppes.

L'erreur courante qui fausse toutes vos projections

L'erreur la plus fréquente dans les tableurs d'épargne maison : et la plus coûteuse : consiste à traiter la fiscalité du fonds euros comme celle des unités de compte. En UC, les prélèvements sociaux de 17,2 % sont prélevés uniquement au moment du rachat. Sur le fonds euros, ils sont prélevés chaque année par l'assureur, au fil de l'eau, sur les intérêts crédités.

La conséquence d'un tableur qui ne déduit pas ces prélèvements annuels : le capital final affiché est surestimé. Sur le scénario de 200 €/mois à 2,5 % pendant 10 ans, omettre les 17,2 % annuels gonfle le capital affiché d'environ 1 200 €. Le piège joue à plein sur une simulation à 20 ou 30 ans, où l'écart cumulé dépasse plusieurs milliers d'euros. La correction est simple : ajouter une colonne qui soustrait chaque mois Intérêts_bruts × 17,2 % du solde final.

Les prélèvements sociaux annuels sur le fonds euros : le calcul souvent oublié

Sur un contrat 100 % fonds euros, les intérêts sont crédités annuellement (parfois trimestriellement) et les prélèvements sociaux sont aussitôt déduits. Le taux net servi par l'assureur : par exemple 2,5 % en 2025 : est déjà net de frais de gestion mais brut de prélèvements sociaux. Le rendement réellement disponible sur le contrat est donc : taux net publié × (1 − 17,2 %).

Avec un taux net de 2,5 %, le rendement après prélèvements sociaux tombe à environ 2,07 %. La formule Excel doit refléter cette réalité : soit en intégrant les prélèvements dans une colonne dédiée, soit en utilisant un taux déjà « super net » dans la colonne d'intérêts et en conservant une colonne de PS pour le suivi.

La première approche est préférable pour un suivi précis : elle distingue clairement l'enrichissement brut du prélèvement social dans deux colonnes séparées. Vous visualisez immédiatement ce que l'État prélève chaque année sur votre épargne.

Taux brut vs taux net : ne pas confondre rendement affiché et rendement réel

Deuxième confusion classique : utiliser le taux de rendement brut affiché par l'assureur dans la presse, au lieu du taux net de frais de gestion de votre contrat. Le taux affiché est généralement un taux moyen tous contrats confondus, ou celui du meilleur contrat de la gamme. Votre contrat peut servir un taux inférieur d'un demi-point.

Si votre contrat prélève 0,75 % de frais de gestion annuels sur le fonds euros et que le rendement brut du support est de 3 %, le taux à saisir dans le tableur est 2,25 % (3 − 0,75), pas 3 %. Un écart de 0,75 point sur 15 ans modifie le capital final de plusieurs milliers d'euros. Vérifiez le taux net servi sur votre relevé annuel : il figure généralement en toutes lettres sur la première page.

Aller plus loin : alternatives et compléments à votre simulation Excel

Votre tableur est construit. Il projette le capital, intègre la fiscalité et calcule l'effort d'épargne nécessaire. Mais deux vérifications restent utiles. Croiser vos résultats avec un simulateur institutionnel permet de détecter une erreur de formule. Et prolonger la simulation vers la rente viagère ou la transmission donne une vision patrimoniale complète.

Ces outils complémentaires ne remplacent pas le tableur : ils le valident ou l'étendent. Un simulateur en ligne bien calibré doit donner un résultat proche du vôtre à hypothèses identiques. Si l'écart dépasse 5 %, cherchez l'erreur dans vos formules avant de faire confiance au résultat.

Simulateurs officiels et bancaires : utiles pour croiser les résultats

Deux simulateurs en ligne méritent d'être utilisés comme points de contrôle. Celui de Boursorama intègre les frais et la fiscalité de base : saisissez-y vos hypothèses pour comparer le résultat final avec votre tableur. Le simulateur proposé par le Crédit Agricole (module gestion sous mandat) donne également une estimation exploitable.

Le simulateur de rachat d'assurance vie de service-public.fr (2026) est la référence pour vérifier votre modélisation fiscale. Il applique les règles officielles (abattement, PFU, barème progressif) et constitue un garde-fou pour la feuille de calcul de rachat de votre fichier. Utilisez-le systématiquement avant de prendre une décision de rachat fondée sur votre tableur.

Et si vous simuliez aussi la rente viagère ou la transmission ?

Une fois la phase d'accumulation modélisée, le prolongement naturel est la simulation de la rente viagère : quel capital accumulé permet de générer quelle rente mensuelle, et pendant combien d'années. Le guide sur la simulation de rente viagère détaille le calcul actuariel à intégrer dans un onglet séparé.

La dimension transmission mérite aussi une feuille dédiée. Le simulateur de droits de succession de service-public.fr (2026) permet d'estimer la charge fiscale pour vos bénéficiaires selon le montant transmis et le lien de parenté. Couplez cette estimation avec votre tableur de projection : le capital net transmis n'est pas le capital brut accumulé, surtout au-delà des abattements successoraux.

Sources

• service-public.gouv.fr
• simulateur-ir-ifi.impots.gouv.fr
• service-public.gouv.fr
• boursorama.com
• credit-agricole.fr

Ces informations sont données à titre indicatif et ne remplacent pas l'avis d'un conseiller financier. Étudiez votre situation avec un professionnel agréé avant de vous engager.