10 handige Excel formules voor beleggers

Microsoft Excel is een uitermate krachtig programma voor beleggers. Het kent vele functies en formules. De mogelijkheden zijn eindeloos als je financiële overzichten of berekeningen wilt maken.

Echter kun je soms lang bezig zijn om een werkblad naar je zin in te richten. De gedachte die dan door je hoofd kan spelen is: dit moet toch gemakkelijker kunnen? En meestal is dat ook zo…

Excel voor beleggers: de basis

In dit artikel ga ik ervan uit dat je enigszins bekend bent met Excel. Basisfuncties en gangbare formules worden dus niet uitgelegd.

Het is echter wel handig om de basis te kennen. Deze formules bijvoorbeeld (Nederlandse vertaling tussen haakjes):

  • MIN: weergeef de kleinste waarde uit een reeks cellen.
  • MAX: weergeef de grootste waarde uit een reeks cellen.
  • ABS: weergeef de absolute waarde uit een cel.
  • COUNT (AANTAL): tel het aantal cellen die getallen bevatten.
  • SUM (SOM): sommeer de getallen uit een reeks cellen.

Formules in de bovenstaande lijst zijn altijd handig om te onthouden. Ze zijn in bijna iedere omvangrijke Excel-tabel toe te passen.

Hieronder volgen vooral wat minder gangbare functies. Deze 10 functies zijn handig om specifieke (financiële) berekeningen of bewerkingen uit te voeren.

1. FV (TW): Toekomstige waarde

=FV(rente; aantal periodes; betaling na periode; [huidige waarde]; [type])
  • Rente: de rente of het rendement per periode.
  • Aantal periodes: het aantal periodes waarover de berekening gaat.
  • Betaling na periode: de hoogte van de betaling of extra inleg bij iedere periode.
  • Huidige waarde: de huidige waarde (standaard 0).
  • Type: een betaling vindt plaats voor (1) of na (0) de periode (standaard 0).

Met deze formule kun je de toekomstige waarde van een betaling of investering uitrekenen. Handig als je bijvoorbeeld wilt uitrekenen wat je uiteindelijke vermogen wordt als je start met beleggen.

FV voorbeeld

2. PV (HW): Huidige waarde

=PV(rente; aantal periodes; betaling na periode; [huidige waarde]; [type])
  • Rente: de rente of het rendement per periode.
  • Aantal periodes: het aantal periodes waarover de berekening gaat.
  • Betaling na periode: de hoogte van de betaling of extra inleg bij iedere periode.
  • Huidige waarde: de huidige waarde (standaard 0).
  • Type: een betaling vindt plaats voor (1) of na (0) de periode (standaard 0).

De tegenhanger van FV is de PV formule. Hiermee bereken je de huidige waarde van toekomstige betalingen of investeringen. Handig als je bepaalde cashflows verwacht in de toekomst en wilt weten wat deze vandaag de dag waard zijn.

PV voorbeeld

3. XNPV (NHW2): Netto contante waarde van toekomstige cashflows

=XNPV(rente; cashflows; periodes)
  • Rente: de rente of het rendement per periode.
  • Cashflows: een reeks van toekomstige cashflows.
  • Periodes: een reeks van datums die corresponderen met de cashflows.

De XNPV formule is nuttig als je een discounted cashflow analyse wilt doen. Deze analyse wordt vaak gebruikt voor het waarderen van aandelen. Ook kun je ermee berekenen of een investering uiteindelijk wordt terugverdiend.

Het voordeel van de XNPV formule ten opzichte van andere soortgelijke formules is dat je datums op kunt geven. Daarmee hoeven cashflows dus niet per se in vaste periodes binnen te komen.

XNPV voorbeeld

4. XIRR (IR.SCHEMA): Rendementsverwachting van een investering

=XIRR(cashflows; periodes)
  • Cashflows: een reeks van toekomstige cashflows.
  • Periodes: een reeks van datums die corresponderen met de cashflows.

De XIRR formule is gerelateerd aan de XNPV hierboven. Met XIRR kun je het te verwachten rendement van een investering berekenen.

Ook voor XIRR zijn alternatieve formules beschikbaar, maar het voordeel van deze is dat je datums kunt opgeven voor je cashflows. Zeker als cash onregelmatig binnenkomt (bijvoorbeeld met dividend) is dat handig.

XIRR voorbeeld

5. PMT (AFLOSSING): Hoogte van een aflossing bepalen

=PMT(rente; aantal periodes; huidige waarde; [toekomstige waarde]; [type])
  • Rente: de rente of het rendement per periode.
  • Aantal periodes: het aantal periodes waarover de berekening gaat.
  • Huidige waarde: de huidige waarde.
  • Toekomstige waarde: de waarde nadat de laatste betaling is voldaan (standaard 0).
  • Type: een betaling vindt plaats voor (1) of na (0) de periode (standaard 0).

Met de PMT formule kun je de hoogte van periodieke betalingen uitrekenen om een geleend bedrag af te lossen. Vooral handig als je een hypotheek binnen een bepaalde periode wilt aflossen. Hiermee weet je direct hoeveel je dan per periode moet betalen.

PMT voorbeeld

6. COUNTIF (AANTAL.ALS): Tel met een voorwaarde

=COUNTIF(reeks; voorwaarde)
  • Reeks: de reeks van cellen die je wilt tellen.
  • Voorwaarde: de voorwaarde die bepaalt of een cel wordt meegeteld of niet.

Met de COUNTIF formule kun je cellen tellen met een bepaalde voorwaarde. Handig als je wilt weten hoeveel betalingen je hebt ontvangen van een bepaalde partij, zoals in het voorbeeld hieronder:

COUNTIF voorbeeld

7. SUMIF (SOM.ALS): Sommeer met een voorwaarde

=SUMIF(reeks; voorwaarde; [reeks om te sommeren])
  • Reeks: de reeks van cellen die je wilt checken.
  • Voorwaarde: de voorwaarde die bepaalt of een cel wordt meegeteld of niet.
  • Reeks om te sommeren: de cellen die je wilt sommeren (standaard wordt de eerste reeks gesommeerd).

Met SUMIF kun je cellen onder voorwaarde laten sommeren. Uit een lijst van dividendbetalingen kun je bijvoorbeeld gemakkelijk berekenen hoeveel dividend je hebt ontvangen van een bepaalde onderneming.

SUMIF voorbeeld

8. SUMPRODUCT (SOMPRODUCT): Vermenigvuldig en sommeer

=SUMPRODUCT(reeks1; [reeks2]; [reeks3]; …)
  • Reeks1: de reeks van cellen die je wilt vermenigvuldigen en sommeren.
  • Reeks2 en verder: de reeks van cellen die je wilt vermenigvuldigen en sommeren (standaard leeg).

SUMPRODUCT is een erg krachtige maar ook complexe formule. Je kunt cellen vermenigvuldigen en daarna de resultaten met elkaar sommeren. In het voorbeeld hieronder zie je de meest simpele toepassing.

De formule laat het echter ook toe om te delen in plaats van te vermenigvuldigen. Bovendien kun je ook met een voorwaarde sommeren. De mogelijkheden zijn bijna eindeloos, maar het vergt wat training om er vertrouwd mee te raken.

SUMPRODUCT voorbeeld

9. XLOOKUP (X.ZOEKEN): Horizontaal of verticaal zoeken

=XLOOKUP(zoekwaarde; reeks om in te zoeken; resultaatreeks; [als niet gevonden]; [match mode])
  • Zoekwaarde: de waarde die je wilt zoeken.
  • Reeks om in te zoeken: de reeks waarin je de zoekwaarde wilt zoeken.
  • Resultaatreeks: de reeks met het resultaat die je wilt hebben.
  • Als niet gevonden: de resultaatwaarde als je de zoekwaarde niet hebt gevonden (standaard #N/A).
  • Match mode: het match type (standaard 0).

Met XLOOKUP kun je zowel horizontaal als verticaal waardes zoeken in een tabel. Deze functie maakt het mogelijk om gemakkelijk data vanuit verschillende tabellen op te vragen en samen te voegen. Een erg krachtige functie om complexe Excel tabellen mee te kunnen maken.

XLOOKUP voorbeeld

10. TRANSPOSE (TRANSPONEREN): Een reeks roteren

=TRANSPOSE(reeks)
  • Reeks: de reeks die je wilt transponeren.

Met TRANSPOSE kun je een reeks roteren. Bijvoorbeeld van een verticale tabel een horizontale tabel maken. Het is een simpele functie, maar goed toepasbaar als je dubbele data wilt voorkomen.

TRANSPOSE voorbeeld

Tot slot over Excel voor beleggers

Microsoft Excel is onmisbaar voor beleggers als het gaat om het bewerken van financiële data en het maken van analyses. Hopelijk wordt het met bovenstaande formules wat gemakkelijker om complexe, maar werkbare Excel-werkbladen in te richten.

Mocht je een gratis alternatief van Excel willen, dan is Libreoffice Calc een goede keuze. Een groot gedeelte van de bovenstaande formules werkt ook in Calc.