Excel: Financieel rekenen met waardepapier

Algemeen
Een belegging in effecten kan – vanuit het gezichtspunt van de belegger – worden gezien als een reguliere investeringsbeslissing waarbij ernaar wordt gestreefd om – gegeven een aanvaardbare risicograad – een zo hoog mogelijk rendement te behalen. In dit licht kan bij effectenberekeningen dan ook prima gebruik gemaakt worden van de functies waarmee ook de Netto Contante Waarde en de Interne Rentabiliteit van een investeringsproject berekend worden.

In een vorige artikelenreeks zagen we dat we – bij “kortere termijn” investeringsanalyses voor het berekenen van de Netto Contante Waarde en de Interne Rentabiliteit – gebruik kunnen maken van de functies NHW2 (rente;waarden;datums) en IR.SCHEMA (waarden;datums;schatting). De Engelstalige tegenhangers worden gevormd door de functies XNPV (rente;waarden;datums) en XIRR(waarden;schatting).
Onderliggend maakt Excel bij de berekening van de NCW gebruik van de vergelijking:

en bij de berekening van de Interne Rentabiliteit van de vergelijking:

met:

Beide functies werken op basis van een datumschema, waarbij de rente op dagbasis wordt toegerekend. Daarbij wordt standaard uitgegaan van het werkelijke aantal dagen binnen de rekenperiode.

Daarnaast beschikt Excel over een aantal specifieke tot zeer specifieke functies die met name betrekking hebben op vastrentende waardepapieren (obligaties) en op (Amerikaans) schatkistpapier. In dit artikel beperken we ons tot de meest gangbare, welke algemeen toepasbaar zijn voor vastrentende waardepapieren.
__________________________________________________________________________________
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.
__________________________________________________________________________________

Waar de functies NHW2 en IR.SCHEMA werken op basis van het werkelijk aantal dagen, maken de op waardepapieren gerichte functies gebruik van een variabel instelbaar datumschema (in de vorm van het argument “soort-jaar”). Dit kan, bij de inzet van verschillende functies, in de praktijk leiden tot onderling geringe uitkomstverschillen.

De algemene functies PRIJS.NOM en RENDEMENT voor vastrentende waardepapieren
Met de functie PRIJS.NOM () kunnen we op basis van een reeks cashflows voor een vastrentend waardepapier (doorgaans obligaties met een regelmatige reeks couponbetalingen) de aankoopwaarde bepalen, met de functie RENDEMENT () het rendement.

De functies:

PRIJS.NOM (stortingsdatum;vervaldatum;rente;rendem;aflossingsprijs;frequentie;soort_jaar)

en

RENDEMENT(stortingsdatum;vervaldatum;rente;prijs;aflossingsprijs;frequentie;soort_jaar)

vormen tegenhangers van de eerder behandelde generiek inzetbare functies NHW2() en IR.SCHEMA(). In de Engelstalige Officeversie zijn beide functies terug te vinden onder de noemers PRICE() en YIELD().

De argumenten die in deze functies gebruikt worden hebben de volgende betekenis:

Voorbeeld
We sluiten aan op het rekenvoorbeeld dat – ter verklaring van de werking van de functie PRIJS.NOM ()  – al sinds jaar en dag door Microsoft gebruikt wordt.

De parameters uit dit voorbeeld nemen we in onderstaande sheet (1) over voor de functie  PRIJS.NOM (). En dit doen we in kolom C ook voor de functie RENDEMENT (), omdat we weten dat beide functie direct samenhangen.

De ingevulde argumenten zijn voor beide functies identiek, m.u.v. de argumenten “rendem” (B5) en “prijs” (C4). Voor deze argumenten vullen we, in verband met de samenhang, over en weer de functie uitkomsten in (C10 en B10).

NB:
Opmerkelijk genoeg maakt Microsoft nergens gewag van deze toch belangrijke samenhang.

Hoe we de uitkomsten van deze functies moeten interpreteren tonen we door het voorbeeld in ondergelegen rijen 13 t/m 33 om te zetten in een datumschema en vervolgens de berekening te reconstrueren met behulp van de ons al bekende functies NHW2() en IR.SCHEMA().

Daarvoor maken we gebruik van een tweetal datumschema’s: één op basis van de werkelijke dagen en één op basis van de dagentelling (argument “soort jaar”) waarmee de functies PRIJS.NOM en RENDEMENT werken.

In het eerste datumschema (kolom A) hebben we de data uit het Microsoft voorbeeld als volgt verwerkt:

– op 15 februari 2008 (A13) vindt de aankoop / storting plaats;
– de eerste halfjaarlijkse rentebetaling  vindt plaats op 15 mei 2008 (A3);
– de volgende halfjaarlijkse rentebetalingen (A14:A32) vinden steeds plaats op 15 november en 15 mei;
– de laatste halfjaarlijkse rentebetaling vindt plaats op 15 mei 2017 (A32);
– de aflossing vindt tot slot plaats op 15 november 2017 (A33).
__________________________________________________________________________________
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.
__________________________________________________________________________________

Daarnaast hebben we, om een benadering te krijgen van de berekening van het Interne Rendement met de functie RENDEMENT, in kolom C een tweede datumschema opgenomen. De functie RENDEMENT rekent in sheet 1 voor het argument “soort jaar” met de waarde “0” Dit volgens de dagentelling in de onderstaande matrix:

Uit deze matrix blijkt dat “0” staat voor maandelijks 30 en jaarlijks 360 rentedagen. Hierop aansluitend hebben we in kolom B het aantal tussenliggende dagen bepaald m.b.v. de functie “Dagen360 ()”. Als voorbeeld het dialoogvenster voor cel B15:

Het met behulp van deze functie berekende aantal rentedagen hebben we in kolom C opgeteld bij het oorspronkelijke datumschema van kolom A. Hiermee verkrijgen we in kolom C een aangepaste basis, waarmee we met behulp van de functie IR.SCHEMA de interne rentabiliteit kunnen berekenen op een soortgelijke wijze waarop de functie RENDEMENT rekent met een 30/360 dagenschema.

In kolom D hebben we de aankoopprijs (D13) en de aflossing (D33) ingebracht. De aankoopprijs is gelijk aan de uitkomst van de functie PRIJS.NOM (B10) en de ingebrachte prijs bij de functie RENDEMENT (C4 ).

In kolom E hebben we de tweejaarlijkse (coupon) rentebetalingen ingebracht. Voor de eerste betaling op 15 mei is deze gelijk aan 0,5 * € 100 * (5,75 % : 2)  en voor alle overige data is € 100 * (5,75 % : 2)  ingevuld.

In kolom F (Cashflow Totaal) zijn de bedragen uit kolom D en E opgeteld.

Als we nu de Interne Rentabiliteit berekenen op basis van het oorspronkelijke datumschema (lees: o.b.v. werkelijke dagen) geeft dit in cel B35 als resultaat:

IR.SCHEMA(F13:F33;A13:A33) = 6,37%.

Dit ligt op een lager niveau als de interne rentabiliteit welke we berekenden met de functie RENDEMENT in C10 (6,50 %). Dit komt door het grotere aantal dagen waarmee de functie IR.SCHEMA van nature rekent.

Als we de functie IR.SCHEMA echter loslaten op het aangepaste 30/360 datumschema dan blijkt de uitkomst heel wat dichter in de buurt te komen:

IR.SCHEMA(F13:F33;C13:C33) = 6,47%.

Er resteert dan nog een “te verwaarlozen” verschil van 3 procent punt, ofwel 0,03%.
Hiermee hebben we werking van de functies PRIJS.NOM en RENDEMENT voldoende onderbouwd en verklaard.

De specifieke functies AFW.ET.PRIJS (), AFW.LT.PRIJS (), AFW.ET.RENDEMENT () en AFW.LT.RENDEMENT ().

De functies:

AFW.ET.PRIJS(stortingsdatum;vervaldatum;uitgifte;eerste_coupon;rente;rendem;aflossingsprijs;frequentie;soort_jaar)

en

AFW.ET.REND(stortingsdatum;vervaldatum;uitgifte;eerste_coupon;rente;prijs;aflossingsprijs;frequentie;soort_jaar)

respectievelijk

AFW.LT.PRIJS(stortingsdatum;vervaldatum;laatste_rente;rente;rendem;aflossingsprijs;frequentie;soort_jaar)

en

AFW.LT.REND(stortingsdatum;vervaldatum;laatste_rente;rente;prijs;aflossingsprijs;frequentie;soort_jaar)

lijken qua opzet erg veel op de algemene functies PRIJS.NOM () en RENDEMENT ().

Het verschil zit in de afwijkende berekening van de eerste (“ET”) respectievelijk de laatste (“LT”) betaling binnen de couponrente betaalreeks. Omdat “ET” en “LT” berekeningen onderling veel op elkaar lijken, beperken we ons tot een voorbeeld waarbij sprake is van een afwijkende eerste couponbetaling (“ET”).

Voor het gemak sluiten we weer aan op het Microsoft rekenvoorbeeld.

De parameters uit dit voorbeeld nemen we over in onderstaande sheet 3: voor de functie  AFW.ET.PRIJS in kolom B en voor de direct daarmee samenhangende functie AFW.ET.REND in kolom C.
__________________________________________________________________________________
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.
__________________________________________________________________________________

De ingevulde argumenten zijn voor beide functies weer identiek, m.u.v. de argumenten “rendem” (B6) en “prijs” (C5). Voor deze argumenten vullen we, in verband met de samenhang, over en weer de functie uitkomsten in (C12 en B12).

Hoe we de uitkomsten van deze functies moeten interpreteren tonen we door het voorbeeld in ondergelegen rijen 15 t/m 40 om te zetten in een datumschema en vervolgens de berekening te reconstrueren met behulp van de functies NHW2 en IR.SCHEMA.

Daarvoor maken we weer gebruik van een tweetal datumschema’s: één op basis van de werkelijke dagen en één op basis van de dagentelling (argument “soort jaar”) waarmee de functies AFW.ET.PRIJS en AFW.ET.REND werken.

In het eerste datumschema (kolom A) hebben we de data uit het Microsoft voorbeeld als volgt verwerkt:

– op 11 november 2008 (A15) vindt de aankoop / storting plaats;
– de eerste en afwijkende halfjaarlijkse rentebetaling  (“ET”) vindt plaats op 1 maart 2009 (A16);
– de volgende halfjaarlijkse regelmatige rentebetalingen (A17:A39) vinden steeds plaats op 1 maart en 1 september;
– aflossing vindt tot slot plaats op 1 maart 2021 (A40).

Daarnaast hebben we, om een benadering te krijgen van de berekening van het Interne Rendement met de functie AFW.ET.REND, in kolom C weer een tweede datumschema opgenomen. De functie AFW.ET.REND rekent in sheet 3 voor het argument “soort jaar” met de waarde “4” Dit volgens de dagentelling in de onderstaande matrix:

Uit deze matrix blijkt dat “4” staat voor maandelijks 30 en jaarlijks 360 rentedagen. Hierop aansluitend hebben we in kolom B het aantal tussenliggende dagen weer bepaald m.b.v. de functie “Dagen360 ()”.

Het met behulp van de functie “Dagen360” berekende aantal rentedagen hebben we in kolom C opgeteld bij het oorspronkelijke datumschema van kolom A. Hiermee verkrijgen we in kolom C een aangepaste basis, waarmee we met behulp van de functie IR.SCHEMA de  interne rentabiliteit kunnen berekenen op een soortgelijke wijze waarop de functie AFW.ET.REND rekent met een 30/360 dagenschema.

In kolom D hebben we de aankoopprijs (D15) en de nominale aflossing (D40) ingebracht. De aankoopprijs is gelijk aan de uitkomst van de functie AFW.ET.PRIJS (B12) en de ingebrachte prijs bij de functie AFW.ET.REND (C5 ).

In kolom E hebben we de tweejaarlijkse (coupon) rentebetalingen ingebracht.

Voor de regelmatige coupon betaalreeks (E16:E39) is deze gelijk aan € 100 * (7,85 % : 2)  = € 3,93
De eerste onregelmatige betaling (“ET” in E15) is volgens de datatelling van tabel 1 als volgt te berekenen:

In kolom F (Cashflow Totaal) zijn de bedragen uit kolom D en E opgeteld.

Als we nu de Interne Rentabiliteit berekenen op basis van het oorspronkelijke datumschema geeft dit in cel B42 als resultaat:

IR.SCHEMA(F15:F40;A15:A40) = 6,14%

Dit ligt op een lager niveau als de interne rentabiliteit welke we berekenden met de functie AFW.ET.REND in C12 (6,25 %). Dit wordt weer veroorzaakt door het grotere aantal dagen waarmee de functie IR.SCHEMA van nature werkt.

Als we de functie IR.SCHEMA loslaten op het aangepaste 30/360 datumschema dan blijkt de uitkomst weer heel dicht in de buurt te komen ( B44):

IR.SCHEMA(F15:F40;C15:C40) = 6,23%

Er is sprake van een “te verwaarlozen” verschil van 2 procent punt, ofwel 0,02 %. Hiermee hebben we de werking van de functies AFW.ET.PRIJS en AFW.ET.REND verklaard.

Tot slot: Wanneer gebruiken we welke functies
In zijn algemeenheid kan bij waardepapierberekeningen het volgende advies dienen:

– komt het op uiterste nauwkeurigheid aan (d.w.z. tot op de procentpunt nauwkeurig) kies dan voor specifieke Excel functies zoals PRIJS.NOM, AFW.ET.PRIJS, AFW.LT.PRIJS, RENDEMENT, AFW.ET.REND en AFW.LT.REND ;

– komt het ietsje minder aan op uiterste nauwkeurigheid en wenst u vooral transparantie en flexibiliteit kies dan voor de functies NHW2 en IR.SCHEMA en zet alle gegevens overzichtelijk in een (aangepast) datumschema.

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