KutoolsforOffice — Eén oplossing, vijf krachtige tools.Meer bereiken met minder moeite.

Maak een aflossingsplan voor een lening in Excel – Een stapsgewijze handleiding

AuteurXiaoyang Wijzigingsdatum

Het maken van een leningsaflossingsschema in Excel kan een waardevolle vaardigheid zijn, waarmee u uw leningsaflossingen effectief kunt visualiseren en beheren. Een aflossingsschema is een tabel die elke periodieke betaling van een aflossende lening (meestal een hypotheek of autolening) gedetailleerd weergeeft. Het splitst elke betaling op in rente- en aflossingscomponenten en toont het resterende saldo na elke betaling. Laten we stap voor stap bekijken hoe u zo’n schema in Excel maakt.

Maak een aflossingsplan voor een lening

Wat is een aflossingsschema?

Maak een aflossingsschema in Excel

Maak een aflossingsschema voor een variabel aantal perioden

Maak een aflossingsschema met extra betalingen

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

Download voorbeeldbestand

Maak een aflossingsschema in Excel

Wat is een aflossingsschema?

Een aflossingsschema is een gedetailleerde tabel die wordt gebruikt bij leningsberekeningen en laat zien hoe een lening in de loop van de tijd wordt afgelost. Aflossingsschema’s worden veel gebruikt voor leningen met een vaste rente, zoals hypotheken, autoleningen en persoonlijke leningen, waarbij het maandbedrag gedurende de gehele looptijd gelijk blijft, maar het aandeel dat naar rente dan wel aflossing gaat, geleidelijk verandert.

Om een leningsaflossingsschema in Excel te maken, zijn de ingebouwde functies PMT, PPMT en IPMT essentieel. Laten we bekijken wat elk van deze functies precies doet:

  • PMT-functie: Gebruik deze functie om de vaste periodieke betaling voor een lening te berekenen, op basis van constante betalingen en een vast rentepercentage.
  • IPMT-functie: Deze functie berekent het rentedeel van een betaling in een specifieke periode.
  • PPMT-functie: Gebruik deze functie om het aflossingsdeel van een betaling voor een specifieke periode te berekenen.

Met deze functies in Excel kunt u een gedetailleerd aflossingsschema opstellen dat voor elke betaling zowel de rente- als de aflossingscomponent toont, plus het resterende leningsaldo na die betaling.

Maak een aflossingsschema in Excel

In deze sectie presenteren we twee verschillende methoden om in Excel een aflossingsschema te maken. Ze zijn afgestemd op uiteenlopende gebruikersvoorkeuren en vaardigheidsniveaus, zodat iedereen – ongeacht zijn of haar Excel-kennis – met succes een gedetailleerd en nauwkeurig aflossingsschema voor zijn of haar lening kan opstellen.

Formules bieden dieper inzicht in de onderliggende berekeningen en de flexibiliteit om het schema naar specifieke wensen aan te passen. Deze aanpak is perfect voor wie hands-on ervaring op wil doen en helder inzicht wil krijgen in hoe elke betaling wordt verdeeld over rente en aflossing. Laten we nu stap voor stap het proces doornemen om een aflossingsschema in Excel op te zetten:

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

  1. Voer de relevante leningsgegevens in, zoals het jaarlijkse rentepercentage, de looptijd van de lening in jaren, het aantal betalingen per jaar en het geleende bedrag, in de cellen zoals op de volgende schermafbeelding wordt weergegeven:
    Voer de relevante leningsgegevens in
  2. Maak vervolgens in Excel een aflossingstabel met de opgegeven labels—Periode, Betaling, Rente, Aflossing en Resterend saldo—in de cellen A7 t/m E7.
  3. Voer in de kolom **Periode** de periodenummers in. In dit voorbeeld bedraagt het totale aantal betalingen 24 maanden (2 jaar), dus vult u de getallen 1 tot en met 24 in bij **Periode**. Zie schermafbeelding:
    voer de periodenummers in
  4. Nadat u de tabel hebt ingesteld met labels en periodenummers, voert u formules en waarden in voor de kolommen Betaling, Rente, Aflossing en Saldo op basis van de specifieke kenmerken van uw lening.

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

De syntaxis van PMT is:

=-BET()rentepercentage per periode,totaal aantal termijnen, leensom)
  • rente per periode: Als uw leningsrente jaarlijks is, deelt u deze door het aantal betalingen per jaar. Bijvoorbeeld: bij een jaarlijkse rente van 5 % en maandelijkse betalingen bedraagt de rente per periode 5 % / 12. In dit voorbeeld wordt de rente weergegeven als B1/B3.
  • totaal aantal betalingen: Vermenigvuldig de looptijd van de lening in jaren met het aantal betalingen per jaar. In dit voorbeeld wordt dat weergegeven als B2*B3.
  • Leenbedrag: Dit is het hoofdbedrag dat u hebt geleend. In dit voorbeeld is dat B4.
  • minteken (-): De PMT-functie retourneert een negatief getal, omdat het een uitgaande betaling vertegenwoordigt. Plaats een minteken voor de PMT-functie om de betaling als positief getal weer te geven.

Voer de volgende formule in cel B7 in en sleep vervolgens de vulgreep naar beneden om de formule naar andere cellen te kopiëren. U krijgt dan een constant betalingsbedrag voor alle perioden. Zie schermafbeelding:

= -PMT($B$1/$B$3, $B$2*$B$3, $B$4)
Opmerking: Gebruik hier absolute verwijzingenin de formule, zodat deze ongewijzigd blijft wanneer u deze naar de cellen eronder kopieert.

Bereken het totale betaalde bedrag met behulp van de PMT-functie

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

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

=-IBET()rentepercentage per periode,specifieke periode,totaal aantal termijnen, leensom)
  • rente per periode: Als uw leningsrente jaarlijks is, deelt u deze door het aantal betalingen per jaar. Bijvoorbeeld: bij een jaarlijkse rente van 5 % en maandelijkse betalingen bedraagt de rente per periode 5 % / 12. In dit voorbeeld wordt de rente weergegeven als B1/B3.
  • specifieke periode: De specifieke periode waarvoor u de rente wilt berekenen. Meestal begint deze met 1 in de eerste rij van uw schema en loopt per rij met 1 op. In dit voorbeeld start de periode in cel A7.
  • totaal aantal betalingen: Vermenigvuldig de looptijd van de lening in jaren met het aantal betalingen per jaar. In dit voorbeeld wordt dat weergegeven als B2*B3.
  • Leenbedrag: Dit is het hoofdbedrag dat u hebt geleend. In dit voorbeeld is dat B4.
  • minteken (-): De PMT-functie geeft een negatief getal terug, omdat het een uitgaande betaling vertegenwoordigt. Plaats een minteken voor de PMT-functie om de betaling als positief getal weer te geven.

Voer de volgende formule in cel C7 in en sleep vervolgens de vulgreep naar beneden om de rente voor elke periode te berekenen.

=-IPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4)
Opmerking: In de bovenstaande formule is A7 ingesteld als een relatieve verwijzing, zodat deze dynamisch aanpast aan de specifieke rij waarnaar de formule wordt uitgebreid.

Bereken de rente met behulp van de IPMT-functie

⭐️ Stap 4: Bereken de aflossing met de PPMT-functie

Nadat u de rente voor elke periode hebt berekend, is de volgende stap bij het opstellen van een aflossingsschema het bepalen van het aflossingsdeel van elke betaling. Hiervoor gebruikt u de PPMT-functie, die speciaal is ontworpen om het aflossingsdeel van een betaling voor een bepaalde periode te berekenen op basis van vaste termijnen en een constant rentepercentage.

De syntaxis van IPMT is:

=-PBET()rentepercentage per periode,specifieke periode,totaal aantal termijnen, leensom)

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

Voer de volgende formule in cel D7 in en sleep vervolgens de vulgreep naar beneden om de aflossing voor elke periode automatisch in te vullen. Zie schermafbeelding:

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

Bereken het aflossingsbedrag met behulp van de PPMT-functie

⭐️ Stap 5: Bereken het resterende saldo

Nadat u zowel de rente als de aflossing van elke betaling hebt berekend, is de volgende stap in uw aflossingsschema het bepalen van het resterende leningsaldo na elke betaling. Dit is een cruciaal onderdeel van het schema, omdat het duidelijk laat zien hoe uw 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 gelijk is aan het oorspronkelijke leningbedrag minus het aflossingsdeel van de eerste betaling:
    =B4-D7
    Bereken het resterende saldo
  2. Bereken voor de tweede en alle daaropvolgende perioden het resterende saldo door de hoofdsomaflossing van de huidige periode af te trekken van het saldo uit de vorige periode. Pas hiervoor de volgende formule toe in cel E8:
    =E7-D8
    Opmerking: De verwijzing naar de saldocel moet relatief zijn, zodat deze automatisch wordt bijgewerkt wanneer u de formule naar beneden sleept.
    Bereken het resterende saldo
  3. Sleep daarna de vulgreep naar beneden in de kolom. Zoals u ziet, past elke cel zich automatisch aan om het resterende saldo te berekenen op basis van de bijgewerkte aflossingen.
    sleep de vulgreep naar beneden in de kolom

⭐️ Stap 6: Maak een leningsoverzicht

Nadat u uw gedetailleerde aflossingsschema hebt ingesteld, kunt u een leningsoverzicht genereren dat in één oogopslag de belangrijkste kenmerken van uw lening toont – waaronder de totale kosten en de totaal betaalde rente.

● Om de totale betalingen te berekenen:

=SUM(B7:B30)

● Om de totale rente te berekenen:

=SUM(C7:C30)

Maak een leningsoverzicht

⭐️ Resultaat:

Er is nu een eenvoudig en toch uitgebreid leningsaflossingsschema succesvol opgesteld. Zie de schermafbeelding:

er is een eenvoudig aflossingsplan voor een lening gemaakt

een schermafbeelding van kutools for excel ai

Ontgrendel de magie van Excel met KUTOOLS AI

  • Slimme uitvoering: Voer celbewerkingen uit, analyseer gegevens en maak grafieken – allemaal met eenvoudige opdrachten.
  • aangepaste formules: Genereer op maat gemaakte formules om uw workflows te stroomlijnen.
  • VBA-programmeren: Schrijf en implementeer VBA-code moeiteloos.
  • Formule-uitleg: Begrijp complexe formules moeiteloos.
  • Tekstvertaling: Doorbreek taalbarrières in uw spreadsheets.
Breid uw Excel-mogelijkheden uit met AI-gestuurde tools.Download nuen ervaar efficiëntie zoals nooit tevoren!

Maak een aflossingsschema voor een variabel aantal perioden

In het vorige voorbeeld stelden we een aflossingsschema op voor een vast aantal betalingen — een aanpak die perfect geschikt is voor een specifieke lening of hypotheek met ongewijzigde voorwaarden.

Maar als u een flexibel aflossingsplan wilt maken dat herhaaldelijk kan worden gebruikt voor leningen met verschillende looptijden, zodat u het aantal termijnen naar behoefte kunt aanpassen voor verschillende leenscenario’s, dan moet u een gedetailleerdere methode volgen.

⭐️ Stap 1: Stel de leengegevens en het aflossingsplan in

  1. Voer de relevante leningsgegevens in, zoals het jaarlijkse rentepercentage, de looptijd van de lening in jaren, het aantal betalingen per jaar en het geleende bedrag, in de cellen zoals op de volgende schermafbeelding wordt weergegeven:
    Voer de relevante leningsgegevens in
  2. Maak vervolgens in Excel een aflossingstabel met de opgegeven labels — Periode, Betaling, Rente, Aflossing en Resterend saldo — in de cellen A7 t/m E7.
  3. Voer in de kolom Periode het hoogste aantal betalingen in dat u voor een lening zou overwegen, bijvoorbeeld een reeks van 1 tot 360. Zo dekt u zelfs een standaard hypotheek van 30 jaar bij maandelijkse betalingen.
    voer het hoogste aantal betalingen in

⭐️ Stap 2: Pas de formules voor termijn, rente en aflossing aan met de ALS-functie

Voer de volgende formules in de bijbehorende cellen in en sleep daarna de vulgreep omlaag tot het maximale aantal termijnen dat u heeft ingesteld.

● Formule voor de termijn:

Normaal gesproken gebruikt u de BET-functie om de looptijd te berekenen. Om een ALS-instructie op te nemen, ziet de formulesyntaxis er als volgt uit:

=ALS()huidige periode<=totaal aantal perioden, -BET()rentepercentage per periode,totaal aantal perioden, leensom), „" )

De formule wordt dan als volgt:

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

● Renteformule:

De syntaxis van de formule is:

=ALS()huidige periode<=totaal aantal perioden, -IBET()rentepercentage per periode,huidige periode,totaal aantal perioden, leensom), „" )

De formule wordt dan als volgt:

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

● Aflossingsformule:

De syntaxis van de formule is:

=ALS()huidige periode<=totaal aantal perioden, -PBET()rentepercentage per periode,huidige periode,totaal aantal perioden, leensom), „" )

De formule wordt dan als volgt:

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

Pas de formules voor betaling, rente en aflossing aan met de ALS-functie

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

Voor het resterende saldo trekt u normaal gesproken de aflossing af van het vorige saldo. Met een ALS-instructie past u dit als volgt aan:

● De eerste saldocel: (E7)

=B4-D7

● De tweede saldocel: (E8)

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

Pas het resterende saldo aan

⭐️ Stap 4: Maak een leensamenvatting

Nadat u het aflossingsplan hebt ingesteld met de aangepaste formules, maakt u vervolgens een leensamenvatting.

● Om de totale betalingen te berekenen:

=SUM(B7:B366)

● Om de totale rente te berekenen:

=SUM(C7:C366)

Maak een leningsoverzicht

⭐️ Resultaat:

U beschikt nu over een uitgebreid en dynamisch aflossingsplan in Excel, inclusief een gedetailleerde leensamenvatting. Elke keer dat u de looptijd van de termijnen aanpast, wordt het volledige aflossingsplan automatisch bijgewerkt om deze wijzigingen direct weer te geven. Bekijk de demo hieronder:

Maak een aflossingsschema met extra betalingen

Door extra betalingen bovenop de geplande termijnen te doen, lost u uw lening sneller af. Een aflossingsplan dat rekening houdt met extra betalingen, laat precies zien hoe deze de looptijd verkorten én de totale rentekosten verlagen. Zo stelt u dit in:

⭐️ Stap 1: Stel de leengegevens en het aflossingsplan in

  1. Voer de relevante leningsgegevens in, zoals het jaarlijkse rentepercentage, de looptijd van de lening in jaren, het aantal betalingen per jaar, het geleende bedrag en extra betaling in de cellen zoals op de volgende schermafbeelding wordt weergegeven:
    Voer de relevante leningsgegevens in
  2. Bereken vervolgens de geplande betaling.
    Naast de invoercellen is er nog één vooraf gedefinieerde cel nodig voor onze verdere berekeningen: het bedrag van de geplande betaling. Dit is het vaste maandbedrag van een lening, uitgaande van geen extra aflossingen. Voer de volgende formule in cel B6 in:
    =IFERROR(-PMT($B$1/$B$3, $B$2*$B$3, $B$4),"")

    bereken de geplande betaling
  3. Maak vervolgens een aflossingstabel in Excel:
    • Stel de opgegeven labels in, zoals Periode, Geplande betaling, Extra betaling, Totaalbedrag, Rente, Aflossing, Resterend saldo in cellen A8:G8;
    • Voer in de kolom Periode het hoogste aantal betalingen in dat u voor een lening zou overwegen – bijvoorbeeld een reeks van 0 tot 360. Zo dekt u gemakkelijk een standaard 30-jarige lening met maandelijkse aflossingen.
    • Voor Periode 0 (rij 9 in ons voorbeeld) haalt u de Saldo-waarde op met deze formule =B4, die overeenkomt met het oorspronkelijke leenbedrag. Alle andere cellen in deze rij blijven leeg.
    • maak een aflossingstabel

⭐️ Stap 2: Maak de formules voor het aflossingsplan met extra betalingen

Voer de volgende formules één voor één in de overeenkomstige cellen in. Voor een betere foutafhandeling plaatsen we deze – en alle toekomstige formules – binnen de ALS.FOUT-functie, zodat u diverse fouten voorkomt die kunnen ontstaan wanneer invoercellen leeg zijn of ongeldige waarden bevatten.

● Bereken de geplande termijn:

Voer de volgende formule in cel B10 in:

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

Bereken de geplande betaling

● Bereken de extra betaling:

Voer de volgende formule in cel C10 in:

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

Bereken de extra betaling

● Bereken de totale betaling:

Voer de volgende formule in cel D10 in:

=IFERROR(B10+C10, "")

Bereken de totale betaling

● Bereken de aflossing:

Voer de volgende formule in cel E10 in:

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

Bereken het aflossingsbedrag

● Bereken de rente:

Voer de volgende formule in cel F10 in:

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

Bereken de rente

● Bereken het resterende saldo

Voer de volgende formule in cel G10 in:

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

Bereken het resterende saldo

Nadat u alle formules hebt ingevoerd, selecteert u het celbereik B10:G10 en sleept u de vulgreep omlaag om de formules uit te breiden over de volledige reeks termijnen. Voor niet-gebruikte perioden tonen de cellen 0. Zie schermafbeelding:
gebruik de vulgreep om deze formules naar beneden te slepen en uit te breiden

⭐️ Stap 3: Maak een leensamenvatting

● Aantal geplande termijnen:

=B2:B3

● Werkelijk aantal termijnen:

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

● Totaal extra betalingen:

=SUM(C10:C369)

● Totale rente:

=SUM(F10:F369)

Maak een leningsoverzicht

⭐️ Resultaat:

Door deze stappen te volgen, creëert u een dynamisch aflossingsplan in Excel dat automatisch rekening houdt met extra betalingen.

Maak een aflossingsplan met een Excel-sjabloon

Het opstellen van een aflossingsplan in Excel met een sjabloon is een eenvoudige en tijdbesparende oplossing. Excel biedt ingebouwde sjablonen die automatisch de rente, de aflossing en het resterende saldo per termijn berekenen. Zo stelt u in een handomdraai een professioneel aflossingsplan op met een Excel-sjabloon:

  1. Klik op Bestand > Nieuw, typ in het zoekvak aflossingsschema en druk op Enter. Selecteer vervolgens de sjabloon die het beste bij uw behoeften past door erop te klikken. Bijvoorbeeld: hier kies ik de sjabloon Eenvoudige leningscalculator. Zie schermafbeelding:
    Maak een aflossingsplan met een sjabloon
  2. Nadat u een sjabloon hebt geselecteerd, klikt u op de knop Maken om deze te openen als een nieuw werkblad.
  3. Voer daarna uw persoonlijke leningsgegevens in – de sjabloon berekent alles automatisch en vult het schema direct in op basis van uw invoer.
  4. De beste hulpmiddelen voor kantoorproductiviteit