Voordat u 'n lening aanneem, sal dit lekker wees om alle betalings daarop te bereken. Dit sal die lener in die toekoms red van verskeie onverwagte probleme en teleurstellings wanneer dit blyk dat die oorbetaling te groot is. Excel-gereedskap kan in hierdie berekening help. Kom ons uitvind hoe om die annuïteitsleningbetalings in hierdie program te bereken.
Berekening van betaling
Allereerst moet ek sê dat daar twee tipes kredietbetalings is:
- gedifferensieerde;
- Annuïteit.
Met 'n gedifferensieerde skema maak die kliënt 'n maandelikse gelyke deel van die betalings aan die bank op die leningsorgaan plus rentebetalings. Die bedrag van rentebetalings elke maand verminder namate die leningsliggaam waarvan hulle bereken word, verminder. So word die totale maandelikse betaling ook verminder.
'N Annuïteitsskema gebruik 'n effens ander benadering. Die maandelikse kliënt maak dieselfde bedrag van die totale betaling, wat uit betalings op die leningsliggaam en rentebetalings bestaan. Aanvanklik word rentebetalings verantwoordelik vir die volle bedrag van die lening, maar soos die liggaam afneem, word rente verminder. Maar die totale bedrag van die betaling bly onveranderd as gevolg van die maandelikse toename in betalings op die lening. So, met verloop van tyd, neem die verhouding van rente in die totale maandelikse betaling af, terwyl die persentasie betaling per liggaam toeneem. Terselfdertyd verander die totale maandelikse betaling self nie deur die hele leningsperiode nie.
Net op die berekening van die annuïteitsbetaling stop ons. Daarbenewens is dit relevant, aangesien die meeste banke tans hierdie spesifieke skema gebruik. Dit is ook gerieflik vir kliënte, want in hierdie geval verander die totale bedrag nie, bly vaste. Kliënte weet altyd hoeveel om te betaal.
Fase 1: Maandelikse Fooi Berekening
Om die maandelikse fooi te bereken wanneer u die annuïteitsskema in Excel gebruik, is daar 'n spesiale funksie - PMT. Dit behoort tot die kategorie van finansiële operateurs. Die formule vir hierdie funksie is soos volg:
= PMT (koers; nper; ps; bs; tipe)
Soos u kan sien, het die gespesifiseerde funksie 'n redelike groot aantal argumente. Ware, die laaste twee van hulle is nie verpligtend nie.
argument "Bet" dui die rentekoers vir 'n spesifieke tydperk aan. As byvoorbeeld die jaarkoers gebruik word, maar die lening maandeliks betaal word, moet die jaarlikse koers gedeel word met 12 en gebruik die resultaat as 'n argument. Indien die kwartaallikse betaalmetode gebruik word, moet die jaarkoers in hierdie geval verdeel word 4 en so aan
"NPER" dui die totale aantal leningterugbetalingsperiodes aan. Dit is, as 'n lening vir een jaar met 'n maandelikse betaling geneem word, dan word die aantal tydperke oorweeg 12as vir twee jaar, dan is die aantal tydperke 24. As 'n lening vir twee jaar geneem word met kwartaallikse betaling, dan is die aantal tydperke gelyk aan 8.
"Ps" dui die huidige waarde aan. In eenvoudige terme is dit die totale bedrag van die lening aan die begin van die lening, dit is die bedrag wat jy leen, met uitsluiting van rente en ander addisionele betalings.
"BS" - dit is die toekomstige waarde. Hierdie waarde, wat die lening van die lening sal wees ten tye van die voltooiing van die leningsooreenkoms. In die meeste gevalle is hierdie argument "0", aangesien die lener aan die einde van die leningsperiode die lener ten volle moet betaal. Die gespesifiseerde argument is opsioneel. As dit dan val, word dit as nul beskou.
argument "Tipe" bepaal die tyd van berekening: 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 jy dit weglaat, word dit beskou as nul.
Nou is dit tyd om na 'n spesifieke voorbeeld te beweeg om die maandelikse fooi te bereken deur die PMT-funksie te gebruik. Vir berekening gebruik ons 'n tabel met die oorspronklike data, waar die rentekoers op die lening aangedui word (12%), leningsbedrag (500,000 roebels) en die leningstermyn (24 maande). In hierdie geval word die betaling maandeliks aan die einde van elke periode gedoen.
- Kies die element op die blad waarin die berekeningsresultaat vertoon sal word en klik op die ikoon "Voeg funksie in"naby die formulebalk geplaas.
- Die venster word van stapel gestuur. Funksie Meesters. In die kategorie "Finansiële" kies die naam "PMT" en klik op die knoppie "OK".
- Daarna word die venster van die operateur se argument oop. PMT.
In die veld "Bet" moet die bedrag van rente vir die tydperk invul. Dit kan handmatig gedoen word deur net 'n persentasie te plaas, maar hier word dit in 'n aparte sel op die blad gelys, dus gee ons 'n skakel. Stel die wyser in die veld en klik dan op die ooreenstemmende sel. Maar, soos ons onthou, het ons 'n jaarlikse rentekoers in die tabel, en die betalingstydperk is gelykstaande aan 'n maand. Daarom verdeel ons die jaarlikse koers, of eerder die verwysing na die sel waarin dit voorkom, deur die nommer 12wat ooreenstem met die aantal maande in 'n jaar. Die afdeling word direk in die veld van die argument venster uitgevoer.
In die veld "NPER" stel die leningsperiode in. Hy is gelyk aan ons 24 maande. U kan 'n nommer in die veld invoer 24 handmatig, maar ons, soos in die vorige geval, verskaf 'n skakel na die ligging van hierdie aanwyser in die oorspronklike tabel.
In die veld "Ps" dui die aanvanklike waarde van die lening aan. Sy is gelyk aan 500,000 roebels. Soos in die vorige gevalle, dui ons die verwysing na die element van die vel met hierdie aanwyser aan.
In die veld "BS" dui die bedrag van die lening aan, na volle betaling. Soos ons onthou, is hierdie waarde amper altyd nul. Stel die nommer in hierdie veld "0". Alhoewel hierdie argument geheel en al weggelaat kan word.
In die veld "Tipe" dui aan aan die begin of aan die einde van die maand betaling is gemaak. Ons, soos in die meeste gevalle, word aan die einde van die maand gemaak. Stel dus die nommer in "0". Soos in die geval van die vorige argument, in hierdie veld kan jy niks invoer nie, dan sal die program standaard aanvaar dat daar 'n waarde gelyk aan nul is.
Nadat al die data ingevoer is, klik op die knoppie "OK".
- Daarna word die resultaat van die berekening vertoon in die sel wat ons in die eerste paragraaf van hierdie handleiding gekies het. Soos u kan sien, is die waarde van die maandelikse totale lening betaal 23536.74 roebels. Moet nie verwar word deur die "-" teken voor hierdie bedrag nie. Excel wys daarop dat dit 'n kontantvloei is, dit is 'n verlies.
- Ten einde die totale bedrag van die betaling vir die hele leningsperiode te bereken, met inagneming van die terugbetaling van die leningsliggaam en die maandelikse rente, is dit genoeg om die bedrag van die maandelikse betaling te vermeerder (23536.74 roebels) vir die aantal maande (24 maande). Soos u kan sien, het die totale bedrag van die betalings vir die totale leningsperiode in ons geval beloop 564881.67 roebels.
- Nou kan jy die bedrag van die oorbetaling op die lening bereken. Hiervan moet u wegneem van die totale bedrag van die betalings op die lening, insluitende rente en die lening van die lening, die aanvanklike bedrag geleen. Maar ons onthou dat die eerste van hierdie waardes reeds onderteken is "-". Daarom, in ons besondere geval, blyk dit dat hulle gevou moet word. Soos u kan sien, het die totale bedrag van die oorbetaling op die lening vir die hele tydperk beloop 64881.67 roebels.
les: Excel funksie wizard
Fase 2: Betaalbesonderhede
En nou, met die hulp van ander operateurs, sal Excel maandelikse betalingsbesonderhede maak om te sien hoeveel ons op 'n bepaalde maand op die leningsliggaam betaal en hoeveel rente dit beloop. Vir hierdie doeleindes teken ons in Excel 'n tabel wat ons met data sal invul. Die rye van hierdie tabel sal ooreenstem met die ooreenstemmende tydperk, dit is die maand. Gegee dat die kredietydperk wat ons het, is 24 maand, dan sal die aantal rye ook gepas wees. Die kolomme dui op die betaling van die leningsliggaam, rentebetalings, die totale maandelikse betaling, wat die som van die vorige twee kolomme is, asook die oorblywende bedrag wat betaal moet word.
- Om die bedrag van die betaling op die lening van die lening te bepaal, gebruik die funksie OSPLTwat net vir hierdie doel bedoel is. Stel die wyser in die sel, wat in die lyn is "1" en in die kolom "Betaling op die lening van die lening". Ons druk die knoppie "Voeg funksie in".
- Gaan na Funksie Wizard. In die kategorie "Finansiële" Merk die naam "OSPLT" en druk die knoppie "OK".
- Die argument venster van die OSPLT operateur word geloods. Dit het die volgende sintaksis:
= OSPLT (Rate; Periode; Kper; Ps; Bs)Soos u kan sien, val die argumente van hierdie funksie amper heeltemal saam met die argumente van die operateur PMT, net in plaas van die opsionele argument "Tipe" Bygevoegde vereiste argument "Periode". Dit dui die nommer van die terugbetalingsperiode aan, en in ons besondere geval die nommer van die maand.
Vul die bekende funksie venster velde in OSPLT dieselfde data wat vir die funksie gebruik is PMT. Slegs met inagneming van die feit dat in die toekoms kopiëring van die formule deur die vulpenne gebruik sal word, is dit nodig om al die skakels in die velde absoluut te maak sodat dit nie verander nie. Om dit te kan doen, moet jy 'n dollar teken voor elke waarde van die koördinate vertikaal en horisontaal plaas. Maar dit is makliker om dit te doen deur eenvoudig die koördinate te kies en die funksie sleutel te druk. F4. Die dollar teken sal outomaties op die regte plekke geplaas word. Vergeet ook nie dat die jaarkoers verdeel moet word nie 12.
- Maar ons het nog 'n nuwe argument, wat die funksie nie gehad het nie PMT. Hierdie argument "Periode". In die ooreenstemmende veld stel ons die skakel na die eerste sel van die kolom. "Periode". Hierdie bladitem bevat 'n nommer "1"wat die nommer van die eerste maand van kredieting aandui. Maar in teenstelling met die vorige velde, verlaat ons die skakel relatief in die gespesifiseerde veld, en ons maak nie 'n absolute verwysing daaruit nie.
Nadat al die data waaroor ons gepraat het, ingeskryf is, klik op die knoppie "OK".
- Daarna, in die sel wat ons voorheen toegeken het, sal die bedrag van die betaling op die leningsliggaam vir die eerste maand vertoon word. Sy sal maak 18536.74 roebels.
- Dan, soos hierbo genoem, moet ons hierdie formule na die oorblywende selle van die kolom kopieer met die vulpen. Om dit te doen, stel die wyser in die onderste regterkantste hoek van die sel, wat die formule bevat. Die wyser word dan na 'n kruis omskep, wat 'n vullerwyser genoem word. Hou die linker muisknop vas en sleep dit na die einde van die tafel.
- As gevolg hiervan word al die selle in die kolom gevul. Nou het ons 'n maandelikse lening terugbetaling skedule. Soos hierbo genoem, verhoog die bedrag van die betaling vir hierdie artikel met elke nuwe tydperk.
- Nou moet ons 'n maandelikse berekening van rentebetalings maak. Vir hierdie doeleindes gebruik ons die operateur IPMT. Kies die eerste leë sel in die kolom. "Rentebetaling". Ons druk die knoppie "Voeg funksie in".
- In die opstartvenster Funksie Meesters in kategorie "Finansiële" maak die keuse van die naam IPMT. Voer 'n klik op die knoppie. "OK".
- Die funksie argument venster begin. IPMT. Die sintaksis is soos volg:
= PRPLT (Rate; Periode; Kper; Ps; Bs)Soos u kan sien, is die argumente van hierdie funksie absoluut identies aan dié van die operateur OSPLT. Daarom gee ons net die data in wat ons in die vorige argument venster ingevoer het. Moenie vergeet dat die skakel in die veld nie "Periode" moet relatief wees, en in alle ander velde moet die koördinate tot absolute vorm verminder word. Klik daarna op die knoppie "OK".
- Dan word die uitslag van die berekening van die bedrag van die betaling op rente vir die lening vir die eerste maand in die toepaslike sel vertoon.
- Nadat u die vulsteker toegepas het, maak ons 'n afskrif van die formule aan die oorblywende elemente van die kolom, waardeur u 'n maandelikse betalingskedule vir rente op die lening verkry. Soos u kan sien, soos vroeër gesê, verminder die waarde van hierdie tipe betaling van maand tot maand.
- Nou moet ons die totale maandelikse betaling bereken. Vir hierdie berekening moet u nie aan enige operateur toevlug nie, aangesien u 'n eenvoudige rekenkundige formule kan gebruik. Vou die inhoud van die selle van die eerste maand van die kolomme "Betaling op die lening van die lening" en "Rentebetaling". Om dit te doen, stel die teken in "=" in die eerste leë kolom sel "Totale maandelikse betaling". Klik dan op die bogenoemde twee elemente en plaas die teken tussen hulle "+". Ons druk op die sleutel Tik.
- Vervolgens vul die kolom met data, soos in die vorige gevalle, met die vulpenmerk. Soos u kan sien, sal die bedrag van die totale maandelikse betaling, insluitend betaling op die leningsliggaam en rente betaling, gedurende die duur van die kontrak wees 23536.74 roebels. Eintlik het ons hierdie syfer vroeër bereken met behulp van PMT. Maar in hierdie geval word dit duideliker aangebied, presies soos die bedrag van die betaling op die lening en rente.
- Nou moet jy data by die kolom voeg waar die balans van die leningsbedrag wat nog betaal moet word maandeliks vertoon word. In die eerste sel van die kolom "Balans betaalbaar" Die berekening sal die maklikste wees. Ons moet wegneem van die aanvanklike grootte van die lening, wat in die tabel met die primêre data getoon word, die betaling op die lening liggaam vir die eerste maand in die berekeningstabel. Maar, gegewe die feit dat een van die getalle wat ons reeds het, 'n teken het "-", hulle moet nie weggeneem word nie, maar gevou word. Doen dit en klik op die knoppie. Tik.
- Maar die berekening van die saldo wat na die tweede en daaropvolgende maande betaal moet word, sal ietwat moeiliker wees. Om dit te kan doen, moet ons die totale bedrag van die betalings op die leningsliggaam aftrek vir die vorige tydperk van die lening aan die begin van die lening. Stel die teken "=" in die tweede sel van die kolom "Balans betaalbaar". Gee vervolgens die skakel aan die sel wat die aanvanklike leningsbedrag bevat. Ons maak dit absoluut deur die sleutel te kies en te druk. F4. Sit dan 'n teken "+", aangesien die tweede waarde vir ons negatief sal wees. Klik daarna op die knoppie "Voeg funksie in".
- begin Funksie Wizardwaarin jy moet skuif na die kategorie "Wiskundige". Daar kies ons die opskrif "Som" en klik op die knoppie "OK".
- Die funksie argument venster begin. som. Die gespesifiseerde operateur dien die data in die selle op te som, wat ons in die kolom moet uitvoer "Betaling op die lening van die lening". Dit het die volgende sintaksis:
= SUM (nommer1; getal2; ...)Die argumente is verwysings na selle wat getalle bevat. Ons stel die wyser in die veld. "Nommer 1". Dan hou ons die linker muisknop vas en kies die eerste twee selle van die kolom op die vel. "Betaling op die lening van die lening". Soos u kan sien, word die skakel na die reeks vertoon in die veld. Dit bestaan uit twee dele geskei deur 'n kolon: verwysings na die eerste sel van die reeks en die laaste. Ten einde die aangeduide formule in die toekoms deur middel van 'n vulpenmerk te kan kopieer, maak ons die eerste deel van die verwysing na die absolute omvang. Kies dit en klik op die funksie sleutel. F4. Die tweede deel van die skakel word relatief gelaat. Nou wanneer die vul merker gebruik word, sal die eerste sel van die reeks vasgemaak word, en die laaste sal strek soos dit beweeg. Dit is wat ons nodig het om ons doelwitte te bereik. Klik dan op die knoppie "OK".
- Dus, die resultaat van die kredietskuldbalans na die tweede maand word in die sel vertoon. Nou, vanaf hierdie sel, kopieer ons die formule in die leë elemente van die kolom met die vulpen.
- Maandelikse berekening van leningsaldo's is vir die hele kredietperiode gemaak. Soos dit moet wees, is hierdie bedrag aan die einde van die kwartaal nul.
So, ons het nie net die berekening van betaling op die lening gemaak nie, maar het 'n soort leningrekenaar georganiseer. Wat sal onder die annuïteitsskema werk. As ons byvoorbeeld in die bron tabel die grootte van die lening en die jaarlikse rentekoers verander, dan word die data outomaties herbereken in die finale tabel. Daarom kan dit nie net een keer vir 'n spesifieke geval gebruik word nie, maar kan dit in verskillende situasies gebruik word om leningopsies te bereken deur die annuïteitsskema te gebruik.
les: Finansiële funksies in Excel
Soos u kan sien, gebruik Excel by die huis, kan u maklik die totale maandelikse leningbetaling met behulp van die annuïteitsskema bereken deur die operateur te gebruik. PMT. Daarbenewens, met behulp van funksies OSPLT en IPMT U kan die bedrag van die betalings op die lening van die lening en op rente vir die bepaalde tydperk bereken. Deur al hierdie bagasie van funksies toe te pas, is dit moontlik om 'n kragtige leningsrekenaar te skep wat meer as een keer gebruik kan word om die annuïteitsbetaling te bereken.
