Formule voor het berekenen van een lijfrente-uitkering in Excel

Leningbetalingen zijn gemakkelijker en sneller te berekenen met Microsoft Office Excel. Er wordt veel meer tijd besteed aan handmatige berekening. Dit artikel gaat in op lijfrente-uitkeringen, de kenmerken van hun berekening, voor- en nadelen.

Wat is een lijfrente-uitkering?

Een methode van maandelijkse aflossing van een lening, waarbij het gestorte bedrag niet verandert gedurende de gehele looptijd van de lening. Die. op bepaalde data van elke maand stort iemand een bepaald bedrag totdat de lening volledig is terugbetaald.

Bovendien is de rente op de lening al inbegrepen in het totale bedrag dat aan de bank wordt betaald.

Classificatie lijfrente

Lijfrentebetalingen zijn onder te verdelen in de volgende soorten:

1. Gemaakt. Betalingen die niet veranderen hebben een vast tarief ongeacht externe omstandigheden.
2. Munteenheid. De mogelijkheid om het bedrag van de betaling te wijzigen in geval van een daling of stijging van de wisselkoers.
3. geïndexeerd. Betalingen afhankelijk van het niveau, inflatie-indicator. Tijdens de uitleenperiode verandert hun grootte vaak.
4. Variabelen. Lijfrente, die kan veranderen afhankelijk van de toestand van het financiële systeem, instrumenten.

Let op! Vaste betalingen hebben de voorkeur voor alle kredietnemers, omdat ze weinig risico lopen.

Voor- en nadelen van lijfrente-uitkeringen

Om het onderwerp beter te begrijpen, is het noodzakelijk om de belangrijkste kenmerken van dit soort leningbetalingen te bestuderen. Het heeft de volgende voordelen:

• Vaststelling van een specifiek betalingsbedrag en de datum van betaling.
• Hoge beschikbaarheid voor leners. Vrijwel iedereen kan een lijfrente aanvragen, ongeacht de financiële situatie.
• De mogelijkheid om het bedrag van de maandelijkse termijn te verlagen bij een stijging van de inflatie.

Niet zonder nadelen:

• Hoog tarief. De lener zal een groter bedrag te veel betalen in vergelijking met de differentiële betaling.
• Problemen die voortkomen uit de wens om de schuld vervroegd af te lossen.
• Geen herberekeningen voor vervroegde betalingen.

Wat is de leningbetaling?

De lijfrenteuitkering bestaat uit de volgende onderdelen:

• Rente betaald door een persoon bij het aflossen van een lening.
• Een deel van de hoofdsom.

Als gevolg hiervan is het totale bedrag aan rente bijna altijd hoger dan het bedrag dat de lener heeft bijgedragen om de schuld te verminderen.

Basisformule voor lijfrentebetaling in Excel

Zoals hierboven vermeld, kunt u in Microsoft Office Excel werken met verschillende soorten betalingen voor leningen en voorschotten. Lijfrente is geen uitzondering. In het algemeen is de formule waarmee u de lijfrentepremies snel kunt berekenen als volgt:  

Belangrijk! Het is onmogelijk om haakjes in de noemer van deze uitdrukking te openen om het te vereenvoudigen.

De belangrijkste waarden van de formule worden als volgt ontcijferd:

• AP – lijfrente-uitkering (de naam is afgekort).
• O – de grootte van de hoofdschuld van de lener.
• PS – de rentevoet die op maandbasis door een bepaalde bank wordt voorgesteld.
• C is het aantal maanden dat de lening duurt.

Om de informatie te verwerken, volstaat het om enkele voorbeelden te geven van het gebruik van deze formule. Ze zullen verder worden besproken.

Voorbeelden van het gebruik van de PMT-functie in Excel

We geven een eenvoudige toestand van het probleem. Het is noodzakelijk om de maandelijkse leningbetaling te berekenen als de bank een rente van 23% naar voren brengt en het totale bedrag 25000 roebel is. De lening duurt 3 jaar. Het probleem wordt opgelost volgens het algoritme:

1. Maak een algemene spreadsheet in Excel op basis van de brongegevens.

2. Activeer de PMT-functie en voer de argumenten ervoor in het daarvoor bestemde vak in.
3. Voer in het veld "Inzet" de formule "B3/B5" in. Dit wordt de rente op de lening.
4. Schrijf in de regel "Nper" de waarde in de vorm "B4*B5". Dit is het totale aantal betalingen voor de gehele looptijd van de lening.
5. Vul het veld "PS" in. Hier moet u het aanvankelijke bedrag dat van de bank is afgenomen, aangeven door de waarde "B2" te schrijven.

6. Zorg ervoor dat na het klikken op "OK" in de brontabel, de waarde "Maandelijkse betaling" is berekend.

Extra informatie! Een negatief getal geeft aan dat de lener geld uitgeeft.

Een voorbeeld van het berekenen van het te veel betaalde bedrag op een lening in Excel

In dit probleem moet u het bedrag berekenen dat een persoon die gedurende 50000 jaar een lening van 27 roebel heeft aangegaan tegen een rentevoet van 5%, te veel zal betalen. In totaal doet de lener 12 betalingen per jaar. Oplossing:

1. Stel de originele gegevenstabel samen.

2. Trek van het totale bedrag aan betalingen het initiële bedrag af volgens de formule «=ABS(ПЛТ(B3/B5;B4*B5;B2)*B4*B5)-B2». Het moet in de formulebalk bovenaan het hoofdmenu van het programma worden ingevoegd.
3. Als gevolg hiervan verschijnt het te veel betaalde bedrag in de laatste regel van het aangemaakte plaatje. De lener zal 41606 roebel te veel betalen.

De formule voor het berekenen van de optimale maandelijkse leningbetaling in Excel

Een taak met de volgende voorwaarde: de klant heeft een bankrekening geregistreerd voor 200000 roebel met de mogelijkheid van maandelijkse aanvulling. Het is noodzakelijk om het bedrag te berekenen dat een persoon elke maand moet betalen, zodat hij na 4 jaar 2000000 roebel op zijn rekening heeft staan. Het tarief is 11%. Oplossing:

1. Maak een spreadsheet op basis van de originele gegevens.

2. Voer de formule in de Excel-invoerregel in «=ПЛТ(B3/B5;B6*B5;-B2;B4)» en druk op "Enter" vanaf het toetsenbord. De letters verschillen afhankelijk van de cellen waarin de tabel is geplaatst.
3. Controleer of het contributiebedrag automatisch wordt berekend in de laatste regel van de tabel.

Let op! Dus om de klant in 4 jaar 2000000 roebel te laten verzamelen met een snelheid van 11%, moet hij elke maand 28188 roebel storten. De min in het bedrag geeft aan dat de klant verliezen lijdt door geld aan de bank te geven.

Kenmerken van het gebruik van de PMT-functie in Excel

In het algemeen wordt deze formule als volgt geschreven: =PMT(tarief; nper; ps; [bs]; [type]). De functie heeft de volgende kenmerken:

1. Bij de berekening van de maandelijkse bijdragen wordt alleen rekening gehouden met het jaartarief.
2. Bij het specificeren van de rente is het belangrijk om te herberekenen op basis van het aantal termijnen per jaar.
3. In plaats van het argument "Nper" in de formule wordt een specifiek getal aangegeven. Dit is de betalingstermijn.

Betalingsberekening

Over het algemeen wordt de lijfrente-uitkering in twee fasen berekend. Om het onderwerp te begrijpen, moet elk van de fasen afzonderlijk worden beschouwd. Dit zal verder worden besproken.

Stap 1: berekening van het maandbedrag

Om in Excel het bedrag te berekenen dat u maandelijks moet betalen aan een lening met een vaste rente, moet u:

1. Compileer de brontabel en selecteer de cel waarin u het resultaat wilt weergeven en klik bovenaan op de knop "Functie invoegen".

2. Selecteer in de lijst met functies "PLT" en klik op "OK".

3. Stel in het volgende venster de argumenten voor de functie in, waarbij u de corresponderende regels in de gecompileerde tabel aangeeft. Aan het einde van elke regel moet u op het pictogram klikken en vervolgens de gewenste cel in de array selecteren.

4. Wanneer alle argumenten zijn ingevuld, wordt de juiste formule geschreven in de regel voor het invoeren van waarden en verschijnt het berekeningsresultaat met een minteken in het veld van de tabel "Maandelijkse betaling".

Belangrijk! Na het berekenen van het termijnbedrag is het mogelijk om het bedrag te berekenen dat de lener te veel zal betalen voor de gehele leenperiode.

Stap 2: betalingsgegevens

Het te veel betaalde bedrag kan maandelijks worden berekend. Als gevolg hiervan zal een persoon begrijpen hoeveel geld hij elke maand aan een lening zal uitgeven. Detailberekening wordt als volgt uitgevoerd:

1. Maak een spreadsheet voor 24 maanden.

2. Plaats de cursor in de eerste cel van de tabel en voeg de functie "OSPLT" in.

3. Vul de functieargumenten op dezelfde manier in.

4. Wanneer u het veld "Periode" invult, moet u verwijzen naar de eerste maand in de tabel, waarbij cel 1 wordt aangegeven.

5. Controleer of de eerste cel in de kolom "Betaling door het lichaam van de lening" is gevuld.
6. Om alle rijen van de eerste kolom te vullen, moet je de cel uitrekken tot het einde van de tabel

7. Selecteer de functie "PRPLT" om de tweede kolom van de tabel in te vullen.
8. Vul alle argumenten in het geopende venster in volgens onderstaande schermafbeelding.

9. Bereken de totale maandelijkse betaling door de waarden in de vorige twee kolommen toe te voegen.

10. Om het "te betalen saldo" te berekenen, moet u de rentevoet optellen bij de betaling op het lichaam van de lening en deze uitrekken tot het einde van de plaat om alle maanden van de lening te vullen.

Extra informatie! Bij het berekenen van de rest moeten dollartekens aan de formule worden gehangen, zodat deze niet naar buiten beweegt wanneer deze wordt uitgerekt.

Berekening van lijfrentebetalingen op een lening in Excel

De PMT-functie is verantwoordelijk voor het berekenen van de lijfrente in Excel. Het principe van de berekening in het algemeen is om de volgende stappen uit te voeren:

1. Stel de originele gegevenstabel samen.
2. Stel voor elke maand een aflossingsschema op.
3. Selecteer de eerste cel in de kolom "Betalingen op de lening" en voer de berekeningsformule in "PLT ($B3/12;$B$4;$B$2)".
4. De resulterende waarde wordt uitgerekt voor alle kolommen van de plaat.

Berekening in MS Excel aflossing van de hoofdsom van de schuld

Lijfrentebetalingen dienen maandelijks in vaste bedragen te worden betaald. En de rente verandert niet.

Berekening van het saldo van de hoofdsom (met BS=0, type=0)

Stel dat een lening van 100000 roebel wordt genomen voor 10 jaar tegen 9%. Het is noodzakelijk om het bedrag van de hoofdschuld in de 1e maand van het 3e jaar te berekenen. Oplossing:

1. Stel een datasheet samen en bereken de maandelijkse betaling met behulp van de bovenstaande PV-formule.
2. Bereken het deel van de betaling dat nodig is om een ​​deel van de schuld af te betalen met behulp van de formule «=-PMT-(PS-PS1)*item=-PMT-(PS +PMT+PS*item)».
3. Bereken het bedrag van de hoofdschuld voor 120 perioden met behulp van een bekende formule.
4. Gebruik de HPMT-operator om het betaalde rentebedrag voor de 25e maand te vinden.
5. Controleer resultaat.

Het bedrag van de hoofdsom berekenen dat tussen twee perioden is betaald

Deze berekening kan het beste op een eenvoudige manier worden gedaan. U moet de volgende formules gebruiken om het bedrag in het interval voor twee perioden te berekenen:

• =«-BS(item; con_periode; plt; [ps]; [type]) /(1+type *item)».
• = “+ BS(tarief; start_periode-1; plt; [ps]; [type]) /IF(start_periode =1; 1; 1+type *tarief)”.

Let op! Letters tussen haakjes worden vervangen door specifieke waarden.

Vervroegde aflossing met verkorte looptijd of betaling

Als u de leentermijn moet verkorten, moet u aanvullende berekeningen uitvoeren met de IF-operator. Het zal dus mogelijk zijn om het nulsaldo te controleren, dat niet vóór het einde van de betalingsperiode mag worden bereikt.

Om de uitkering te verlagen, moet u de premie van elke voorgaande maand opnieuw berekenen.

Leningcalculator met onregelmatige betalingen

Er zijn verschillende lijfrente-opties waarbij de lener op elke dag van de maand variabele bedragen kan storten. In een dergelijke situatie wordt voor elke dag het saldo van de schuld en rente berekend. Tegelijkertijd hebt u in Excel nodig:

1. Voer de dagen van de maand in waarvoor betalingen worden gedaan en vermeld hun aantal.
2. Controleer op negatieve en positieve bedragen. Negatieven hebben de voorkeur.
3. Tel de dagen tussen twee datums waarop geld is gestort.

Berekening van de periodieke betaling in MS Excel. Termijndeposito

In Excel kunt u snel het bedrag aan periodieke betalingen berekenen, mits er al een vast bedrag is opgebouwd. Deze actie wordt uitgevoerd met behulp van de PMT-functie nadat de initiële tabel is gecompileerd.

Conclusie

Zo zijn lijfrente-uitkeringen makkelijker, sneller en efficiënter te berekenen in Excel. De PMT-operator is verantwoordelijk voor hun berekening. Meer gedetailleerde voorbeelden vindt u hierboven.