Hoe u uw eigen leningaflossingcalculator maakt |

De meeste mensen denken dat er een soort voodoo-magie in het creëren van een lening zit afschrijvingsschema . Maar ik ben blij om u te vertellen dat er geen - en zelfs beter - zelfs een beginner kan er gemakkelijk een maken met behulp van Microsoft Excel.

Een aflossingstabel voor leningen geeft u veel goede informatie: het totale te betalen bedrag aan de kredietgever, het deel dat de rentebetaling is, het gedeelte dat de hoofdsom is en het uitstaande leningenaldo.

Wanneer een lening wordt afgeschreven, betekent dit eenvoudigweg dat een rentebetaling en een hoofdbetaling worden gecombineerd tot één periodieke betaling en dat de betalingsbedragen voor elke betalingstermijn gelijk zijn. Maar een van de meest interessante kenmerken van een afgeschreven lening is dat bij elke betaling het bedrag van de rente en de hoofdsom wordt betaald.

Excel is een uiterst nuttig hulpmiddel voor het berekenen van uw eigen aflossingstabel. Je kunt zelfs experimenteren met de verschillende inputs om te zien hoe verschillende rentetarieven, terugbetalingen en hoofdbedragen van toepassing zijn op de leenbetalingen die je zult doen.

Laten we een voorbeeld bekijken om het proces te illustreren.

Stel dat u net een auto hebt gekocht met een lening van 20.000 dollar die volledig moet worden terugbetaald in 4 jaar. De jaarlijkse rente op de lening is 3%. Omdat de lening afgeschreven wordt, zullen de maandelijkse betalingen allemaal hetzelfde bedrag zijn.

De eerste stap is om het maandelijkse betalingsbedrag te berekenen. Dit kan gedaan worden met behulp van de PMT (wat staat voor "payment") functie in Excel.

Begin met het maken van een nieuwe spreadsheet en voer de volgende gegevens in die nodig zijn voor de berekening van het betalingsbedrag.

Kolom A bevat de volgende labels voor onze gegevens:

A1 = de oorspronkelijke lening (uw geleende bedrag)

A2 = de leentermijn in jaren

A3 = de jaarlijkse rente

A4 = het aantal betalingen per jaar

A5 = het betalingsbedrag (wat we berekenen)

Kolom B zal bevatten de corresponderende waarden voor elk label in kolom A:

B1 = $ 20.000

B2 = 4

B3 = 3%

B4 = 12

B5 = de waarde die we zoeken

De PMT-functie van Excel is te vinden in het financiële functiemenu. Selecteer eerst cel B5, klik vervolgens op het financiële functiemenu op het tabblad Formules en selecteer de PMT-functie in de lijst. Er wordt een venster weergegeven waarin u de gegevens uit de spreadsheet moet invoeren zoals deze in deze afbeelding staan:

Hier zijn een paar belangrijke zaken op te merken. Eerst moeten we twee belangrijke aanpassingen doen, omdat we maandelijkse betalingen berekenen. We moeten ons jaartarief met 12 delen om de maandelijkse rentevoet (Tarief) te krijgen, en we moeten 4 per 12 vermenigvuldigen om het totale aantal maanden in onze terugbetalingsperiode (Nper) te krijgen.

Ten tweede wordt de contante waarde (PV) als een negatieve waarde ingevoerd omdat dit bedrag aan de kredietgever verschuldigd is.

Zodra uw vakjes zijn ingevuld, drukt u op "OK".

Uw spreadsheet zou er nu als volgt uit moeten zien:

Nu gaan we beginnen met het opstellen van de aflossingstabel.

Sla een rij onder de spreadsheet in deel 1 over en maak, beginnend in rij 7, het volgende labels beginnend in kolom A en eindigend in kolom E:

A7 = Maand

B7 = Betaling

C7 = Rente

D7 = Opdrachtgever

E7 = Evenwicht

Dit zijn de vijf titels voor de aflossingstabel.

Nu is het tijd om de tabel in te vullen. De eerste stap is om het aantal maanden in de looptijd van de lening in te voeren. Typ onder de kop "month" in cel A8 (het tijdstip waarop de lening is uitgegeven). Vul de volgende cellen in de juiste volgorde in via nummer 48 (waardoor je in cel A56 terecht komt). Eén truc: nadat je 0 hebt ingevoerd, zorg je dat de cel is geselecteerd (A8) en sleep je de rechterbenedenhoek van de cel naar regel 56. Selecteer vervolgens 'reeks vullen' in het kleine vak dat rechts van de laatste cel. Dit moet de numerieke reeks automatisch invullen voor de 48 maanden.

Vervolgens kunnen alle betalingen worden ingevuld omdat de waarde van elke maandelijkse betaling al is vastgesteld. Vanaf maand 1 (er is geen betaling bij maand 0 omdat betalingen aan het einde van elke maand plaatsvinden) vult u de waarde $ 442,69 in voor elke maand 1-48. Hierna kunnen de rente- en hoofdbedragen worden berekend met behulp van hun respectieve Excel-functies.

Het berekenen van de rente- en hoofdcomponenten van de betalingen met behulp van de Excel-functie is eenvoudig en kan worden gebruikt om de volledige aflossingstabel in te vullen. Om de rentebetaling te berekenen, selecteert u de cel C9 en klikt u vervolgens op het financiële functiemenu op het tabblad Formules. Selecteer de IPMT (voor "rentebetaling") functie uit de lijst. Er wordt een venster weergegeven waarin u de gegevens uit de spreadsheet moet invoeren zoals deze in deze afbeelding staan:

Opmerking: de "$" -tekens tussen de celletters en cijfers zijn nodig voor wanneer we kopiëren de formules tot de resterende cellen later. De "$" vergrendelt de bedoelde cel, dus de formules blijven intact wanneer we ze naar de resterende cellen kopiëren. Je kunt de cellen vergrendelen (en de "$" zien) door op F4 te drukken.

Vervolgens kunnen we het bedrag van de hoofdsom in de betaling berekenen. Vergelijk met de IPMT-berekening, selecteer cel D9 en klik vervolgens op het financiële functiemenu op het tabblad Formules. Selecteer de PPMT (voor de functie "hoofdbetaling) in de lijst.Een vakje wordt weergegeven waar u de gegevens uit het werkblad moet invoeren zoals deze in deze afbeelding zijn:

Opmerking: De principal zou worden berekend met behulp van deze functie of door simpelweg het rentebedrag af te trekken van het totale betalingsbedrag. Ook zou de rente kunnen worden berekend als we de hoofdsom eerst hebben berekend met behulp van de PPMT-functie en die waarde vervolgens van de totale betaling hebben afgetrokken. om te weten hoe beide formules te gebruiken, waardoor ook de rest van de tabel efficiënter kan worden ingevuld, zoals u hieronder zult zien.

De laatste kolom (kolom E) is de kolom voor het resterende saldo. Deze kolom kan eenvoudig worden berekend voor elke periode waarin we de bedragen van rente en hoofdsom hebben bepaald.Voor maand 0 zijn er nog geen betalingen gedaan, dus het saldo is $ 20.000, maar we willen Excel vragen om automatisch het saldo in elke periode te berekenen, dus in plaats van manu bondgenoot die "$ 20.000" invoert, voer "= B1" in, verwijzend naar de bovenstaande tabel voor de berekening van de betaling. Voor maand 1, omdat het saldo wordt berekend door het saldo van de vorige maand af te trekken met de hoofdbetaling van de huidige maand, voert u "= E8-D9" in cel E9 in. Het saldo wordt automatisch berekend. Uw aflossingstabel is nu klaar voor maand 1 en ziet er precies zo uit:

Alle gegevens voor de resterende maanden kunnen worden ingevuld door eenvoudig de cellen B9-E9 te selecteren, de selectie te kopiëren en de selectie in cellen te plakken B10-E10. Omdat we de formules voor elke cel hebben ingevoerd en onze cellen hebben vergrendeld die in elke berekening worden gebruikt, worden de waarden voor rente, hoofdsom en saldo automatisch berekend. Ziende dat werk voor maand 2, kan de volledige lijst worden voltooid door eenvoudig de kolommen B-E tot en met maand 48 te selecteren en de reeds gekopieerde informatie te plakken. Excel heeft nu elke waarde voor de aflossingstabel berekend met behulp van de formules die we hebben ingevoerd. Uw voltooide tabel moet er precies zo uitzien:

Eerste jaar van bruikleen:

Laatste jaar van bruikleen:

Opmerking: deze tabellen bevatten alleen het eerste en laatste jaar van de aflossingstabel. Jaren 2 en 3 zouden zich tussen deze twee tabellen bevinden en een ononderbroken aflossingstabel vormen voor alle 48 maanden.

Als u geïnteresseerd bent in het berekenen van hypotheekbetalingen voor een hypotheek met vaste rente, kunt u zelf een aflossingstabel maken met behulp van deze stappen, of u kunt onze financiële calculator gebruiken om het voor u te doen. Onze hypotheekcalculator toont u uw maandelijkse betaling en een volledig aflossingsschema.