Het effectief opzetten van amortisatieschema’s in Excel

11 april 2013

Amortisatieschema's worden met name gebruikt bij de aflossing van leningen. In dit eerste deel van de reeks "Het opzetten van Amortisatie- en Cashflowschema's in Excel" besteden we aandacht aan de opbouw van een amortisatieschema op basis van een annuïtair leningschema. Via concrete voorbeelden en visuals leert u in no time hoogst kwalitatieve amortisatieschema's op te zetten.

Dit artikel maakt deel uit van de reeks “Het opzetten van Amortisatie- en Cashflowschema’s in Excel”. Lees ook het tweede deel: “Het effectief opzetten van cashflowschema’s in Excel”.

Inleiding
Amortisatie- en cashflowschema’s zijn hulpmiddelen om geldstromen in de tijd weer te geven. Het fundamentele verschil tussen beiden is dat bij amortisatieschema’s – in tegenstelling tot cashflowschema’s – altijd sprake is van een “(nul)saldo” waarnaar wordt toegewerkt in de laatst opgenomen periode. Amortisatieschema’s worden met name gebruikt bij de aflossing van leningen, terwijl cashflowschema’s vaak worden ingezet bij de berekening van de contante- of eindwaarde van geldstromen.

Amortisatieschema’s in Excel
Bij het ontwerp en de bouw van een optimaal werkend amortisatieschema nemen we de volgende 4 spelregels in acht:
a) De invoer moet juist zijn;
b) De invoer moet volledig zijn;
c) Als de invoer juist en volledig is, moet het model zich zoveel mogelijk automatisch vullen tot het moment waarop het nulsaldo wordt bereikt;
d) De rijen die liggen onder de rij waarin het nulsaldo wordt bereikt moeten onderdrukt worden, zodat geen overtollige rijen met nulwaarden of hinderlijke foutmeldingen verschijnen.

Deze spelregels geven we gestalte in een voorbeeld dat werkt op basis van annuïteiten.
Hierbij maken we – voor de berekening van de annuïteit zelf – gebruik van de ons uit vorige artikelen reeds bekende functie BET.
Op basis van de uitkomst van deze functie is voor ieder termijn het rente- en aflossingsdeel te bepalen m.b.v. een amortisatieschema.

Daarnaast is het mogelijk om – als aanvulling of los van een amortisatieschema – het rente en aflossingsdeel per termijn te berekenen met behulp van de functies IBET respectievelijk PBET. Hetzelfde is – maar dan voor meerdere aaneengesloten termijnen tegelijk – mogelijk met de functies CUM.RENTE en CUM.HOOFDSOM

Het voorbeeld
De parameterinvoer voor het amortisatieschema nemen we op in het bereik C3:C7

Als we kijken naar voorwaarde onder a. (“de invoer dient juist te zijn”) dan kunnen we dat binnen het invoerbereik op celniveau vastleggen m.b.v. “Gegevensvalidatie (Tab “Gegevens” -> “Hulpmiddelen voor gegevens”)

Als voorbeeld hebben we voor de startdatum (C7) de volgende validatie opgenomen:

Bovendien willen we de omvang van het model maximeren zodat maximaal sprake is van 30 jaar (C5) en 12 termijnen per jaar (C6).

Deze regels leggen we eveneens vast met behulp van “Gegevensvalidatie”.
__________________________________________________________________________________
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.
__________________________________________________________________________________

Voor cel C5 (“Leenperiode in jaren”) wordt het maximum aantal jaren (30) daarbij als volgt ingevuld:

En voor C6 (“Aantal termijnen per jaar”) stellen we vervolgens het maximum op 12 termijnen per jaar.

Een en ander impliceert dat de rekenhorizon maximaal 30 maal 12, ofwel 360 termijnen omvat. Dit is in het model het maximum aantal regels (rijen) dat tot het moment van leegloop gevuld kan worden. Is sprake van minder termijnen dan moeten de regels vanaf het moment van leegloop automatisch onderdrukt worden.

Als controlemiddel dat de invoer juist (voorwaarde a) en volledig (voorwaarde b) is ingevuld hebben we onder G3 en G4 een klein dashboard opgenomen met daarin de – op basis van de functie BET – berekende annuïteit en het totaal aantal perioden.

De toets op juistheid en volledigheid (voorwaarde “c”) hebben we binnen het “EN” deel van cel G3 als volgt ingevuld:

=ALS(EN($C$30;$C$40;$C$50;$C$60);-BET($C$4/$C$6;$C$5*$C$6;$C$3);””)

Dit wil zeggen dat alle benodigde parameters moeten zijn ingevuld (0) voordat m.b.v. de functie BET de annuïteit berekend wordt in het tweede deel.
Zolang dit niet het geval is wordt de uitkomst onderdrukt, zoals opgenomen in het laatste deel (“ “).

En op soortgelijke wijze werkt ook cel G4 bij de bepaling van het aantal termijnen:

=ALS(EN($C$30;$C$40;$C$50;$C$60);$C$5*$C$6;””)

Als de invoer juist en volledig heeft plaatsgevonden, vult het model zich automatisch voor 20 perioden en blijven alle hieronder gelegen rijen leeg.

Om de werking van het vulmodel te achterhalen bekijken we de vulling per kolom.

Om te beginnen kijken we in kolom A naar de opbouw van cel A10, waarin het nummer voor de eerste periode (“1”) is weergegeven.

=ALS(EN($C$30;$C$40;$C$50;$C$60);1;””)

Ook hier zien we de volledigheidstoets opgenomen in het voorste deel. Om de nummeraanduiding voor de eerste periode te laten verschijnen moeten alle benodigde parameters zijn ingevuld (0). Zolang dit niet het geval is wordt de uitkomst onderdrukt, zoals opgenomen in het laatste deel (“ “).
__________________________________________________________________________________
Volg de succesvolle cursus Excel 2010 voor financieel managers
Wilt u voorkomen dat gegevens worden overgetypt, administraties onduidelijk blijven, analyses en rapportages niet worden geautomatiseerd en spreadsheets fouten bevatten? Volg dan de succesvolle cursus Excel 2010 voor financieel managers. Lees verder
__________________________________________________________________________________

Alle ondergelegen cellen in kolom A werken identiek, met dien verstande dat steeds naar de bovengelegen cel wordt verwezen.
Als voorbeeld nemen we cel A11 voor de tweede periode (“2”)

=ALS(EN($C$40;$C$50;$C$60;$C$70);A10+1;””)

In kolom B, met daarin de betaaldata, wordt in het linker deel (=ALS(A10″”) eerst gekeken naar de aanwezigheid van een nummer in kolom A

=ALS(A10″”;DATUM(JAAR($C$7);MAAND($C$7)+(A10)*12/$C$6;DAG($C$7));””)

Als in kolom A een nummer is opgenomen treedt het tweede deel, dat werkt m.b.v. de Excel functie DATUM(jaar;maand;dag), in werking. Deze functie kijkt naar de startdatum in cel C7 en telt hier – bij de maandbepaling – de factor “+(A10)*12/$C$6” bij op.

Ontbreekt in kolom A een nummer , dan wordt de uitkomst in kolom B onderdrukt (“ “).

In kolom C staan (opnieuw onder de voorwaarde dat in kolom A een nummer is opgenomen) de beginsaldi die verwijzen naar de eindsaldi van de voorgaande periode uit kolom G.
Voor de tweede periode is dit als voorbeeld:

C11 =ALS(A11″”;G10;””)

In kolom D staat – onder verwijzing naar de cel G3 (de annuïteit) en G4 (het aantal termijnen): – de vaste periodieke annuïteit opgenomen

D10 =ALS(A10<=$G$4;$G$3;"")

Ook kolom D wordt de uitkomst onderdrukt (“ “) als het corresponderende nummer in kolom A ontbreekt. In de kolommen E en F bepalen we achtereenvolgens het aflossings- en het rentedeel van de annuïteit.

De aflossing over de eerste termijn wordt daarbij als volgt berekend:

E10 =ALS(A10″”;D10-F10;””)

En de rente als:

F10 = ALS(A10″”;C10*($C$4/$C$6);””)

Ook in de kolom E en F wordt de uitkomst onderdrukt (“ “) als het nummer in kolom A ontbreekt. Bij de bepaling van het eindsaldo in kolom G wordt als voorwaarde ingebracht dat de periodieke annuïteit altijd kleiner dient te zijn dan het beginsaldo (D10<C10).

G10 =ALS(EN(A10″”;D10<C10);C10-E10;ALS(A10″”;0;””)).

In kolom H bepalen we tot slot de cumulatief berekende rente, welke gelijk is aan de som van de naastgelegen plus alle bovenliggende cellen in kolom F.

Met het bereiken van het nulsaldo aan het eind van periode 20 (G29) is het de bedoeling dat – overeenkomstig voorwaarde “d”- alle ondergelegen rijen onderdrukt worden.

Dit bereiken we door binnen “Voorwaardelijke opmaak” (Start -> Voorwaardelijk Opmaak -> Regels beheren) de opmaakregels als volgt te definiëren:

We hebben binnen de velden “van toepassing op” ($A$10:$H$370) onder verwijzing naar cel $F10 gesteld dat – als er geen sprake meer is van rente ($F10 = 0) – dat zowel de achtergrond als het lettertype “wit” dienen te zijn. (als immers geen sprake meer is van rente, dan mogen we aannemen dat het eindpunt bereikt 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.
__________________________________________________________________________________

Bij de verwijzing naar $F10 gaat voorwaardelijke opmaak steeds uit van de bovenste cel in het voorwaardelijke bereik. Hiermee hebben we aan alle spelregels voldaan: het amortisatieschema loopt naar behoren leeg en alle rijen vanaf periode 20 worden onderdrukt.

Ondersteunende Excel functies bij amortisatieschema’s
Het annuïteiten amortisatieschema werkt voor de berekening van de periodieke annuïteiten op basis van de Excel functie BET.

In cel G3 hebben we deze als volgt berekend:

Daarnaast is het mogelijk om – als aanvulling of los van een amortisatieschema – het rente en aflossingsdeel per termijn te berekenen met behulp van de functies IBET respectievelijk PBET.
Hetzelfde is – maar dan voor meerdere aaneengesloten termijnen tegelijk – mogelijk met de functies CUM.RENTE en CUM.HOOFDSOM.

Als voorbeeld nemen we voor de functies IBET en PBET de 10e termijn en voor de functies CUM.RENTE en CUM.HOOFDSOM de aaneengesloten reeks van de eerste 10 termijnen.

De functie IBET
De functie IBET ziet er als volgt uit:

IBET(rente;termijn;aantal-termijnen;hw;tw;type_getal)
Als we de argumenten hard invullen krijgen we:

=IBET(5%/4;10;20;125.000;0;0) = € 907,16

Deze uitkomst correspondeert exact met de waarde van cel F19 uit het amortisatieschema.

De functie PBET
De functie PBET ziet er als volgt uit:

PBET(rente;termijn;aantal-termijnen;hw;tw;type_getal)

Als we de argumenten hard invullen krijgen we:

=PBET(5%/4;10;20;125.000;0;0) = € 6.195,40

Deze uitkomst correspondeert exact met de waarde van cel E19 uit het amortisatieschema.

De functie CUM.RENTE
De functie CUM.RENTE ziet er als volgt uit:

CUM.RENTE(rente;aantal_termijnen;hw;begin_periode;einde_periode;type_getal)

Als we de argumenten hard invullen krijgen we:
=CUM.RENTE(5%/4;20;125.000;1;10;0) = € 12.402,54

Deze uitkomst correspondeert met de som van de cellen (F10:F19) uit het amortisatieschema.

De functie CUM.HOOFDSOM
De functie CUM.HOOFDSOM ziet er als volgt uit:

CUM.HOOFDSOM(rente;aantal_termijnen;hw;begin_periode;einde_periode;type_getal)

Als we de argumenten hard invullen krijgen we:

=CUM.HOOFDSOM(5%/4;20;125.000;1;10;0) = € 58.622,95

Deze uitkomst correspondeert tot slot ook weer exact met de som van de cellen (E10:E19) uit het amortisatieschema.

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.