Maak een leningaflossingsschema in Excel – Een stap-voor-stap handleiding

Het maken van een aflossingsschema voor een lening in Excel kan een waardevolle vaardigheid zijn, waarmee je je leningaflossingen effectief kunt visualiseren en beheren. Een aflossingsschema is een tabel die elke periodieke betaling op een aflossingslening (meestal een hypotheek of autolening) gedetailleerd weergeeft. Het verdeelt elke betaling in rente- en hoofdsomcomponenten en toont het resterende saldo na elke betaling. Laten we in een stap-voor-stap handleiding zien hoe je zo'n schema in Excel kunt maken.

Wat is een aflossingsschema?

Maak een aflossingsschema in Excel

Maak een aflossingsschema voor een variabel aantal periodes

Maak een aflossingsschema met extra betalingen

Maak een aflossingsschema (met extra betalingen) door een Excel-sjabloon te gebruiken

Wat is een aflossingsschema?

Een aflossingsschema is een gedetailleerde tabel die wordt gebruikt bij leningberekeningen en het proces laat zien van het aflossen van een lening in de loop van de tijd. Aflossingsschema's worden vaak gebruikt voor vaste renteleningen zoals hypotheken, autoleningen en persoonlijke leningen, waarbij het bedrag van de betaling constant blijft gedurende de looptijd van de lening, maar de verhouding tussen rente en hoofdsom in de loop van de tijd verandert.

Om een leningaflossingsschema in Excel te maken, zijn de ingebouwde functies PMT, PPMT en IPMT inderdaad cruciaal. Laten we begrijpen wat elke functie doet:

• PMT Functie: Deze functie wordt gebruikt om de totale betaling per periode voor een lening te berekenen op basis van constante betalingen en een constante rentevoet.
• IPMT Functie: Deze functie berekent het rentegedeelte van een betaling voor een bepaalde periode.
• PPMT Functie: Deze functie wordt gebruikt om het hoofdsomgedeelte van een betaling voor een specifieke periode te berekenen.

Door deze functies in Excel te gebruiken, kun je een gedetailleerd aflossingsschema maken dat de rente- en hoofdsomcomponenten van elke betaling toont, samen met het resterende saldo van de lening na elke betaling.

Maak een aflossingsschema in Excel

In deze sectie introduceren we twee verschillende methoden om een aflossingsschema in Excel te maken. Deze methoden zijn gericht op verschillende gebruikersvoorkeuren en vaardigheidsniveaus, zodat iedereen, ongeacht hun bekendheid met Excel, succesvol een gedetailleerd en nauwkeurig aflossingsschema voor hun lening kan opstellen.

Formules bieden een dieper inzicht in de onderliggende berekeningen en bieden flexibiliteit om het schema aan te passen aan specifieke vereisten. Deze aanpak is ideaal voor wie een praktische ervaring wil opdoen en een duidelijk inzicht wil krijgen in hoe elke betaling wordt opgesplitst in hoofdsom- en rentecomponenten. Laten we nu stap voor stap het proces doorlopen om een aflossingsschema in Excel te maken:

⭐️ Stap 1: Stel de leningsinformatie en het aflossingsschema in

1. Voer de relevante leningsinformatie in, zoals jaarlijkse rentevoet, looptijd in jaren, aantal betalingen per jaar en leningsbedrag in de cellen zoals in de volgende schermafbeelding te zien is:
   
2. Maak vervolgens een aflossingstabel in Excel met de gespecificeerde labels, zoals Periode, Betaling, Rente, Hoofdsom, Resterend Saldo in cellen A7:E7.
3. Voer in de kolom Periode de periodenummers in. Voor dit voorbeeld is het totale aantal betalingen 24 maanden (2 jaar), dus voer je de nummers 1 tot en met 24 in de kolom Periode in. Zie schermafbeelding:
   
4. Zodra je de tabel hebt ingesteld met de labels en periodenummers, kun je verder gaan met het invoeren van formules en waarden voor de kolommen Betaling, Rente, Hoofdsom en Saldo op basis van de specificaties van je lening.

⭐️ Stap 2: Bereken het totale betalingsbedrag met behulp van de PMT-functie

De syntaxis van de PMT is:

Voer de volgende formule in cel B7 in, en sleep vervolgens de vulgreep naar beneden om deze formule naar andere cellen te kopiëren, en je zult een constant betalingsbedrag voor alle periodes zien. Zie schermafbeelding:

= -PMT($B$1/$B$3, $B$2*$B$3, $B$4)

⭐️ Stap 3: Bereken de rente met behulp van de IPMT-functie

In deze stap bereken je de rente voor elke betalingsperiode met behulp van de IPMT-functie in Excel.

Voer de volgende formule in cel C7 in, en sleep vervolgens de vulgreep omlaag langs de kolom om deze formule te kopiëren en de rente voor elke periode te verkrijgen.

=-IPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4)

⭐️ Stap 4: Bereken de hoofdsom met behulp van de PPMT-functie

Nadat je de rente voor elke periode hebt berekend, is de volgende stap bij het maken van een aflossingsschema om het hoofdsomgedeelte van elke betaling te berekenen. Dit wordt gedaan met behulp van de PPMT-functie, die is ontworpen om het hoofdsomgedeelte van een betaling voor een specifieke periode te bepalen, gebaseerd op constante betalingen en een constante rentevoet.

De syntaxis van de IPMT is:

De syntaxis en parameters voor de PPMT-formule zijn identiek aan die welke in de eerder besproken IPMT-formule werden gebruikt.

Voer de volgende formule in cel D7 in, en sleep vervolgens de vulgreep omlaag langs de kolom om de hoofdsom voor elke periode in te vullen. Zie schermafbeelding:

=-PPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4)

⭐️ Stap 5: Bereken het resterende saldo

Nadat je zowel de rente als de hoofdsom van elke betaling hebt berekend, is de volgende stap in je aflossingsschema om het resterende saldo van de lening na elke betaling te berekenen. Dit is een cruciaal onderdeel van het schema, omdat het laat zien hoe het leningensaldo in de loop van de tijd afneemt.

1. Voer in de eerste cel van je saldokolom – E7, de volgende formule in, wat betekent dat het resterende saldo het originele leningsbedrag min het hoofdsomgedeelte van de eerste betaling zal zijn:

   =B4-D7

   
2. Voor de tweede en alle volgende periodes bereken je het resterende saldo door de hoofdsombetaling van de huidige periode af te trekken van het saldo van de vorige periode. Pas de volgende formule toe in cel E8:

   =E7-D8

    Opmerking: De verwijzing naar de saldocel moet relatief zijn, zodat deze wordt bijgewerkt wanneer je de formule naar beneden sleept.
   
3. Sleep vervolgens de vulgreep omlaag langs de kolom. Zoals je kunt zien, zal elke cel automatisch aanpassen om het resterende saldo te berekenen op basis van de bijgewerkte hoofdsombetalingen.
   

⭐️ Stap 6: Maak een leningoverzicht

Nadat je je gedetailleerde aflossingsschema hebt ingesteld, kan het maken van een leningoverzicht een snelle samenvatting bieden van de belangrijkste aspecten van je lening. Dit overzicht omvat meestal de totale kosten van de lening, de totale betaalde rente.

● Om de totale betalingen te berekenen:

=SUM(B7:B30)

● Om de totale rente te berekenen:

=SUM(C7:C30)

⭐️ Resultaat:

Nu is een eenvoudig maar uitgebreid leningaflossingsschema succesvol aangemaakt. Zie schermafbeelding:

Ontdek de Magie van Excel met Kutools AI

• Slimme Uitvoering: Voer celbewerkingen uit, analyseer gegevens en maak diagrammen – allemaal aangestuurd door eenvoudige commando's.
• Aangepaste Formules: Genereer op maat gemaakte formules om uw workflows te versnellen.
• VBA-codering: Schrijf en implementeer VBA-code moeiteloos.
• Formule-uitleg: Begrijp complexe formules gemakkelijk.
• Tekstvertaling: Overbrug taalbarrières binnen uw spreadsheets.

Verhoog uw Excel-mogelijkheden met AI-aangedreven tools. Download Nu en ervaar een ongekende efficiëntie!

Maak een aflossingsschema voor een variabel aantal periodes

In het vorige voorbeeld hebben we een leningaflossingsschema gemaakt voor een vast aantal betalingen. Deze aanpak is perfect voor het afhandelen van een specifieke lening of hypotheek waarbij de voorwaarden niet veranderen.

Maar als je een flexibel aflossingsschema wilt maken dat herhaaldelijk kan worden gebruikt voor leningen met variabele periodes, waarmee je het aantal betalingen naar behoefte kunt wijzigen voor verschillende leningsscenario's, dan moet je een meer gedetailleerde methode volgen.

⭐️ Stap 1: Stel de leningsinformatie en het aflossingsschema in

1. Voer de relevante leningsinformatie in, zoals jaarlijkse rentevoet, looptijd in jaren, aantal betalingen per jaar en leningsbedrag in de cellen zoals in de volgende schermafbeelding te zien is:
   
2. Maak vervolgens een aflossingstabel in Excel met de gespecificeerde labels, zoals Periode, Betaling, Rente, Hoofdsom, Resterend Saldo in cellen A7:E7.
3. Voer in de kolom Periode het hoogste aantal betalingen in dat je mogelijk in overweging neemt voor elke lening, bijvoorbeeld vul de nummers van 1 tot 360 in. Dit kan een standaard 30-jarige lening dekken als je maandelijkse betalingen doet.
   

⭐️ Stap 2: Wijzig de betalings-, rente- en hoofdsomformules met de ALS-functie

Voer de volgende formules in de bijbehorende cellen in, en sleep vervolgens de vulgreep om deze formules naar beneden uit te breiden tot het maximum aantal betalingsperiodes dat je hebt ingesteld.

● Betalingsformule:

Normaal gesproken gebruik je de PMT-functie om de betaling te berekenen. Om een ALS-instructie te integreren, is de syntaxisformule:

Dus de formules zijn als volgt:

=IF(A7<=$B$2*$B$3, -PMT($B$1/$B$3, $B$2*$B$3, $B$4), "")

● Renteformule:

De syntaxisformule is:

Dus de formules zijn als volgt:

=IF(A7<=$B$2*$B$3,-IPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4), "")

● Hoofdsomformule:

De syntaxisformule is:

Dus de formules zijn als volgt:

=IF(A7<=$B$2*$B$3,-PPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4), "")

⭐️ Stap 3: Pas de resterende saldoformule aan

Voor het resterende saldo trek je meestal de hoofdsom af van het vorige saldo. Met een ALS-instructie pas je het als volgt aan:

● De eerste saldocel:(E7)

=B4-D7

● De tweede saldocel:(E8)

=IF(A8<=$B$2*$B$3, E7-D8, "") 

⭐️ Stap 4: Maak een leningoverzicht

Nadat je het aflossingsschema hebt ingesteld met de gewijzigde formules, is de volgende stap het maken van een leningoverzicht.

● Om de totale betalingen te berekenen:

=SUM(B7:B366)

● Om de totale rente te berekenen:

=SUM(C7:C366)

⭐️ Resultaat:

Nu heb je een uitgebreid en dynamisch aflossingsschema in Excel, compleet met een gedetailleerd leningoverzicht. Telkens wanneer je de termijnperiode aanpast, wordt het hele aflossingsschema automatisch bijgewerkt om deze wijzigingen weer te geven. Zie de demo hieronder:

Maak een aflossingsschema met extra betalingen

Door extra betalingen te doen bovenop de geplande betalingen, kan een lening sneller worden afgelost. Een aflossingsschema dat extra betalingen integreert, laat zien hoe deze extra betalingen de aflossing van de lening versnellen en de totale betaalde rente verminderen. Hier is hoe je het kunt instellen:

⭐️ Stap 1: Stel de leningsinformatie en het aflossingsschema in

1. Voer de relevante leningsinformatie in, zoals jaarlijkse rentevoet, looptijd in jaren, aantal betalingen per jaar, leningsbedrag en extra betaling in de cellen zoals in de volgende schermafbeelding te zien is:
   
2. Bereken vervolgens de geplande betaling.
   Naast de invoercellen is er nog een vooraf gedefinieerde cel nodig voor onze latere berekeningen - het geplande betalingsbedrag. Dit is het reguliere betalingsbedrag op een lening, ervan uitgaande dat er geen extra betalingen worden gedaan. Pas de volgende formule toe in cel B6:

   =IFERROR(-PMT($B$1/$B$3, $B$2*$B$3, $B$4),"")

   
   
3. Maak vervolgens een aflossingstabel in Excel:
   • Stel de gespecificeerde labels in, zoals Periode, Geplande Betaling, Extra Betaling, Totale Betaling, Rente, Hoofdsom, Resterend Saldo in cellen A8:G8;
   • Voer in de kolom Periode het hoogste aantal betalingen in dat je mogelijk in overweging neemt voor elke lening. Vul bijvoorbeeld de nummers van 0 tot 360 in. Dit kan een standaard 30-jarige lening dekken als je maandelijkse betalingen doet;
   • Voor Periode 0 (rij 9 in ons geval), haal de saldo-waarde op met deze formule =B4, wat overeenkomt met het initiële leningsbedrag. Alle andere cellen in deze rij moeten leeg blijven.
   • 

⭐️ Stap 2: Creëer de formules voor het aflossingsschema met extra betalingen

Voer de volgende formules een voor een in de bijbehorende cellen in. Om foutafhandeling te verbeteren, omhullen we deze en alle volgende formules met de ALS.FOUT-functie. Deze aanpak helpt om meerdere potentiële fouten te voorkomen die kunnen optreden als een van de invoercellen leeg blijft of incorrecte waarden bevat.

● Bereken de geplande betaling:

Voer de volgende formule in cel B10 in:

=IFERROR(IF($B$6<=G9, $B$6, G9+G9*$B$1/$B$3), "")

● Bereken de extra betaling:

Voer de volgende formule in cel C10 in:

=IFERROR(IF($B$5<G9-E10,$B$5, G9-E10), "")

● Bereken de totale betaling:

Voer de volgende formule in cel D10 in:

=IFERROR(B10+C10, "")

● Bereken de hoofdsom:

Voer de volgende formule in cel E10 in:

=IFERROR(IF(B10>0, MIN(B10-F10, G9), 0), "")

● Bereken de rente:

Voer de volgende formule in cel F10 in:

=IFERROR(IF(B10>0, $B$1/$B$3*G9, 0), "")

● Bereken het resterende saldo

Voer de volgende formule in cel G10 in:

=IFERROR(IF(G9 >0, G9-E10-C10, 0), "")

Zodra je elk van de formules hebt voltooid, selecteer je het celbereik B10:G10, en gebruik je de vulgreep om deze formules naar beneden te slepen en uit te breiden over de hele set betalingsperiodes. Voor niet-gebruikte periodes zullen de cellen 0'en tonen. Zie schermafbeelding:

⭐️ Stap 3: Maak een leningoverzicht

● Haal het geplande aantal betalingen op:

=B2:B3

● Haal het werkelijke aantal betalingen op:

=COUNTIF(D10:D369,">"&0)

● Haal de totale extra betalingen op:

=SUM(C10:C369)

● Haal de totale rente op:

=SUM(F10:F369)

⭐️ Resultaat:

Door deze stappen te volgen, maak je een dynamisch aflossingsschema in Excel dat rekening houdt met extra betalingen.

Maak een aflossingsschema door een Excel-sjabloon te gebruiken

Het maken van een aflossingsschema in Excel met behulp van een sjabloon is een eenvoudige en tijdbesparende methode. Excel biedt ingebouwde sjablonen die automatisch de rente, hoofdsom en resterende saldo van elke betaling berekenen. Hier is hoe je een aflossingsschema maakt met behulp van een Excel-sjabloon:

1. Klik Bestand > Nieuw, typ in het zoekvak aflossingsschema, en druk op Enter toets. Selecteer vervolgens de sjabloon die het beste bij je behoeften past door erop te klikken. Bijvoorbeeld, hier selecteer ik de eenvoudige leningcalculator-sjabloon. Zie schermafbeelding:
   
2. Zodra je een sjabloon hebt geselecteerd, klik je op de Maak-knop om deze te openen als een nieuw werkblad.
3. Voer vervolgens je eigen leningsdetails in, de sjabloon zou automatisch moeten berekenen en het schema moeten invullen op basis van je invoer.
4. Sla ten slotte je nieuwe aflossingsschemawerkmap op.