Simulateur Excel d’assurance habitation — guide détaillé & mode d’emploi

Un simulateur d’assurance habitation permet d’estimer, pour un profil donné, le coût total annuel d’un contrat (prime payée + reste à charge attendu), et de comparer des scénarios (franchise, garanties, sécurité, mode de paiement…). Il relie un moteur de pricing transparent à un module de risques (fréquence × sévérité des sinistres) pour des décisions chiffrées.

1) À quoi sert un simulateur ?

Décider de la franchise optimale : trouver l’équilibre entre prime et reste à charge.
Dimensionner la somme assurée “contenu” et vérifier les plafonds.
Comparer annuel vs mensuel (TAEG implicite).
Mesurer la valeur d’une alarme/télésurveillance (ROI).
Visualiser le risque : distribution du coût total (P50, P90, P95) au lieu d’un seul chiffre.

Résultat : une décision objectivée, justifiable face à un tiers (gestionnaire, copropriété, direction…).

2) Architecture d’un bon simulateur

a) Entrées (profil & garanties)

Logement : type (appartement/maison), surface (m²), étage, zone de risque.
Sécurité : serrures renforcées, alarme, télésurveillance.
Historique sinistres (3 ans), mode de paiement (annuel/mensuel).
Franchise générale (et spécifiques si disponibles).
Garanties activées : Incendie, Dégâts des eaux, Vol, Bris, CatNat, Protection juridique, Assistance.
Valeur du contenu (€).

b) Moteur de pricing
Calcule la prime TTC à partir de barèmes/coefficients explicites.

c) Module risques (stochastique)
Estime, via Monte Carlo, l’indemnisation attendue et le reste à charge selon les garanties, franchises et plafonds.

d) Sorties & KPIs

Prime annuelle TTC / mensuelle
Coût total (prime + OOP), P50/P90/P95, probabilité de dépasser un seuil budget
Loss ratio attendu (indemnités/primes), part de prime dans le coût total
Graphiques : histogramme de coût, décomposition (prime vs OOP vs indemnités)

3) Moteur de pricing : logique et formules

3.1 Base tarifaire

prime_base = max( (€/m² × surface)
                  × coef_type × coef_zone × coef_étage
                  × coef_sécurité × coef_sinistres × coef_franchise,
                  prime_min )

3.2 Chargements de garanties

chargements = prime_base × (Σ(%)_garanties_activées)

3.3 Composant “contenu”

Certaines garanties (Incendie, DDE, Vol) protègent le contenu :

composant_contenu = valeur_contenu × taux_contenu × nb_garanties_contenu

3.4 Taxes & paiement

sous_total = prime_base + chargements + composant_contenu
annuel_TTC = sous_total × (1 + taxes) × (1 + (frais_mensuel si Mensuel sinon remise_annuelle))
mensuel = annuel_TTC / 12

Transparence : tous les coefficients doivent être visibles et éditables (table Hypothèses).

4) Module risques (Monte Carlo) : comment ça marche

4.1 Fréquence (combien de sinistres par an ?)

Paramètre central : λ (sinistres/an).
On peut le décomposer par type (Dégâts des eaux, Vol, Incendie) avec un mix qui somme à 1.
Ajustements via coefficients (zone, étage, sécurité, historique…).

Implémentation pratique (Excel) : approximation Binomiale (n=1000, p = λ/1000) pour tirer un entier de sinistres par type.

4.2 Sévérité (combien coûte un sinistre ?)

Une loi lognormale est souvent pertinente (longue traîne).
Pour chaque sinistre simulé : appliquer franchise et plafond par sinistre.

paiement_assureur = max( min( (sévérité - franchise), plafond_sinistre ), 0 )
reste_à_charge    = sévérité - paiement_assureur

4.3 Coût total annuel simulé

indemnités_annuelles = somme(paiements_assureur_sur_tous_les_sinistres)
OOP_annuel           = somme(restes_à_charge)
coût_total_annuel    = prime_annuelle_TTC + OOP_annuel

Répéter N fois (ex. 1 000 tirages) → stocker la distribution de coût_total_annuel.

5) Indicateurs à lire en priorité

P50 (médiane) : l’année “typique”.
P90 / P95 : années difficiles (1/10, 1/20).
Probabilité de dépasser un seuil budget (ex. 600 €/an pour locataire, 1 200–1 800 € pour propriétaire) :
=NB.SI(plage_coûts;">"&seuil)/N.
Loss ratio attendu : moyenne(indemnités)/prime.
Part de prime dans le coût total : prime / moyenne(coût_total).

6) Choisir la franchise (méthode “coût total”)

Comparer 2–4 niveaux (0 €/150 €/300 €/500 €/1000 €) et retenir le minimum du :

coût_total = prime_ajustée_par_franchise + (λ × [p_petits × ticket_moyen + (1 - p_petits) × franchise])

p_petits : part de sinistres sous la franchise.
Intuition : si vos sinistres sont peu fréquents et plutôt importants, une franchise plus haute baisse la prime et peut gagner à l’année. L’inverse si sinistres récurrents et faibles.

7) Annuel vs Mensuel (TAEG implicite)

Comparer :

coût_annuel_paiement_annuel   = prime_référence × (1 + remise_annuelle)
coût_annuel_paiement_mensuel  = prime_référence × (1 + frais_mensuels)

Calculer un TAEG implicite du mensuel (fonction TAUX/RATE avec 12 mensualités).

Si le TAEG implicite dépasse votre coût d’opportunité (ou un seuil “raisonnable”), payer à l’année est souvent préférable (si trésorerie OK).

8) Estimer correctement la “somme assurée contenu”

Inventaire par catégories (quantités × valeur unitaire) +
Règle par surface (m² × €/m² “meubles & électro”).
Retenir max(inventaire, estimation surface).
Objets de valeur : déclarations/plafonds dédiés, preuves d’achat & photos.

9) Scénarios types (à tester)

Locataire urbain (50–70 m²) : Incendie + DDE + Vol, franchise 150/300 €, paiement annuel si frais mensuels ≥ 3 %.
Propriétaire maison (100–130 m²) : vérifier zone et sécurité; tester ROI alarme (baisse de prime & sinistralité), ajuster la somme de contenu.
Résidence secondaire / RDC : vigilance vol/vandalisme et inhabitation (jours couverts), franchise adaptée.
Historique 1–2 sinistres/3 ans : coef sinistres ↑ → regarder la franchise coût total.

10) Contrôles qualité & bonnes pratiques

Traçabilité des hypothèses : onglet Hypothèses (toutes éditables).
Validation : comparer quelques résultats avec devis réels (sanity check).
Sensibilités : tester λ ± 20 %, ticket moyen ± 20 %, effet sécurité.
Couleurs/feux : seuils lisibles (vert/ambre/rouge) pour prix, couverture, délais.
Vue imprimable : tableau condensé + TOP 3 + KPIs.

11) Limites & biais à connaître

Barèmes pédagogiques ≠ tarifs réels (marque, réseau, souscription).
Les lois de sévérité (lognormale) sont approximatives : ajustez par historique local.
Exclusions & plafonds spécifiques (inhabitation, objets de valeur, nomades) doivent être modélisés si vous en disposez.

12) Mode d’emploi (pas à pas)

Renseignez les entrées (profil, garanties, franchise, paiement).
Lisez la prime TTC et les KPIs (P50, P90, probabilité > seuil).
Ajustez : franchise (viser le minimum de “coût total”), paiement (TAEG), sécurité (ROI), somme de contenu (inventaire/surface).
Capturez la recommandation (scénarios A/B/C) et archivez les paramètres.

13) FAQ

Q. Les résultats sont-ils des devis ?
R. Non. C’est une simulation pour éclairer la décision. Remplacez les barèmes par vos références réelles.

Q. Peut-on ajouter plus de types de sinistres (électrique, grêle) ?
R. Oui : ajoutez leurs λ et lois de sévérité, franchises et plafonds.

Q. Comment “figer” un tirage Monte Carlo ?
R. Copiez/collez valeurs sur l’onglet des tirages pour conserver un scénario.

14) Liste de contrôle (checklist finale)

Entrées cohérentes (surface, contenu, zone, sécurité, historique)
Garanties essentielles actives (Incendie, DDE, Vol)
Franchise comparée sur coût total, pas seulement sur prime
Paiement annuel vs mensuel analysé (TAEG)
Somme “contenu” ≥ max(inventaire, surface) ; objets de valeur déclarés
KPIs lus : P50, P90, probabilité > seuil budget
Scénarios A/B/C conservés (trace & justification)

Le simulateur qui fait gagner : franchise, paiement, contenu, risques

Voici une description claire et complète du fichier Simulateur_Assurance_Habitation.xlsx que je vous ai livré.

1) Objectif du simulateur

Estimer, pour votre profil et vos garanties, le coût total annuel d’une assurance habitation :

Prime calculée de façon transparente (barèmes éditables),
Reste à charge attendu via simulation Monte Carlo (fréquence × sévérité des sinistres),
KPIs décisionnels (P50/P90/P95, probabilité de dépasser un budget, loss ratio…),
Graphiques pour visualiser la distribution des coûts et la décomposition prime/OOP/indemnités.

2) Architecture du classeur

Feuille « Simulateur »

Entrées (col. B) :
Type de logement, Surface (m²), Valeur du contenu (€), Zone de risque, Étage, Sécurité, Sinistres (3 ans), Mode de paiement (Annuel/Mensuel), Franchise.
Garanties (Oui/Non) : Incendie, Dégâts des eaux, Vol, Bris de glace, CatNat, Protection juridique, Assistance.
Coefficients dérivés (bloc à droite) : type/zone/étage/sécurité/sinistres/franchise + multiplicateur de garanties.
Calcul prime :
Prime de base → chargements garanties → composant “contenu” → taxes → remise/frais (annuel vs mensuel) → TTC annuel et mensuel.
Seuil budget : une cellule pour fixer votre budget annuel cible.
KPIs (tableau) : moyenne, médiane (P50), P90 / P95, probabilité de dépasser le budget, part de prime dans le coût moyen, loss ratio attendu.
Graphiques :
1. Histogramme de la distribution des coûts annuels simulés,
2. Barres de décomposition (Prime TTC / OOP moyen / Indemnités moyennes).

Feuille « Hypothèses »

Barèmes & coefficients (éditables) :
- Type de logement, Zone de risque, Étage, Sécurité, Sinistres 3 ans, Franchise.
- Garanties : indicateur de “pertinence contenu” + chargement (%) associé.
- Paramètres assureur générique : €/m², taux contenu, prime minimale, taxes, frais mensuels, remise annuelle.
Risque (Monte Carlo) :
- λ (sinistres/an total), mix par type (Dégâts des eaux / Vol / Incendie),
- Sévérités par type (médiane, sigma lognormal), plafond par sinistre et franchise par sinistre.

Tous ces paramètres peuvent être ajustés pour coller à votre cas ou à vos historiques.

Feuille « MonteCarlo »

1 000 itérations : pour chaque année simulée, on tire le nombre de sinistres par type (approx. binomiale) puis la sévérité de chaque sinistre (lognormale).
Colonnes principales :
Indemnités (total payé par l’assureur), OOP_total (franchises + dépassements de plafond), Coût_total = Prime TTC + OOP_total.
Binning pour l’histogramme** : centres de classes & fréquences (utilisés par le graphique).

Feuille « Mode d’emploi »

Pas-à-pas d’utilisation, rappels sur les hypothèses et conseils pour “figer” un tirage.

3) Logique de calcul (résumé)

Prime

Prime de base = max( €/m² × surface × coefficients (type, zone, étage, sécurité, sinistres, franchise) ; prime minimale ).
Chargements garanties = prime_base × somme des pourcentages des garanties activées.
Composant “contenu” = valeur_contenu × taux_contenu × nombre de garanties qui couvrent le contenu (Incendie/DDE/Vol).
Prime annuelle TTC = (base + chargements + contenu) × (1 + taxes) × (1 + remise annuelle ou frais mensuels).

Risque & coût total

Fréquence : tirage du nombre de sinistres par type selon λ × mix (ajustable).
Sévérité : tirage lognormal par sinistre, puis application franchise & plafond par sinistre →
Paiement assureur = max(min(sévérité – franchise, plafond), 0), OOP = sévérité – paiement.
Année simulée : Coût total = Prime TTC + OOP total.
Répéter 1 000 fois → distribution des coûts (P50/P90/P95…).

4) Ce que vous pouvez lire/décider rapidement

G20 (TTC annuel) et G21 (mensuel).
P50 / P90 / P95 : budget “typique” et “années difficiles”.
Probabilité de dépassement de votre seuil budget : si trop élevée, ajuster franchise, garanties, sécurité, contenu.
Loss ratio attendu & part de prime dans le coût moyen : sanity check de la cohérence.

5) Personnalisation & extensions

Ajouter des types de sinistres (dommages électriques, tempête…), franchises spécifiques par garantie, plafonds par volet (contenu/objets de valeur).
Intégrer une vue Scénarios A/B/C (franchises, garanties, paiement) avec TOP 3.
Relier au Comparateur FR Étendu pour piloter comparateur + simulateur depuis une page unique.
Ajouter une vue A4 imprimable (tableau condensé + KPIs + graphiques).

6) Bonnes pratiques

Remplacer les barèmes de démonstration par vos références (€/m², taux contenu, taxes…).
Calibrer λ et les médianes/sigmas de sévérité d’après vos historiques.
Tester des sensibilités (±20 % sur λ, tickets moyens, effet sécurité).
Documenter vos choix (franchise retenue, garanties actives, seuil budget).