Het maken van een aflossingsschema voor leningen in Excel kan een waardevolle vaardigheid zijn, waarmee u de aflossingen van uw leningen effectief kunt visualiseren en beheren. Een aflossingsschema is een tabel met details over elke periodieke betaling op een aflossingslening (meestal een hypotheek of autolening). Elke betaling wordt opgesplitst in rente- en hoofdbestanddelen, waarbij het resterende saldo na elke betaling wordt weergegeven. Laten we een stapsgewijze handleiding bekijken om zo'n schema in Excel te maken.

Wat is een aflossingsschema?

Maak een afschrijvingsschema in Excel

Maak een afschrijvingsschema voor een variabel aantal perioden

Maak een aflossingsschema met extra betalingen

Maak een aflossingsschema (met extra betalingen) met behulp van een Excel-sjabloon

Wat is een aflossingsschema?

Een aflossingsschema is een gedetailleerde tabel die wordt gebruikt bij leningberekeningen en waarin het aflossingsproces van een lening in de loop van de tijd wordt weergegeven. Aflossingsschema's worden vaak gebruikt voor leningen met een vaste rente, zoals hypotheken, autoleningen en persoonlijke leningen, waarbij het betalingsbedrag gedurende de looptijd van de lening constant blijft, maar de verhouding tussen de rente en de hoofdsom in de loop van de tijd verandert.

Om een ​​aflossingsschema voor leningen in Excel te maken, zijn de ingebouwde functies PMT, PPMT en IPMT 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 constant rentepercentage.
• IPMT-functie: deze functie berekent het rentegedeelte van een betaling voor een bepaalde periode.
• PPMT-functie: Deze functie wordt gebruikt om het hoofdgedeelte van een betaling voor een specifieke periode te berekenen.

Door deze functies in Excel te gebruiken, kunt u een gedetailleerd aflossingsschema maken waarin de rente- en hoofdsomcomponenten van elke betaling worden weergegeven, samen met het resterende saldo van de lening na elke betaling.

Maak een afschrijvingsschema in Excel

In deze sectie introduceren we twee verschillende methoden om een ​​aflossingsschema in Excel te maken. Deze methoden komen tegemoet aan verschillende gebruikersvoorkeuren en vaardigheidsniveaus, waardoor iedereen, ongeacht zijn vaardigheid met Excel, met succes een gedetailleerd en nauwkeurig aflossingsschema voor zijn 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 degenen die praktische ervaring willen hebben en een duidelijk inzicht willen hebben in hoe elke betaling is opgesplitst in hoofdsom- en rentecomponenten. Laten we nu het proces van het stap voor stap maken van een aflossingsschema in Excel opsplitsen:

⭐️ Stap 1: Stel de leninginformatie en aflossingstabel in

1. Voer de relatieve leninginformatie in, zoals de jaarlijkse rente, de looptijd van de lening in jaren, het aantal betalingen per jaar en het geleende bedrag in de cellen, zoals in de onderstaande schermafbeelding:
   
2. Maak vervolgens een aflossingstabel in Excel met de opgegeven labels, zoals Periode, Betaling, Rente, Hoofdsom, Resterend saldo in de cellen A7:E7.
3. Voer in de kolom Periode de periodenummers in. Voor dit voorbeeld is het totale aantal betalingen 24 maanden (2 jaar). U vult dus de cijfers 1 tot en met 24 in de kolom Periode in. Zie schermafbeelding:
   
4. Nadat u de tabel met de labels en periodenummers heeft ingesteld, kunt u doorgaan met het invoeren van formules en waarden voor de kolommen Betaling, Rente, Hoofdsom en Saldo, op basis van de specificaties van uw 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 vullen. U ziet dan een constant betalingsbedrag voor alle perioden. Zie schermafbeelding:

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

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

In deze stap berekent u de rente voor elke betalingsperiode met behulp van de IPMT-functie van Excel.

Voer de volgende formule in cel C7 in en sleep vervolgens de vulgreep naar beneden in de kolom om deze formule te vullen 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

Na het berekenen van de rente voor elke periode, is de volgende stap bij het opstellen van een aflossingsschema het berekenen van het hoofdgedeelte van elke betaling. Dit gebeurt met behulp van de PPMT-functie, die is ontworpen om de hoofdsom van een betaling voor een specifieke periode te bepalen, op basis van constante betalingen en een constant rentepercentage.

De syntaxis van de IPMT is:

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

Voer de volgende formule in cel D7 in en sleep vervolgens de vulgreep naar beneden in 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 u zowel de rente als de hoofdsom van elke betaling hebt berekend, is de volgende stap in uw aflossingsschema het berekenen van het resterende saldo van de lening na elke betaling. Dit is een cruciaal onderdeel van het schema, omdat het laat zien hoe het leningsaldo in de loop van de tijd afneemt.

1. Voer in de eerste cel van uw saldokolom – E7, de volgende formule in, wat betekent dat het resterende saldo het oorspronkelijke geleende bedrag minus het hoofdgedeelte van de eerste betaling is:

   =B4-D7

   
2. Voor de tweede en alle daaropvolgende perioden berekent u 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

    Note: De verwijzing naar de saldocel moet relatief zijn, zodat deze wordt bijgewerkt naarmate u de formule naar beneden sleept.
   
3. En sleep vervolgens de vulgreep naar de kolom. Zoals u kunt zien, wordt elke cel automatisch aangepast om het resterende saldo te berekenen op basis van de bijgewerkte hoofdbetalingen.
   

⭐️Stap 6: Maak een leningsoverzicht

Nadat u uw gedetailleerde aflossingsschema hebt opgesteld, kunt u door het maken van een leningoverzicht een snel overzicht krijgen van de belangrijkste aspecten van uw lening. Dit overzicht omvat doorgaans de totale kosten van de lening en 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 er met succes een eenvoudig maar uitgebreid aflossingsschema voor leningen opgesteld. zie schermafdruk:

Maak een afschrijvingsschema voor een variabel aantal perioden

In het vorige voorbeeld maken we een aflossingsschema voor een lening 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 u een flexibel aflossingsschema wilt opstellen dat herhaaldelijk kan worden gebruikt voor leningen met verschillende looptijden, zodat u het aantal betalingen kunt aanpassen zoals vereist voor verschillende leningscenario's, moet u een meer gedetailleerde methode volgen.

⭐️ Stap 1: Stel de leninginformatie en aflossingstabel in

1. Voer de relatieve leninginformatie in, zoals de jaarlijkse rente, de looptijd van de lening in jaren, het aantal betalingen per jaar en het geleende bedrag in de cellen, zoals in de onderstaande schermafbeelding:
   
2. Maak vervolgens een aflossingstabel in Excel met de opgegeven labels, zoals Periode, Betaling, Rente, Hoofdsom, Resterend saldo in de cellen A7:E7.
3. Voer in de kolom Periode het hoogste aantal betalingen in dat u voor een lening in overweging kunt nemen. Vul bijvoorbeeld de getallen in van 1 tot 360. Dit kan een standaardlening met een looptijd van 30 jaar dekken als u maandelijkse betalingen doet.
   

⭐️ Stap 2: Wijzig de betalings-, rente- en hoofdformules met de IF-functie

Voer de volgende formules in de overeenkomstige cellen in en sleep vervolgens de vulgreep om deze formules uit te breiden tot het maximale aantal betalingstermijnen dat u heeft ingesteld.

● Betalingsformule:

Normaal gesproken gebruikt u de PMT-functie om de betaling te berekenen. Om een ​​IF-instructie op te nemen, is de syntaxisformule:

De formules zijn dus deze:

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

● Renteformule:

De syntaxisformule is:

De formules zijn dus deze:

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

● Principeformule:

De syntaxisformule is:

De formules zijn dus deze:

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

⭐️ Stap 3: Pas de formule voor het resterende saldo aan

Voor het resterende saldo kunt u doorgaans de hoofdsom aftrekken van het vorige saldo. Met een IF-instructie wijzigt u deze als:

● De eerste balanscel:(E7)

=B4-D7

● De tweede balanscel:(E8)

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

⭐️Stap 4: Maak een leningsoverzicht

Nadat u het aflossingsschema met de aangepaste formules hebt opgesteld, 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 beschikt u over een uitgebreid en dynamisch aflossingsschema in Excel, compleet met een gedetailleerd kredietoverzicht. Telkens wanneer u de betalingstermijn aanpast, wordt het volledige aflossingsschema automatisch bijgewerkt om deze wijzigingen weer te geven. Zie de demo hieronder:

Maak een aflossingsschema met extra betalingen

Door extra betalingen te doen naast de geplande betalingen, kan een lening sneller worden afbetaald. Een aflossingsschema waarin extra betalingen zijn opgenomen, als het in Excel wordt gemaakt, laat zien hoe deze extra betalingen de uitbetaling van de lening kunnen bespoedigen en de totale betaalde rente kunnen verlagen. Zo kun je het instellen:

⭐️ Stap 1: Stel de leninginformatie en aflossingstabel in

1. Voer de relatieve leninginformatie in, zoals de jaarlijkse rente, de looptijd van de lening in jaren, het aantal betalingen per jaar, het geleende bedrag en de extra betaling in de cellen, zoals in de onderstaande schermafbeelding:
   
2. Bereken vervolgens de geplande betaling.
   Naast de invoercellen is er nog een vooraf gedefinieerde cel nodig voor onze daaropvolgende berekeningen: het geplande betalingsbedrag. Dit is het reguliere betalingsbedrag voor een lening, ervan uitgaande dat er geen aanvullende 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 afschrijvingstabel in Excel:
   • Stel de opgegeven labels in, zoals Periode, Schemabetaling, Extra betaling, Totale betaling, Rente, Hoofdsom, Resterend saldo in cellen A8:G8;
   • Voer in de kolom Periode het hoogste aantal betalingen in dat u voor een lening kunt overwegen. Vul bijvoorbeeld de cijfers van 0 tot 360 in. Dit kan een standaardlening van 30 jaar dekken als u maandelijkse betalingen doet;
   • Haal voor Periode 0 (in ons geval rij 9) de Saldowaarde op met deze formule = B4, wat overeenkomt met het initiële leningbedrag. Alle andere cellen in deze rij moeten leeg worden gelaten.
   • 

⭐️ Stap 2: Creeer de formules voor het aflossingsschema met extra betalingen

Voer de volgende formules één voor één in de overeenkomstige cellen in. Om de foutafhandeling te verbeteren, omsluiten we deze en alle toekomstige formules in de functie ALS. Deze aanpak helpt meerdere potentiële fouten te voorkomen die kunnen optreden als een van de invoercellen leeg blijft of onjuiste 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 u elk van de formules heeft voltooid, selecteert u het celbereik B10:G10 en gebruikt u de vulgreep om deze formules naar beneden te slepen en uit te breiden over de gehele reeks betalingsperioden. Voor perioden die niet worden gebruikt, worden in de cellen nullen weergegeven. Zie schermafbeelding:

⭐️Stap 3: Maak een leningsoverzicht

● Ontvang een gepland aantal betalingen:

=B2:B3

● Ontvang het werkelijke aantal betalingen:

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

● Ontvang totale extra betalingen:

=SUM(C10:C369)

● Krijg totale rente:

=SUM(F10:F369)

⭐️ Resultaat:

Door deze stappen te volgen, maakt u in Excel een dynamisch aflossingsschema waarin rekening wordt gehouden met extra betalingen.

Maak een aflossingsschema met behulp van de Excel-sjabloon

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, de hoofdsom en het resterende saldo van elke betaling berekenen. U kunt als volgt een aflossingsschema maken met behulp van een Excel-sjabloon:

1. Klik Dien in > NewTyp in het zoekvak afschrijvingsschemaen druk op Enter sleutel. Selecteer vervolgens de sjabloon die het beste bij uw behoeften past door erop te klikken. Hier selecteer ik bijvoorbeeld de sjabloon Eenvoudige leningcalculator. Zie schermafbeelding:
   
2. Nadat u een sjabloon heeft geselecteerd, klikt u op de creëren om deze als een nieuwe werkmap te openen.
3. Voer vervolgens uw eigen leninggegevens in. De sjabloon zou het schema automatisch moeten berekenen en invullen op basis van uw invoer.
4. Sla ten slotte uw nieuwe werkmap voor het aflossingsschema op.