Ustvarjanje amortizacijskega načrta posojila v Excelu je lahko dragocena veščina, ki vam omogoča vizualizacijo in učinkovito upravljanje odplačil posojila. Amortizacijski načrt je tabela s podrobnostmi o vsakem rednem plačilu amortizacijskega posojila (običajno hipoteke ali avtomobilskega posojila). Vsako plačilo razdeli na komponente obresti in glavnice ter prikaže preostalo stanje po vsakem plačilu. Poglobimo se v vodnik po korakih za ustvarjanje takšnega urnika v Excelu.

Kaj je amortizacijski načrt?

Ustvarite amortizacijski načrt v Excelu

Ustvarite amortizacijski načrt za spremenljivo število obdobij

Ustvarite amortizacijski načrt z dodatnimi plačili

Ustvarite amortizacijski načrt (z dodatnimi plačili) z uporabo Excelove predloge

Kaj je amortizacijski načrt?

Amortizacijski načrt je podrobna tabela, ki se uporablja pri izračunih posojila in prikazuje postopek odplačevanja posojila skozi čas. Amortizacijski načrti se običajno uporabljajo za posojila s fiksno obrestno mero, kot so hipoteke, posojila za avtomobile in osebna posojila, kjer ostane znesek plačila nespremenjen skozi celotno obdobje posojila, vendar se delež plačila obresti glede na glavnico sčasoma spremeni.

Za ustvarjanje amortizacijskega načrta posojila v Excelu so vgrajene funkcije PMT, PPMT in IPMT res ključne. Razumejmo, kaj počne vsaka funkcija:

• Funkcija PMT: Ta funkcija se uporablja za izračun celotnega plačila na obdobje za posojilo na podlagi stalnih plačil in konstantne obrestne mere.
• Funkcija IPMT: Ta funkcija izračuna obrestni del plačila za dano obdobje.
• Funkcija PPMT: Ta funkcija se uporablja za izračun glavnice plačila za določeno obdobje.

Z uporabo teh funkcij v Excelu lahko ustvarite podroben amortizacijski načrt, ki prikazuje obresti in glavnico vsakega plačila, skupaj s preostalim stanjem posojila po vsakem plačilu.

Ustvarite amortizacijski načrt v Excelu

V tem razdelku bomo predstavili dve različni metodi za ustvarjanje amortizacijskega načrta v Excelu. Te metode ustrezajo različnim uporabniškim željam in stopnjam spretnosti ter zagotavljajo, da lahko vsakdo, ne glede na njihovo znanje z Excelom, uspešno sestavi podroben in natančen amortizacijski načrt za svoje posojilo.

Formule ponujajo globlje razumevanje osnovnih izračunov in nudijo prilagodljivost za prilagajanje urnika glede na posebne zahteve. Ta pristop je idealen za tiste, ki želijo imeti praktične izkušnje in jasen vpogled v to, kako je vsako plačilo razdeljeno na sestavine glavnice in obresti. Zdaj pa razčlenimo postopek ustvarjanja amortizacijskega načrta v Excelu korak za korakom:

⭐️ 1. korak: Nastavite podatke o posojilu in amortizacijsko tabelo

1. Vnesite podatke o relativnem posojilu, kot so letna obrestna mera, rok posojila v letih, število plačil na leto in znesek posojila v celice, kot je prikazano na spodnjem posnetku zaslona:
   
2. Nato v Excelu ustvarite amortizacijsko tabelo z določenimi oznakami, kot so Obdobje, Plačilo, Obresti, Glavnica, Preostalo stanje v celicah A7:E7.
3. V stolpec Obdobje vnesite številke obdobij. Za ta primer je skupno število plačil 24 mesecev (2 leti), zato boste v stolpec Obdobje vnesli številke od 1 do 24. Oglejte si posnetek zaslona:
   
4. Ko nastavite tabelo z oznakami in številkami obdobij, lahko nadaljujete z vnosom formul in vrednosti za stolpce Plačilo, Obresti, Glavnica in Stanje glede na posebnosti vašega posojila.

⭐️ 2. korak: Z uporabo funkcije PMT izračunajte skupni znesek plačila

Sintaksa PMT je:

Vnesite naslednjo formulo v celico B7 in nato povlecite ročico za polnjenje navzdol, da to formulo zapolnite v druge celice, in videli boste stalen znesek plačila za vsa obdobja. Oglejte si posnetek zaslona:

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

⭐️ 3. korak: Izračunajte obresti s funkcijo IPMT

V tem koraku boste izračunali obresti za vsako plačilno obdobje z uporabo Excelove funkcije IPMT.

Vnesite naslednjo formulo v celico C7 in nato povlecite ročico za polnjenje navzdol po stolpcu, da izpolnite to formulo in pridobite obresti za vsako obdobje.

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

⭐️ 4. korak: Izračunajte glavnico s funkcijo PPMT

Po izračunu obresti za vsako obdobje je naslednji korak pri ustvarjanju amortizacijskega načrta izračun glavnice vsakega plačila. To se izvede s funkcijo PPMT, ki je zasnovana za določanje glavnice plačila za določeno obdobje na podlagi stalnih plačil in konstantne obrestne mere.

Sintaksa IPMT je:

Sintaksa in parametri za formulo PPMT so enaki tistim, uporabljenim v prej obravnavani formuli IPMT.

Vnesite naslednjo formulo v celico D7 in nato povlecite ročico za polnjenje navzdol po stolpcu, da izpolnite glavnico za vsako obdobje. Oglejte si posnetek zaslona:

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

⭐️ 5. korak: Izračunajte preostalo stanje

Po izračunu obresti in glavnice vsakega plačila je naslednji korak v vašem amortizacijskem načrtu izračun preostalega stanja posojila po vsakem plačilu. To je ključni del razporeda, saj prikazuje, kako se stanje posojila sčasoma zmanjšuje.

1. V prvo celico stolpca stanja – E7 vnesite naslednjo formulo, kar pomeni, da bo preostalo stanje prvotni znesek posojila minus glavnica prvega plačila:

   =B4-D7

   
2. Za drugo in vsa naslednja obdobja izračunajte preostalo stanje tako, da odštejete plačilo glavnice tekočega obdobja od stanja prejšnjega obdobja. Uporabite naslednjo formulo v celici E8:

   =E7-D8

    Opombe: Sklic na celico ravnotežja mora biti relativen, tako da se posodablja, ko povlečete formulo navzdol.
   
3. In nato povlecite ročico za polnjenje navzdol v stolpec. Kot lahko vidite, se bo vsaka celica samodejno prilagodila za izračun preostalega stanja na podlagi posodobljenih plačil glavnice.
   

⭐️ 6. korak: Naredite povzetek posojila

Po nastavitvi podrobnega amortizacijskega načrta lahko ustvarjanje povzetka posojila zagotovi hiter pregled ključnih vidikov vašega posojila. Ta povzetek običajno vključuje skupne stroške posojila in skupne plačane obresti.

● Za izračun skupnih plačil:

=SUM(B7:B30)

● Za izračun skupnih obresti:

=SUM(C7:C30)

⭐️ Rezultat:

Zdaj je bil uspešno ustvarjen preprost, a obsežen načrt amortizacije posojila. glej posnetek zaslona:

Ustvarite amortizacijski načrt za spremenljivo število obdobij

V prejšnjem primeru izdelamo načrt odplačevanja posojila za določeno število plačil. Ta pristop je kot nalašč za obravnavanje določenega posojila ali hipoteke, kjer se pogoji ne spremenijo.

Če pa želite ustvariti prilagodljiv amortizacijski načrt, ki ga je mogoče večkrat uporabiti za posojila z različnimi obdobji, kar vam omogoča spreminjanje števila plačil, kot je potrebno za različne scenarije posojila, boste morali slediti podrobnejši metodi.

⭐️ 1. korak: Nastavite podatke o posojilu in amortizacijsko tabelo

1. Vnesite podatke o relativnem posojilu, kot so letna obrestna mera, rok posojila v letih, število plačil na leto in znesek posojila v celice, kot je prikazano na spodnjem posnetku zaslona:
   
2. Nato v Excelu ustvarite amortizacijsko tabelo z določenimi oznakami, kot so Obdobje, Plačilo, Obresti, Glavnica, Preostalo stanje v celicah A7:E7.
3. V stolpec Obdobje vnesite največje število plačil, ki bi jih lahko upoštevali pri katerem koli posojilu, na primer izpolnite številke od 1 do 360. To lahko pokrije standardno 30-letno posojilo, če plačujete mesečna plačila.
   

⭐️ 2. korak: spremenite formule za plačilo, obresti in glavnice s funkcijo IF

Vnesite naslednje formule v ustrezne celice in nato povlecite ročico za polnjenje, da te formule razširite na največje število plačilnih obdobij, ki ste jih nastavili.

● Plačilna formula:

Običajno za izračun plačila uporabite funkcijo PMT. Za vključitev stavka IF je sintaksna formula naslednja:

Formule so torej naslednje:

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

● Obrestna formula:

Sintaksna formula je:

Formule so torej naslednje:

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

● Osnovna formula:

Sintaksna formula je:

Formule so torej naslednje:

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

⭐️ 3. korak: Prilagodite formulo preostalega stanja

Za preostalo stanje lahko običajno od prejšnjega stanja odštejete glavnico. S stavkom IF ga spremenite kot:

● Prva bilančna celica: (E7)

=B4-D7

● Druga bilančna celica: (E8)

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

⭐️ 4. korak: Naredite povzetek posojila

Ko nastavite amortizacijski načrt s spremenjenimi formulami, je naslednji korak ustvariti povzetek posojila.

● Za izračun skupnih plačil:

=SUM(B7:B366)

● Za izračun skupnih obresti:

=SUM(C7:C366)

⭐️ Rezultat:

Zdaj imate obsežen in dinamičen amortizacijski načrt v Excelu, skupaj s podrobnim povzetkom posojila. Kadarkoli prilagodite obdobje plačilnega obdobja, se bo celoten amortizacijski načrt samodejno posodobil, da bo odražal te spremembe. Oglejte si spodnjo predstavitev:

Ustvarite amortizacijski načrt z dodatnimi plačili

Z dodatnimi vplačili, ki presegajo predvidena, je mogoče posojilo hitreje odplačati. Amortizacijski načrt, ki vključuje dodatna plačila, ko je ustvarjen v Excelu, prikazuje, kako lahko ta dodatna plačila pospešijo odplačilo posojila in zmanjšajo skupne plačane obresti. To lahko nastavite tako:

⭐️ 1. korak: Nastavite podatke o posojilu in amortizacijsko tabelo

1. V celice vnesite podatke o relativnem posojilu, kot so letna obrestna mera, rok posojila v letih, število plačil na leto, znesek posojila in dodatno plačilo, kot je prikazano na spodnjem posnetku zaslona:
   
2. Nato izračunajte načrtovano plačilo.
   Poleg vnosnih celic je za naše nadaljnje izračune potrebna še ena vnaprej določena celica - predvideni znesek plačila. To je redni znesek plačila posojila ob predpostavki, da ni dodatnih plačil. Uporabite naslednjo formulo v celici B6:

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

   
   
3. Nato ustvarite amortizacijsko tabelo v Excelu:
   • Nastavite določene oznake, kot so Obdobje, Razpored plačil, Dodatno plačilo, Skupno plačilo, Obresti, Glavnica, Preostalo stanje v celicah A8:G8;
   • V stolpec Obdobje vnesite največje število plačil, ki bi jih lahko upoštevali pri katerem koli posojilu. Na primer, izpolnite številke od 0 do 360. To lahko pokrije standardno 30-letno posojilo, če plačujete mesečna plačila;
   • Za obdobje 0 (vrstica 9 v našem primeru) pridobite vrednost stanja s to formulo = B4, kar ustreza začetnemu znesku posojila. Vse druge celice v tej vrstici naj bodo prazne.
   • 

⭐️ 2. korak: Ustvarite formule za amortizacijski načrt z dodatnimi plačili

Eno za drugo vnesite naslednje formule v ustrezne celice. Da bi izboljšali obravnavo napak, smo to in vse prihodnje formule vključili v funkcijo IFERROR. Ta pristop pomaga preprečiti številne morebitne napake, do katerih lahko pride, če je katera od vnosnih celic prazna ali vsebuje nepravilne vrednosti.

● Izračunajte načrtovano plačilo:

V celico B10 vnesite naslednjo formulo:

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

● Izračunajte dodatno plačilo:

V celico C10 vnesite naslednjo formulo:

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

● Izračunajte skupno plačilo:

V celico D10 vnesite naslednjo formulo:

=IFERROR(B10+C10, "")

● Izračunajte glavnico:

V celico E10 vnesite naslednjo formulo:

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

● Izračunajte obresti:

V celico F10 vnesite naslednjo formulo:

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

● Izračunajte preostalo stanje

V celico G10 vnesite naslednjo formulo:

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

Ko dokončate vsako od formul, izberite obseg celic B10:G10 in uporabite ročico za polnjenje, da povlečete in razširite te formule navzdol skozi celoten nabor plačilnih obdobij. Za vsa neizkoriščena obdobja bodo celice pokazale 0 s. Oglejte si posnetek zaslona:

⭐️ 3. korak: Naredite povzetek posojila

● Pridobite načrtovano število plačil:

=B2:B3

● Pridobite dejansko število plačil:

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

● Pridobite skupna dodatna plačila:

=SUM(C10:C369)

● Pridobite skupne obresti:

=SUM(F10:F369)

⭐️ Rezultat:

Če sledite tem korakom, ustvarite dinamični načrt amortizacije v Excelu, ki upošteva dodatna plačila.

Ustvarite amortizacijski načrt s pomočjo Excelove predloge

Ustvarjanje amortizacijskega načrta v Excelu z uporabo predloge je enostavna in časovno učinkovita metoda. Excel ponuja vgrajene predloge, ki samodejno izračunajo obresti, glavnico in preostalo stanje vsakega plačila. Tukaj je opisano, kako ustvarite amortizacijski načrt z uporabo Excelove predloge:

1. klik file > Novo, v iskalno polje vnesite amortizacijski načrtin pritisnite Vnesite ključ. Nato izberite predlogo, ki najbolj ustreza vašim potrebam, tako da jo kliknete. Tukaj bom na primer izbral predlogo preprostega posojilnega kalkulatorja. Oglejte si posnetek zaslona:
   
2. Ko izberete predlogo, kliknite na ustvarjanje gumb, da ga odprete kot nov delovni zvezek.
3. Nato vnesite lastne podatke o posojilu, predloga mora samodejno izračunati in zapolniti razpored na podlagi vaših vnosov.
4. Končno shranite svoj novi delovni zvezek amortizacijskega načrta.