az oktatóanyag bemutatja, hogyan építsünk egy amortizációs ütemtervet az Excelben, hogy részletezzük az amortizáló kölcsön vagy jelzálog időszakos kifizetéseit.
az amortizáló hitel csak egy divatos módja annak, hogy meghatározzuk a kölcsönt, amelyet részletekben fizetnek vissza a hitel teljes futamideje alatt.
alapvetően minden hitel valamilyen módon amortizálódik. Például egy teljesen amortizáló hitel 24 hónapra 24 egyenlő havi kifizetéssel rendelkezik., Minden kifizetés bizonyos összeget alkalmaz a tőke felé, mások pedig a kamat felé. A hitel minden kifizetésének részletezéséhez építhet egy hitel amortizációs ütemtervet.
az amortizációs ütemterv egy táblázat, amely felsorolja a kölcsön vagy jelzálog időszakos kifizetéseit az idő múlásával, lebontja az egyes kifizetéseket tőkére és kamatokra, és megmutatja a fennmaradó egyenleget minden kifizetés után.,
- Hogyan lehet létrehozni egy hiteltörlesztés ütemezése az Excel
- Törlesztés ütemezése változó időszakok száma
- hiteltörlesztés ütemezése a további kifizetések
- Excel amortizáció template
, Hogyan kell létrehozni egy hiteltörlesztés ütemezése az Excel
építeni egy hitel vagy jelzáloghitel-törlesztés ütemezése az Excel, meg kell, hogy használja a következő funkciók érhetők el:
- RÉSZLET funkció – számítja a teljes összeg a rendszeres kifizetés. Ez az összeg a hitel teljes időtartama alatt állandó marad.,
- PPMT funkció-megkapja az egyes kifizetések fő részét, amely a kölcsönkötelezett felé halad, azaz a kölcsönzött összeget. Ez az összeg a későbbi kifizetéseknél nő.
- IPMT funkció-megtalálja az egyes kifizetések kamatrészét, amely a kamat felé halad. Ez az összeg minden fizetésnél csökken.
most lépjünk át a folyamaton lépésről lépésre.,
Állítsa be az amortizáció táblázat
először is, adjuk meg a bemeneti sejtek, ahol meg kell adnia az ismert összetevők egy hitel:
- C2 – éves kamatláb
- C3 – hitel kifejezés év
- C4 – kifizetések száma évente
- C5 – kölcsön összege
A következő dolog, amit tenned, hogy hozzon létre egy amortizáció táblázat a címkék (Időszak, Fizetés, Kamat, Tőke, Egyensúly) a A7:E7., Ebben az Időszakban oszlopba írja be egy számsor egyenlő a teljes kifizetések száma (1 – 24 ebben a példában):
az összes ismert elemek a helyén, menjünk a legérdekesebb része – hiteltörlesztés képletek.
Számítsa ki a teljes fizetési összeget (PMT formula)
a fizetési összeget a PMT(rate, nper, pv, , ) funkcióval kell kiszámítani.
a különböző fizetési gyakoriságok helyes kezeléséhez (például heti, havi, negyedéves stb.,), akkor összhangban kell lennie a megadott értékek az arány és NPER érvek:
- ráta-Oszd meg az éves kamatláb száma fizetési időszakok évente ($C$2 / $C$4).
- NPER-szorozza meg az évek számát az évi fizetési időszakok számával ($C$3*$C$4).
- a pv argumentumhoz írja be a hitel összegét ($C$5).
- az fv és a típus argumentumok elhagyhatók, mivel alapértelmezett értékeik csak jól működnek számunkra (az utolsó kifizetés utáni egyenleg 0; a kifizetések minden időszak végén történnek).,
Amivel a fenti érvek együtt ezt a képlet:
=PMT($C$2/$C$4, $C$3*$C$4, $C$5)
felhívjuk a figyelmet, hogy használjuk abszolút cellahivatkozások, mert ez a formula kell másolni, hogy az alábbi sejtek változtatások nélkül.,
adja meg a PMT képlet B8, húzza le az oszlop, mint állandó fizetési összeget minden időszakokban:
Számítani kamat (RRÉSZLET képlet)
ahhoz, Hogy megtalálja a kamat része minden rendszeres fizetés, használja a RRÉSZLET(kamatláb, per, időszakok_száma, pv, , ) funkció: =IPMT($C$2/$C$4, A8, $C$3*$C$4, $C$5)
Minden az érvek ugyanazok, mint a PMT képlet, kivéve az egy érv, amely megadja, hogy a fizetési határidő., Ezt az argumentumot relatív cellás referenciaként (A8) adjuk meg, mivel azt egy sor relatív pozíciója alapján kell megváltoztatni, amelyre a képletet másolják.,
Ez a képlet megy, C8, majd másolja le, hogy annyi sejtek szükséges:
Keresés igazgató (PRÉSZLET képlet)
számítani a fő része minden rendszeres fizetés, használja ezt a PRÉSZLET képlet: =PPMT($C$2/$C$4, A8, $C$3*$C$4, $C$5)
A szintaxis, valamint az érvek pontosan ugyanaz, mint a RRÉSZLET formula fent tárgyalt:
Ez a képlet megy, hogy a ” D ” oszlopban a kezdet a D8:
Szerezd meg a fennmaradó egyenleget
a fennmaradó egyenleg kiszámításához minden egyes időszakra két különböző képletet használunk.
az egyenleg megtalálásához az E8-ban történő első kifizetés után össze kell adni a hitelösszeget (C5) és az első időszak tőkéjét (D8):
=C5+D8
mivel a hitelösszeg pozitív szám, a tőke negatív szám, az utóbbit ténylegesen levonják az előbbiből.,
a második, valamint az összes következő időszakokban, add fel a korábbi egyensúly ez az időszak fő:
=E8+D9
A fenti képlet megy E9, majd másolja le az oszlop. A relatív cellahivatkozások használata miatt a képlet minden sorhoz helyesen igazodik.
ennyi! A havi hiteltörlesztés ütemezése történik:
Tipp: Vissza kifizetések, mint a pozitív számok
Mert egy hitel fizetett ki a bankszámlát, Excel függvények vissza a fizetés, a kamat, illetve igazgató, mint a negatív számok., Alapértelmezés szerint ezek az értékek pirossal vannak kiemelve, zárójelben vannak elhelyezve, amint az a fenti képen látható.
Ha az összes eredményt pozitív számként szeretné megadni, tegyen egy mínusz jelet a PMT, IPMT és PPMT függvények előtt.
a Mérleg képletek használata kivonás helyett, mellett, mint látható a lenti képen:
Törlesztés ütemezése változó időszakok száma
a fenti példában, építettünk egy hiteltörlesztés ütemezése az előre meghatározott számú fizetési határidők., Ez a gyors egyszeri megoldás jól működik egy adott kölcsön vagy jelzálog esetében.
ha újrafelhasználható amortizációs ütemtervet szeretne létrehozni változó számú periódussal, akkor az alábbiakban ismertetett átfogóbb megközelítést kell alkalmaznia.
adja meg az időszak oszlopban a
időszakok maximális számát, illessze be a maximális számú kifizetést, amelyet engedélyezni fog bármilyen kölcsönre, például 1 – től 360-ig. Az Excel Automatikus kitöltési funkcióját kihasználhatja, hogy gyorsabban adjon meg egy sor számot.,
használja, ha az amortizációs képletekben szereplő kimutatások
mivel most sok túlzott időszakszám van, valahogy korlátoznia kell a számításokat egy adott kölcsön kifizetéseinek tényleges számára. Ezt úgy lehet megtenni, hogy az egyes képleteket IF utasításba csomagoljuk. Az IF kimutatás logikai tesztje ellenőrzi, hogy az aktuális sorban szereplő időszakszám kisebb vagy egyenlő-e a kifizetések teljes számával. Ha a logikai teszt igaz,akkor a megfelelő függvény kiszámításra kerül; ha hamis, akkor egy üres karakterlánc kerül vissza.,
feltételezve, hogy az 1. időszak a 8. sorban van, írja be a következő képleteket a megfelelő cellákba, majd másolja át őket az egész táblára.,
Fizetés (B8):
=IF(A8<=$C$3*$C$4, PMT($C$2/$C$4, $C$3*$C$4, $C$5), "")
Kamat (C8):
=IF(A8<=$C$3*$C$4, IPMT($C$2/$C$4, A8, $C$3*$C$4, $C$5), "")
Fő (D8): =IF(A8<=$C$3*$C$4,PPMT($C$2/$C$4, A8, $C$3*$C$4, $C$5), "")
Egyenleg:
1. Időszak (E8), a képlet ugyanaz, mint az előző példa:
=C5+D8
Időszak 2 (E9), valamint az összes későbbi időszakokban a formula ezt a formát: =IF(A9<=$C$3*$C$4, E8+D9, "")
az eredmény, van egy helyesen számított amortizáció menetrend, meg egy rakás üres sorok az időszak számok a kölcsön után fizetett ki.,
elrejtése extra időszakok számok
ha lehet élni egy csomó felesleges időszak számok után jelenik meg az utolsó fizetés, akkor úgy a munkát, és kihagyja ezt a lépést. Ha tökéletességre törekszik, akkor rejtse el az összes fel nem használt időszakot egy feltételes formázási szabály létrehozásával, amely az utolsó fizetés után a betűtípus színét fehérre állítja.,
ehhez válassza ki az összes adatsort, ha az amortizációs táblázat (A8: e367 esetünkben), majd kattintson a Kezdőlap fülre > Feltételes formázás > új szabály… > Használjon képletet annak meghatározásához, hogy mely cellákat formázza.
a megfelelő mezőbe írja be az alábbi képletet, amely ellenőrzi, hogy az A oszlopban szereplő időszakszám nagyobb-e, mint a kifizetések teljes száma:
=$A8>$C$3*$C$4
Ezután kattintson a Format… gombra, majd válassza ki a fehér betűszínt. Kész!,
Készítsen hitelösszefoglalót
a kölcsönre vonatkozó összefoglaló információk áttekintéséhez adjon hozzá még néhány képletet az amortizációs ütemterv tetején.
összes kifizetés (F2):
=-SUM(B8:B367)
teljes kamat (F3):
=-SUM(C8:C367)
ha pozitív számokkal rendelkezik, távolítsa el a mínusz jelet a fenti képletekből.
ennyi! A hitel törlesztési ütemezés befejeződött, jó menni!,
Letöltés hiteltörlesztés ütemezése Excel
, Hogyan kell egy hiteltörlesztés ütemezése extra kifizetések Excel
Az amortizáció menetrend tárgyalt az előző példák könnyű létrehozni, majd kövesse (remélhetőleg :). Azonban kihagynak egy hasznos funkciót, amelyet sok hitelfizető érdekel – további kifizetések a hitel gyorsabb kifizetéséhez. Ebben a példában megnézzük, hogyan lehet létrehozni egy hitel amortizációs ütemtervet extra kifizetésekkel.
definiálja a bemeneti cellákat
a szokásos módon, kezdje a bemeneti cellák beállításával., Ebben az esetben, legyen a neve, ezek a sejtek, mint ahogy írtam alább, hogy a képletek könnyebben olvasható:
- InterestRate – C2 (éves kamatláb)
- LoanTerm – C3 (hitel kifejezés év)
- PaymentsPerYear – C4 (kifizetések száma / év)
- LoanAmount – C5 (a teljes kölcsön összege)
- ExtraPayment – C6 (extra fizetési időszakonként)
Kiszámítja egy ütemezett fizetési
kivéve a bemeneti sejtek, még egy előre meghatározott cella szükséges a további számítások – az ütemezett kifizetési összeg, azaz, az összeget kell fizetni a kölcsön, ha nincs extra kifizetések. Ezt az összeget a következő képlettel kell kiszámítani:
=IFERROR(-PMT(InterestRate/PaymentsPerYear, LoanTerm*PaymentsPerYear, LoanAmount), "")
kérjük, vegye figyelembe, hogy a PMT funkció előtt mínusz jelet helyezünk el, hogy az eredmény pozitív szám legyen. A hibák elkerülése érdekében, ha a bemeneti cellák egy része üres, a PMT képletet az IFERROR funkcióba helyezzük.
írja be ezt a képletet néhány cellába (esetünkben G2), majd nevezze meg azt a cellát, amelyet Ütemezettfizetés.,
állítsa be az amortizációs táblázatot
Hozzon létre egy hitel amortizációs táblázatot az alábbi képernyőképen látható fejlécekkel. Az időszak oszlopban írja be a nullával kezdődő számsorozatot (szükség esetén elrejtheti a 0-as sort később).
ha újrafelhasználható amortizációs ütemtervet kíván létrehozni, adja meg a fizetési időszakok maximális számát (ebben a példában 0-360).
a 0. időszakra (esetünkben a 9.sor) húzza meg az egyenleg értékét, amely megegyezik az eredeti hitel összegével., A sor összes többi cellája üres marad:
képlet G9-ben:
=LoanAmount
4. Készítsen képleteket az amortizációs ütemtervhez extra kifizetésekkel
Ez a munkánk kulcsfontosságú része. Mivel az Excel beépített funkciói nem biztosítanak további kifizetéseket, az összes matematikát egyedül kell elvégeznünk.
írja be a következő képleteket a 10. sorban (1.időszak), majd másolja le őket az összes fennmaradó időszakra.
ütemezett fizetés (B10):
Ha az ütemezett fizetési összeg (G2 cellának nevezett) kisebb vagy egyenlő a fennmaradó egyenleggel (G9), használja a tervezett fizetést. Ellenkező esetben adja hozzá az előző hónap fennmaradó egyenlegét és kamatait.
=IFERROR(IF(ScheduledPayment<=G9, ScheduledPayment, G9+G9*InterestRate/PaymentsPerYear), "")
további óvintézkedésként ezt és az összes későbbi képletet az IFERROR funkcióba csomagoljuk., Ez megakadályozza egy csomó különböző hibát, ha a bemeneti cellák egy része üres vagy érvénytelen értékeket tartalmaz.
Extra fizetés (C10):
használjon IF képletet a következő logikával:
Ha az ExtraPayment összeg (C6 cellának nevezett) kisebb, mint a fennmaradó egyenleg és az ezen időszak főkötelezettje (G9-E10) közötti különbség, térítse vissza az Extrapaymentet; egyébként használja a különbséget.
=IFERROR(IF(ExtraPayment<G9-E10, ExtraPayment, G9-E10), "")
a Teljes Kifizetés (D10)
Egyszerűen adja hozzá a befizetéssel (B10), valamint a plusz kifizetés (C10), a jelenlegi idő:
=IFERROR(B10+C10, "")
Fő (E10)
Ha a fizetést az adott időszakban nagyobb, mint nulla, vissza egy kisebb, a két érték: ütemezett fizetés mínusz kamat (B10-F10), vagy a fennmaradó egyenleg (G9), ellenkező esetben vissza nulla.
=IFERROR(IF(B10>0, MIN(B10-F10, G9), 0), "")
Felhívjuk figyelmét, hogy a megbízó csak a tervezett fizetés egy részét tartalmazza (nem az extra fizetést!) a hitelező felé fordul.,
kamat (F10)
Ha egy adott időszakra vonatkozó ütemezés kifizetése nagyobb, mint nulla, ossza meg az éves kamatlábat (C2 cellának nevezett) az éves kifizetések számával (C4 cellának nevezett), és szorozza meg az eredményt az előző időszak után fennmaradó egyenleggel; ellenkező esetben térjen vissza 0.
=IFERROR(IF(B10>0, InterestRate/PaymentsPerYear*G9, 0), "")
egyenleg (G10)
Ha a fennmaradó egyenleg (G9) nagyobb, mint nulla, vonja le a fizetés fő részét (E10) és az extra fizetést (C10) az előző időszak után fennmaradó egyenlegből (G9); ellenkező esetben térjen vissza 0.,
=IFERROR(IF(G9 >0, G9-E10-C10, 0), "")
Ha minden helyesen tette, a hiteltörlesztés ütemezése ezen a ponton kellene valahogy így néz ki:
Elrejteni extra időszakok
létrehozott feltételes formázási szabályt, hogy elrejtse az értékek, a fel nem használt időszakok, amint azt ez a tipp.,a kifizetések évente:
=LoanTerm*PaymentsPerYear
a Tényleges kifizetések száma:
Gróf sejtek a Teljes Fizetési oszlopot, amely nagyobb, mint nulla, kezdve Időszak 1:
=COUNTIF(D10:D369,">"&0)
a Teljes extra kifizetések:
Hozzáadás fel sejtek a Plusz Kifizetés, oszlop, kezdve Időszak 1:
=SUM(C10:C369)
Összes kamat:
Hozzáadás fel sejtek a Kamat oszlopban kezdődő Időszak 1:
=SUM(F10:F369)
Opcionálisan, elrejteni az az Időszak, 0 sor, valamint a hiteltörlesztés ütemezése a további kifizetések kész!, A lenti képen látható a végeredmény:
Letöltés hiteltörlesztés ütemezése extra kifizetések
Törlesztés ütemezése Excel sablon
ahhoz, Hogy egy remek hiteltörlesztés ütemezése, nincs idő, hogy használja az Excel beépített sablonok. Csak menj Fájl > Új típusú “amortizáció menetrend” a keresőmezőbe, majd választani a sablont, mint, például, ez egy extra kifizetések:
Az, hogy hozzon létre egy hitel vagy jelzáloghitel-törlesztés ütemezése az Excel., Köszönöm, hogy elolvasta, és remélem, hogy találkozunk a blog jövő héten!
elérhető Letöltések
amortizációs ütemterv Minta munkafüzet
- hogyan lehet kiszámítani az összetett érdeklődést az Excel-ben
- hogyan lehet megtalálni a CAGR-t (összetett éves növekedési ráta) az Excel-ben
- százalékos arány kiszámítása az Excelben a következő képlettel: