de tutorial laat zien hoe je een aflossingsschema in Excel bouwt om periodieke betalingen op een aflossingslening of hypotheek in detail te beschrijven.
een afschrijvingslening is slechts een mooie manier om een lening te definiëren die gedurende de gehele looptijd van de lening in termijnen wordt terugbetaald.
in principe worden alle leningen op de een of andere manier afgeschreven. Bijvoorbeeld, een volledig aflossende lening voor 24 maanden zal 24 gelijke maandelijkse betalingen., Elke betaling is van toepassing op een bepaald bedrag aan hoofdsom en een ander bedrag aan rente. Om elke betaling op een lening detail, kunt u een lening aflossingsschema op te bouwen.
een aflossingsschema is een tabel met periodieke betalingen op een lening of hypotheek in de loop van de tijd, uitgesplitst elke betaling in hoofdsom en rente, en toont het resterende saldo na elke betaling.,
- Hoe maak ik een aflossingsschema voor leningen in Excel
- aflossingsschema voor een variabel aantal perioden
- aflossingsschema voor leningen met extra betalingen
- Excel aflossingsschema
Hoe maak ik een aflossingsschema voor leningen in Excel
om een aflossingsschema voor leningen of hypotheken in Excel op te bouwen, moeten we de volgende functies gebruiken:
- PMT – functie-berekent het totale bedrag van een periodieke betaling. Dit bedrag blijft constant gedurende de gehele looptijd van de lening.,
- ppmt-functie-krijgt het hoofddeel van elke betaling die naar de hoofdsom van de lening gaat, d.w.z. het bedrag dat u geleend hebt. Dit bedrag wordt verhoogd voor latere betalingen.
- IPMT-functie-vindt het rentegedeelte van elke betaling die naar rente gaat. Dit bedrag daalt met elke betaling.
laten we nu stap voor stap het proces doorlopen.,
set up the amortization table
om te beginnen, definieer de invoercellen waar u de bekende componenten van een lening invoert:
- C2 – annual interest rate
- C3 – leentermijn in jaren
- C4 – aantal betalingen per jaar
- C5 – leenbedrag
het volgende wat u doet is een aflossingstabel maken met de labels (periode, betaling, rente, hoofdsom, saldo) in a7:E7., Voer in de kolom periode een reeks getallen in die gelijk zijn aan het totale aantal betalingen (1 – 24 in dit voorbeeld):
Met alle bekende componenten op hun plaats, laten we naar de meest interessante afschrijvingsformules voor gedeeltelijke leningen gaan.
Bereken het totale betalingsbedrag(PMT-formule)
het betalingsbedrag wordt berekend met de functie PMT (rate, nper, pv,,).
om verschillende betalingsfrequenties correct af te handelen (zoals wekelijks, maandelijks, driemaandelijks, enz.,), moet u consistent zijn met de opgegeven waarden voor de rente-en nper-argumenten:
- Rate-deel de jaarlijkse rente door het aantal betalingstermijnen per jaar ($C$2/$C$4).
- Nper-vermenigvuldig het aantal jaren met het aantal betalingstermijnen per jaar ($3*$4).
- voer voor het PV-argument het bedrag van de lening in ($C $ 5).
- de FV-en type-argumenten kunnen worden weggelaten omdat hun standaardwaarden voor ons prima werken (saldo na de laatste betaling wordt verondersteld 0 te zijn; betalingen worden gedaan aan het einde van elke periode).,
door de bovenstaande argumenten samen te voegen, krijgen we deze formule:
=PMT($C$2/$C$4, $C$3*$C$4, $C$5)
let op, dat we absolute celverwijzingen gebruiken omdat deze formule zonder wijzigingen naar de onderstaande cellen moet kopiëren.,
Voer de PMT-formule in in B8, sleep deze naar beneden in de kolom en u ziet een constant betalingsbedrag voor alle perioden:
Bereken rente (IPMT-formule)
om het rentegedeelte van elke periodieke betaling te vinden, gebruik de IPMT(rate, per, nper, pv,,) functie:
=IPMT($C$2/$C$4, A8, $C$3*$C$4, $C$5)
alle argumenten zijn hetzelfde als in de PMT-formule, behalve het per argument dat de betalingstermijn specificeert., Dit argument wordt geleverd als een relatieve celverwijzing (A8) omdat het verondersteld wordt te veranderen op basis van de relatieve positie van een Rij waarnaar de formule wordt gekopieerd.,
deze formule gaat naar C8, en dan kopieert u het naar zoveel cellen als nodig is:
zoek principal (ppmt-formule)
om het belangrijkste deel van elke periodieke betaling te berekenen, gebruik deze ppmt-formule:
=PPMT($C$2/$C$4, A8, $C$3*$C$4, $C$5)
syntaxis en argumenten zijn precies hetzelfde als in de IPMT-formule hierboven besproken:
deze formule gaat naar kolom D, beginnend in d8:
haal het resterende saldo
om het resterende saldo voor elke periode te berekenen, gebruiken we twee verschillende formules.
om het saldo na de eerste betaling in E8 te vinden, wordt het bedrag van de lening (C5) en de hoofdsom van de eerste periode (D8) opgeteld:
=C5+D8
omdat het bedrag van de lening een positief getal is en de hoofdsom een negatief getal is, wordt het laatste in feite van het eerste afgetrokken.,
voor de tweede en alle volgende perioden, tel het vorige saldo op en de hoofdsom van deze periode:
=E8+D9
de bovenstaande formule gaat naar E9, en dan kopieer je het naar beneden in de kolom. Door het gebruik van relatieve celverwijzingen past de formule zich correct aan voor elke rij.
dat is het! Ons maandelijkse aflossingsschema voor leningen wordt uitgevoerd:
Tip: betalingen retourneren als positieve getallen
omdat een lening wordt betaald uit uw bankrekening, retourneren Excel-functies de betaling, rente en hoofdsom als negatieve getallen., Standaard worden deze waarden in rood gemarkeerd en tussen haakjes geplaatst, zoals u kunt zien in de afbeelding hierboven.
Als u alle resultaten als positieve getallen wilt hebben, plaatst u een minteken voor de functies PMT, IPMT en PPMT.
voor de Saldoformules, gebruik Aftrekken in plaats van optellen zoals weergegeven in de screenshot hieronder:
aflossingsschema voor een variabel aantal perioden
in het bovenstaande voorbeeld hebben we een aflossingsschema voor leningen gemaakt voor het vooraf gedefinieerde aantal betalingsperioden., Deze snelle eenmalige oplossing werkt goed voor een specifieke lening of hypotheek.
Als u op zoek bent naar een herbruikbaar aflossingsschema met een variabel aantal perioden, zult u een uitgebreidere benadering moeten volgen die hieronder wordt beschreven.
voer het maximum aantal perioden in
in de kolom periode, vul het maximum aantal betalingen in dat u toestaat voor een lening, bijvoorbeeld van 1 tot 360. U kunt gebruik maken van Excel ‘ s AutoFill-functie om sneller een reeks getallen in te voeren.,
gebruik als verklaringen in aflossingsformules
omdat u nu veel overmatige periodegetallen hebt, moet u op de een of andere manier de berekeningen beperken tot het werkelijke aantal betalingen voor een bepaalde lening. Dit kan worden gedaan door elke formule in een IF statement te wikkelen. De logische test van het if-statement controleert of het periodenummer in de huidige rij kleiner is dan of gelijk is aan het totale aantal betalingen. Als de logische test TRUE is, wordt de bijbehorende functie berekend; als FALSE, wordt een lege string geretourneerd.,
ervan uitgaande dat Periode 1 in rij 8 staat, voert u de volgende formules in de overeenkomstige cellen in en kopieert u ze over de hele tabel.,
Betaling (B8):
=IF(A8<=$C$3*$C$4, PMT($C$2/$C$4, $C$3*$C$4, $C$5), "")
Interesse (C8):
=IF(A8<=$C$3*$C$4, IPMT($C$2/$C$4, A8, $C$3*$C$4, $C$5), "")
de Hoofdsom (D8):
=IF(A8<=$C$3*$C$4,PPMT($C$2/$C$4, A8, $C$3*$C$4, $C$5), "")
Balans:
Periode 1 (E8), de formule is hetzelfde als in het vorige voorbeeld:
=C5+D8
Periode 2 (E9) en alle volgende perioden, de formule gaat uit van deze vorm:
=IF(A9<=$C$3*$C$4, E8+D9, "")
Als het resultaat, je hebt een correct berekend aflossingsschema en een hoop lege rijen met de periode getallen nadat de lening is afbetaald.,
Verberg extra perioden nummers
Als u kunt leven met een hoop overbodige periode nummers weergegeven na de laatste betaling, kunt u overwegen het werk gedaan en sla deze stap over. Als u streeft naar perfectie, Verberg dan alle ongebruikte periodes door een voorwaardelijke opmaakregel te maken die de lettertypekleur instelt op wit voor alle rijen na de laatste betaling.,
selecteer hiervoor alle gegevensrijen als uw afschrijvingstabel (A8: e367 in ons geval) en klik op tabblad Home > Voorwaardelijke opmaak > nieuwe regel… > gebruik een formule om te bepalen welke cellen moeten worden opgemaakt.
voer de onderstaande formule in die controleert of het periodeaantal in kolom A groter is dan het totale aantal betalingen:
=$A8>$C$3*$C$4
klik daarna op de knop Formaat… en kies de witte letterkleur. Klaar!,
Maak een overzicht van de lening
om de beknopte informatie over uw lening in één oogopslag te bekijken, voeg een paar formules toe aan de bovenkant van uw aflossingsschema.
totale betalingen (F2):
=-SUM(B8:B367)
totale rente (F3):
=-SUM(C8:C367)
Als u betalingen als positieve getallen hebt, verwijder dan het minteken uit de bovenstaande formules.
dat is het! Onze lening aflossingsschema is voltooid en goed om te gaan!,
download aflossingsschema voor leningen voor Excel
Hoe maak je een aflossingsschema voor leningen met extra betalingen in Excel
de aflossingsschema ‘ s besproken in de vorige voorbeelden zijn eenvoudig te maken en te volgen (hopelijk :). Echter, ze laten een nuttige functie die veel lening betalers zijn geïnteresseerd in-extra betalingen om een lening sneller af te betalen. In dit voorbeeld, zullen we kijken naar hoe u een lening aflossingsschema met extra betalingen te creëren.
definieer invoercellen
zoals gewoonlijk, begin met het instellen van de invoercellen., In dit geval laten we de naam van deze cellen zoals hieronder geschreven om onze formules eenvoudiger te lezen:
- Bereik – C2 (jaarlijkse rente)
- LoanTerm – C3 (looptijd in jaren)
- PaymentsPerYear – C4 (aantal betalingen per jaar)
- LoanAmount – C5 (totale bedrag van de lening)
- ExtraPayment – C6 (extra betaling per periode)
het Berekenen van een geplande betaling
Afgezien van de input-cellen, een meer vooraf gedefinieerde cel is nodig voor onze verdere berekeningen – de geplande betaling bedrag, dat wil zeggen, het bedrag dat moet worden betaald op een lening als er geen extra betalingen worden gedaan. Dit bedrag wordt berekend met de volgende formule:
=IFERROR(-PMT(InterestRate/PaymentsPerYear, LoanTerm*PaymentsPerYear, LoanAmount), "")
let op dat we een minteken vóór de PMT-functie plaatsen om het resultaat als een positief getal te hebben. Om fouten te voorkomen in het geval dat sommige invoercellen leeg zijn, sluiten we de PMT-formule in de IFERROR-functie.
Voer deze formule in een cel in (G2 in ons geval) en geef die cel een naam die is gepland payment.,
stel de afschrijvingstabel op
Maak een afschrijvingstabel aan met de headers in de onderstaande schermafbeelding. Voer in de kolom periode een reeks getallen in die begint met nul (u kunt de rij periode 0 later verbergen indien nodig).
Als u een herbruikbaar aflossingsschema wilt maken, voer dan het maximaal mogelijke aantal betalingstermijnen in (0 tot 360 in dit voorbeeld).
Voor periode 0 (rij 9 in ons geval), trek de balanswaarde, die gelijk is aan het oorspronkelijke leningbedrag., Alle andere cellen in deze rij blijven leeg:
formule in G9:
=LoanAmount
4. Bouw formules voor afschrijvingsschema met extra betalingen
Dit is een belangrijk onderdeel van ons werk. Omdat de ingebouwde functies van Excel niet voorzien in extra betalingen, zullen we alle wiskunde zelf moeten doen.
Voer de volgende formules in rij 10 (Periode 1) in en kopieer ze vervolgens naar beneden voor alle resterende perioden.
geplande betaling (B10):
als het geplande betalingsbedrag (cel G2 genoemd) kleiner is dan of gelijk is aan het resterende saldo (G9), gebruik dan de geplande betaling. Voeg anders het resterende saldo en de rente van de vorige maand toe.
=IFERROR(IF(ScheduledPayment<=G9, ScheduledPayment, G9+G9*InterestRate/PaymentsPerYear), "")
als extra voorzorgsmaatregel wikkelen we dit en alle volgende formules in de IFERROR-functie., Dit voorkomt een aantal verschillende fouten als sommige invoercellen leeg zijn of ongeldige waarden bevatten.
Extra betaling (C10):
Gebruik een if-formule met de volgende logica:
indien het Extrapaymentbedrag (cel C6 genoemd) kleiner is dan het verschil tussen het resterende saldo en de hoofdsom van deze periode (G9-E10), retourneer ExtraPayment; anders gebruik het verschil.
=IFERROR(IF(ExtraPayment<G9-E10, ExtraPayment, G9-E10), "")
totale betaling (D10)
simply, tel de geplande betaling (B10) en de extra betaling (C10) voor de huidige periode op:
=IFERROR(B10+C10, "")
hoofdsom (E10)
als de geplande betaling voor een bepaalde periode groter is dan nul, retourneer dan een kleinere van de twee waarden: geplande betaling minus rente (B10-F10) of de resterende balans (G9); anders geeft u nul terug.
=IFERROR(IF(B10>0, MIN(B10-F10, G9), 0), "")
houd er rekening mee dat de opdrachtgever alleen het deel van de geplande betaling omvat (niet de extra betaling!) dat gaat naar de hoofdsom van de lening.,
rente (F10)
indien de geplande betaling voor een bepaalde periode groter is dan nul, deel dan de jaarlijkse rente (cel C2 genoemd) door het aantal betalingen per jaar (cel C4 genoemd) en vermenigvuldig het resultaat met het resterende saldo na de vorige periode; anders geeft u 0 terug.
=IFERROR(IF(B10>0, InterestRate/PaymentsPerYear*G9, 0), "")
saldo (G10)
indien het resterende saldo (G9) groter is dan nul, trek het hoofdbestanddeel van de betaling (E10) en de extra betaling (C10) af van het resterende saldo na de vorige periode (G9); anders geef 0 terug.,
=IFERROR(IF(G9 >0, G9-E10-C10, 0), "")
als alles correct is gedaan, zou uw aflossingsschema er op dit punt ongeveer zo uit moeten zien:
extra perioden verbergen
Stel een voorwaardelijke opmaakregel op om de waarden in ongebruikte perioden te verbergen, zoals uitgelegd in deze tip.,betalingen per jaar:
=LoanTerm*PaymentsPerYear
de Werkelijke aantal betalingen:
cellen Tellen in het Totaal van de kolom die groter zijn dan nul, te beginnen met de Periode 1:
=COUNTIF(D10:D369,">"&0)
de Totale extra betalingen:
toe te Voegen tot de cellen in de Extra kolom, te beginnen met de Periode 1:
=SUM(C10:C369)
Totaal rente:
optellen cellen in het Belang kolom, te beginnen met de Periode 1:
=SUM(F10:F369)
Eventueel verberg de Periode 0 rij, en-uw lening aflossingsschema met extra betalingen is gedaan!, De screenshot hieronder toont het eindresultaat:
Download aflossingsschema voor leningen met extra betalingen
aflossingsschema Excel template
om in een mum van tijd een eersteklas aflossingsschema voor leningen te maken, maakt u gebruik van de ingebouwde sjablonen van Excel. Ga gewoon naar Bestand > Nieuw, typ” amortisatieschema”in het zoekvak en kies de sjabloon die u leuk vindt, bijvoorbeeld deze met extra betalingen:
zo maakt u een aflossingsschema voor leningen of hypotheken in Excel., Ik dank u voor het lezen en hoop u te zien op onze blog volgende week!
beschikbare downloads
Amortisatieschema Sample Workbook
- Hoe samengestelde rente in Excel te berekenen
- Hoe CAGR (compound annual growth rate) in Excel te vinden
- percentage berekenen in Excel met formulevoorbeelden