Cyfrifo taliad blwydd-dal yn Microsoft Excel

Cyn cymryd benthyciad, byddai'n braf cyfrifo'r holl daliadau arno. Bydd hyn yn arbed y benthyciwr yn y dyfodol rhag amryw drafferthion a siom annisgwyl pan fydd yn ymddangos bod y gordaliad yn rhy fawr. Gall offer Excel helpu gyda'r cyfrifiad hwn. Gadewch i ni ddarganfod sut i gyfrifo taliadau benthyciad blwydd-dal yn y rhaglen hon.

Cyfrifiad taliad

Yn gyntaf oll, rhaid dweud bod dau fath o daliad benthyciad:

• Gwahaniaethol;
• Blwydd-dal.

Mewn cynllun gwahaniaethol, mae'r cleient yn gwneud symiau cyfartal o daliadau ar y corff benthyciadau ynghyd â thaliadau llog yn fisol i'r banc. Mae swm y taliadau llog yn lleihau bob mis, wrth i gorff y benthyciad y cânt eu cyfrif ohono ostwng. Felly, mae cyfanswm y taliad misol hefyd yn cael ei leihau.

Mae cynllun blwydd-dal yn defnyddio dull ychydig yn wahanol. Mae'r cleient yn fisol yn gwneud yr un faint o gyfanswm y taliad, sy'n cynnwys taliadau ar y corff benthyciadau a thaliadau llog. I ddechrau, mae taliadau llog yn cael eu cyfrif ar gyfer swm cyfan y benthyciad, ond wrth i'r corff leihau, mae croniad llog yn lleihau. Ond mae cyfanswm y taliad yn aros yr un fath oherwydd y cynnydd misol yn swm y taliadau ar y corff benthyciadau. Felly, dros amser, mae canran y llog yng nghyfanswm y taliad misol yn gostwng, ac mae cyfran y taliad yn ôl corff yn cynyddu. At hynny, nid yw cyfanswm y taliad misol ei hun yn newid trwy gydol tymor y benthyciad.

Ar ôl cyfrifo'r taliad blwydd-dal, byddwn yn dod i ben. Ar ben hynny, mae hyn yn berthnasol, oherwydd ar hyn o bryd mae'r mwyafrif o fanciau'n defnyddio'r cynllun penodol hwn. Mae'n gyfleus i gwsmeriaid, oherwydd yn yr achos hwn nid yw cyfanswm y taliad yn newid, gan aros yn sefydlog. Mae cwsmeriaid bob amser yn gwybod faint i'w dalu.

Cam 1: cyfrifiad rhandaliadau misol

Ar gyfer cyfrifo'r cyfraniad misol wrth ddefnyddio'r cynllun blwydd-dal yn Excel mae swyddogaeth arbennig - PMT. Mae'n perthyn i'r categori gweithredwyr ariannol. Mae'r fformiwla ar gyfer y swyddogaeth hon fel a ganlyn:

= PLT (cyfradd; nper; ps; bs; math)

Fel y gallwch weld, mae gan y swyddogaeth hon nifer eithaf mawr o ddadleuon. Yn wir, mae'r ddau olaf ohonynt yn ddewisol.

Dadl Bid yn nodi'r gyfradd llog am gyfnod penodol. Er enghraifft, os defnyddir y gyfradd flynyddol, ond bod y benthyciad yn cael ei dalu bob mis, yna dylid rhannu'r gyfradd flynyddol â 12 a defnyddio'r canlyniad fel dadl. Os defnyddir math chwarterol o daliad, yna yn yr achos hwn dylid rhannu'r gyfradd flynyddol â 4 ac ati.

"Nper" yn nodi cyfanswm nifer y cyfnodau ad-dalu benthyciad. Hynny yw, os cymerir benthyciad am flwyddyn gyda thaliad misol, yna ystyrir nifer y cyfnodau 12os am ddwy flynedd, yna mae nifer y cyfnodau 24. Os cymerir y benthyciad am ddwy flynedd gyda thaliad chwarterol, yna mae nifer y cyfnodau yn gyfartal 8.

Ps yn nodi'r gwerth presennol ar hyn o bryd. Mewn geiriau syml, dyma gyfanswm y benthyciad ar ddechrau'r benthyciad, hynny yw, y swm rydych chi'n ei fenthyg, ac eithrio llog a thaliadau ychwanegol eraill.

"Bs" yw'r gwerth yn y dyfodol. Y gwerth hwn, a fydd corff y benthyciad ar adeg cwblhau'r cytundeb benthyciad. Yn y rhan fwyaf o achosion, mae'r ddadl hon yn "0", gan fod yn rhaid i'r benthyciwr ar ddiwedd tymor y benthyciad dalu'r benthyciwr yn llawn. Mae'r ddadl benodol yn ddewisol. Felly, os yw'n gostwng, yna fe'i hystyrir yn hafal i sero.

Dadl "Math" yn pennu'r amser cyfrifo: ar ddiwedd neu ar ddechrau'r cyfnod. Yn yr achos cyntaf, mae'n cymryd y gwerth "0"ac yn yr ail - "1". Mae'r rhan fwyaf o sefydliadau bancio yn defnyddio'r union opsiwn gyda thaliad ar ddiwedd y cyfnod. Mae'r ddadl hon hefyd yn ddewisol, ac os caiff ei hepgor ystyrir ei bod yn sero.

Nawr mae'n bryd symud ymlaen at enghraifft benodol o gyfrifo'r rhandaliad misol gan ddefnyddio'r swyddogaeth PMT. Ar gyfer y cyfrifiad, rydym yn defnyddio'r tabl gyda'r data ffynhonnell, lle mae'r gyfradd llog ar y benthyciad wedi'i nodi (12%), swm y benthyciad (500,000 rubles) a thymor y benthyciad (24 mis) Ar ben hynny, telir yn fisol ar ddiwedd pob cyfnod.

1. Dewiswch yr elfen ar y ddalen lle bydd canlyniad y cyfrifiad yn cael ei arddangos, a chliciwch ar yr eicon "Mewnosod swyddogaeth"wedi'i osod ger y bar fformiwla.



2. Mae'r ffenestr yn cael ei lansio. Dewiniaid Swyddogaeth. Yn y categori "Ariannol" dewiswch yr enw "PLT" a chlicio ar y botwm "Iawn".



3. Wedi hynny, mae ffenestr dadleuon gweithredwyr yn agor. PMT.

   Yn y maes Bid nodwch y ganran ar gyfer y cyfnod. Gellir gwneud hyn â llaw, dim ond trwy osod y ganran, ond rydym wedi'i nodi mewn cell ar wahân ar y ddalen, felly byddwn yn rhoi dolen iddi. Rydyn ni'n gosod y cyrchwr yn y maes, ac yna'n clicio ar y gell gyfatebol. Ond, fel rydyn ni'n cofio, yn ein tabl mae'r gyfradd llog flynyddol wedi'i gosod, ac mae'r cyfnod talu yn hafal i fis. Felly, rydym yn rhannu'r gyfradd flynyddol, neu yn hytrach y ddolen i'r gell y mae wedi'i chynnwys ynddi, i'r rhif 12sy'n cyfateb i nifer y misoedd mewn blwyddyn. Perfformir y rhaniad yn uniongyrchol ym maes ffenestr y ddadl.

   Yn y maes "Nper" pennir tymor benthyciad. Mae'n gyfartal â ni 24 am fisoedd. Gallwch nodi rhif yn y maes 24 â llaw, ond rydym ni, fel yn yr achos blaenorol, yn nodi dolen i leoliad y dangosydd hwn yn y tabl gwreiddiol.

   Yn y maes Ps Nodir swm y benthyciad cychwynnol. Mae hi'n gyfartal 500,000 rubles. Fel mewn achosion blaenorol, rydym yn nodi'r ddolen i'r elfen ddalen y mae'r dangosydd hwn wedi'i chynnwys ynddi.

   Yn y maes "Bs" yn nodi swm y benthyciad, ar ôl taliad llawn. Fel y cofiwn, mae'r gwerth hwn bron bob amser yn sero. Gosodwch y rhif yn y maes hwn "0". Er y gellir hepgor y ddadl hon yn gyfan gwbl.

   Yn y maes "Math" nodi ar ddechrau neu ar ddiwedd y mis y telir. Yma, fel yn y rhan fwyaf o achosion, mae'n cael ei gynhyrchu ar ddiwedd y mis. Felly, gosodwch y rhif "0". Fel yn achos y ddadl flaenorol, ni allwch nodi unrhyw beth yn y maes hwn, yna bydd y rhaglen yn tybio yn ddiofyn ei bod yn cynnwys gwerth sy'n hafal i sero.

   Ar ôl i'r holl ddata gael ei gofnodi, cliciwch ar y botwm "Iawn".



4. Ar ôl hynny, mae canlyniad y cyfrifiad yn cael ei arddangos yn y gell y gwnaethom dynnu sylw ati ym mharagraff cyntaf y llawlyfr hwn. Fel y gallwch weld, swm cyfanswm y taliad benthyciad misol yw 23536.74 rubles. Peidiwch â chael eich drysu gan yr arwydd “-” o flaen y swm hwn. Felly mae Excel yn nodi mai gwariant arian parod yw hwn, hynny yw, colled.



5. Er mwyn cyfrifo cyfanswm y taliad ar gyfer tymor cyfan y benthyciad, gan ystyried ad-daliad y corff benthyciad a'r llog misol, mae'n ddigon i luosi swm y taliad misol (23536.74 rubles) yn ôl nifer y misoedd (24 mis) Fel y gallwch weld, cyfanswm ein taliadau ar gyfer tymor cyfan y benthyciad yn ein hachos ni 564881.67 rubles.



6. Nawr gallwch chi gyfrifo swm y gordaliad ar y benthyciad. I wneud hyn, tynnwch o gyfanswm y taliadau ar y benthyciad, gan gynnwys llog a'r corff benthyciad, y swm cychwynnol a fenthycwyd. Ond rydyn ni'n cofio bod y cyntaf o'r gwerthoedd hyn eisoes wedi'i arwyddo "-". Felly, yn ein hachos ni ni yn benodol, mae'n ymddangos bod angen eu plygu. Fel y gallwch weld, cyfanswm y gordaliad benthyciad am y cyfnod cyfan 64 881.67 rubles.

Gwers: Dewin Nodwedd Excel

Cam 2: manylion talu

Ac yn awr, gyda chymorth gweithredwyr Excel eraill, byddwn yn gwneud manylion misol am daliadau i weld faint rydyn ni'n ei dalu ar fenthyciad mewn mis penodol, a faint yw'r llog. At y dibenion hyn, rydym yn tynnu tabl yn Excel y byddwn yn ei lenwi â data. Bydd y rhesi yn y tabl hwn yn cyfateb i'r cyfnod cyfatebol, hynny yw, i'r mis. O ystyried bod y cyfnod benthyca gyda ni 24 misoedd, yna bydd nifer y rhesi hefyd yn briodol. Mae'r colofnau'n nodi taliad y corff benthyciadau, taliad llog, cyfanswm y taliad misol, sef swm y ddwy golofn flaenorol, yn ogystal â'r swm sy'n weddill yn daladwy.

1. I bennu swm y taliad gan gorff benthyciad, defnyddiwch y swyddogaeth OSPLT, sydd wedi'i gynllunio at y dibenion hyn yn unig. Gosodwch y cyrchwr i'r gell sydd yn y rhes "1" ac yn y golofn "Ad-daliad ar gorff y benthyciad". Cliciwch ar y botwm "Mewnosod swyddogaeth".



2. Ewch i Dewin Nodwedd. Yn y categori "Ariannol" marciwch yr enw OSPLT a gwasgwch y botwm "Iawn".



3. Mae ffenestr dadleuon gweithredwr OSPLT yn cychwyn. Mae ganddo'r gystrawen ganlynol:

   = OSPLT (Bet; Cyfnod; Nper; Ps; BS)

   Fel y gallwch weld, mae dadleuon y swyddogaeth hon bron yn hollol gyd-fynd â dadleuon y gweithredwr PMT, dim ond yn lle dadl ddewisol "Math" ychwanegu'r ddadl ofynnol "Cyfnod". Mae'n nodi nifer y cyfnod talu, ac yn ein hachos ni ni, nifer y mis.

   Rydym yn llenwi meysydd sydd eisoes yn gyfarwydd yn y ffenestr dadl swyddogaeth OSPLT yr un data a ddefnyddiwyd ar gyfer y swyddogaeth PMT. Dim ond o ystyried y ffaith y bydd y fformiwla yn cael ei chopïo yn y dyfodol gan ddefnyddio'r marciwr llenwi, mae angen i chi wneud pob dolen yn y meysydd yn absoliwt fel na fyddant yn newid. I wneud hyn, rhowch arwydd doler o flaen pob gwerth cyfesuryn yn fertigol ac yn llorweddol. Ond mae'n haws gwneud hyn trwy dynnu sylw at y cyfesurynnau a phwyso'r allwedd swyddogaeth yn unig F4. Bydd yr arwydd doler yn cael ei osod yn y lleoedd iawn yn awtomatig. Peidiwch ag anghofio hefyd y dylid rhannu'r gyfradd flynyddol â 12.



4. Ond mae gennym un ddadl newydd arall nad oedd gan y swyddogaeth. PMT. Y ddadl hon "Cyfnod". Yn y maes cyfatebol, gosodwch y ddolen i gell gyntaf y golofn "Cyfnod". Mae'r elfen ddalen hon yn cynnwys rhif "1", sy'n nodi nifer y mis cyntaf o fenthyca. Ond yn wahanol i'r meysydd blaenorol, yn y maes penodedig rydym yn gadael y ddolen yn gymharol, ac nid ydym yn ei gwneud yn absoliwt.

   Ar ôl nodi'r holl ddata y buom yn siarad amdano, cliciwch ar y botwm "Iawn".



5. Ar ôl hynny, yn y gell a ddyrannwyd gennym o'r blaen, bydd swm yr ad-daliad ar y corff benthyciadau am y mis cyntaf yn cael ei arddangos. Bydd hi'n gwneud 18,536.74 rubles.



6. Yna, fel y soniwyd uchod, dylem gopïo'r fformiwla hon i weddill celloedd y golofn gan ddefnyddio'r marciwr llenwi. I wneud hyn, gosodwch y cyrchwr yng nghornel dde isaf y gell sy'n cynnwys y fformiwla. Mae'r cyrchwr yn cael ei drawsnewid yn groes, a elwir y marciwr llenwi. Daliwch botwm chwith y llygoden a'i lusgo i lawr i ddiwedd y bwrdd.



7. O ganlyniad, mae'r holl gelloedd yn y golofn wedi'u llenwi. Nawr mae gennym amserlen ad-dalu benthyciad misol. Fel y soniwyd uchod, mae swm y taliad o dan yr erthygl hon yn cynyddu gyda phob cyfnod newydd.



8. Nawr mae angen i ni wneud cyfrifiad misol o daliadau llog. At y dibenion hyn byddwn yn defnyddio'r gweithredwr PRPLT. Dewiswch y gell wag gyntaf yn y golofn Taliad Llog. Cliciwch ar y botwm "Mewnosod swyddogaeth".



9. Yn y ffenestr gychwyn Dewiniaid Swyddogaeth yn y categori "Ariannol" rydym yn gwneud y dewis PRPLT. Cliciwch ar y botwm. "Iawn".



10. Mae'r ffenestr dadleuon swyddogaeth yn cychwyn. PRPLT. Mae ei gystrawen fel a ganlyn:

    = PRPLT (Bet; Cyfnod; Nper; Ps; BS)

    Fel y gallwch weld, mae dadleuon y swyddogaeth hon yn hollol union yr un fath ag elfennau tebyg y gweithredwr OSPLT. Felly, rydym yn syml yn nodi'r un data yn y ffenestr a nodwyd gennym yn y ffenestr ddadl flaenorol. Nid ydym yn anghofio ar yr un pryd bod y ddolen yn y maes "Cyfnod" rhaid iddynt fod yn gymharol, ac ym mhob maes arall rhaid lleihau'r cyfesurynnau i ffurf absoliwt. Ar ôl hynny, cliciwch ar y botwm "Iawn".



11. Yna, mae canlyniad cyfrifo swm y llog ar y benthyciad am y mis cyntaf yn cael ei arddangos yn y blwch priodol.



12. Gan gymhwyso'r marciwr llenwi, rydym yn copïo'r fformiwla i'r elfennau sy'n weddill o'r golofn, gan sicrhau amserlen talu misol ar gyfer llog ar y benthyciad. Fel y gwelwn, fel y dywedwyd yn gynharach, o fis i fis mae gwerth y math hwn o daliad yn gostwng.



13. Nawr mae'n rhaid i ni gyfrifo cyfanswm y taliad misol. Ar gyfer y cyfrifiad hwn, ni ddylech droi at unrhyw weithredwr, oherwydd gallwch ddefnyddio fformiwla rhifyddeg syml. Ychwanegwch gynnwys celloedd mis cyntaf y colofnau "Ad-daliad ar gorff y benthyciad" a Taliad Llog. I wneud hyn, gosodwch yr arwydd "=" i gell wag gyntaf colofn "Cyfanswm y taliad misol". Yna rydym yn clicio ar y ddwy elfen uchod, gan osod arwydd rhyngddynt "+". Cliciwch ar yr allwedd Rhowch i mewn.



14. Nesaf, gan ddefnyddio'r marciwr llenwi, fel mewn achosion blaenorol, llenwch y golofn â data. Fel y gallwch weld, trwy gydol y contract, bydd cyfanswm y taliad misol, gan gynnwys taliad gan y corff benthyciadau a llog 23536.74 rubles. Mewn gwirionedd, gwnaethom gyfrifo'r dangosydd hwn eisoes gan ddefnyddio PMT. Ond yn yr achos hwn fe'i cyflwynir yn gliriach, yn union fel swm y taliad ar y corff benthyciadau a'r llog.



15. Nawr mae angen ichi ychwanegu'r data at y golofn, a fydd yn arddangos balans swm y benthyciad y mae angen ei dalu o hyd. Yng nghell gyntaf y golofn "Balans yn daladwy" y cyfrifiad fydd yr hawsaf. Mae angen i ni dynnu o swm cychwynnol y benthyciad, a nodir yn y tabl gyda'r data sylfaenol, y taliad ar sail y benthyciad am y mis cyntaf yn y tabl cyfrifo. Ond, o ystyried y ffaith bod un o'r rhifau gennym eisoes arwydd "-", yna ni ddylid eu cymryd i ffwrdd, ond eu plygu. Rydyn ni'n gwneud hyn ac yn clicio ar y botwm Rhowch i mewn.



16. Ond bydd cyfrifo'r balans sy'n ddyledus ar ôl yr ail fis a'r misoedd dilynol ychydig yn fwy cymhleth. I wneud hyn, mae angen i ni dynnu cyfanswm y taliadau ar y corff benthyciadau ar gyfer y cyfnod blaenorol o'r corff benthyciadau ar ddechrau'r benthyciad. Gosodwch yr arwydd "=" yn ail gell y golofn "Balans yn daladwy". Nesaf, rydym yn nodi'r ddolen i'r gell, sy'n cynnwys swm cychwynnol y benthyciad. Ei wneud yn absoliwt trwy dynnu sylw a phwyso'r allwedd F4. Yna rydyn ni'n rhoi arwydd "+", gan y bydd yr ail werth yn ein hachos ni yn negyddol. Ar ôl hynny, cliciwch ar y botwm "Mewnosod swyddogaeth".



17. Yn cychwyn Dewin Nodweddlle mae angen i chi symud i'r categori "Mathemategol". Yno, rydyn ni'n tynnu sylw at yr arysgrif SUM a chlicio ar y botwm "Iawn".



18. Mae'r ffenestr dadl swyddogaeth yn cychwyn SUM. Mae'r gweithredwr penodedig yn gwasanaethu i grynhoi'r data yn y celloedd, y mae'n rhaid i ni eu perfformio yn y golofn "Ad-daliad ar gorff y benthyciad". Mae ganddo'r gystrawen ganlynol:

    = SUM (rhif1; rhif2; ...)

    Mae'r dadleuon yn gyfeiriadau at gelloedd sy'n cynnwys rhifau. Rydyn ni'n gosod y cyrchwr i'r cae "Rhif1". Yna rydyn ni'n dal botwm chwith y llygoden i lawr ac yn dewis dwy gell gyntaf y golofn ar y ddalen "Ad-daliad ar gorff y benthyciad". Yn y maes, fel y gwelwn, arddangosir dolen i'r ystod. Mae'n cynnwys dwy ran, wedi'u gwahanu gan golon: cysylltiadau â chell gyntaf yr ystod ac i'r olaf. Er mwyn gallu copïo'r fformiwla benodol gan ddefnyddio'r marciwr llenwi yn y dyfodol, rydym yn gwneud rhan gyntaf y ddolen i'r ystod yn absoliwt. Dewiswch ef a chlicio ar yr allwedd swyddogaeth F4. Mae ail ran y ddolen yn dal i fod yn gymharol. Nawr, wrth ddefnyddio'r marciwr llenwi, bydd cell gyntaf yr ystod yn sefydlog, a bydd yr olaf yn ymestyn wrth iddi symud i lawr. Dyma beth sydd ei angen arnom i gyflawni ein nodau. Nesaf, cliciwch ar y botwm "Iawn".



19. Felly, mae canlyniad balans y ddyled credyd ar ôl yr ail fis yn cael ei arddangos yn y gell. Nawr, gan ddechrau o'r gell hon, rydyn ni'n copïo'r fformiwla yn elfennau colofn gwag gan ddefnyddio'r marciwr llenwi.



20. Cyfrifiad misol o falansau benthyciad ar gyfer holl gyfnod y benthyciad. Yn ôl y disgwyl, ar ddiwedd y tymor mae'r swm hwn yn sero.

Felly, ni wnaethom gyfrifo'r taliad ar y benthyciad yn unig, ond trefnwyd math o gyfrifiannell benthyciad. A fydd yn gweithredu ar gynllun blwydd-dal. Os ydym yn y tabl gwreiddiol, er enghraifft, yn newid maint y benthyciad a'r gyfradd llog flynyddol, yna yn y tabl terfynol bydd y data'n cael ei ailgyfrifo'n awtomatig.Felly, gellir ei ddefnyddio nid yn unig unwaith ar gyfer achos penodol, ond ei ddefnyddio mewn amrywiol sefyllfaoedd i gyfrifo opsiynau credyd yn ôl cynllun blwydd-dal.

Gwers: Swyddogaethau Ariannol yn Excel

Fel y gallwch weld, gan ddefnyddio'r rhaglen Excel gartref, gallwch chi gyfrifo cyfanswm y taliad benthyciad misol yn hawdd yn ôl y cynllun blwydd-dal gan ddefnyddio'r gweithredwr at y dibenion hyn. PMT. Yn ogystal, defnyddio swyddogaethau OSPLT a PRPLT gallwch gyfrifo swm y taliadau ar gorff y benthyciad a'r llog am y cyfnod penodedig. Gan gymhwyso'r holl fagiau swyddogaethau hyn gyda'i gilydd, mae'n bosibl creu cyfrifiannell benthyciad pwerus y gellir ei ddefnyddio fwy nag unwaith i gyfrifo'r taliad blwydd-dal.