Excel: Effectief financieel rekenen met onregelmatige reeksen – Deel 3

Introductie
In de voorgaande artikelreeks hebben we ons gericht op eind- en contante waardeberekeningen voor enkelvoudige bedragen en eind- en contante waardeberekeningen voor regelmatige of met regelmaat groeiende reeksen.

De regelmatigheid van bovengenoemde reeksen zat – bij de basisfuncties (TW,HW, BET, NPER, Rente) – steeds in een combinatie van:
• gelijkheid van de bedragen (argument bet) binnen een reeks;
• gelijkheid van de rentevoet (argument rente) waarmee bedragen binnen een reeks gekapitaliseerd worden;
• vaste, constante intervallen waarmee de betalingen binnen een reeks plaatsvonden (argument aantal_termijnen).

In deze driedelige artikelreeks gaan we in op de waardeberekening van onregelmatige geldstromen. Onregelmatigheid binnen geldstromen kan zich – al of niet in combinatie – manifesteren in de vorm van bedragen, rentepercentages of betaalintervallen. Ook hierbij maken we weer gebruik van de vijf basisfuncties, die we bij onregelmatige reeksen meervoudig inzetten. Met meervoudige inzet van functies hebben we in voorgaande artikelen al wat ervaring opgedaan.

Achtereenvolgens bekijken we hoe we eind- en contante waarde kunnen berekenen van de volgende onregelmatigheidsvormen:
• de eind- en contante waarde van eindige reeksen bij wisselende bedragen;
• de eind- en contante waarde van eindige reeksen bij een wisselende rentevoet;
• de eind- en contante waarde van eindige discontinue reeksen betalingen

Dit zijn drie basisbewerkingen op het gebied van onregelmatigheid welke apart of in combinatie te berekenen zijn. En u kunt het zo bont niet maken of het is met de basisfuncties van Excel te berekenen, bijvoorbeeld de eind- of contante waarde van een discontinue reeks wisselende betalingen met daarbovenop nog een wisselende rentevoet.

Per basisbewerking werken we een voorbeeld uit van een eind- en contante waardeberekening. We eindigen met een berekening waarbij alle drie de onregelmatigheidsvormen gecombineerd worden ingezet.

Deel 3 – De eind- en contante waarde van een eindige discontinue reeks

In het voorbeeld dat we nu uitwerken (3) is sprake van een vast stortingsbedrag (€ 1.000 ) en een vaste rentevoet (5%). De discontinuïteit zit in het feit dat storting van het vaste bedrag alleen in de even jaren 2,4 en 6 plaatsvindt. In de oneven jaren 1,3 en 5 vindt geen storting plaats.

Voorbeeld
We storten discontinu, om het jaar gedurende zes jaar, aan het begin van het jaar een vast bedrag van € 1.000 op een spaarrekening. We willen weten tot welk eindbedrag de stortingen gegroeid zijn aan het einde van het zesde jaar als de samengestelde intrestvoet 5% bedraagt.
 
Dit geeft volgens het bijbehorende kasstroomschema het volgende beeld te zien:

Als we de rekenexercitie handmatig uitvoeren krijgen we:

EW = 1.000*((1,05)5+ (1,05)3 +(1,05))

Dit geeft, als u het doorrekent, een eindwaarde te zien van € 3.483,91

Voor de berekening met Excel zetten we de basisfunctie TW(rente;aantal_ termijnen;bet;hw;type_getal) weer 6 maal in.
Om de eindwaarde te verkrijgen tellen we de functie uitkomsten uit het bereik (B9:G9) op in cel H9.

Elke TW kolom vertegenwoordigt hierbij steeds weer één van de optelfactoren uit de handmatige reeks, waarbij de oneven jaren een nulwaarde vertegenwoordigen. Het enige wat u nu moet doen is erop letten dat u de bedragen in de juiste jaargangen plaatst bij het functie argument HW (in de 4e rij) en dat u het aantal termijnen (2e rij) aflopend weergeeft.
__________________________________________________________________________________
Volg een topcursus Excel 
Ontdek snel en eenvoudig hoe u de tijd die u aan Excel besteedt halveert.
Benut Excel’s potentie volledig. Volg een van de Excel cursussen.
Bekijk het overzicht en meld u direct aan.
__________________________________________________________________________________

Als we vice versa willen weten wat de contante waarde is van de reeks dan zetten we de basisfunctie HW(rente;aantal-termijnen;bet;tw;type_getal) ook zesmaal in.
We hebben ook hier de berekening direct onder het blok van de eindwaarde berekening geplaatst.

In regel 17 hebben we bij het argument TW de storting van € 1.000 uitsluitend onder de even jaren  ingevuld. In regel 12 is de oplopende reeks jaarreeks 1 tot en met 6 opgenomen.

De contante waarde van de reeks bedraagt € 2.475,95 (H19) en is gelijk aan de optelling van het cellenbereik (B19:G19).

Ingeval we deze berekening met de hand uitvoeren dan ziet deze er als volgt uit:

Elke HW kolom vertegenwoordigt hierbij steeds weer één van de optelfactoren uit de handmatige reeks, waarbij de even jaren een nulwaarde vertegenwoordigen.

Omdat sprake is van een constante intrestvoet zijn de beide einduitkomsten weer eenvoudig tot elkaar te herleiden met behulp van de vergelijking:

Tot slot: een potpourri aan onregelmatigheden
Bij de inleiding over onregelmatige reeksen poneerden we de stelling dat – op het gebied van onregelmatigheid – praktisch alles te berekenen valt met de 5 basisfuncties.
Achtereenvolgens hebben we rekenvoorbeelden uitgewerkt met onregelmatigheid op het gebied van bedragen (voorbeeld 1), rentevoeten (voorbeeld 2) en betalingsintervallen (voorbeeld 3).

We sluiten af met een rekenvoorbeeld (4) waarbij we tegelijkertijd alle besproken vormen van onregelmatigheid in de strijd gooien.
Bovendien bekijken we in het voorbeeld hoe om te gaan met een gebroken jaar, als de storting niet op 1 januari, maar pas op een latere datum plaatsvindt.

Voorbeeld
We willen de eind- en contante waarde weten indien het stortingspatroon over de jaren 1 t/m 6 er als volgt uitziet:

• In jaar 1 (tijdstip nul) storten we op 1 januari een bedrag van € 1.000 en er geldt een jaarpercentage van 3,5%;
• In jaar 2 storten we pas op 1 juli een bedrag van € 500 en er geldt een (te verwachten) jaarpercentage van 3%;
• In jaar 3 slaan we vanwege onvoldoende liquide middelen de storting een jaartje over;
• In jaar 4 verwachten we nog steeds een tekort en zijn we zelfs gedwongen om op 1 januari € 1.000 van de rekening op te nemen bij een (te verwachten) intrestpercentage van 4%;
• In jaar 5 en jaar 6 bedraagt de storting per 1 januari weer € 1.000 bij een (te verwachten) intrestpercentage van 6%.

Als we deze rekenexercitie voor de eindwaarde handmatig uitvoeren krijgen we:

EW = 1.000* (1,035)6 + 500*(1,03)4,5- 1.000*(1,04)3 + 1.000*(1,06)2 +1.000*(1,05) = 2.589,12

U ziet dat we in het 2e jaar, bij de stortingdatum van 1 juli, gewerkt hebben met de exponent 4,5. Dit is de manier waarop ook Excel omgaat met gebroken jaren binnen basisfuncties.
En bij de opname van € 1.000 in jaar 4 werken we met een min post.
__________________________________________________________________________________
Volg een topcursus Excel 
Ontdek snel en eenvoudig hoe u de tijd die u aan Excel besteedt halveert.
Benut Excel’s potentie volledig. Volg een van de Excel cursussen.
Bekijk het overzicht en meld u direct aan.
__________________________________________________________________________________

We halen het inmiddels bekende 6-jaars sjabloon uit vorige voorbeelden weer van stal en vullen hierin de bovenstaande waarden in bij de berekening van de eindwaarde met behulp van de  basisfunctie TW(rente;aantal_ termijnen;bet;hw;type_getal).

De eindwaarde van de reeks bedraagt € 2.859,12 (H9) en is gelijk aan de optelling van de functie uitkomsten uit het cellenbereik (B9:G9).

Tot slot doen we hetzelfde voor de contante waardeberekening met behulp van de functie
HW(rente;aantal-termijnen;bet;tw;type_getal).

De contante waarde van de reeks bedraagt € 2.027,98 (H19) en is gelijk aan de optelling van het cellenbereik (B19:G19).

Als we het handmatig per kolom narekenen komen we tot een identieke uitkomst:

Bij de contante waarde is, in het gebroken 2e jaar, gewerkt met de exponent 2,5.

Geert Wessels (1957) studeerde bedrijfseconomie aan de KUB (doctoraal 1981: specialisatie ondernemingsfinanciering) en volgde daarna nog diverse postdoctorale informaticastudies. Hij heeft verschillende staf-, advies- en managementfuncties vervuld binnen met name de gezondheidszorg en de volkshuisvesting. Zijn ervaringen met Excel liggen met name op het terrein van de financiële rekenkunde.

Klassiekers uit het archief van Financieel-Management.nl:
10 veel voorkomende fouten in Excel
10 veel voorkomende fouten in Excel – deel 2

Bekijk ook de inspirerende video’s:
Excel Video #1: Winstmaximalisatie met de oplosser
Excel Video #2: Bekijk alle opties met ‘wat-als’ analyses
Excel Video #3: Overzicht in een handomdraai met een ‘histogram’
Excel Video #4: Elimineer fouten met snijpuntoperatoren
Excel Video #5: Boek tijdswinst met draaitabellen
Excel Video #6: Foutloos vermenigvuldigen met ‘Productmat’
Excel Video #7: Effectief Formules controleren
Excel Video #8: Optimale rooster- of werkplanning
Excel Video #9: Sneller rekenen met ‘plakken speciaal’
Excel Video #10: Snel foutmeldingen opsporen en herstellen

Ontdek meer verbluffende en eenvoudige mogelijkheden van Excel:
Cursus Excel 2010
Cursus Effectieve liquiditeitsprognoses met Excel
Cursus Investeringsanalyses met Excel
Cursus VBA Excel