Gall creu amserlen amorteiddio benthyciad yn Excel fod yn sgil werthfawr, gan eich galluogi i ddelweddu a rheoli eich ad-daliadau benthyciad yn effeithiol. Mae atodlen amorteiddio yn dabl sy’n manylu ar bob taliad cyfnodol ar fenthyciad amorteiddio (fel arfer morgais neu fenthyciad car). Mae'n rhannu pob taliad yn log a phrif gydrannau, gan ddangos y balans sy'n weddill ar ôl pob taliad. Gadewch i ni blymio i mewn i ganllaw cam wrth gam i greu amserlen o'r fath yn Excel.

Beth yw amserlen amorteiddio?

Creu amserlen amorteiddio yn Excel

Creu amserlen amorteiddio ar gyfer nifer amrywiol o gyfnodau

Creu amserlen amorteiddio gyda thaliadau ychwanegol

Creu amserlen amorteiddio (gyda thaliadau ychwanegol) trwy ddefnyddio templed Excel

Beth yw amserlen amorteiddio?

Mae amserlen amorteiddio yn dabl manwl a ddefnyddir wrth gyfrifo benthyciad sy'n dangos y broses o dalu benthyciad dros amser. Defnyddir amserlenni amorteiddio yn gyffredin ar gyfer benthyciadau cyfradd sefydlog megis morgeisi, benthyciadau ceir, a benthyciadau personol, lle mae swm y taliad yn aros yn gyson trwy gydol tymor y benthyciad, ond mae cyfran y taliad tuag at log yn erbyn prif newid dros amser.

Er mwyn creu amserlen amorteiddio benthyciad yn Excel, yn wir, mae'r swyddogaethau adeiledig PMT, PPMT, ac IPMT yn hanfodol. Gadewch i ni ddeall beth mae pob swyddogaeth yn ei wneud:

• Swyddogaeth PMT: Defnyddir y swyddogaeth hon i gyfrifo cyfanswm y taliad fesul cyfnod ar gyfer benthyciad yn seiliedig ar daliadau cyson a chyfradd llog gyson.
• Swyddogaeth IPMT: Mae'r ffwythiant hwn yn cyfrifo cyfran llog taliad am gyfnod penodol.
• Swyddogaeth PPMT: Defnyddir y swyddogaeth hon i gyfrifo prif gyfran taliad am gyfnod penodol.

Trwy ddefnyddio'r swyddogaethau hyn yn Excel, gallwch greu amserlen amorteiddio fanwl sy'n dangos y llog a phrif gydrannau pob taliad, ynghyd â gweddill y benthyciad ar ôl pob taliad.

Creu amserlen amorteiddio yn Excel

Yn yr adran hon, byddwn yn cyflwyno dau ddull gwahanol o greu amserlen amorteiddio yn Excel. Mae'r dulliau hyn yn darparu ar gyfer gwahanol ddewisiadau defnyddwyr a lefelau sgiliau, gan sicrhau bod unrhyw un, waeth beth fo'u hyfedredd gydag Excel, yn gallu llunio amserlen amorteiddio fanwl a chywir ar gyfer eu benthyciad yn llwyddiannus.

Mae fformiwlâu yn cynnig dealltwriaeth ddyfnach o'r cyfrifiadau sylfaenol ac yn darparu hyblygrwydd i addasu'r amserlen yn unol â gofynion penodol. Mae'r dull hwn yn ddelfrydol ar gyfer y rhai sy'n dymuno cael profiad ymarferol a mewnwelediad clir i'r ffordd y caiff pob taliad ei rannu'n brif gydrannau a llog. Nawr, gadewch i ni dorri i lawr y broses o greu amserlen amorteiddio yn Excel gam wrth gam:

⭐️ Cam 1: Gosodwch y tabl gwybodaeth am fenthyciadau ac amorteiddiad

1. Rhowch y wybodaeth benthyciad cymharol, megis cyfradd llog blynyddol, tymor benthyciad mewn blynyddoedd, nifer y taliadau y flwyddyn a swm y benthyciad i mewn i'r celloedd fel y sgrinlun a ddangosir:
   
2. Yna, creu tabl amorteiddio yn Excel gyda'r labeli penodedig, megis Cyfnod, Taliad, Llog, Prif, Balans sy'n weddill yng nghelloedd A7: E7.
3. Yn y golofn Cyfnod, nodwch rifau'r cyfnod. Ar gyfer yr enghraifft hon, cyfanswm nifer y taliadau yw 24 mis (2 flynedd), felly, byddwch yn nodi rhifau 1 i 24 yn y golofn Cyfnod. Gweler y sgrinlun:
   
4. Unwaith y byddwch wedi sefydlu'r tabl gyda'r labeli a'r rhifau cyfnod, gallwch fynd ymlaen i nodi fformiwlâu a gwerthoedd ar gyfer y colofnau Talu, Llog, Prif a Balans yn seiliedig ar fanylion eich benthyciad.

⭐️ Cam 2: Cyfrifwch gyfanswm y taliad trwy ddefnyddio'r swyddogaeth PMT

Cystrawen y PMT yw:

Rhowch y fformiwla ganlynol i mewn i gell B7, ac yna, llusgwch y ddolen llenwi i lawr i lenwi'r fformiwla hon i gelloedd eraill, a byddwch yn gweld swm taliad cyson ar gyfer yr holl gyfnodau. Gweler y sgrinlun:

= -PMT($B$1/$B$3, $B$2*$B$3, $B$4)

⭐️ Cam 3: Cyfrifwch log trwy ddefnyddio swyddogaeth IPMT

Yn y cam hwn, byddwch yn cyfrifo'r llog ar gyfer pob cyfnod talu gan ddefnyddio swyddogaeth IPMT Excel.

Rhowch y fformiwla ganlynol i mewn i gell C7, ac yna, llusgwch y ddolen llenwi i lawr y golofn i lenwi'r fformiwla hon a chael y llog ar gyfer pob cyfnod.

=-IPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4)

⭐️ Cam 4: Cyfrifwch y pennaeth trwy ddefnyddio swyddogaeth PPMT

Ar ôl cyfrifo'r llog ar gyfer pob cyfnod, y cam nesaf wrth greu amserlen amorteiddio yw cyfrifo prif gyfran pob taliad. Gwneir hyn gan ddefnyddio swyddogaeth PPMT, sydd wedi'i chynllunio i bennu prif ran taliad am gyfnod penodol, yn seiliedig ar daliadau cyson a chyfradd llog gyson.

Cystrawen yr IPMT yw:

Mae cystrawen a pharamedrau'r fformiwla PPMT yn union yr un fath â'r rhai a ddefnyddiwyd yn y fformiwla IPMT a drafodwyd yn flaenorol.

Rhowch y fformiwla ganlynol i mewn i gell D7, ac yna, llusgwch y ddolen llenwi i lawr y golofn i lenwi'r egwyddor ar gyfer pob cyfnod. Gweler y sgrinlun:

=-PPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4)

⭐️ Cam 5: Cyfrifwch y balans sy'n weddill

Ar ôl cyfrifo llog a phrifswm pob taliad, y cam nesaf yn eich amserlen amorteiddio yw cyfrifo gweddill y benthyciad sy'n weddill ar ôl pob taliad. Mae hyn yn rhan hanfodol o'r amserlen gan ei fod yn dangos sut mae balans y benthyciad yn lleihau dros amser.

1. Yng nghell gyntaf eich colofn balans - E7, nodwch y fformiwla ganlynol, sy'n golygu mai'r balans sy'n weddill fydd swm y benthyciad gwreiddiol llai prif gyfran y taliad cyntaf:

   =B4-D7

   
2. Ar gyfer yr ail gyfnod a phob cyfnod dilynol, cyfrifwch y balans sy'n weddill trwy dynnu prif daliad y cyfnod cyfredol o falans y cyfnod blaenorol. Cymhwyswch y fformiwla ganlynol i gell E8:

   =E7-D8

    Nodyn: Dylai'r cyfeiriad at y gell cydbwysedd fod yn gymharol, felly mae'n diweddaru wrth i chi lusgo'r fformiwla i lawr.
   
3. Ac yna llusgwch yr handlen llenwi i lawr i'r golofn. Fel y gallwch weld, bydd pob cell yn addasu'n awtomatig i gyfrifo'r balans sy'n weddill yn seiliedig ar y prif daliadau wedi'u diweddaru.
   

⭐️ Cam 6: Gwnewch grynodeb benthyciad

Ar ôl sefydlu eich amserlen amorteiddio fanwl, gall creu crynodeb benthyciad roi trosolwg cyflym o agweddau allweddol eich benthyciad. Bydd y crynodeb hwn fel arfer yn cynnwys cyfanswm cost y benthyciad, cyfanswm y llog a dalwyd.

● I gyfrifo cyfanswm y taliadau:

=SUM(B7:B30)

● I gyfrifo cyfanswm y llog:

=SUM(C7:C30)

⭐️ Canlyniad:

Nawr, mae amserlen amorteiddio benthyciadau syml ond cynhwysfawr wedi'i chreu'n llwyddiannus. gweler y sgrinlun:

Creu amserlen amorteiddio ar gyfer nifer amrywiol o gyfnodau

Yn yr enghraifft flaenorol, rydym yn creu amserlen ad-dalu benthyciad ar gyfer nifer sefydlog o daliadau. Mae'r dull hwn yn berffaith ar gyfer delio â benthyciad neu forgais penodol lle nad yw'r telerau'n newid.

Ond, os ydych chi'n bwriadu creu amserlen amorteiddio hyblyg y gellir ei defnyddio dro ar ôl tro ar gyfer benthyciadau gyda chyfnodau amrywiol, gan ganiatáu i chi addasu nifer y taliadau yn ôl yr angen ar gyfer gwahanol senarios benthyciad, bydd angen i chi ddilyn dull mwy manwl.

⭐️ Cam 1: Gosodwch y tabl gwybodaeth am fenthyciadau ac amorteiddiad

1. Rhowch y wybodaeth benthyciad cymharol, megis cyfradd llog blynyddol, tymor benthyciad mewn blynyddoedd, nifer y taliadau y flwyddyn a swm y benthyciad i mewn i'r celloedd fel y sgrinlun a ddangosir:
   
2. Yna, creu tabl amorteiddio yn Excel gyda'r labeli penodedig, megis Cyfnod, Taliad, Llog, Prif, Balans sy'n weddill yng nghelloedd A7: E7.
3. Yn y golofn Cyfnod, nodwch y nifer uchaf o daliadau y gallech eu hystyried ar gyfer unrhyw fenthyciad, er enghraifft, llenwch y rhifau sy'n amrywio o 1 i 360. Gall hyn gwmpasu benthyciad 30 mlynedd safonol os ydych yn gwneud taliadau misol.
   

⭐️ Cam 2: Addasu'r fformiwlâu talu, llog ac egwyddor gyda swyddogaeth IF

Rhowch y fformiwlâu canlynol yn y celloedd cyfatebol, ac yna llusgwch y ddolen llenwi i ymestyn y fformiwlâu hyn i lawr i uchafswm nifer y cyfnodau talu rydych chi wedi'u gosod.

● Fformiwla talu:

Fel arfer, rydych chi'n defnyddio'r swyddogaeth PMT i gyfrifo taliad. I ymgorffori datganiad IF, y fformiwla gystrawen yw:

Felly dyma'r fformiwlâu:

=IF(A7<=$B$2*$B$3, -PMT($B$1/$B$3, $B$2*$B$3, $B$4), "")

● Fformiwla llog:

Y fformiwla gystrawen yw:

Felly dyma'r fformiwlâu:

=IF(A7<=$B$2*$B$3,-IPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4), "")

● Fformiwla egwyddor:

Y fformiwla gystrawen yw:

Felly dyma'r fformiwlâu:

=IF(A7<=$B$2*$B$3,-PPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4), "")

⭐️ Cam 3: Addaswch y fformiwla balans sy'n weddill

Ar gyfer y balans sy'n weddill, efallai y byddwch fel arfer yn tynnu'r prifswm o'r balans blaenorol. Gyda datganiad IF, addaswch ef fel:

● Y gell cydbwysedd cyntaf:(E7)

=B4-D7

● Yr ail gell cydbwysedd: (E8)

=IF(A8<=$B$2*$B$3, E7-D8, "") 

⭐️ Cam 4: Gwnewch grynodeb benthyciad

Ar ôl i chi sefydlu'r amserlen amorteiddio gyda'r fformiwlâu wedi'u haddasu, y cam nesaf yw creu crynodeb benthyciad.

● I gyfrifo cyfanswm y taliadau:

=SUM(B7:B366)

● I gyfrifo cyfanswm y llog:

=SUM(C7:C366)

⭐️ Canlyniad:

Nawr, mae gennych amserlen amorteiddio gynhwysfawr a deinamig yn Excel, ynghyd â chrynodeb benthyciad manwl. Pryd bynnag y byddwch yn addasu tymor y cyfnod talu, bydd yr amserlen amorteiddio gyfan yn diweddaru'n awtomatig i adlewyrchu'r newidiadau hyn. Gweler y demo isod:

Creu amserlen amorteiddio gyda thaliadau ychwanegol

Trwy wneud taliadau ychwanegol y tu hwnt i'r rhai a drefnwyd, gellir ad-dalu benthyciad yn gyflymach. Mae amserlen amorteiddio sy'n cynnwys taliadau ychwanegol, pan gaiff ei chreu yn Excel, yn dangos sut y gall y taliadau ychwanegol hyn gyflymu ad-daliad y benthyciad a lleihau cyfanswm y llog a delir. Dyma sut y gallwch chi ei sefydlu:

⭐️ Cam 1: Gosodwch y tabl gwybodaeth am fenthyciadau ac amorteiddiad

1. Rhowch y wybodaeth benthyciad cymharol, megis cyfradd llog blynyddol, tymor benthyciad mewn blynyddoedd, nifer y taliadau y flwyddyn, swm y benthyciad a thaliad ychwanegol i mewn i'r celloedd fel y sgrinlun a ddangosir:
   
2. Yna, cyfrifwch y taliad a drefnwyd.
   Yn ogystal â'r celloedd mewnbwn, mae angen cell arall wedi'i diffinio ymlaen llaw ar gyfer ein cyfrifiannau dilynol - swm y taliad a drefnwyd. Dyma swm y taliad rheolaidd ar fenthyciad gan gymryd na wneir unrhyw daliadau ychwanegol. Cymhwyswch y fformiwla ganlynol i gell B6:

   =IFERROR(-PMT($B$1/$B$3, $B$2*$B$3, $B$4),"")

   
   
3. Yna, crëwch dabl amorteiddio yn Excel:
   • Gosodwch y labeli penodedig, megis Cyfnod, Taliad Amserlen, Taliad Ychwanegol, Cyfanswm Taliad, Llog, Pennawd, Balans sy'n weddill yng nghelloedd A8:G8;
   • Yn y golofn Cyfnod, nodwch y nifer uchaf o daliadau y gallech eu hystyried ar gyfer unrhyw fenthyciad. Er enghraifft, llenwch y rhifau sy'n amrywio o 0 i 360. Gall hyn gwmpasu benthyciad safonol 30 mlynedd os ydych yn gwneud taliadau misol;
   • Ar gyfer Cyfnod 0 (rhes 9 yn ein hachos ni), adalw'r gwerth Balans gyda'r fformiwla hon = B4, sy'n cyfateb i swm y benthyciad cychwynnol. Dylid gadael pob cell arall yn y rhes hon yn wag.
   • 

⭐️ Cam 2: Cretiwch y fformiwlâu ar gyfer amserlen amorteiddio gyda thaliadau ychwanegol

Rhowch y fformiwlâu canlynol yn y celloedd cyfatebol fesul un. Er mwyn gwella'r modd yr ymdrinnir â gwallau, rydym yn amgáu hyn a holl fformiwla'r dyfodol o fewn swyddogaeth IFERROR. Mae'r dull hwn yn helpu i osgoi gwallau posibl lluosog a allai godi os bydd unrhyw un o'r celloedd mewnbwn yn cael eu gadael yn wag neu'n dal gwerthoedd anghywir.

● Cyfrifwch y taliad a drefnwyd:

Rhowch y fformiwla ganlynol i mewn i gell B10:

=IFERROR(IF($B$6<=G9, $B$6, G9+G9*$B$1/$B$3), "")

● Cyfrifwch y taliad ychwanegol:

Rhowch y fformiwla ganlynol i mewn i gell C10:

=IFERROR(IF($B$5<G9-E10,$B$5, G9-E10), "")

● Cyfrifwch gyfanswm y taliad:

Rhowch y fformiwla ganlynol i mewn i gell D10:

=IFERROR(B10+C10, "")

● Cyfrifwch y prifswm:

Rhowch y fformiwla ganlynol i mewn i gell E10:

=IFERROR(IF(B10>0, MIN(B10-F10, G9), 0), "")

● Cyfrifwch y llog:

Rhowch y fformiwla ganlynol i mewn i gell F10:

=IFERROR(IF(B10>0, $B$1/$B$3*G9, 0), "")

● Cyfrifwch y balans sy'n weddill

Rhowch y fformiwla ganlynol i mewn i gell G10:

=IFERROR(IF(G9 >0, G9-E10-C10, 0), "")

Unwaith y byddwch wedi cwblhau pob un o'r fformiwlâu, dewiswch yr ystod celloedd B10: G10, a defnyddiwch y ddolen lenwi i lusgo ac ymestyn y fformiwlâu hyn i lawr trwy'r set gyfan o gyfnodau talu. Am unrhyw gyfnodau na ddefnyddir, bydd y celloedd yn dangos 0s. Gweler y sgrinlun:

⭐️ Cam 3: Gwnewch grynodeb benthyciad

● Sicrhewch nifer y taliadau a drefnwyd:

=B2:B3

● Cael nifer gwirioneddol y taliadau:

=COUNTIF(D10:D369,">"&0)

● Cael cyfanswm taliadau ychwanegol:

=SUM(C10:C369)

● Sicrhewch gyfanswm llog:

=SUM(F10:F369)

⭐️ Canlyniad:

Trwy ddilyn y camau hyn, rydych chi'n creu amserlen amorteiddio ddeinamig yn Excel sy'n cyfrif am daliadau ychwanegol.

Creu amserlen amorteiddio trwy ddefnyddio templed Excel

Mae creu amserlen amorteiddio yn Excel gan ddefnyddio templed yn ddull syml ac amser-effeithiol. Mae Excel yn darparu templedi adeiledig sy'n cyfrifo llog, prifswm a balans pob taliad yn awtomatig. Dyma sut i greu amserlen amorteiddio gan ddefnyddio templed Excel:

1. Cliciwch Ffeil > Nghastell Newydd Emlyn, yn y blwch chwilio, teipiwch amserlen amorteiddio, a'r wasg Rhowch cywair. Yna, dewiswch y templed sy'n gweddu orau i'ch anghenion trwy glicio arno. Er enghraifft, yma, byddaf yn dewis Templed cyfrifiannell benthyciad syml. Gweler y sgrinlun:
   
2. Unwaith y byddwch wedi dewis templed, cliciwch ar y Creu botwm i'w agor fel llyfr gwaith newydd.
3. Yna, rhowch fanylion eich benthyciad eich hun, dylai'r templed gyfrifo a phoblogi'r amserlen yn awtomatig yn seiliedig ar eich mewnbynnau.
4. O'r diwedd, arbedwch eich llyfr gwaith amserlen amorteiddio newydd.