Excel, VBA et Python pour l’actuariat

1. Le test (A2:A5>100) renvoie VRAI ou FAUX pour chaque cellule, que la multiplication transforme en 1 ou 0 :VRAI, FAUX, VRAI, FAUX → 1, 0, 1, 0
2. Multiplié par les montants :1 × 120, 0 × 80, 1 × 300, 0 × 50 → 120, 0, 300, 0
3. SOMMEPROD additionne le tout :120 + 300 = 420

Ici, =SOMME.SI(A2:A5;">100") donne le même résultat. L’intérêt de SOMMEPROD : combiner plusieurs conditions, par exemple un montant et une année de survenance.

1. SOMME.SI.ENS additionne la plage C2:C6 pour les lignes qui remplissent tous les critères : année 2025 et garantie Auto.Lignes 4 et 6 : 2 500 + 700 = 3 200

Contrairement à SOMME.SI, la plage à additionner vient en premier. Les critères s’ajoutent ensuite par paires : plage, puis valeur.

1. Le 4^e argument, laissé vide, est la valeur à renvoyer si rien n’est trouvé. Le 5^e, −1, est le mode de correspondance : exacte, sinon la valeur immédiatement inférieure.
2. 45 n’est pas dans A2:A6. La valeur immédiatement inférieure est 30, en ligne 4 :B4 = 1,00

Sans le −1, RECHERCHEX cherche une correspondance exacte et renvoie #N/A. Avec RECHERCHEV, l’équivalent est le dernier argument VRAI, qui exige une colonne triée par ordre croissant.

1. EQUIV("Santé";A2:A5;0) cherche une correspondance exacte :position 3
2. INDEX(B2:B5;3) :1 150

INDEX et EQUIV font la même chose que RECHERCHEX, dans toutes les versions d’Excel.

1. VPM(taux ; nombre de périodes ; valeur actuelle) : le taux est mensuel, 5 % / 12, sur 60 périodes.20 000 × (0,05 / 12) / (1 − (1 + 0,05 / 12)⁻⁶⁰) ≈ 377,42 €

La valeur actuelle est saisie en négatif : Excel suit la convention des flux de trésorerie, comme la BA II Plus. Ce que l’on reçoit est positif, ce que l’on verse est négatif.

1. TRI cherche le taux r tel que :−1 000 + 300 / (1 + r) + 400 / (1 + r)² + 500 / (1 + r)³ = 0
2. Par essais successifs, ou avec la fonction :r ≈ 8,90 %

1. Le test sinistre > franchise est faux (800 < 1 000).
2. Le code exécute la branche Else :paiement = 0

C’est la fonction max(X − d, 0) du lexique, écrite avec une condition. Voir la franchise.

1. La boucle additionne cinq termes :1 000 / 1,02 + 1 000 / 1,02² + … + 1 000 / 1,02⁵
2. C’est la valeur actuelle de cinq versements de 1 000 € en fin d’année, au taux de 2 % :1 000 × a₅ = 1 000 × (1 − 1,02⁻⁵) / 0,02 ≈ 4 713,46

En VBA, l’opérateur ^ est prioritaire sur la division : 1000 / 1.02 ^ i se lit bien 1 000 / (1,02ⁱ). Une fonction personnalisée peut généraliser ce calcul à n’importe quelle durée.

1. La comparaison s’applique élément par élément :[True, False, True, False]
2. Pour NumPy, True vaut 1 et False vaut 0. La moyenne vaut donc :(1 + 0 + 1 + 0) / 4 = 0,5

Autrement dit, la moitié des sinistres dépasse 100. C’est une façon rapide de mesurer une fréquence de dépassement, par exemple au-delà d’une franchise.

1. Le masque sélectionne les éléments qui vérifient la condition :sinistres[sinistres > 100] → [120, 300]
2. 120 + 300 = 420

1. cumsum renvoie les sommes cumulées :[400, 400 + 250, 650 + 100, 750 + 50] = [400, 650, 750, 800]
2. Les indices commencent à 0 :cumules[2] / cumules[1] = 750 / 650 ≈ 1,1538

C’est le facteur individuel de la 2^e à la 3^e année de développement. Chain Ladder pondère ensuite ces facteurs sur toutes les années de survenance.

1. groupby additionne les colonnes pour chaque zone :Zone A : 0 + 2 + 1 = 3 sinistres ; 1 + 1 + 0,5 = 2,5 contrats-années
2. Fréquence de la zone A :3 / 2,5 = 1,2

On additionne d’abord, on divise ensuite. Faire la moyenne des fréquences contrat par contrat donnerait trop de poids aux contrats courts. Voir l’exposition dans le lexique.

1. pivot_table range chaque paiement dans la case (survenance, développement) et additionne :case (2024, 2) : 40 + 30 = 70

On obtient un triangle de paiements incrémentaux ; cumsum(axis=1) le transforme en triangle cumulé.