Excel-ում վարկերի ամորտիզացիայի ժամանակացույցի ստեղծումը կարող է արժեքավոր հմտություն լինել, որը թույլ է տալիս արդյունավետ կերպով պատկերացնել և կառավարել ձեր վարկի մարումները: Ամորտիզացիայի ժամանակացույցը աղյուսակ է, որը մանրամասնում է ամորտիզացիոն վարկի (սովորաբար հիփոթեքային կամ մեքենայի վարկ) յուրաքանչյուր պարբերական վճարումը: Այն բաժանում է յուրաքանչյուր վճարում տոկոսների և հիմնական բաղադրիչների` ցույց տալով յուրաքանչյուր վճարումից հետո մնացած մնացորդը: Եկեք սուզվենք քայլ առ քայլ ուղեցույցում՝ Excel-ում նման ժամանակացույց ստեղծելու համար:

Ի՞նչ է ամորտիզացիայի ժամանակացույցը:

Ստեղծեք ամորտիզացիայի ժամանակացույց Excel-ում

Ստեղծեք ամորտիզացիայի ժամանակացույց փոփոխական թվով ժամանակաշրջանների համար

Ստեղծեք ամորտիզացիոն ժամանակացույց հավելավճարներով

Ստեղծեք ամորտիզացիայի ժամանակացույց (հավելավճարներով)՝ օգտագործելով Excel ձևանմուշ

Ի՞նչ է ամորտիզացիայի ժամանակացույցը:

Ամորտիզացիայի ժամանակացույցը մանրամասն աղյուսակ է, որն օգտագործվում է վարկի հաշվարկներում, որը ցույց է տալիս ժամանակի ընթացքում վարկի մարման գործընթացը: Ամորտիզացիայի ժամանակացույցերը սովորաբար օգտագործվում են ֆիքսված տոկոսադրույքով վարկերի համար, ինչպիսիք են հիփոթեքը, ավտոմեքենայի վարկերը և անձնական վարկերը, որտեղ վճարման գումարը մնում է հաստատուն վարկի ողջ ժամկետի ընթացքում, սակայն տոկոսների նկատմամբ վճարման համամասնությունը ժամանակի ընթացքում փոխվում է:

Excel-ում վարկի ամորտիզացիայի ժամանակացույց ստեղծելու համար իսկապես կարևոր են ներկառուցված PMT, PPMT և IPMT գործառույթները: Եկեք հասկանանք, թե ինչ է անում յուրաքանչյուր գործառույթ.

• PMT գործառույթԱյս ֆունկցիան օգտագործվում է վարկի համար մեկ ժամանակաշրջանի ընդհանուր վճարումը հաշվարկելու համար՝ հիմնված մշտական ​​վճարումների և հաստատուն տոկոսադրույքի վրա:
• IPMT գործառույթԱյս ֆունկցիան հաշվարկում է վճարման տոկոսային մասը տվյալ ժամանակահատվածի համար:
• PPMT գործառույթԱյս ֆունկցիան օգտագործվում է որոշակի ժամանակահատվածի համար վճարման հիմնական մասը հաշվարկելու համար:

Օգտագործելով այս գործառույթները Excel-ում, դուք կարող եք ստեղծել ամորտիզացիայի մանրամասն ժամանակացույց, որը ցույց է տալիս յուրաքանչյուր վճարման տոկոսները և հիմնական բաղադրիչները՝ յուրաքանչյուր վճարումից հետո վարկի մնացորդի հետ միասին:

Ստեղծեք ամորտիզացիայի ժամանակացույց Excel-ում

Այս բաժնում մենք կներկայացնենք երկու տարբեր եղանակներ Excel-ում ամորտիզացիայի ժամանակացույց ստեղծելու համար: Այս մեթոդները բավարարում են օգտատերերի տարբեր նախապատվությունները և հմտությունների մակարդակը՝ ապահովելով, որ ցանկացած ոք, անկախ Excel-ի իմացությունից, կարող է հաջողությամբ կազմել իր վարկի համար մանրամասն և ճշգրիտ ամորտիզացիոն ժամանակացույց:

Բանաձևերը առաջարկում են հիմքում ընկած հաշվարկների ավելի խորը պատկերացում և ապահովում են ճկունություն՝ ժամանակացույցը հարմարեցնելու համար՝ ըստ հատուկ պահանջների: Այս մոտեցումը իդեալական է նրանց համար, ովքեր ցանկանում են ունենալ գործնական փորձ և հստակ պատկերացում, թե ինչպես է յուրաքանչյուր վճարում բաժանվում հիմնական և տոկոսային բաղադրիչների: Այժմ եկեք մանրամասնենք Excel-ում ամորտիզացիոն ժամանակացույցի ստեղծման գործընթացը քայլ առ քայլ.

⭐️ Քայլ 1. Սահմանեք վարկի մասին տեղեկատվության և ամորտիզացիայի աղյուսակը

1. Մուտքագրեք վարկի հարաբերական տեղեկատվությունը, ինչպիսիք են տարեկան տոկոսադրույքը, վարկի ժամկետը տարիներով, տարեկան վճարումների քանակը և վարկի գումարը բջիջներում, ինչպես ցույց է տրված հետևյալ սքրինշոթը.
   
2. Այնուհետև Excel-ում ստեղծեք ամորտիզացիոն աղյուսակ՝ նշված պիտակներով, ինչպիսիք են ժամկետը, վճարումը, տոկոսը, հիմնականը, մնացորդը A7:E7 բջիջներում:
3. «Ժամանակաշրջան» սյունակում մուտքագրեք ժամանակաշրջանի համարները: Այս օրինակի համար վճարումների ընդհանուր թիվը 24 ամիս է (2 տարի), հետևաբար, դուք մուտքագրեք 1-ից 24 համարները «Ժամանակաշրջան» սյունակում: Տեսեք սքրինշոթը՝
   
4. Երբ ստեղծեք աղյուսակը պիտակներով և ժամկետների համարներով, կարող եք շարունակել մուտքագրել բանաձևեր և արժեքներ Վճարման, Տոկոսների, Հիմնական և Մնացորդ սյունակների համար՝ ելնելով ձեր վարկի առանձնահատկություններից:

⭐️ Քայլ 2. Հաշվեք վճարման ընդհանուր գումարը՝ օգտագործելով PMT ֆունկցիան

PMT-ի շարահյուսությունը հետևյալն է.

Մուտքագրեք հետևյալ բանաձևը B7 բջիջում, այնուհետև քաշեք լրացման բռնակը ներքև՝ այս բանաձևը այլ բջիջներ լցնելու համար, և դուք կտեսնեք մշտական ​​վճարման գումար բոլոր ժամանակաշրջանների համար: Տեսեք սքրինշոթը՝

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

⭐️ Քայլ 3. Հաշվարկել տոկոսները՝ օգտագործելով IPMT ֆունկցիան

Այս քայլում դուք կհաշվարկեք տոկոսները յուրաքանչյուր վճարման ժամանակահատվածի համար՝ օգտագործելով Excel-ի IPMT ֆունկցիան:

Մուտքագրեք հետևյալ բանաձևը C7 բջիջի մեջ, այնուհետև քաշեք լրացման բռնակը սյունակի ներքև՝ այս բանաձևը լրացնելու և յուրաքանչյուր ժամանակաշրջանի տոկոսը ստանալու համար:

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

⭐️ Քայլ 4. Հաշվեք հիմնական գումարը՝ օգտագործելով PPMT ֆունկցիան

Յուրաքանչյուր ժամանակաշրջանի համար տոկոսները հաշվարկելուց հետո, ամորտիզացիոն ժամանակացույցի ստեղծման հաջորդ քայլը յուրաքանչյուր վճարման հիմնական մասի հաշվարկն է: Դա արվում է օգտագործելով PPMT ֆունկցիան, որը նախատեսված է որոշակի ժամանակահատվածի համար վճարման հիմնական մասը որոշելու համար՝ հիմնված մշտական ​​վճարումների և մշտական ​​տոկոսադրույքի վրա:

IPMT-ի շարահյուսությունը հետևյալն է.

PPMT բանաձևի շարահյուսությունը և պարամետրերը նույնական են նախկինում քննարկված IPMT բանաձևում օգտագործվածներին:

Մուտքագրեք հետևյալ բանաձևը D7 բջիջում, այնուհետև քաշեք լրացման բռնիչը սյունակի ներքև՝ յուրաքանչյուր ժամանակաշրջանի հիմնական գումարը լրացնելու համար: Տես սքրինշոթը.

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

⭐️ Քայլ 5. Հաշվեք մնացած մնացորդը

Յուրաքանչյուր վճարման և՛ տոկոսների, և՛ մայր գումարի հաշվարկից հետո, ձեր ամորտիզացիայի ժամանակացույցի հաջորդ քայլը յուրաքանչյուր վճարումից հետո վարկի մնացած մնացորդի հաշվարկն է: Սա ժամանակացույցի կարևոր մասն է, քանի որ ցույց է տալիս, թե ինչպես է վարկի մնացորդը նվազում ժամանակի ընթացքում:

1. Ձեր հաշվեկշռի սյունակի առաջին բջիջում՝ E7, մուտքագրեք հետևյալ բանաձևը, ինչը նշանակում է, որ մնացած մնացորդը կլինի վարկի սկզբնական գումարը՝ հանած առաջին վճարման հիմնական մասը.

   =B4-D7

   
2. Երկրորդ և հաջորդ բոլոր ժամանակաշրջանների համար հաշվարկեք մնացած մնացորդը՝ հանելով ընթացիկ ժամանակաշրջանի մայր գումարը նախորդ ժամանակաշրջանի մնացորդից: Խնդրում ենք կիրառել հետևյալ բանաձևը E8 բջիջում.

   =E7-D8

    ՆշումՀաշվեկշռի բջիջի հղումը պետք է հարաբերական լինի, այնպես որ այն թարմացվում է, երբ բանաձևը քաշեք ներքև:
   
3. Եվ այնուհետև քաշեք լրացման բռնակը ներքև դեպի սյունակ: Ինչպես տեսնում եք, յուրաքանչյուր բջիջ ինքնաբերաբար կկարգավորվի մնացորդը հաշվարկելու համար՝ հիմնվելով նորացված հիմնական վճարումների վրա:
   

⭐️ Քայլ 6. Կազմեք վարկի ամփոփագիր

Ձեր մանրամասն ամորտիզացիայի ժամանակացույցը կազմելուց հետո, վարկի ամփոփագրի ստեղծումը կարող է արագ ակնարկ տրամադրել ձեր վարկի հիմնական ասպեկտների վերաբերյալ: Այս ամփոփագիրը սովորաբար ներառում է վարկի ընդհանուր արժեքը, վճարված ընդհանուր տոկոսը:

● Ընդհանուր վճարումները հաշվարկելու համար.

=SUM(B7:B30)

● Ընդհանուր տոկոսները հաշվարկելու համար.

=SUM(C7:C30)

⭐️ Արդյունք.

Այժմ հաջողությամբ ստեղծվել է վարկի ամորտիզացիայի պարզ, բայց համապարփակ ժամանակացույց: տես սքրինշոթը՝

Ստեղծեք ամորտիզացիայի ժամանակացույց փոփոխական թվով ժամանակաշրջանների համար

Նախորդ օրինակում մենք ստեղծում ենք վարկի մարման ժամանակացույց ֆիքսված քանակությամբ վճարումների համար: Այս մոտեցումը կատարյալ է կոնկրետ վարկի կամ հիփոթեքի հետ աշխատելու համար, որտեղ պայմանները չեն փոխվում:

Բայց եթե ցանկանում եք ստեղծել ամորտիզացիայի ճկուն ժամանակացույց, որը կարող է բազմիցս օգտագործվել տարբեր ժամկետներով վարկերի համար, ինչը թույլ կտա փոփոխել վճարումների քանակը, ինչպես պահանջվում է վարկի տարբեր սցենարների համար, դուք պետք է հետևեք ավելի մանրամասն մեթոդին:

⭐️ Քայլ 1. Սահմանեք վարկի մասին տեղեկատվության և ամորտիզացիայի աղյուսակը

1. Մուտքագրեք վարկի հարաբերական տեղեկատվությունը, ինչպիսիք են տարեկան տոկոսադրույքը, վարկի ժամկետը տարիներով, տարեկան վճարումների քանակը և վարկի գումարը բջիջներում, ինչպես ցույց է տրված հետևյալ սքրինշոթը.
   
2. Այնուհետև Excel-ում ստեղծեք ամորտիզացիոն աղյուսակ՝ նշված պիտակներով, ինչպիսիք են ժամկետը, վճարումը, տոկոսը, հիմնականը, մնացորդը A7:E7 բջիջներում:
3. «Ժամանակաշրջան» սյունակում մուտքագրեք վճարումների ամենաբարձր թիվը, որը կարող եք դիտարկել ցանկացած վարկի համար, օրինակ՝ լրացրեք 1-ից մինչև 360 թվերը: Սա կարող է ծածկել ստանդարտ 30 տարվա վարկը, եթե դուք ամսական վճարումներ եք կատարում:
   

⭐️ Քայլ 2. Փոփոխեք վճարման, տոկոսների և սկզբունքային բանաձևերը IF գործառույթով

Մուտքագրեք հետևյալ բանաձևերը համապատասխան բջիջներում, այնուհետև քաշեք լրացման բռնակը, որպեսզի ընդլայնեք այս բանաձևերը մինչև ձեր սահմանած վճարման ժամկետների առավելագույն քանակը:

● Վճարման բանաձև.

Սովորաբար վճարումը հաշվարկելու համար օգտագործում եք PMT ֆունկցիան: IF հայտարարություն ներառելու համար շարահյուսական բանաձևը հետևյալն է.

Այսպիսով, բանաձևերը հետևյալն են.

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

● Տոկոսների բանաձև.

Շարահյուսության բանաձևը հետևյալն է.

Այսպիսով, բանաձևերը հետևյալն են.

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

● Սկզբունքային բանաձև.

Շարահյուսության բանաձևը հետևյալն է.

Այսպիսով, բանաձևերը հետևյալն են.

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

⭐️ Քայլ 3. Կարգավորեք մնացորդի մնացորդի բանաձևը

Մնացած մնացորդի համար դուք կարող եք սովորաբար հանել հիմնական գումարը նախորդ մնացորդից: IF հայտարարությամբ փոփոխեք այն հետևյալ կերպ.

● Առաջին հաշվեկշռի բջիջը՝ (E7)

=B4-D7

● Երկրորդ հաշվեկշռի բջիջը՝ (E8)

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

⭐️ Քայլ 4. Կազմեք վարկի ամփոփագիր

Փոփոխված բանաձևերով ամորտիզացիայի ժամանակացույցը կազմելուց հետո հաջորդ քայլը վարկի ամփոփագիր ստեղծելն է:

● Ընդհանուր վճարումները հաշվարկելու համար.

=SUM(B7:B366)

● Ընդհանուր տոկոսները հաշվարկելու համար.

=SUM(C7:C366)

⭐️ Արդյունք.

Այժմ դուք ունեք համապարփակ և դինամիկ ամորտիզացիայի ժամանակացույց Excel-ում, որը լրացվում է վարկի մանրամասն ամփոփագրով: Ամեն անգամ, երբ դուք ճշգրտում եք վճարման ժամկետը, ամբողջ ամորտիզացիայի ժամանակացույցը ավտոմատ կերպով կթարմացվի՝ արտացոլելու այս փոփոխությունները: Դիտեք ստորև ներկայացված ցուցադրությունը.

Ստեղծեք ամորտիզացիոն ժամանակացույց հավելավճարներով

Լրացուցիչ վճարումներ կատարելով նախատեսվածից դուրս՝ վարկը կարող է ավելի արագ մարվել։ Ամորտիզացիայի ժամանակացույցը, որը ներառում է լրացուցիչ վճարումներ, երբ ստեղծվում է Excel-ում, ցույց է տալիս, թե ինչպես այդ լրացուցիչ վճարումները կարող են արագացնել վարկի մարումը և նվազեցնել վճարված ընդհանուր տոկոսը: Ահա թե ինչպես կարող եք կարգավորել այն.

⭐️ Քայլ 1. Սահմանեք վարկի մասին տեղեկատվության և ամորտիզացիայի աղյուսակը

1. Մուտքագրեք վարկի հարաբերական տեղեկատվությունը, ինչպիսիք են տարեկան տոկոսադրույքը, վարկի ժամկետը տարիներով, տարեկան վճարումների քանակը, վարկի գումարը և հավելավճարը բջիջներում, ինչպես ցույց է տրված հետևյալ սքրինշոթը.
   
2. Այնուհետև հաշվարկեք նախատեսված վճարումը:
   Բացի մուտքային բջիջներից, մեր հետագա հաշվարկների համար անհրաժեշտ է ևս մեկ նախապես սահմանված բջիջ՝ նախատեսված վճարման գումարը: Սա վարկի կանոնավոր վճարման գումարն է՝ ենթադրելով, որ լրացուցիչ վճարումներ չեն կատարվել: Խնդրում ենք կիրառել հետևյալ բանաձևը B6 բջիջում.

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

   
   
3. Այնուհետև Excel-ում ստեղծեք ամորտիզացիոն աղյուսակ.
   • Սահմանեք նշված պիտակները, ինչպիսիք են Ժամկետաշրջանը, Վճարման ժամանակացույցը, Լրացուցիչ վճարումը, Ընդհանուր վճարումը, Տոկոսները, Հիմնական գումարը, Մնացած մնացորդը բջիջներում A8:G8;
   • «Ժամանակաշրջան» սյունակում մուտքագրեք վճարումների ամենաբարձր թիվը, որը կարող եք դիտարկել ցանկացած վարկի համար: Օրինակ, լրացրեք 0-ից մինչև 360 թվերը: Սա կարող է ծածկել ստանդարտ 30 տարվա վարկը, եթե դուք ամսական վճարումներ եք կատարում.
   • 0-րդ ժամանակաշրջանի համար (մեր դեպքում՝ 9-րդ տող), առբերեք հաշվեկշռի արժեքը այս բանաձևով = B4, որը համապատասխանում է վարկի սկզբնական գումարին։ Այս տողի մյուս բոլոր բջիջները պետք է դատարկ մնան:
   • 

⭐️ Քայլ 2. Ստեղծեք ամորտիզացիայի ժամանակացույցի բանաձևերը հավելավճարներով

Մուտքագրեք հետևյալ բանաձևերը համապատասխան բջիջներում մեկ առ մեկ. Սխալների կառավարումը բարելավելու համար մենք այս և ապագա բոլոր բանաձևերը ամփոփում ենք IFERROR ֆունկցիայի մեջ: Այս մոտեցումը օգնում է խուսափել բազմաթիվ պոտենցիալ սխալներից, որոնք կարող են առաջանալ, եթե մուտքային բջիջներից որևէ մեկը դատարկ մնա կամ պահի սխալ արժեքներ:

● Հաշվարկել նախատեսված վճարումը.

B10 բջիջում մուտքագրեք հետևյալ բանաձևը.

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

● Հաշվարկել հավելյալ վճարը.

Մուտքագրեք հետևյալ բանաձևը C10 բջիջում.

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

● Հաշվարկել ընդհանուր վճարումը.

Մուտքագրեք հետևյալ բանաձևը D10 բջիջում.

=IFERROR(B10+C10, "")

● Հաշվարկել հիմնականը.

Մուտքագրեք հետևյալ բանաձևը E10 բջիջում.

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

● Հաշվարկել տոկոսադրույքը.

F10 բջիջում մուտքագրեք հետևյալ բանաձևը.

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

● Հաշվեք մնացած մնացորդը

G10 բջիջում մուտքագրեք հետևյալ բանաձևը.

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

Բանաձևերից յուրաքանչյուրն ավարտելուց հետո ընտրեք բջիջների միջակայքը B10:G10 և օգտագործեք լրացման բռնիչը՝ քաշելու և երկարացնելու այս բանաձևերը ներքև վճարման ժամանակահատվածների ամբողջ փաթեթով: Չօգտագործված ցանկացած ժամանակաշրջանի համար բջիջները ցույց կտան 0: Տեսեք սքրինշոթը՝

⭐️ Քայլ 3. Կազմեք վարկի ամփոփագիր

● Ստացեք վճարումների պլանավորված քանակը.

=B2:B3

● Ստացեք վճարումների իրական թիվը.

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

● Ստացեք ընդհանուր լրացուցիչ վճարումներ.

=SUM(C10:C369)

● Ստացեք ընդհանուր տոկոսադրույքը.

=SUM(F10:F369)

⭐️ Արդյունք.

Հետևելով այս քայլերին՝ դուք ստեղծում եք դինամիկ ամորտիզացիայի ժամանակացույց Excel-ում, որը հաշվի է առնում հավելյալ վճարումները:

Ստեղծեք ամորտիզացիայի ժամանակացույց՝ օգտագործելով Excel ձևանմուշ

Կաղապարի միջոցով Excel-ում ամորտիզացիոն ժամանակացույցի ստեղծումը պարզ և ժամանակի արդյունավետ մեթոդ է: Excel-ը տրամադրում է ներկառուցված ձևանմուշներ, որոնք ավտոմատ կերպով հաշվարկում են յուրաքանչյուր վճարման տոկոսը, մայր գումարը և մնացած մնացորդը: Ահա թե ինչպես կարելի է ստեղծել ամորտիզացիայի ժամանակացույց՝ օգտագործելով Excel ձևանմուշ.

1. Սեղմել Ֆայլ > նոր, որոնման դաշտում մուտքագրեք ամորտիզացիայի ժամանակացույցըեւ սեղմեք Մտնել բանալի. Այնուհետև ընտրեք այն ձևանմուշը, որը լավագույնս համապատասխանում է ձեր կարիքներին՝ սեղմելով դրա վրա: Օրինակ, այստեղ ես կընտրեմ Simple loan calculator template: Տես սքրինշոթը.
   
2. Երբ դուք ընտրել եք ձևանմուշ, սեղմեք դրա վրա Ստեղծել կոճակ՝ այն որպես նոր աշխատանքային գրքույկ բացելու համար:
3. Այնուհետև մուտքագրեք ձեր սեփական վարկի մանրամասները, ձևանմուշը պետք է ավտոմատ կերպով հաշվարկի և լրացնի ժամանակացույցը ձեր մուտքերի հիման վրա:
4. Ի վերջո, պահպանեք ձեր նոր ամորտիզացիոն գրաֆիկի աշխատանքային գրքույկը: