A hiteltörlesztési ütemterv elkészítése az Excelben értékes készség lehet, amely lehetővé teszi a hiteltörlesztések hatékony megjelenítését és kezelését. Az amortizációs ütemterv egy táblázat, amely részletezi a törlesztő kölcsönök (általában jelzálog- vagy autóhitel) minden időszakos kifizetését. Az egyes kifizetéseket kamat- és tőkeelemekre bontja, és minden kifizetés után megmutatja a fennmaradó egyenleget. Nézzünk egy lépésről lépésre szóló útmutatót egy ilyen ütemezés elkészítéséhez az Excelben.

Mi az amortizációs ütemezés?

Készítsen amortizációs ütemtervet Excelben

Hozzon létre amortizációs ütemtervet változó számú időszakra

Készítsen amortizációs ütemtervet extra kifizetésekkel

Készítsen amortizációs ütemtervet (extra kifizetésekkel) az Excel sablon használatával

Mi az amortizációs ütemezés?

Az amortizációs ütemterv egy részletes táblázat, amelyet a hitelszámításoknál használnak, és amely bemutatja a kölcsön időbeli visszafizetésének folyamatát. Az amortizációs ütemezést általában fix kamatozású hiteleknél, például jelzálog-, autóhitelek és személyi kölcsönöknél alkalmazzák, ahol a fizetés összege a kölcsön futamideje alatt változatlan marad, de a kamat és a tőkeösszeg aránya idővel változik.

A hiteltörlesztési ütemterv Excelben történő létrehozásához valóban elengedhetetlenek a beépített PMT, PPMT és IPMT funkciók. Nézzük meg, mit csinálnak az egyes funkciók:

• PMT funkció: Ezzel a funkcióval kiszámítható a kölcsön időszakonkénti teljes kifizetése állandó törlesztőrészletek és állandó kamatláb alapján.
• IPMT funkció: Ez a funkció kiszámítja egy fizetés kamat részét egy adott időszakra.
• PPMT funkció: Ez a funkció egy adott időszakra vonatkozó fizetés tőkerészének kiszámítására szolgál.

Az Excel ezen funkcióinak használatával részletes törlesztési ütemtervet hozhat létre, amely az egyes kifizetések kamat- és tőkeösszetevőit mutatja, valamint a kölcsön fennmaradó egyenlegét minden egyes fizetés után.

Készítsen amortizációs ütemtervet Excelben

Ebben a részben két különböző módszert mutatunk be az amortizációs ütemezés Excelben történő létrehozására. Ezek a módszerek különféle felhasználói preferenciákat és készségszinteket szolgálnak ki, biztosítva, hogy bárki, függetlenül attól, hogy jártas-e az Excelben, sikeresen összeállíthassa a hitelének részletes és pontos törlesztési ütemezését.

A képletek mélyebb megértést tesznek lehetővé az alapul szolgáló számításokról, és rugalmasságot biztosítanak az ütemezés egyedi igényeknek megfelelő testreszabásához. Ez a megközelítés ideális azok számára, akik gyakorlati tapasztalatot szeretnének szerezni, és világos betekintést szeretnének szerezni az egyes kifizetések tőke- és kamatkomponensekre való felosztásába. Most bontsuk le lépésről lépésre az amortizációs ütemterv létrehozásának folyamatát az Excelben:

⭐️ 1. lépés: Állítsa be a hitelinformációkat és a törlesztési táblázatot

1. Írja be a cellákba a relatív hitelinformációkat, például az éves kamatlábat, a hitel futamidejét években, az évi fizetések számát és a kölcsön összegét a következő képernyőkép szerint:
   
2. Ezután hozzon létre egy amortizációs táblázatot az Excelben a megadott címkékkel, például Időszak, Fizetés, Kamat, Tőke, Fennmaradó egyenleg az A7:E7 cellákban.
3. Az Időszak oszlopba írja be az időszak számait. Ebben a példában a kifizetések teljes száma 24 hónap (2 év), ezért az 1-től 24-ig terjedő számokat kell beírnia az Időszak oszlopba. Lásd a képernyőképet:
   
4. Miután beállította a táblázatot a címkékkel és az időszakszámokkal, folytathatja a képletek és értékek megadását a Fizetés, Kamat, Tőke és Egyenleg oszlopokba a kölcsön sajátosságai alapján.

⭐️ 2. lépés: Számítsa ki a teljes fizetési összeget a PMT funkció segítségével

A PMT szintaxisa a következő:

Írja be a következő képletet a B7 cellába, majd húzza le a kitöltő fogantyút a képlet más cellákba való kitöltéséhez, és látni fogja az összes időszakra vonatkozó állandó fizetési összeget. Lásd a képernyőképet:

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

⭐️ 3. lépés: Számítsa ki a kamatot az IPMT funkció segítségével

Ebben a lépésben minden fizetési időszakra kiszámolja a kamatot az Excel IPMT funkciójával.

Írja be a következő képletet a C7 cellába, majd húzza le a kitöltő fogantyút az oszlopban a képlet kitöltéséhez, és megkapja az egyes időszakokra vonatkozó kamatot.

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

⭐️ 4. lépés: Számítsa ki a tőkeösszeget a PPMT függvény használatával

Az egyes időszakokra vonatkozó kamat kiszámítása után az amortizációs ütemterv létrehozásának következő lépése az egyes kifizetések tőkerészének kiszámítása. Ez a PPMT funkcióval történik, amely arra szolgál, hogy meghatározza a fizetés tőkerészét egy adott időszakra, állandó fizetések és állandó kamatláb alapján.

Az IPMT szintaxisa a következő:

A PPMT képlet szintaxisa és paraméterei megegyeznek a korábban tárgyalt IPMT képletben használtakkal.

Írja be a következő képletet a D7 cellába, majd húzza le a kitöltő fogantyút az oszlopban, hogy kitöltse az egyes időszakok főösszegét. Lásd a képernyőképet:

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

⭐️ 5. lépés: Számítsa ki a fennmaradó egyenleget

Az egyes kifizetések kamatának és tőkeösszegének kiszámítása után a törlesztési ütemterv következő lépéseként minden fizetés után kiszámítja a kölcsön fennmaradó egyenlegét. Ez az ütemezés kulcsfontosságú része, mivel megmutatja, hogyan csökken a hitelegyenleg idővel.

1. Az egyenleg oszlop első cellájába – E7 írja be a következő képletet, ami azt jelenti, hogy a fennmaradó egyenleg az eredeti hitelösszeg lesz, mínusz az első befizetés tőkerésze:

   =B4-D7

   
2. A második és az azt követő időszakokra vonatkozóan úgy számítsa ki a fennmaradó egyenleget, hogy az előző időszak egyenlegéből levonja az aktuális időszak tőkerészletét. Alkalmazza a következő képletet az E8 cellába:

   =E7-D8

    Megjegyzések: A mérlegcellára való hivatkozásnak relatívnak kell lennie, így a képlet lehúzásakor frissül.
   
3. Ezután húzza le a kitöltő fogantyút az oszlopra. Amint láthatja, minden cella automatikusan igazodik a fennmaradó egyenleg kiszámításához a frissített tőkefizetések alapján.
   

⭐️ 6. lépés: Készítsen hitelösszesítést

A részletes törlesztési ütemterv összeállítása után a hitelösszesítő elkészítése gyors áttekintést nyújthat a kölcsön legfontosabb szempontjairól. Ez az összefoglaló jellemzően tartalmazza a kölcsön teljes költségét, a teljes fizetett kamatot.

● A teljes kifizetés kiszámításához:

=SUM(B7:B30)

● A teljes kamat kiszámítása:

=SUM(C7:C30)

⭐️ Eredmény:

Most egy egyszerű, de átfogó hiteltörlesztési ütemtervet sikerült létrehozni. lásd a képernyőképet:

Hozzon létre amortizációs ütemtervet változó számú időszakra

Az előző példában rögzített számú fizetéshez készítünk egy hiteltörlesztési ütemtervet. Ez a megközelítés tökéletes egy adott kölcsön vagy jelzáloghitel kezelésére, ahol a feltételek nem változnak.

Ha azonban olyan rugalmas törlesztési ütemtervet szeretne létrehozni, amely ismételten felhasználható változó futamidejű hitelekhez, és lehetővé teszi a fizetések számának módosítását a különböző hitelforgatókönyvek esetén, akkor részletesebb módszert kell követnie.

⭐️ 1. lépés: Állítsa be a hitelinformációkat és a törlesztési táblázatot

1. Írja be a cellákba a relatív hitelinformációkat, például az éves kamatlábat, a hitel futamidejét években, az évi fizetések számát és a kölcsön összegét a következő képernyőkép szerint:
   
2. Ezután hozzon létre egy amortizációs táblázatot az Excelben a megadott címkékkel, például Időszak, Fizetés, Kamat, Tőke, Fennmaradó egyenleg az A7:E7 cellákban.
3. Az Időtartam oszlopban adja meg a fizetések legnagyobb számát, amelyet bármely hitel esetében fontolóra vehet, például töltse ki az 1-től 360-ig terjedő számokat. Ez egy normál 30 éves kölcsönre is kiterjedhet, ha havi törlesztőrészletet fizet.
   

⭐️ 2. lépés: Módosítsa a fizetési, kamat- és alapképleteket IF funkcióval

Írja be a következő képleteket a megfelelő cellákba, majd húzza el a kitöltő fogantyút a képletek kiterjesztéséhez a beállított fizetési időszakok maximális számára.

● Fizetési képlet:

Általában a PMT függvényt használja a fizetés kiszámításához. Az IF utasítás beépítéséhez a szintaktikai képlet a következő:

Tehát a képletek a következők:

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

● Kamatképlet:

A szintaktikai képlet a következő:

Tehát a képletek a következők:

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

● Alapképlet:

A szintaktikai képlet a következő:

Tehát a képletek a következők:

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

⭐️ 3. lépés: Állítsa be a fennmaradó egyenleg képletét

A fennmaradó egyenleg esetében általában levonhatja a tőke összegét az előző egyenlegből. IF utasítással módosítsa a következőképpen:

● Az első egyensúlyi cella: (E7)

=B4-D7

● A második mérlegcella: (E8)

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

⭐️ 4. lépés: Készítsen hitelösszesítést

Miután beállította az amortizációs ütemezést a módosított képletekkel, a következő lépés egy hitelösszesítés létrehozása.

● A teljes kifizetés kiszámításához:

=SUM(B7:B366)

● A teljes kamat kiszámítása:

=SUM(C7:C366)

⭐️ Eredmény:

Most egy átfogó és dinamikus amortizációs ütemterv áll rendelkezésére az Excelben, a hitelösszesítéssel kiegészítve. Amikor módosítja a fizetési időszak időtartamát, a teljes amortizációs ütemezés automatikusan frissül, hogy tükrözze ezeket a változásokat. Tekintse meg az alábbi bemutatót:

Készítsen amortizációs ütemtervet extra kifizetésekkel

Az ütemezetten túli pótbefizetésekkel gyorsabban törleszthető a hitel. Az Excelben elkészített extra kifizetéseket tartalmazó amortizációs ütemterv bemutatja, hogy ezek a kiegészítő kifizetések hogyan gyorsíthatják fel a kölcsön kifizetését és csökkenthetik a teljes fizetett kamatot. A következőképpen állíthatja be:

⭐️ 1. lépés: Állítsa be a hitelinformációkat és a törlesztési táblázatot

1. Adja meg a relatív hitelinformációkat, például az éves kamatlábat, a kölcsön futamidejét években, az évi fizetések számát, a kölcsön összegét és az extra befizetést a cellákba az alábbi képernyőképen látható módon:
   
2. Ezután számítsa ki az ütemezett fizetést.
   A további számításokhoz a bemeneti cellákon kívül még egy előre meghatározott cellára van szükség - az ütemezett fizetési összegre. Ez a kölcsön szokásos fizetési összege, feltéve, hogy nem történik további kifizetés. Alkalmazza a következő képletet a B6 cellába:

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

   
   
3. Ezután hozzon létre egy amortizációs táblázatot az Excelben:
   • Állítsa be a megadott címkéket, például Időszak, Fizetési ütemezés, Extra fizetés, Teljes fizetés, Kamat, Tőke, Fennmaradó egyenleg az A8:G8 cellákban;
   • Az Időszak oszlopban adja meg a fizetések legmagasabb számát bármely kölcsön esetében. Például írja be a 0 és 360 közötti számokat. Ez egy normál 30 éves kölcsönre is kiterjedhet, ha havi törlesztőrészletet fizet;
   • A 0. periódushoz (esetünkben a 9. sor) ezzel a képlettel kérje le az Egyenleg értéket = B4, amely megfelel a kezdeti hitelösszegnek. Ebben a sorban az összes többi cellát üresen kell hagyni.
   • 

⭐️ 2. lépés: Kréta az amortizációs ütemezés képletei extra kifizetésekkel

Írja be egyenként a következő képleteket a megfelelő cellákba. A hibakezelés javítása érdekében ezt és az összes jövőbeli képletet az IFERROR függvénybe foglaljuk. Ez a megközelítés segít elkerülni a többszörös lehetséges hibákat, amelyek akkor fordulhatnak elő, ha a beviteli cellák bármelyike ​​üresen marad, vagy helytelen értékeket tartalmaz.

● Számítsa ki az ütemezett fizetést:

Írja be a következő képletet a B10 cellába:

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

● Számítsa ki a többletfizetést:

Írja be a következő képletet a C10-es cellába:

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

● Számítsa ki a teljes kifizetést:

Írja be a következő képletet a D10 cellába:

=IFERROR(B10+C10, "")

● Számítsa ki a főösszeget:

Írja be a következő képletet az E10 cellába:

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

● Számítsa ki a kamatot:

Írja be a következő képletet az F10 cellába:

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

● Számítsa ki a fennmaradó egyenleget

Írja be a következő képletet a G10 cellába:

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

Miután kitöltötte az összes képletet, válassza ki a B10:G10 cellatartományt, és a kitöltő fogantyúval húzza le és terjesztheti ki ezeket a képleteket a teljes fizetési időszakon keresztül. A fel nem használt időszakok esetén a cellák 0-t mutatnak. Lásd a képernyőképet:

⭐️ 3. lépés: Készítsen hitelösszesítést

● Kapjon ütemezett számú kifizetést:

=B2:B3

● A kifizetések tényleges számának megtekintése:

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

● Teljes extra kifizetések:

=SUM(C10:C369)

● Teljes kamat:

=SUM(F10:F369)

⭐️ Eredmény:

Az alábbi lépések követésével dinamikus amortizációs ütemezést hoz létre az Excelben, amely figyelembe veszi a többletbefizetéseket.

Készítsen amortizációs ütemtervet Excel-sablon segítségével

Az amortizációs ütemezés elkészítése az Excelben sablon segítségével egyszerű és időtakarékos módszer. Az Excel beépített sablonokat biztosít, amelyek automatikusan kiszámítják az egyes kifizetések kamatait, tőkéjét és fennmaradó egyenlegét. A következőképpen hozhat létre amortizációs ütemezést Excel-sablon használatával:

1. Kattints filé > Új, a keresőmezőbe írja be amortizációs ütemterv, és nyomja meg a gombot belép kulcs. Ezután válassza ki az igényeinek leginkább megfelelő sablont, kattintson rá. Például itt az Egyszerű hitelkalkulátor sablont választom. Lásd a képernyőképet:
   
2. Miután kiválasztotta a sablont, kattintson a Teremt gombbal nyithatja meg új munkafüzetként.
3. Ezután adja meg saját hiteladatait, a sablonnak automatikusan ki kell számítania és kitöltenie az ütemezést az Ön által megadott adatok alapján.
4. Végül mentse el az új amortizációs ütemezési munkafüzetet.