Excel: De omzetting van aflossings- of betaalplannen

Door Geert Wessels

Dit artikel maakt deel uit van de tweedelige reeks “Het converteren en gelijkschakelen van kasstromen met Excel”, bestaande uit:
Deel 1 – De omzetting van aflossings- of betaalplannen
Deel 2 – Sparen ter financiering van vaststaande of variabele uitgaven in de toekomst

Inleiding
De handmatige berekening van vraagstukken rond waardevergelijking van verschillende kasstromen vereist veel geduld en blijkt – vooral als kasstromen omvangrijk zijn – nogal foutgevoelig. Dit geldt – weliswaar in iets mindere mate – ook voor het gebruik van wetenschappelijke calculators, waarbij veel toetsaanslagen nodig zijn en het zicht op de eigenlijke berekening schuil gaat achter een klein venstertje.

Met Excel werkt alles een stuk comfortabeler door middel van de gecombineerde of meervoudige inzet van de basisfuncties HW, TW en BET. Daarnaast introduceren we de functie NHW (zie ook de reeks Effectief Financieel Rekenen met Excel voor uitleg HW en BET).

In dit eerste artikel van de tweedelige reeks kijken we naar de omzetting van aflossings- of betaalplannen.

1. De omzetting van aflossings- of betaalplannen
Bij dit soort omzettingen geldt dat de hoogte van de contante waarde van de nieuw te vormen periodieke betalingsreeks altijd gelijk dient te zijn aan die van de bestaande. Daarbij maken we gecombineerd gebruik van de in een eerder artikel genoemde basisfunctie HW en BET met onderliggend de (enigszins ingewikkeld ogende) vergelijking van een dalende meetkundige reeks:

met:

CW1 =  de contante waarde voor de eerste (om te zetten) reeks;
CW2 =  de contante waarde voor de tweede (nieuwe) reeks;
r        =  de reden van de meetkundige reeks;
n       =  het aantal termen van de eerste meetkundige reeks;
m      =  het aantal termen van de tweede meetkundige reeks.

De door Excel op te lossen variabele is termijnbedrag2. Dit is – in termen van een dalende meetkundige reeks – de eerste term van de nieuwe reeks.

Maar laten we dit ingewikkeld stukje rekenwerk maar even voor wat het is. Excel gaat er, zoals we zullen zien, in de praktijk heel wat transparanter en praktischer mee om.

Voorbeeld 1
Ook u heeft als ondernemer te maken met economisch mindere tijden en u wilt graag in onderhandeling met uw financier om het bestaande aflossingsplan van een bestaande lening over een langere periode te spreiden.

In de bestaande financieringsovereenkomst staat opgenomen dat u de verplichting heeft jaarlijks over de jaren 2013 tot en met 2020 op 1 juli een rond bedrag van € 5.000 af te lossen, dus in 8 aflossingstermijnen.

U wilt graag in onderhandeling om het aflossingsplan tot 20 jaar op te rekken, tot en met 1 juli 2032. De intrest op de bestaande lening bedraagt 5 % jaarlijks en u wilt dat dit percentage ook voor de langere periode blijft gelden..

Bij het omzetten van de bestaande naar de nieuwe reeks betalingen maken we in Excel  gecombineerd gebruik van de basisfuncties HW(rente;aantal-termijnen;bet;tw;type_getal) en BET(rente;aantal-termijnen;hw;tw;type_getal).

Vanuit het perspectief van de financier geeft de omzetting het volgende beeld:

De bestaande reeks betalingen is uitgezet in de cellen B2 tot en met B8. Deze reeks geeft een contante waarde HW van –€ 33.931,87 in cel B9 (==HW(B5;B2;B3;B7;B8)).
__________________________________________________________________________________
Excelleer met Excel
Werkt u regelmatig met Excel? Bent u ook veel energie kwijt aan het opstellen van analyses en presentaties? Ontdek nu de verbluffende mogelijkheden van Excel die u veel voordeel zullen opleveren, waarmee u forse tijdwinst boekt en onnodige fouten voorkomt. Benut Excel’s potentieel volledig. Volg een van de cursussen. Meld u direct aan.

__________________________________________________________________________________

Deze waarde wordt als argument HW (C4) ingevuld in de functie BET. De functie BET – welke berekend wordt met behulp van de in de cellen C2 tot en met C8 opgenomen argumenten – geeft vervolgens € 2.953,12 als uitkomst (C9). In beide reeksen gaan we uit van een prenumerando betalingsreeks (betaling vooraf: type_getal = 1).

Dus:

Als u de offerte van uw financier ontvangt moet, onder gelijke condities en een gelijkblijvend rentepercentage, het nieuwe aflossingsbedrag ligt op € 2.593,12.

De uitkomst van € 2.593,12 is ook te benaderen volgens de eerder gegeven vergelijking van een afnemende reeks.

Als we de proef op de som nemen geeft invulling het volgende te zien:

Invulling van de onbekende “termijnbedrag2” met de gevonden uitkomst (€ 2.593, 12) levert inderdaad een kloppende, sluitende vergelijking op.

Voorbeeld 2
We gaan weer uit van de bestaande financieringsovereenkomst van voorbeeld 1,
maar nu is het uitgangspunt van de ten tonele gevoerde ondernemer precies omgekeerd.
Het gaat hem uitstekend en hij beschikt over voldoende middelen om de lening vervroegd, in één keer af te lossen. Bovendien is de rente gezakt en kan hij eventueel elders een vergelijkbare lening tegen een actuele rente van 3% afsluiten.

Als de ondernemer naar zijn financier gaat krijgt hij echter te maken met de volgende “Contante waarde” clausule (boetebeding):

“– Als de actuele rente lager is dan de verschuldigde rente, is er renteverlies voor “de bank”. In dat geval wordt voor alle nog te betalen maandtermijnen afzonderlijk berekend om welk bedrag dit gaat. Dit leidt tot één totaalbedrag aan renteverlies voor de resterende rentevaste periode. Dit bedrag wordt naar beneden gecorrigeerd omdat u dit bedrag nu in een keer moet betalen in plaats van gespreid, en dat zou zonder deze correctie nadelig zijn voor u en voordelig voor “de bank”. Deze correctie heet “contant maken”. U moet dus de contante waarde van het totale renteverlies vergoeden aan “de bank”. Het contant maken gebeurt tegen de actuele rente –”.

Wat deze passage financieel voor de ondernemer betekent rekenen we na met Excel.

Om te beginnen schetsen we de uitgangssituatie waarbij we nog 8 jaarlijkse aflossingstermijnen van € 5.000 te gaan hebben volgens de oorspronkelijke overeenkomst.
In de kolom “gemiddeld restant” gaan we uit van aflossing per medio van het jaar.

In de laatste kolom kruipen we in de huid van de bank en kijken wat de bank aan rente inkomsten derft tengevolge van de lagere actuele rente.
(NB: achterliggende gedachte hierbij is dat de bank ook effectief verlies leidt omdat de door bank aangetrokken lening ter financiering van de lening van de ondernemer nog steeds op 5% staat. In de bijgaande rekenexercitie hanteren we dit percentage dan ook in de disconteringsvoet van de bankier)

De boeteberekening ziet er aansluitend in Excel als volgt uit:

We hebben hierbij de functie HW meervoudig (HW 1 t/m HW8) ingezet om zo de contant gemaakte inkomstenderving te kunnen berekenen. Bij het argument tw_verschil is het inkomstenverschil per jaar ingevuld en vanuit het perspectief van de bank rekenen we met disconteringspercentage van 5%.

In cel J32 resulteert als som van de per jaar berekende HW uitkomsten (J32 =SOM(B32:I32) de boeterente, welke € 2.750,41 bedraagt.

Praktische aanwijzing
Dit soort berekeningen (met meervoudige parallelle inzet van eenzelfde functie) lijkt op het eerste gezicht veel handenarbeid te vergen. In de praktijk valt dit erg mee omdat het een kwestie is van kopiëren van kolommen. Daarna pas je selectief de veranderende cellen aan (zoals in het voorbeeld het “aantal termijnen” waarbij de teller steeds met “1” opgehoogd wordt en de “tw” waarden). Tot slot tel je alles op in de toe te voegen totaalkolom.

Alternatieve berekeningswijze: de functie NHW2
Indien sprake is van gelijke termijnen (in ons voorbeeld steeds één jaar) en een gelijkblijvend rentepercentage over de gehele periode (in ons voorbeeld 5%) dan kan – in plaats van parallelle inzet van de functie HW – ook gebruik gemaakt worden van de functie
NHW.

In de helpfunctie van Excel treft u over deze functie onder andere het volgende aan:

NHW(rente;waarde1;waarde2; …)
rente is het discontopercentage over één termijn.
waarde1; waarde2;… zijn maximaal 29 argumenten die de betalingen en inkomsten weergeven.
– De transacties met waarde1; waarde2;… moeten met regelmatige tussenpozen en aan het einde van elke termijn plaatsvinden.
– NHW gebruikt de volgorde van waarde1; waarde2;… om de volgorde van de cashflows te bepalen. Zorg ervoor dat u de betalingen en inkomsten in de juiste volgorde invoert.

Met de functie NHW is het dus mogelijk om alle tw waarden (tot een maximum van 29 waarden) tegelijk in te brengen.

Als we dit doen (alleen de eerste 4 waarden zijn zichtbaar) dan geeft dit het volgende beeld in het dialoogvenster van deze functie:

Linksonder in het scherm zien we de ons al bekende uitkomst (€ 2.750,41) verschijnen. In volgende artikelen komen we nog uitgebreid terug op de mogelijkheden van de functie NHW, met name bij investeringsvraagstukken.

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