rendement berekenen

Schrijf je in op onze nieuwsbrief en ontvang elke week het beste van Clickx in je mailbox.

Denk je erover na om te investeren en zou je het rendement zelf wel eens willen berekenen? Dat kan perfect, met de functie TW (toekomstige waarde) in Microsoft Excel. Als je een investering doet van 10.000 euro en je verwacht een rendement van 5% per jaar, dan wil je weten wat de waarde is over een aantal jaren. Die bereken je als volgt. Het rendement na één jaar is 5% maal 10.000, dat is 500 euro. De totale waarde is dan 10.000, plus deze 500. In formulevorm kan je dat noteren als:

=10000+10000*5%

De uitkomst is 10.500, maar het kan eenvoudiger:

=10000*(1+5%)

Na twee jaar brengt deze 10.000 weer 500 op, plus 5% over de 500 van het eerste jaar. Dit is samengestelde rente, want hier speelt het rente-op-rente-effect. Dat bereken je met:

=10000*(1+5%)*(1+5%)

Dat noteren we korter als:

=10000*(1+5%)^2

De waarde is na twee jaar in totaal 11.025.

Na het derde jaar geldt de rente tot de derde macht. De formule hiervoor is:

=10000*(1+5%)^3

De waarde is inmiddels gestegen naar 11.576.

•             Schrijf je 1+5% liever als 105%, dan kan je de haakjes achterwege laten en wordt de laatste formule:

=10000*105%^3

Betalingen zijn negatief

De financiële functies van Excel kan je gebruiken voor investeringen en voor leningen. Het grote verschil zit in het gebruik van positieve of negatieve getallen. Bedragen die je betaalt, zijn negatief, zoals een investering, storting op een rekening of aflossing van een lening. Bedragen die je ontvangt zijn positief, zoals rente die je ontvangt of de toename van een investering.

De functie TW

In het voorbeeld met het rendement in de vorige paragraaf investeer je één keer een bedrag, dat vervolgens in waarde toeneemt. Je kan ook een startkapitaal inleggen en daar ieder jaar een vast bedrag aan toevoegen. Dan brengt iedere bijstorting op zich ook weer rente op. Voor het berekenen van de eindwaarde van deze constructie is de functie TW (de afkorting van ‘toekomstige waarde’).

Syntaxis van TW

=TW(rente per jaar; aantal jaren; jaarlijkse storting; eenmalig startkapitaal; 0 of 1)

Deze functie heeft vijf argumenten. Achtereenvolgens geef je op: de rente per jaar; het aantal jaren; de betalingen per jaar; het startkapitaal; of je betaalt aan het einde van de termijn (0) of aan het begin (1). De bedragen die je stort (per jaar en eenmalig), noteert je als negatieve getallen.

Resultaat: het kapitaal dat ontstaat aan het eind van de periode.

Bij een berekening met deze functie wordt ervan uitgegaan dat de rente de hele periode gelijk blijft en dat met vaste tussenpozen (termijnen) hetzelfde bedrag wordt betaald. De eerste vier argumenten zijn verplicht; je moet ze ook in die volgorde opgeven. Als je het laatste argument weglaat, geldt dat als 0 en gaat Excel uit van betaling aan het einde van iedere periode. We nemen als voorbeeld weer 5% rente. We willen weten wat de waarde is na tien jaar als we beginnen met een startkapitaal van 10.000 en ieder jaar 1000 euro betalen; de betalingen van 1.000 euro doen we aan het begin van ieder jaar (daarvoor geven we 1 op). De volgende formule geeft een toekomstige waarde van 29.496:

=TW(5%;10;-1000;-10000;1)

Zoals je ziet, staan de bedragen die je stort als negatieve getallen in de formule.

Waar je anders een tabel voor nodig zou hebben, doet de functie TW het in één formule.

In het voorbeeld in de vorige paragraaf, waarin de rente 5% is, de periode drie jaar en je geen periodieke betalingen doet maar alleen een investering, is de formule:

=10000*(1+5%)^3

Met de functie TW wordt de formule:

=TW(5%;3;0;-10000;1)

Het rendement is 5%, de periode drie jaar, de jaarlijkse betaling bedraagt 0 euro en het startkapitaal is 10.000. Ook nu is het resultaat 11.576.

Leg je geen startkapitaal in, maar verricht je enkel betalingen van 1.000 per jaar, dan is je formule:

=TW(5%;10;-1000;0;1)

Hier is de periode tien jaar, de betaling 1.000 per jaar en is voor het startkapitaal 0 ingevuld. De uitkomst is nu 13.207.

Argument leeg laten

De meeste functies in dit artikel bestaan uit een vast aantal argumenten waarvoor je stuk voor stuk een waarde moet opgeven. Wil je voor een argument geen waarde opgeven, zoals in het vorige voorbeeld waarin geen jaarlijkse betaling plaatsvindt, dan vul je op die plaats een 0 in. De plaats van ieder argument is belangrijk, want op grond van achtereenvolgens de rente, het aantal jaren, de betaling per jaar en het startkapitaal maakt Excel de berekening. Je mag op de plaats van het argument ook een spatie typen of dat argument leeg laten. Typ wel altijd een puntkomma op die plaats, want deze doet dienst als zogeheten plaatshouder: Excel verwacht op die plaats een bepaald argument. De volgende formule heeft 0 als derde argument, dus de jaarlijkse betaling bedraagt 0 euro:=TW(5%;10;0;-10000;1). Je mag op die plaats ook een spatie typen, zodat de formule eruitziet als: =TW(5%;10; ;-10000;1). Of je laat dat argument leeg: =TW(5%;10;;-10000;1). Is een argument niet verplicht en staat dat aan het eind, dan mag je dat helemaal weglaten.

Rekenmodel voor eindkapitaal opzetten

Het is erg belangrijk dat je de argumenten in de vaste volgorde opgeeft. Om vergissingen te voorkomen en omdat het prettiger werkt, zet je een rekenmodel op zoals in de afbeelding hierna. In C3 komt het rentepercentage; maak die cel op als Procent. Maak C5 op als een heel getal, hierin komt het aantal jaren en maak C7 en C9 op voor bedragen. Om duidelijk te maken dat hierin getallen moeten worden ingevuld, geeft je deze vier cellen een rand.

Desgewenst geef je de achtergrond van het werkblad een kleur, maak je deze vier cellen wit en typ je er aanduidingen bij.

Typ in C11 de formule:

=TW(C3;C5;-C7;-C9;1)

Voor de duidelijkheid staan de cellen zo onder elkaar dat de verwijzingen in dezelfde volgorde staan als de argumenten (alleen het argument voor het begin van de termijn staat als 1 in de formule zelf). De betalingen moeten als negatieve bedragen worden opgegeven, daarvoor staan in deze formule mintekens voor C7 en C9. De gebruiker kan dan eenvoudig positieve getallen invoeren in deze cellen. Zonder iets aan de formule te veranderen, kan je met dit model meteen andere getallen uitproberen om het verschil te zien bij een ander rentepercentage, bij meer of minder jaren, een andere jaarlijkse betaling (of geen betaling) of een ander startkapitaal (of niets).

Maak je een rekenmodel, dan kan je voor een complexe berekening eenvoudig de getallen invoeren.

Maandbedragen berekenen

Als je niet eenmaal per jaar, maar iedere maand een vast bedrag stort, kan je deze functie ook gebruiken. Heb je een bank die iedere maand rente berekent, dan wordt die rente steeds opnieuw bij het saldo opgeteld. Hiervoor deel je de jaarrente door 12 en vermenigvuldig je het aantal termijnen met 12 (maanden). Is het rendement 5%, wil je de waarde na tien jaar weten als je aan het begin van iedere maand 100 euro betaalt en met een startkapitaal van 10.000 begint, dan is je formule:

=TW(5%/12;10*12;-100;-10000;1)

De toekomstige waarde is 32.063.

Doe je alleen maandelijkse betalingen zonder een startkapitaal, dan vervangt je -10000 door 0.

Ook hiervoor kan je een rekenmodel maken; geef daarin aan dat de betalingen per maand worden verricht. De formule voor cel C11 luidt nu:

=TW(C3/12;C5*12;-C7;-C9;1)

Investeer je 12 keer per jaar een vast bedrag, dan deel je de jaarrente door 12 en vermenigvuldig je het aantal termijnen met 12.

Resterende schuld berekenen

Omgekeerd kan je met de functie TW ook berekenen hoe groot je schuld nog is, als je van een lening een aantal termijnen hebt afbetaald. Stel dat je 5.000 leent tegen 10% rente en dat je per maand 100 afbetaalt. Je berekent het bedrag dat na drie jaar nog open staat (het restant van je schuld) met:

=TW(10%/12;3*12;-100;5000)

De uitkomst is -2.563, wat willen zeggen dat dit bedrag nog open staat en dat je bijna de helft van de schuld hebt afbetaald. Zoals je ziet, is de 5.000 in deze formule positief, want de lening is geld dat je ontvangt; de aflossing van 100 is negatief, want die moet je betalen.

Het Complete Boek Excel voor professionals, 4e editie

Dit artikel is een hoofdstuk uit Het Complete Boek Excel voor professionals, 4e editie van Wim de Groot.

Het Complete Boek Excel voor professionals, 4e editie

Microsoft Excel is in de meeste bedrijven onmisbaar bij het dagelijkse kantoorwerk. Toch kan het veel tijd kosten om uit te zoeken hoe het programma werkt. Daarom is het zaak om goed thuis te raken in Excel. Hoe meer mogelijkheden je kent, des te efficiënter je de kracht van dit veelzijdige programma benut. Zo boek je veel tijdwinst. Dit boek helpt je daarbij. Je leert niet alleen wat je moet doen, maar vooral hoe het werkt. Je vindt in dit boek 1.750 voorbeelden van formules die zo worden uitgelegd, dat je ze kan aanpassen voor je eigen werk. Nadat in het eerste hoofdstuk tips worden gegeven om sneller met Excel te werken, gaat het boek de diepte in.

Deze uitgave van Vanduuren Media is onder andere verkrijgbaar bij Standaard Boekhandel, voor 64,99 euro.

Schrijf je in op onze nieuwsbrief en ontvang elke week het beste van Clickx in je mailbox.