Berekening van annuïteit betaal in Excel

Voordat die lening, sal dit lekker wees om alle betalings te bereken op dit. Dit sal die lener in die toekoms van verskeie onverwagte probleme en teleurstellings te red wanneer dit blyk dat oorbetaling is te groot. Hulp op hierdie kan bereken die Excel-program gereedskap. Kom ons vind uit hoe om die annuïteitbetalings op die lening te bereken in hierdie program.

Berekening van betaling

In die eerste plek, moet dit gesê word dat daar twee tipes krediet betalings:

• onderskei;
• Annuïteit.

Met 'n gedifferensieerde stelsel, die kliënt bring 'n maandelikse gelyke aandeel van betalings op die liggaam van 'n lening plus betalings aan belangstelling. Die grootte van die rente elke maand verminder, as die liggaam van die lening verminder waaruit dit bereken. So, is die algehele maandelikse betaling ook verminder.

Ann tante skema maak gebruik van 'n effens ander benadering. Die kliënt maak 'n enkele bedrag van die totale betaling maandeliks, wat bestaan ​​uit betalings op die liggaam van 'n lening en die betaling van rente. Aanvanklik is belangstelling bydraes getel op die volle bedrag van die lening, maar as die liggaam verminder, is rente verminder en rente aanwas. Maar die totale bedrag van betaling bly onveranderd te danke aan 'n maandelikse styging in die bedrag van die betalings deur die liggaam van die lening. So, met verloop van tyd, die deel van die belangstelling in die totale maandelikse betaling val, en die verhouding gewig van die liggaam groei. Terselfdertyd, is die algemene maandelikse betaling self nie verander gedurende die kwartaal van krediet.

Net op die berekening van die annuïteit betaling, sal ons stop. Veral, dit is relevant, aangesien nou die meeste banke gebruik hierdie spesifieke skema. Dit is gerieflik vir kliënte, want in hierdie geval die totale bedrag van betaling nie verander nie, oorblywende vasgestel. Kliënte weet altyd hoeveel jy nodig het om te betaal.

Stap 1: maandelikse bydrae Berekening

Om 'n maandelikse bydrae bereken wanneer die gebruik van 'n annuïteit kring in Excele, daar is 'n spesiale funksie - PPT. Dit verwys na die kategorie van finansiële operateurs. Die formule van hierdie funksie is soos volg:

= PPT (koers; KPER; PS; BS, tipe)

Soos ons sien, die spesifieke funksie het 'n redelike groot aantal argumente. True, die laaste twee van hulle is nie verpligtend nie.

Die "koers" argument dui op 'n persentasie vir 'n spesifieke tydperk. As, byvoorbeeld, 'n jaarlikse koers gebruik word, maar die lening betaal word maandeliks gedoen, dan moet die jaarlikse koers word verdeel in 12 en die gevolg is gebruik as 'n argument. As 'n kwartaallikse tipe betaling word toegepas, dan in hierdie geval die jaarlikse verbintenis moet verdeel word in 4, ens

"Cper" beteken die totale aantal periodes van lening betalings. Dit is, indien die lening vir 'n jaar geneem met die maandelikse betaling, dan die aantal periodes word beskou as 12, as twee jaar, dan is die aantal periodes - 24. Indien die lening geneem vir twee jaar met kwartaallikse betaling, dan die aantal periodes is 8.

"PS" dui op die huidige waarde op die oomblik. Praat met 'n eenvoudige woorde, dit is die totale bedrag van die lening aan die begin van die lening, dit wil sê die bedrag wat jy geleen het, met uitsluiting van rente en ander bykomende betalings.

"BS" is 'n toekomstige koste. Hierdie waarde sal 'n lening liggaam wees ten tyde van die voltooiing van die leningsooreenkoms. In die meeste gevalle, hierdie argument is "0", aangesien die lener aan die einde van die krediet tydperk ten volle moet skik met die man wat uitleen. Die gespesifiseerde argument is nie verpligtend nie. Daarom, indien dit het geval dit word beskou as nul.

Die "Tipe" argument bepaal die berekening tyd: aan die einde of aan die begin van die tydperk. In die eerste geval, dit neem die waarde "0", en in die tweede - "1". Die meeste bankinstellings gebruik presies die opsie met betaling aan die einde van die tydperk. Hierdie argument is ook opsioneel, en as dit is weggelaat, word daar geglo dat dit nul.

Nou is dit tyd om te skuif na 'n spesifieke voorbeeld van die berekening van 'n maandelikse bydrae met behulp van die PL funksie. Om te bereken, gebruik ons ​​'n tafel met die bron data, waar die rentekoers op die lening (12%) aangedui, die lening waarde (500,000 roebels) en die lening tydperk (24 maande). Terselfdertyd, is die betaling maandeliks aan die einde van elke periode.

1. Kies die element op die blad waarin die gevolg hiervan sal vertoon word, en klik op die ikoon "funksie Voeg", geplaas naby die formule ry.



2. Die venster venster towenaar is van stapel gestuur. In die kategorie "Finansiële" toe te ken die naam "PLT" en klik op die knoppie "OK".



3. Daarna het open die argumente venster van die PL operateur.

   In die "Tempo" veld, moet jy die persentasie waarde vir die tydperk te betree. Dit kan met die hand gedoen word, net om 'n persentasie, maar dit word in 'n aparte sel op die vel, sodat ons 'n skakel sal gee om dit te. Die installering van die wyser in die veld, en kliek dan op die ooreenstemmende sel. Maar, as ons onthou, ons het die jaarlikse rentekoers in ons tafel, en die betaling tydperk is gelyk aan die maand. Daarom deel ons die jaarlikse verbintenis, en eerder 'n skakel na die sel waarin dit vervat deur die nommer 12, wat ooreenstem met die aantal maande in die jaar. Afdeling loop direk in die veld Argument venster.

   In die veld Cper, is uitleen stel. Hy is gelyk aan 24 maande. Jy kan aansoek doen in die hand veld nommer 24, maar ons, as in die vorige geval, spesifiseer 'n skakel na die plek van hierdie aanwyser in die bron tafel.

   In die veld "PS" dui die aanvanklike lening waarde. Dit is gelyk aan 500,000 roebels. Soos in die vorige gevalle, spesifiseer ons 'n skakel na die blaar element, wat hierdie aanwyser bevat.

   In die veld "BS" dui op die grootte van die lening, nadat sy volle betaling. As jy onthou, hierdie waarde is byna altyd nul. Installeer in hierdie gebied die aantal "0". Alhoewel hierdie argument oor die algemeen kan weggelaat word.

   In die veld "Tipe", ons spesifiseer aan die begin of aan die einde van die maand betaling gemaak word. Ons, as in die meeste gevalle, is dit geproduseer word aan die einde van die maand. Daarom het ons die aantal "0". Soos in die geval van die vorige argument, is dit moontlik om iets te gaan na die veld, dan is die verstek program sal aanvaar dat dit nul gelyk aan dit.

   Na al die data is ingeskryf, druk die "OK" -knoppie.



4. Daarna het in die sel wat ons in die eerste paragraaf van die handleiding toegeken, die resultaat van die berekening vertoon. Soos jy kan sien, die grootte van die maandelikse algemene betaling op die lening is 23536,74 roebels. Laat jou nie die teken verwar "-" voor hierdie bedrag. So Exile dui daarop dat hierdie is die vloei van geld, dit is, 'n verlies.



5. Met die oog op die totale bedrag van betaling vir die hele lening tydperk bereken, met inagneming van die terugbetaling van die liggaam van 'n lening en maandelikse rente, eerder vermeerder die bedrag van die maandelikse betaling (23536,74 roebels) vir die aantal maande (24 maande ). Soos jy kan sien, die totale bedrag van die betalings vir die hele lening tydperk in ons geval was 564.881,67 roebels.



6. Nou kan jy die bedrag van die lening oorbetaling te bereken. Om dit te doen, wat nodig is om weg te neem van die totale bedrag van die betalings op die lening, insluitende rente en die lening liggaam dit is, die aanvanklike bedrag geëis. Maar ons onthou dat die eerste van hierdie waardes reeds met die teken "-". Daarom, in die besonder, ons geval is dit blyk dat hulle nodig het om gevou. Soos ons sien, die totale oorbetaling van die lening oor die hele tydperk was 64881,67 roebels.

Les: Meester van funksies in Excel

Fase 2: Betalingsbesonderhede

En nou, met die hulp van ander Excel operateurs, maak ons ​​'n maandelikse detail van betalings om te sien hoeveel in 'n spesifieke maand ons betaal deur die liggaam van die lening, en hoeveel is die bedrag van die rente. Vir hierdie doeleindes, smid in die ballingskap tafel, wat ons in die data sal vul. Die lyne van hierdie tafel verantwoordelik vir die ooreenstemmende tydperk, dit wil sê, die maand sal wees. Gegewe dat die tydperk van die lening is 24 maande, die aantal rye sal ook gepas wees. Die kolomme aangedui 'n lening liggaam, rentebetalings, 'n totale maandelikse betaling, wat is die som van die vorige twee kolomme, sowel as die res van die bedrag om te betaal.

1. Om die bedrag van die betaling bepaal deur die liggaam van die lening, gebruik die OSP funksie, wat net bedoel is vir hierdie doeleindes. Ons vestig die wyser in die sel, wat geleë is in die lyn "1" en in die kolom "betaling deur die liggaam van die lening". Klik op die "Plak Function" knoppie.



2. Gaan na die Meester van funksies. In die kategorie "Finansiële", let ons die naam "OSPLT" en klik op die "OK" knoppie.



3. Die argumente van die OSP operateur argumente begin. Dit het die volgende opdrag:

   = Ospult (koers, tydperk; KPER; PS; BS)

   Soos ons kan sien, die argumente van hierdie funksie byna heeltemal saam te val met die argumente van die PLT operateur, net in plaas van die opsionele argument "Tipe" het 'n verpligte argument "tydperk". Dit dui op die aantal van die tydperk te betaal, en in ons spesifieke geval van die aantal van die maand.

   Vul die argumente van die OSR funksie argumente reeds bekend aan ons deur dieselfde data, wat gebruik is vir die PL funksie. Net die lig van die feit dat in die toekoms, die kopiëring van 'n formule sal gebruik word deur 'n vul merker, moet jy al die skakels in die velde doen absolute sodat hulle nie verander nie. Dit vereis 'n dollar teken voor elke waarde van vertikale en horisontale koördinate sit. Maar dit is makliker om dit te doen, net kies die koördinate en kliek op die F4 funksie sleutel. Die dollar teken sal op die regte plekke geplaas word outomaties. Ons het ook nie vergeet dat die jaarlikse verbintenis moet verdeel word in 12.



4. Maar ons het nog 'n nuwe argument, wat nie uit die PL funksie was. Hierdie argument "tydperk". In die toepaslike veld, stel 'n verwysing na die eerste sel van die kolom "tydperk". Hierdie element van die vel bevat die aantal "1", wat die getal van die eerste maand van die lening dui. Maar in teenstelling met die vorige velde, laat ons die skakel relatief in die gespesifiseerde veld, en nie absolute nie daaruit.

   Na al die data waaroor ons hierbo gepraat word bekendgestel, druk die "OK" knoppie.



5. Daarna het in die sel, wat ons voorheen toegeken, die bedrag van die betaling deur die liggaam van die lening vir die eerste maand sal verskyn. Dit sal wees 18536,74 roebels.



6. Dan, soos hierbo genoem, ons moet hierdie formule kopieer na die res van die kolom selle met behulp van 'n vul merker. Om dit te doen, stel die wyser na die onderste regterhoek van die sel, wat die formule bevat. Die wyser is omgeskakel na 'n kruis, wat 'n vul merker genoem. Klik op die links muis knoppie en trek dit af na die einde van die tafel.



7. As gevolg hiervan word alle selkolomme gevul. Nou het ons 'n grafiek van die betaling van 'n lening maandelikse. Soos hierbo genoem, verhoog die bedrag van die betaling op hierdie artikel met elke nuwe tydperk.



8. Nou moet ons 'n maandelikse betaling berekening van rente te maak. Vir hierdie doeleindes sal ons die PRT-operateur gebruik. Ons ken die eerste leë sel in die kolom "Betalingspersentasie" toe. Klik op die knoppie "Plakfunksie".



9. In die funksies van die Meester van funksies in die kategorie "Finansiële", produseer ons die name van die NAMP. Doen 'n klik op die "OK" knoppie.



10. Die argumente venster van die SS funksie begin. Die sintaksis lyk soos volg:

    = PRT (koers; tydperk; CPU; PS; BS)

    Soos ons kan sien, is die argumente van hierdie funksie absoluut identies aan soortgelyke elemente van die OSP-operateur. Daarom, gaan net die dieselfde data in die venster wat ons in die vorige venster van die argumente ingegaan. Ons vergeet nie dat die verwysing in die "periode" -veld relatief moet wees nie, en in alle ander velde moet die koördinate tot die absolute vorm gebring word. Daarna, klik op die "OK" knoppie.



11. Dan word die resultaat van die berekening van die bedrag van die betaling vir rente vir 'n lening vir die eerste maand in die ooreenstemmende sel vertoon.



12. Die toepassing van die vulling van merker, maak die kopiëring van die formule in die oorblywende elemente van die kolom, op hierdie manier ontvang 'n maandelikse skedule vir persentasies vir die lening. Soos ons kan sien, as dit vroeër gesê, van maand tot maand die waarde van hierdie tipe van betaling verminder.



13. Nou moet ons die algehele maandelikse betaling bereken. Vir hierdie berekening moet 'n mens nie aan enige operateur toevlug nie, aangesien u die eenvoudige rekenkundige formule kan gebruik. Ons vou die inhoud van die selle van die eerste maand van die kolomme "betaling deur die lening van die lening" en "volle rente". Om dit te doen, stel die teken "=" in die eerste leë sel van die kolom "totale maandelikse betaling". Klik dan op die twee bogenoemde elemente deur die "+" teken tussen hulle te stel. Klik op die Enter sleutel.



14. Volgende, met behulp van die vulling merker, as in die vorige gevalle, vul in die kolom data. Soos ons kan sien in die hele aksie van die kontrak, sal die bedrag van die totale maandelikse betaling, wat die betaling van die lening van die lening en betaling van rente insluit, 23536.74 roebels insluit. Eintlik is ons reeds bereken hierdie aanwyser voordat die gebruik van PPT. Maar in hierdie geval is dit meer duidelik aangebied, juis as die bedrag van die betaling deur die liggaam van die lening en rente.



15. Nou moet jy data by te voeg aan die kolom, waar die balans van die leningsbedrag maandelikse vertoon, wat nog moet betaal. In die eerste sel van die kolom "Balans te betaal" sal die berekening van die maklikste wees. Ons moet weg van die aanvanklike lening grootte, wat vermeld in die tabel met primêre data, die betaling deur die liggaam van die lening vir die eerste maand in die berekende tafel geneem moet word. Maar, gegewe die feit dat een van die getalle wat ons reeds saam met die teken "-", dan moet hulle nie weggeneem nie, maar om te vou. Ons maak dit en klik op die Enter knoppie.



16. Maar die berekening van die balans te betaal nadat die tweede en daaropvolgende maande sal wees ietwat meer ingewikkeld. Om dit te doen, moet ons weg te neem van die liggaam van die lening aan die begin van die lening die totale bedrag van die betalings deur die liggaam van die lening vir die vorige tydperk. Die installering van die "=" teken in die tweede sel van die kolom "Palace te betaal". Volgende, spesifiseer 'n skakel na die sel, wat die aanvanklike leningsbedrag bevat. Ons maak dit absolute, met die klem en druk die F4-sleutel. Dan sit ons die teken "+", want ons het die tweede betekenis en so negatief. Daarna, kliek op die "funksie Voeg" knoppie.



17. Die meester van funksies is van stapel gestuur, in wat jy nodig om te skuif na die kategorie "Wiskundige". Daar toeken ons die inskripsie "somme" en druk die "OK" knoppie.



18. Die venster argumente begin die funksie argumente. Die gespesifiseerde operateur dien om die data op te som in die selle wat ons nodig het om uit te voer in die "betaling deur die Lening Liggaam" kolom. Dit het die volgende opdrag:

    = Somme (nommer1; nommer2; ...)

    As argumente, verwysings na selle waarin getalle is vervat. Ons stel die wyser in die veld "nommer 1". pen dan die links muis knoppie en kies die eerste twee selle van die kolom Credit Liggaam op die vel. In die veld, soos ons sien, 'n skakel na die reeks verskyn. Dit bestaan ​​uit twee dele geskei deur die kolon: verwysings na die eerste reeks van die reeks en op die laaste een. Ten einde in staat te wees om die gespesifiseerde formule in die toekoms kopieer deur 'n vul merker, maak ons ​​die eerste skakel na die absolute omvang. Ons beklemtoon dit en klik op die F4 funksie sleutel. Die tweede deel van die verwysing en verlof familielid. Nou, wanneer die gebruik van 'n vul merker, die eerste reeks van die reeks sal bepaal moet word, en laasgenoemde sal strek as dit afwaarts beweeg. Dit is nodig dat ons die doelwitte te vervul. Nou, klik op die "OK" knoppie.



19. So, is die gevolg van die balans van krediet skuld ná die tweede maand ontslaan in die sel. Nou, wat begin met hierdie sel, ons maak die kopiëring van die formule in leë kolom elemente met behulp van 'n vul merker.



20. Maandelikse berekening van oorblyfsels te betaal op die lening is gemaak vir die hele krediet tydperk. Soos dit moet wees, aan die einde van die sperdatum, hierdie bedrag is nul.

Dus, het ons nie net bereken die lening betaal, maar georganiseerde 'n soort van krediet sakrekenaar. Wat sal optree op 'n annuïteit skema. As ons in die bronabel is, verander ons byvoorbeeld die bedrag van die lening en die jaarlikse rentekoers, dan in die finale tabel sal daar outomatiese data-herberekening wees. Daarom kan dit nie net een keer gebruik word vir 'n bepaalde saak, maar om aansoek te doen in verskillende situasies te bereken krediet opsies op 'n annuïteit skema.

Les: Finansiële funksies in Excel

Soos u kan sien, kan u die Excel-program by die huis gebruik, kan u die algehele maandelikse leningsbetaling op die annuïteitskema maklik bereken deur die PL-operateur vir hierdie doeleindes te gebruik. Daarbenewens, met die hulp van OSR funksies en die PRT, is dit moontlik om die bedrag van die betalings te bereken deur die liggaam van die lening en persentasies vir die bepaalde tydperk. Die toepassing van al hierdie bagasie funksies saam, is dit moontlik om 'n kragtige krediet sakrekenaar wat meer as een keer aan die annuïteit betaling bereken kan word te skep.