Excel — Schadecalculatie

Concepts

Excel voor verzekeringscalculaties

Schadeverzekeringen kennen vaak complexe regelsets: is schade gedekt? Welk eigen risico geldt? Wat is de maximale uitkering? Deze regels zijn perfect te automatiseren met Excel-functies. Van Ginkel Solutions BV heeft meerdere verzekeringen — een geautomatiseerd schadecalculatiemodel bespaart tijd en voorkomt fouten.

IF | Als-dan
Laat Excel een keuze maken op basis van een voorwaarde
Gebruik voor: is schade gedekt? (ja/nee)
---
AND | En-logica
Combineert meerdere voorwaarden (alles moet waar zijn)
Gebruik voor: gedekt ÉN boven eigen risico ÉN niet overschreven
---
XLOOKUP | Opzoeken
Zoekt een waarde in een tabel en geeft bijbehorende info terug
Gebruik voor: schadecategorie → eigen risico opzoeken

De IF-functie — gedekt of niet?

IF evalueert een voorwaarde en geeft twee mogelijke uitkomsten: waar of onwaar.

Syntaxis:
=IF(voorwaarde; waarde_als_waar; waarde_als_onwaar)

Voorbeeld: is de schade boven het eigen risico?
=IF(B3 > B4; "Gedekt"; "Niet gedekt")

waarbij:
  B3 = schadebedrag (bijv. €2.500)
  B4 = eigen risico (bijv. €500)

**Uitkeringsberekening met IF:**

Uitkering = schadebedrag - eigen risico, maar nooit negatief

Formule:
=IF(B3 > B4; B3 - B4; 0)

Voorbeeld:
  Schadebedrag = €2.500
  Eigen risico = €500
  Uitkering    = €2.500 - €500 = €2.000 ✓

  Schadebedrag = €300
  Eigen risico = €500
  Uitkering    = €0 (schade onder eigen risico)

> EXAMTIP: Een IF-formule voor verzekeringen heeft altijd drie onderdelen: de test (is schade > eigen risico?), de uitkomst als het klopt (bereken uitkering), en de uitkomst als het niet klopt (€0 of "Niet gedekt").

De AND-functie — meerdere voorwaarden

AND controleert of **meerdere voorwaarden tegelijk** waar zijn. Gebruik dit voor complexere dekkingsregels.

Syntaxis:
=AND(voorwaarde1; voorwaarde2; ...)
→ Geeft WAAR als ALLE voorwaarden kloppen, anders ONWAAR

Voorbeeld: schade is gedekt als:
  1. Schadebedrag > eigen risico
  2. Schade valt binnen de gedekte categorie
  3. Schadebedrag ≤ maximale verzekerde waarde

Formule:
=AND(B3 > B4; B5 = "gedekt"; B3 <= B6)

Combinatie met IF:
=IF(AND(B3 > B4; B5 = "gedekt"; B3 <= B6); B3 - B4; 0)

**Voorbeeld Van Ginkel — inventarisverzekering:**

Schadebedrag    (B3): €8.500
Eigen risico    (B4): €1.000
Categorie       (B5): "gedekt"
Max. uitkering  (B6): €50.000

=IF(AND(B3>B4; B5="gedekt"; B3<=B6); B3-B4; 0)
→ AND(8500>1000; "gedekt"="gedekt"; 8500<=50000)
→ AND(WAAR; WAAR; WAAR)
→ WAAR
→ Uitkering = €8.500 - €1.000 = €7.500

XLOOKUP — schadecategorie opzoeken

XLOOKUP zoekt een waarde in een lijst en geeft de overeenkomstige waarde uit een andere kolom. Dit is ideaal voor het opzoeken van eigen risico's per schadecategorie.

Syntaxis:
=XLOOKUP(zoekwaarde; zoekbereik; resultaatbereik; [als_niet_gevonden])

Voorbeeld: zoek het eigen risico behorend bij een schadecategorie

Opzoektabel (in bereik E2:F6):
  Categorie        | Eigen risico
  Brand            | €1.500
  Diefstal         | €750
  Waterschade      | €500
  Glasbreuk        | €250
  Aansprakelijkheid| €1.000

Formule (schadecategorie staat in cel B5):
=XLOOKUP(B5; E2:E6; F2:F6; "Categorie onbekend")

Als B5 = "Diefstal" → resultaat = €750

> EXAMTIP: XLOOKUP vervangt de oudere VLOOKUP. Het grote voordeel: XLOOKUP kan ook van rechts naar links zoeken, en je geeft zelf aan wat er moet verschijnen als een waarde niet wordt gevonden.

Volledig geautomatiseerde schadecalculator

Combineer IF, AND en XLOOKUP voor een complete schadecalculator:

Structuur schadecalculator Van Ginkel:

  Invoer:
    B2: Schadebedrag       = €3.200
    B3: Schadecategorie    = "Diefstal"
    B4: Maximale uitkering = €25.000

  Automatische berekening:
    B6: Eigen risico       = =XLOOKUP(B3; E2:E6; F2:F6; "Onbekend")
                           → €750 (uit opzoektabel)

    B7: Gedekt?            = =IF(AND(B2 > B6; B2 <= B4); "Ja - gedekt"; "Nee - niet gedekt")
                           → "Ja - gedekt"

    B8: Uitkering          = =IF(B7="Ja - gedekt"; B2 - B6; 0)
                           → €3.200 - €750 = €2.450

  Alles-in-één formule:
    =IF(AND(B2>XLOOKUP(B3;E2:E6;F2:F6;0); B2<=B4); B2-XLOOKUP(B3;E2:E6;F2:F6;0); 0)

Onderverzekering berekenen

Bij onderverzekering keert de verzekeraar niet het volledige schadebedrag uit, maar een evenredig deel.

Formule onderverzekering:
Uitkering = schadebedrag × (verzekerd bedrag / werkelijke waarde)

In Excel:
=B2 * (B8 / B9)

waarbij:
  B2 = schadebedrag
  B8 = verzekerd bedrag (wat je hebt opgegeven)
  B9 = werkelijke waarde (wat het écht waard is)

Voorbeeld Van Ginkel:
  Schadebedrag    = €10.000
  Verzekerd voor  = €40.000
  Werkelijke waarde = €50.000

  Uitkering = €10.000 × (€40.000 / €50.000) = €10.000 × 0,80 = €8.000

> EXAMTIP: Onderverzekering betekent dat je te weinig hebt verzekerd ten opzichte van de werkelijke waarde. De verzekeraar past de uitkering naar rato aan. Controleer altijd of het verzekerd bedrag overeenkomt met de actuele waarde van de bezittingen.

Missie

STORY: Van Ginkel Solutions BV heeft vorige maand drie schades gehad: inbraak in het magazijn, waterschade aan de kantoorruimte en een gebroken etalageraam. Karin vraagt jou een Excel-schadecalculator te bouwen die automatisch de uitkering per schade bepaalt op basis van de verzekeringsvoorwaarden.

Stap 1 — Opzoektabel eigen risico per categorie

Maak in Excel eerst een opzoektabel met de eigen risico's per schadecategorie:

Tabel in bereik E2:F6 (geef het bereik de naam "EigenRisico"):

  E2: Categorie          F2: Eigen risico
  E3: Brand              F3: €1.500
  E4: Diefstal           F4: €750
  E5: Waterschade        F5: €500
  E6: Glasbreuk          F6: €250
  E7: Aansprakelijkheid  F7: €1.000

Tip: selecteer E2:F7, ga naar Formules → Naam definiëren → "EigenRisicoTabel"

Stap 2 — Drie schadegevallen invoeren

Voer de drie schades in en gebruik XLOOKUP om het eigen risico automatisch op te halen:

Kolommen: A = Omschrijving | B = Categorie | C = Schadebedrag | D = Eigen risico | E = Uitkering

Rij 10: Inbraak magazijn    | Diefstal     | €4.200 | =XLOOKUP(B10;E3:E7;F3:F7;0) | (stap 3)
Rij 11: Waterleiding kantoor| Waterschade  | €1.800 | =XLOOKUP(B11;E3:E7;F3:F7;0) | (stap 3)
Rij 12: Etalageraam         | Glasbreuk    | €380   | =XLOOKUP(B12;E3:E7;F3:F7;0) | (stap 3)

Resultaat kolom D (eigen risico):
  Rij 10: €750   (Diefstal)
  Rij 11: €500   (Waterschade)
  Rij 12: €250   (Glasbreuk)

Stap 3 — Uitkering berekenen met IF/AND

Voeg de uitkeringsformule toe in kolom E. Maximale uitkering per schade is €50.000:

Cel E10: =IF(AND(C10>D10; C10<=50000); C10-D10; 0)
         → AND(4200>750; 4200<=50000) = WAAR → €4.200 - €750 = €3.450

Cel E11: =IF(AND(C11>D11; C11<=50000); C11-D11; 0)
         → AND(1800>500; 1800<=50000) = WAAR → €1.800 - €500 = €1.300

Cel E12: =IF(AND(C12>D12; C12<=50000); C12-D12; 0)
         → AND(380>250; 380<=50000) = WAAR → €380 - €250 = €130

Rij 14: Totale uitkering = =SOM(E10:E12) → €3.450 + €1.300 + €130 = €4.880

Van Ginkel Solutions BV ontvangt in totaal €4.880 van de verzekeraar. Karin noteert dat het etalageraam maar €130 oplevert vanwege het relatief hoge eigen risico van €250 — in de toekomst overweegt ze dit eigen risico te verlagen.