Excel: Het samenstellen van investeringsanalyses

Algemeen
Een investeringsbeslissing kan worden genomen op basis van verschillende criteria.
Doorgaans vindt besluitvorming plaats op basis van de te verwachten geldstromen welke uit een investeringsproject voortvloeien. Het terugverdienmodel is in dit verband de eenvoudigste, maar tegelijkertijd ook de minst nauwkeurige en minst betrouwbare methode.

Het is een methode van investeringsbeoordeling die primair uitgaat van de snelheid waarmee liquiditeit gecreëerd wordt. Het project met de kortste terugverdientijd wordt bij deze methode als beste beoordeeld, maar de methode kijkt verder niet naar de termijn welke is gelegen na het terugverdienmoment. Een ander belangrijk nadeel is dat geen rekening wordt gehouden met het tijdselement en met de risicograad van de inkomsten en uitgavenstromen.

Berekeningen volgens het Netto Contante Waarde of Discounted Cash Flow (DCF) principe kennen deze nadelen niet: ze houden rekening met het tijdselement van de inkomsten en uitgaven en er kan gewerkt worden met een risico opslag in de disconteringsvoet. Een belangrijk voordeel is bovendien dat de rekenhorizon flexibel kan worden ingesteld.
Excel beschikt over verschillende functies om de Netto Contante Waarde en de hiermee samenhangende Interne Rentabiliteit van investeringsprojecten te berekenen.

In dit artikel over investeringsanalyses bekijken we de berekening van de Netto Contante Waarde en de Interne Rentabiliteit voor de langere en voor de kortere termijn.

Langere termijn investeringsanalyses
De tijdshorizon strekt zich doorgaans uit over meerdere jaren en de uitkomsten zijn, in aansluiting op de onderliggende geprognosticeerde inkomsten- en uitgavenstromen, eerder indicatief dan exact te noemen. Voor deze berekeningen maken we, bij het berekenen van de Netto Contante Waarde en de Interne rentabiliteit, gebruik van de functies NHW (rente;waarde1;waarde2; …) en IR (waarden;schatting).
De Engelstalige tegenhangers worden gevormd door de functies NPV(rente;waarde1;waarde2; …) en IRR(waarden;schatting).

Kortere termijn investeringsanalyses
De  tijdshorizon strekt zich doorgaans uit over een relatief korte periode van maximaal 1 tot 3 jaar en de onderliggende inkomsten- en uitgavenstromen zijn tot op datum- of dagbasis te herleiden. Vaak wordt bij dit soort analyses ook achterom gekeken om te zien of een investering in de achtergelegen periode rendabel is geweest.

Voor dit soort berekeningen vallen we, bij het berekenen van de Netto Contante Waarde en de Interne rentabiliteit, terug op 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);

Langere termijn investeringsanalyses
De algemene vergelijking voor de berekening van de Netto contante waarde luidt:

met
Z  =  de in- en uitgaande stromen van het investeringsproject;
k  =  de vermogenskostenvoet;
t  =   het aantal perioden.

Om tot de bepaling van de interne rentabiliteit te komen wordt aan deze vergelijking de waarde “0” toegekend:

waarbij “r” berekend kan worden als de interne rentabiliteit van een investeringsproject.

Deze vergelijkingen kunnen op verschillende manieren naar Excel vertaald worden. De wijze waarop is afhankelijk van de structuur van de inkomsten- en uitgavenstromen die in de loop van de tijd uit een project voortvloeien.

Daarbij bekijken we de volgende drie varianten:

1. Langere termijn investeringsanalyse: variant 1
Een investeringsuitgave Z0 op tijdstip t=0 wordt gevolgd door een reeks inkomstenstromen Zt op de tijdstippen t=1 t/m t=n.
Tussen de tijdstippen is sprake van een gelijk tijdsinterval. Het rentepercentage per tijdsinterval bedraagt k %.

2. Langere termijn investeringsanalyse: variant 2
Een investeringsuitgave Z0 op tijdstip t=0 wordt gevolgd door een wisselende reeks inkomsten- en uitgavenstromen Zt op de tijdstippen t=1 t/m t=n.
Tussen de tijdstippen is sprake van een gelijk tijdsinterval. Het rentepercentage per tijdsinterval bedraagt k %.

3. Langere termijn investeringsanalyse: variant 3
Een reeks inkomstenstromen Zt op de tijdstippen t=0 t/m t=n-1 wordt pas op het eind gevolgd door een investeringsuitgave op tijdstip t=n.
Tussen de tijdstippen is opnieuw sprake van een gelijk tijdsinterval, waarbij het rentepercentage k % per tijdsinterval bedraagt.

Langere termijn investeringsanalyse: variant 1
In dit voorbeeld wordt een investeringsuitgave van minus € 500.000 op tijdstip “0” gevolgd door een reeks positieve inkomsten op de tijdstippen t=1 tot en met t=10.
Op tijdstip 1 is sprake van een inkomstenstroom van € 100.000 en op de volgende tijdstippen groeit de inkomstenstroom steeds met € 10.000. Op tijdstip 10 bedragen de inkomsten € 190.000.

Per tijdsinterval rekenen we met een vast rekenpercentage (lees: een vaste disconteringsvoet) van 4%. Genoemde cash flow reeks staat weergegeven in het bereik B4:B14,  het rentepercentage in B.  

De Netto Contante Waarde
Om de netto contante waarde (NCW) in dit voorbeeld te berekenen kunnen we op verschillende manieren te werk gaan. Allereerst kunnen we de NCW handmatig berekenen door de CW per tijdstip naar tijdstip “0” te herleiden en vervolgens alles op te tellen. Dat hebben we in kolom C gedaan, waarbij we voor de berekening verwijzen naar de naastgelegen cellen in kolom D. Optelling van de reeks leidt tot een uitkomst van € 649.903,10 in cel C15.

We kunnen de NCW waarde echter ook berekenen met behulp van de functie HW of met PV als Engelstalige tegenhanger. Dat is gebeurd in kolom E, waarbij we voor de toelichting verwijzen naar de naastgelegen cellen in kolom F. Optelling van deze reeks leidt eveneens tot een uitkomst van € 649.903,10.

Maar het kan sneller met de functies NHW of NPV (als Engelstalige tegenhanger). In cel B17 leidt de berekening NHW(B1;B4:B14) of NPV(B1;B4:B14) in eerste aanleg tot een uitkomst van € 624.906,83. Hierop moeten we bij gebruik van de functie NHW (NPV) altijd een correctie doorvoeren voor tijdstip “0” omdat dit bedrag niet contant hoeft te worden gemaakt, hetgeen NHW wel standaard doet. Inclusief deze correctie (=NHW(B1;B4:B14)*(1+4%))  verschijnt in cel B18 de juiste uitkomst, welke weer gelijk is aan die van de vorige twee methoden.

De Interne Rentabiliteit
De interne rentabiliteit kan verkregen worden door naar het rentepercentage te zoeken waarbij de NCW van de investeringsuitgave in combinatie met de inkomstenstromen precies “0” is. Met behulp van Excel kunnen we deze klus op twee manieren klaren.

We beginnen met de functie IR (waarden;schatting) in cel B19. Dit geeft IR(B4:B14;0) = 22,46%. Met andere woorden bij 22,46% wordt het breakeven punt bereikt waarbij het project nog juist rendabel is.
__________________________________________________________________________________
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.
__________________________________________________________________________________

Deze uitkomst kunnen we ook bereiken door gebruikmaking van de invoegtoepassingen “Doelzoeken” of “Oplosser”. We maken het niet moeilijker dan nodig en we kiezen voor de eerste:

We willen de (gecorrigeerde) NCW in B18 precies op “0” laten uitkomen door wijziging van het rentepercentage (van 4%) in cel B1.

Als we op OK drukken verschijnt in B1 het percentage dat we ook verkregen via de functie IR in cel B19.

Hoe moeten we de werking en uitkomst van de functie IR duiden ?

De werking van de functie IR berust op de volgende twee ficties:

– achter de investering ligt een maatfinancieringsconstructie met achterliggend een rente- en aflossingschema;
– er is geen verschil tussen debet- en creditrente in geval er sprake is van (afwisselend) positieve en negatieve kasstromen.

Om deze ficties zichtbaar te krijgen hebben we het voorbeeld voorzien van een viertal hulpkolommen G,H, I en J (de kolommen D, E en F houden we verborgen).

In kolom G wordt de rente berekend, zoals toegelicht in kolom H. De rente is steeds gelijk aan het IR percentage (22,46%) maal het saldo van de vorige periode c.q. tot en met het laatste tijdstip.

Het nieuwe saldo, zoals toegelicht in kolom J, is steeds gelijk aan het bestaande saldo plus de nieuwe netto cashflow (kolom B) plus de berekende rente over het bestaande saldo (kolom G).

In aansluiting op een NCW van “0” (bij het IR percentage van 22,46%) komt op tijdstip 10 het saldo ook precies op “0” uit (cel I14). De grafische relatie tussen NCW en IR ziet er in dit voorbeeld als volgt uit:

Bij een disconteringsvoet van minder dan 22,46% is sprake van een positieve NCW, bij een hoger percentage is de NCW negatief. Bij een disconteringsvoet van 22,46% ontstaat een breakeven situatie.

Langere termijn investeringsanalyse: variant 2
In dit voorbeeld wordt een investeringsuitgave van minus € 500.000 op tijdstip “0” gevolgd door een reeks positieve inkomsten van € 200.000 op de tijdstippen t=1 tot en met t=9.
Op tijdstip 10 is tot slot weer sprake van een fors negatieve uitgavenstroom van € 1.400.000, voor het ontmantelen en opruimen van de investering.

Per tijdsinterval rekenen we met een vast rekenpercentage (ofwel een vaste disconteringsvoet) van 4%. De cashflow reeks staat weergegeven in het bereik B4:B14, het rentepercentage in B1.  

Als we in cel B17 de NCW met behulp van NHW(B1;B4:B14) of NPV(B1;B4:B14) berekenen verschijnt (in vergelijking met de handmatig berekende reeks in cel C15) in eerste aanleg een te lage uitkomst: € 39.688,93

Hierop passen we weer een correctie toe door voor tijdstip “0”. Inclusief deze correctie (=NHW(B1;B4:B14)*(1+4%)) verschijnt in cel B18 de juiste uitkomst, welke gelijk is aan de handmatig berekende reeks:  € 41.276,49.

De Interne Rentabiliteit: twee mogelijke uitkomsten|
Als we in variant 2 kijken naar de uitkomst voor de functie IR (waarden;schatting) komt in cel B19 een uitkomst van 2,48% naar voren. Daarbij hebben we voor de parameter “schatting” een waarde van “0” ingevuld.

Nu doet zich bij variant 2 het merkwaardige verschijnsel voor dat er nog een tweede uitkomst blijkt te bestaan voor IR welke ook tot een NCW van “0” leidt.
Als we in B20 voor de parameter “schatting” (met wat voorwetenschap: zie grafiek 2) een waarde invullen van “0,3” verschijnt als tweede mogelijke uitkomst een IR percentage van 30,28 %.

En als we de proef op de som nemen door aansluitend in de hulpkolom G te rekenen met 30,28 procent dan blijkt het saldo in I14 ook op “0” uit te komen.

Dat er bij deze variant meer IR percentages bestaan is ook te zien in het grafische verband tussen NCW en IR:

Bij een disconteringsvoet van minder dan 2,48% en meer dan 30,28% is sprake van een negatieve NCW. Bij alle tussengelegen percentages is sprake van een positieve NCW waarde.

Ten aanzien van de vraag welk percentage “het meest juist” is bestaat geen eenduidig antwoord. Een en ander hangt samen met de wijze waarop de functie IR het percentage gebruikt, zoals getoond in de hulptabellen G t/m J.
__________________________________________________________________________________
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.
__________________________________________________________________________________

Overigens moet er nadrukkelijk op gewezen worden dat lang niet in alle gevallen, waarbij sprake is van een wisselend inkomsten- en uitgavenpatroon, ook sprake is van twee IR uitkomsten. In de meeste gevallen leidt een wisselend inkomsten- en uitgavenpatroon ook hier maar tot één unieke IR waarde.

En in de praktijk wordt deze verkregen door de parameter “schatting” standaard van een “0” waarde te voorzien.

Lange termijn investeringsanalyse: variant 3
In dit voorbeeld wordt een toenemende reeks positieve inkomstenstromen op de tijdstippen t=0 tot en met t=9, gevolgd door een eenmalige negatieve uitgavenstroom van € 1.400.000.
op tijdstip t=10.

Per tijdsinterval rekenen we weer met een vast rekenpercentage (disconteringsvoet) van 4%.
De cash flow reeks staat weergegeven in het bereik B4:B14, het rentepercentage in cel B1.  

Binnen variant 3 worden eerst de inkomsten verzameld en wordt de investering pas op het laatste moment gedaan. Hierbij kan gedacht worden aan fondsenwerving of het vooraf financieren van een eenmalig evenement uit lidmaatschappen of bijdragen.

Als we in cel B17 de NCW met behulp van NHW(B1;B4:B14) of NPV(B1;B4:B14) berekenen verschijnt (in vergelijking met de handmatig berekende reeks in cel C15) in eerste aanleg een te lage uitkomst: € 168.966,22

Hierop passen we weer de correctie toe voor tijdstip “0”. Inclusief deze correctie (=NHW(B1;B4:B14)*(1+4%)) verschijnt in cel B18 de uitkomst die  gelijk is aan die van de handmatig berekende reeks:  € 175.756,07.

De Interne Rentabiliteit
Als we de functie IR (waarden;schatting) in cel B19 invoeren verschijnt een uitkomst van 0,59%. De grafische relatie tussen de NCW en de IR ziet er onder variant 3 als volgt uit:

Dit beeld, waarbij de NCW positief is bij een rentevoet hoger dan de berekende IR waarde (=0,59%), blijkt omgekeerd aan het verloop dat we zagen bij de eerste variant waarbij sprake was van een uitgavenstroom op t=0, gevolgd door inkomstenstromen in alle volgende perioden.

Voor de interpretatie wordt verwezen naar de opgenomen hulptabellen G t/m J en de onderliggende fictie dat bij positieve- en negatieve saldi met een gelijk rentepercentage wordt gerekend.

Kortere termijn investeringsanalyses
Bij kortere termijn investeringsanalyses maken we voor het berekenen van de Netto Contante Waarde en de Interne rentabiliteit gebruik 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:

Beide functies werken op basis van een datumschema, waarbij de rente op dagbasis wordt toegerekend. Hierdoor kan met de functies NHW2 en IR.SCHEMA een stuk nauwkeuriger gerekend worden dan met de tegenhangers NHW en IR, welke vooral ingezet worden bij analyses over een langere termijn.

Bij rendementsberekeningen van effecten vormen de functies NHW2 en IR.SCHEMA een ideaal hulpmiddel. Hier komen we in een volgende reeks artikelen nog op terug.

Voorbeeld 1
We gaan uit van 4 tijdstippen, met op tijdstip 0 een uitgaande cashflow van € 300.000, gevolgd door 3 tijdstippen met inkomende cashflows van respectievelijk € 100.000, € 110.000 en € 120.000.

Om de werking van de functies NHW2 en IR.SCHEMA te kunnen vergelijken met de al bekende functies NHW en IR werken we bovendien met datums voor deze tijdstippen. Deze staan opgenomen in B3:B6. Tussen de tijdstippen zit steeds een vaste periode van 365 dagen.

We rekenen met een disconteringsvoet van 4% (cel B1).

Om de netto contante waarde (NCW) in dit voorbeeld te berekenen kunnen we op  verschillende manieren te werk gaan.

Allereerst hebben we de NCW in kolom D handmatig berekend door de CW per tijdstip naar tijdstip “0” te herleiden en vervolgens alles op te tellen. Optelling van de reeks leidt tot een uitkomst van € 4.534,59 in cel D7.
__________________________________________________________________________________
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.
__________________________________________________________________________________

Vervolgens hebben we in B9 met de ons al bekende functie NHW (NPV als Engelstalige tegenhanger) de ongecorrigeerde NCW berekend. Dit leidt via NHW(B1;C3:C6) of NPV(B1;C3:C6) in eerste aanleg tot een te lage uitkomst van € 4.360,19. Inclusief de vereiste correctie (=NHW(B1;B4:B14)*(1+4%)) verschijnt in cel B10 de juiste uitkomst: € 4.534,59 .

Berekening van de bijbehorende Interne Rentabiliteit geeft in B11 (=(IR(C3:C6;0)) een uitkomst te zien van 4,77%. Tot zover nog niets nieuws onder de zon.

Nu gaan we daarnaast, op basis van het datumschema, de Netto contante waarde en de Interne Rentabiliteit berekenen met behulp van de functies NHW2 en IR.SCHEMA.
Dat doen we in cel B13 door middel van NHW2(B1;C3:C6;B3:B6) en in cel B14 door middel van IR.SCHEMA(C3:C6;B3:B6).

In de cellen B13 en B14 verschijnen nu- in vergelijking tot de eerdere NHW en IR berekeningen – volkomen identieke uitkomsten. Dus als we de tussenliggende periode op één jaar of beter gezegd 365 dagen houden is er geen  enkel verschil. Mocht er een schrikkeljaar tussen zitten, dan is er sprake van een miniem verschil.

Voor de liefhebbers is de NCW uitkomst handmatig (op dagbasis) als volgt na te rekenen:

Voorbeeld 2
Nu we in voorbeeld 1 gezien hebben dat voor een vaste interval van een jaar de uitkomsten van de functies NHW en NHW2 respectievelijk IR en IR.SCHEMA identiek zijn, gaan we met het datumschema spelen om daarmee te meerwaarde van de functies NHW2 en IR.SCHEMA te tonen.

We gaan nu uit van twee inkomstenmomenten in een jaar (op 1 juli en op 1 januari) en van een  disconteringsvoet van 4% (B1).

Op basis van het datumschema bedraagt de NCW in B12:

NHW2(B1;B3:B9;A3:A9) = € 7.575,11

en de Interne rentabiliteit in B13:

=IR.SCHEMA(B3:B9;A3:A9) = 5,47%.

Dat, in vergelijking tot voorbeeld 1, zowel bij de Netto contante Waarde als bij de Interne rentabiliteit sprake is van een hogere uitkomst hangt samen met het feit dat de inkomsten gemiddeld eerder ontvangen worden.

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