Berekening van annuïteitsbetaling in Microsoft Excel

Voordat u 'n lening aangaan, is dit lekker om alle betalings daarop te bereken. Dit sal die lener in die toekoms red van verskillende onverwagte probleme en teleurstellings as dit blyk dat die oorbetaling te groot is. Excel-instrumente kan help met hierdie berekening. Kom ons kyk hoe u die annuïteitlenings in hierdie program kan bereken.

Betalingsberekening

In die eerste plek moet daar gesê word dat daar twee soorte leningsbetalings is:

• onderskei;
• Annuïteit.

In 'n gedifferensieerde skema betaal die kliënt maandeliks gelyke bedrae op die leningsliggaam plus rentebetalings aan die bank. Die hoeveelheid rentebetalings neem elke maand af, namate die lening waaruit dit bereken word, afneem. Dus word die totale maandelikse betaling ook verminder.

'N Annuïteitsskema gebruik 'n effens ander benadering. Die kliënt betaal maandeliks dieselfde bedrag van die totale betaling, wat bestaan ​​uit betalings op die leningsliggaam en rentebetalings. Aanvanklik word rentebetalings vir die hele leningsbedrag bereken, maar namate die liggaam verminder, daal die rente-oploop. Die totale bedrag wat betaal word, bly egter onveranderd weens die maandelikse toename in die bedrag aan die leningsliggaam. Met verloop van tyd neem die persentasie rente in die totale maandelikse betaling af, en neem die persentasie van die betaling per liggaam toe. Die totale maandelikse betaling self verander ook nie gedurende die hele leningstermyn nie.

Net met die berekening van die annuïteitsbetaling stop ons. Dit is ook relevant, aangesien die meeste banke tans hierdie spesifieke skema gebruik. Dit is gerieflik vir kliënte, want in hierdie geval verander die totale bedrag nie, maar bly vas. Kliënte weet altyd hoeveel om te betaal.

Fase 1: maandelikse paaiementberekening

Vir die berekening van die maandelikse bydrae by die gebruik van die annuïteitsskema in Excel is daar 'n spesiale funksie - PMT. Dit behoort tot die kategorie finansiële operateurs. Die formule vir hierdie funksie is soos volg:

= PLT (koers; nper; ps; bs; tipe)

Soos u kan sien, bevat hierdie funksie 'n redelike groot aantal argumente. Die laaste twee van hulle is wel opsioneel.

argument "Bet" dui die rentekoers vir 'n spesifieke periode aan. As die jaarlikse koers byvoorbeeld gebruik word, maar die lening maandeliks betaal word, moet die jaarlikse koers gedeel word deur 12 en gebruik die resultaat as 'n argument. As 'n kwartaallikse betaling gebruik word, moet die jaarlikse tarief in hierdie geval gedeel word deur 4 ens

"NPER" dui die totale aantal leningsperiodes aan. Dit wil sê, as 'n lening vir een jaar met 'n maandelikse betaling aangegaan word, word die aantal periodes oorweeg 12as dit vir twee jaar is, is die aantal periodes 24. As die lening vir twee jaar met 'n kwartaallikse betaling geneem word, is die aantal periodes gelyk 8.

"Ps" dui die huidige waarde op die oomblik aan. In eenvoudige woorde, dit is die totale bedrag van die lening aan die begin van die lening, dit wil sê die bedrag wat u leen, uitgesonderd rente en ander bykomende betalings.

"BS" is die toekomstige waarde. Hierdie waarde is die liggaam van die lening ten tyde van die voltooiing van die leningsooreenkoms. In die meeste gevalle is hierdie argument "0", aangesien die lener aan die einde van die leningstermyn die uitlener ten volle moet betaal. Die gespesifiseerde argument is opsioneel. As dit dus daal, word dit as nul beskou.

argument "Tik" bepaal die berekeningstyd: aan die einde of aan die begin van die periode. In die eerste geval neem dit die waarde aan "0"en in die tweede - "1". Die meeste bankinstellings gebruik presies die opsie met betaling aan die einde van die periode. Hierdie argument is ook opsioneel, en as dit weggelaat word, word dit as nul beskou.

Dit is nou tyd om oor te gaan na 'n spesifieke voorbeeld van die berekening van die maandelikse paaiement met behulp van die PMT-funksie. Vir die berekening gebruik ons ​​die tabel met die brondata, waar die rentekoers op die lening aangedui word (12%), leningsbedrag (500,000 roebels) en leningstermyn (24 maande). Daarbenewens word die betaling maandeliks aan die einde van elke periode geskied.

1. Kies die element op die bladsy waarin die berekeningsresultaat vertoon word, en klik op die ikoon "Voeg funksie in"naby die formulebalk geplaas.



2. Die venster word gelanseer. Funksie towenaars. In kategorie "Finansiële" kies die naam "PMT" en klik op die knoppie "OK".



3. Daarna word die venster van operateurargumente oopgemaak. PMT.

   In die veld "Bet" voer die persentasie vir die periode in. Dit kan met die hand gedoen word, eenvoudig deur die persentasie in te stel, maar ons het dit in 'n aparte sel op die blad aangedui, dus gee ons 'n skakel daarvoor. Ons plaas die wyser in die veld en klik dan op die toepaslike sel. Maar soos ons onthou, is die jaarlikse rentekoers in ons tabel vasgestel, en die betalingstydperk is gelyk aan 'n maand. Daarom verdeel ons die jaarlikse koers, of liewer die skakel na die sel waarin dit is, na die nommer 12wat ooreenstem met die aantal maande in 'n jaar. Die verdeling word direk in die veld van die argumentvenster uitgevoer.

   In die veld "NPER" 'n leningstermyn word bepaal. Hy is gelyk aan ons 24 vir maande. U kan 'n nommer in die veld invoer 24 handmatig, maar ons gee, soos in die vorige geval, 'n skakel na die ligging van hierdie aanwyser in die oorspronklike tabel.

   In die veld "Ps" Die aanvanklike leningsbedrag word aangedui. Sy is gelyk 500,000 roebels. Soos in vorige gevalle, dui ons die skakel aan op die bladelement waarin hierdie aanwyser is.

   In die veld "BS" dui die bedrag van die lening aan na die volle betaling. Soos ons onthou, is hierdie waarde byna altyd nul. Stel die nommer in hierdie veld in "0". Alhoewel hierdie argument heeltemal weggelaat kan word.

   In die veld "Tik" gee aan die begin of aan die einde van die maand dat die betaling gedoen word. Hier word, soos in die meeste gevalle, aan die einde van die maand geproduseer. Stel dus die nommer in "0". Soos in die geval met die vorige argument, kan u niks in hierdie veld invoer nie, dan aanvaar die program by verstek dat dit 'n waarde gelyk aan nul bevat.

   Nadat al die data ingevoer is, klik op die knoppie "OK".



4. Daarna word die resultaat van die berekening getoon in die sel wat ons in die eerste paragraaf van hierdie handleiding uitgelig het. Soos u kan sien, is die bedrag van die maandelikse totale leningbetaling 23536.74 roebels. Moenie verwar word deur die "-" teken voor hierdie bedrag nie. Excel gee dus aan dat dit 'n kontantuitgawe is, dit wil sê 'n verlies.



5. Om die totale bedrag vir die hele leningstermyn te bereken, met inagneming van die terugbetaling van die leningsliggaam en die maandelikse rente, is dit voldoende om die bedrag van die maandelikse betaling te vermenigvuldig (23536.74 roebels) volgens die aantal maande (24 maande). Soos u kan sien, beloop die totale bedrag vir die hele leningstermyn in ons geval 564881.67 roebels.



6. U kan nou die bedrag van die te veel bedrag op die lening bereken. Trek dit af van die totale bedrag wat op die lening betaal word, insluitend rente en die leningsliggaam, die aanvanklike bedrag wat geleen is. Maar ons onthou dat die eerste van hierdie waardes reeds onderteken is "-". Daarom blyk dit in ons spesifieke geval dat hulle gevou moet word. Soos u kan sien, beloop die totale leningoorbetaling vir die hele periode 64 881,67 roebels.

les: Excel-funksie-assistent

Fase 2: betalingsbesonderhede

En nou, met die hulp van ander Excel-operateurs, sal ons maandeliks besonderhede oor betalings maak om te sien hoeveel ons op 'n lening in 'n spesifieke maand betaal, en hoeveel rente is. Vir hierdie doeleindes teken ons 'n tabel in Excel wat ons met data sal invul. Die rye in hierdie tabel sal ooreenstem met die ooreenstemmende periode, dit wil sê die maand. Gegewe dat die uitleenperiode by ons is 24 maande, sal die aantal rye ook toepaslik wees. Die kolomme dui die betaling van die leningsliggaam aan, rentebetaling, die totale maandelikse betaling, wat die som is van die vorige twee kolomme, sowel as die oorblywende bedrag wat betaalbaar is.

1. Gebruik die funksie om die bedrag van die betaling per leningliggaam te bepaal OSPLT, wat net vir hierdie doeleindes ontwerp is. Stel die wyser op die sel wat in die ry is "1" en in die kolom "Terugbetaling op die leningstelsel". Klik op die knoppie "Voeg funksie in".



2. Gaan na Funksie-towenaar. In kategorie "Finansiële" merk die naam "OSPLT" en druk op die knoppie "OK".



3. Die argiefvenster van die OSPLT-operateur begin. Dit het die volgende sintaksis:

   = OSPLT (Weddenskap; Periode; Nper; Ps; BS)

   Soos u kan sien, val die argumente van hierdie funksie feitlik heeltemal saam met die argumente van die operateur PMTslegs in plaas van 'n opsionele argument "Tik" vereiste argument bygevoeg "Periode". Dit dui die nommer van die betalingstydperk aan, en in ons spesifieke geval, die nommer van die maand.

   Ons vul die reeds bekende velde van die funksie-argumentvenster in OSPLT dieselfde data wat gebruik is vir die funksie PMT. Slegs die feit dat die formule in die toekoms gekopieër sal word met behulp van die vulmerker, moet u alle skakels in die velde absoluut maak sodat dit nie verander nie. Om dit te doen, plaas 'n dollarteken voor elke koördinaatwaarde vertikaal en horisontaal. Maar dit is makliker om dit te doen deur bloot die koördinate uit te lig en op die funksie-toets te druk F4. Die dollarteken sal outomaties op die regte plekke geplaas word. Moet ook nie vergeet dat die jaarkoers gedeel moet word deur nie 12.



4. Maar ons het nog 'n nuwe argument wat die funksie nie gehad het nie. PMT. Hierdie argument "Periode". Stel die skakel in die ooreenstemmende veld na die eerste sel van die kolom "Periode". Hierdie bladelement bevat 'n nommer "1", wat die nommer van die eerste leningsmaand aandui. Maar anders as die vorige velde, laat ons die skakel relatief in die gespesifiseerde veld en maak dit nie absoluut nie.

   Nadat al die data waaroor ons hierbo gepraat het, ingevoer is, klik op die knoppie "OK".



5. Daarna sal die terugbetalingsbedrag vir die eerste maand in die sel wat ons voorheen toegeken het, vertoon word. Sy sal maak 18.536.74 roebels.



6. Dan moet ons, soos hierbo genoem, hierdie formule kopieer na die oorblywende selle van die kolom met behulp van die vulmerker. Om dit te doen, stel die muisaanwyser in die regteronderhoek van die sel wat die formule bevat. Die wyser word omgeskakel na 'n kruis, wat die vulmerk genoem word. Hou die linkermuisknop vas en sleep dit na die einde van die tafel.



7. As gevolg hiervan word al die selle in die kolom gevul. Nou het ons 'n maandelikse terugbetalingsskedule vir lenings. Soos hierbo genoem, neem die bedrag volgens hierdie artikel met elke nuwe periode toe.



8. Nou moet ons 'n maandelikse berekening van rentebetalings doen. Vir hierdie doeleindes gebruik ons ​​die operateur IPMT. Kies die eerste leë sel in die kolom Rente-betaling. Klik op die knoppie "Voeg funksie in".



9. In die beginvenster Funksie towenaars in kategorie "Finansiële" ons maak die keuse IPMT. Klik op die knoppie. "OK".



10. Die funksie-argumente-venster begin. IPMT. Die sintaksis daarvan is soos volg:

    = PRPLT (Weddenskap; Periode; Nper; Ps; BS)

    Soos u kan sien, is die argumente van hierdie funksie absoluut identies aan die soortgelyke elemente van die operateur OSPLT. Daarom voer ons eenvoudig dieselfde data in die venster in wat ons in die vorige argumentvenster ingevoer het. Ons vergeet nie terselfdertyd dat die skakel in die veld nie "Periode" moet relatief wees, en op alle ander velde moet die koördinate tot 'n absolute vorm verminder word. Klik daarna op die knoppie "OK".



11. Dan word die resultaat van die berekening van die bedrag van die rente op die lening vir die eerste maand in die toepaslike blokkie vertoon.



12. Deur die vulmerker toe te pas, kopieer ons die formule in die oorblywende elemente van die kolom, en verkry dus 'n maandelikse betalingskedule vir rente op die lening. Soos ons reeds sien, neem die waarde van hierdie tipe betaling van maand tot maand af.



13. Nou moet ons die totale maandelikse betaling bereken. Vir hierdie berekening moet u nie by enige operateur gebruik maak nie, aangesien u 'n eenvoudige rekenkundige formule kan gebruik. Voeg die inhoud van die selle van die eerste maand kolomme by "Terugbetaling op die leningstelsel" en Rente-betaling. Stel dit op die bord "=" na die eerste leë sel van 'n kolom "Totale maandelikse uitbetaling". Dan klik ons ​​op die twee elemente hierbo en plaas 'n teken tussenin "+". Klik op die sleutel Tik.



14. Vul daarna die kolom met data in, soos in vorige gevalle. Soos u kan sien, sal die totale maandelikse betaling, insluitend betaling deur die leningsliggaam en rente, gedurende die hele kontrak duur 23536.74 roebels. Ons het hierdie aanwyser reeds bereken met behulp van PMT. Maar in hierdie geval word dit duideliker aangebied, juis as die bedrag wat op die leningsliggaam en rente betaal word.



15. U moet nou die gegewens in die kolom voeg, wat die saldo van die leningsbedrag wat nog betaal moet word, maandeliks vertoon. In die eerste sel van die kolom "Saldo betaalbaar" die berekening sal die maklikste wees. Ons moet die aanvanklike bedrag van die lening, wat in die tabel met die primêre gegewens aangedui is, aftrek van die betaling op die leningsliggaam vir die eerste maand in die berekeningstabel. Maar gegewe die feit dat ons een van die getalle het, het ons reeds 'n teken "-", dan moet hulle nie weggevat word nie, maar gevou word. Ons doen dit en klik op die knoppie Tik.



16. Maar die berekening van die saldo wat na die tweede en daaropvolgende maande verskuldig is, sal ietwat ingewikkelder wees. Om dit te kan doen, moet ons aan die begin van die lening die totale bedrag vir die vorige periode op die leningsliggaam aftrek. Stel die teken op "=" in die tweede sel van die kolom "Saldo betaalbaar". Vervolgens dui ons die skakel na die sel aan, wat die aanvanklike leningsbedrag bevat. Maak dit absoluut deur die toets uit te lig en te druk F4. Dan sit ons 'n bord "+", aangesien die tweede waarde in ons geval negatief sal wees. Klik daarna op die knoppie "Voeg funksie in".



17. Begin Funksie-towenaarwaarin u na die kategorie moet gaan "Wiskundige". Daar beklemtoon ons die inskripsie "Som" en klik op die knoppie "OK".



18. Die funksie-argumentvenster begin som. Die gespesifiseerde operateur dien om die data in die selle op te som wat ons in die kolom moet uitvoer "Terugbetaling op die leningstelsel". Dit het die volgende sintaksis:

    = SUM (getal1; getal2; ...)

    Die argumente is verwysings na selle wat getalle bevat. Ons stel die wyser na die veld "Nommer 1". Dan hou ons die linkermuisknop ingedruk en kies die eerste twee selle van die kolom op die blad "Terugbetaling op die leningstelsel". Soos ons sien, word 'n skakel na die reeks in die veld vertoon. Dit bestaan ​​uit twee dele, geskei deur 'n dubbelpunt: skakels na die eerste sel van die reeks en na die laaste. Om die gegewe formule in die toekoms met behulp van die vulmerker te kan kopieer, maak ons ​​die eerste deel van die skakel na die reeks absoluut. Kies dit en klik op die funksie-toets F4. Die tweede deel van die skakel is nog relatief. As u die vulmerker gebruik, sal die eerste sel van die reeks reggestel word, en die laaste sal rek as dit afwaarts beweeg. Dit is wat ons nodig het om ons doelwitte te bereik. Klik nou op die knoppie "OK".



19. Die resultaat van die saldo van kredietskuld ná die tweede maand word dus in die sel vertoon. Vanaf hierdie sel, kopieer ons die formule in leë kolomelemente met behulp van die vulmerker.



20. 'N Maandelikse berekening van leningsaldo's vir die hele leningsperiode. Soos verwag, is hierdie bedrag aan die einde van die termyn nul.

Ons het dus nie net die betaling op die lening bereken nie, maar 'n soort leningsrekenaar georganiseer. Wat sal optree op 'n annuïteitsskema. As ons byvoorbeeld die grootte van die lening en die jaarlikse rentekoers in die oorspronklike tabel verander, dan word die data outomaties herbereken.Daarom kan dit nie net een keer vir 'n spesifieke geval gebruik word nie, maar in verskillende situasies gebruik word om kredietopsies volgens 'n annuïteitskema te bereken.

les: Finansiële funksies in Excel

Soos u kan sien, met behulp van die Excel-program tuis, kan u die totale maandelikse leningbetaling maklik bereken volgens die annuïteitsskema wat die operateur vir hierdie doeleindes gebruik PMT. Daarbenewens is die gebruik van funksies OSPLT en IPMT u kan die bedrag van die betalings op die liggaam van die lening en die rente vir die bepaalde periode bereken. As u al hierdie funksies saam gebruik, is dit moontlik om 'n kragtige leningsrekenaar te skep wat meer as een keer gebruik kan word om 'n annuïteitsbetaling te bereken.