A hitelfelvétel esetén készülő üzleti tervek egyik legfontosabb kérdése, hogy képes-e a hitelfelvevő teljesíteni az adósságszolgálatot. Finanszírozói oldalról ennek megállapítására a hitelfelvevőtől cash-flow tervet és adósságszolgálati kalkulációt szoktak kérni, amelynek egyik kiemelt mutatója a DSCR, vagyis a „debt service coverage ratio”.
Ez a mutató jövedelem vagy adósságszolgálat nélküli cash-flow és a adósságszolgálat arányát vizsgálja (adósságszolgálat: tőketörlesztés + kamatok, lízing törlesztő részletek).
A következő videóban egy egyszerű példán keresztül megmutatjuk, hogy miként lehet a mutató értékének alakulását excel segítségével modellezni.
Az Excel számos függvénnyel támogatja a hiteltörlesztések alakulásának modellezést, mi ezekből a PRÉSZLET() és RÉSZLET() függvényt fogjuk használni, 3 különböző esetre vonatkoztatva, kicsit eltérő módon. A példában mindhárom hitel annuitásos törlesztésű, fix kamatozású hitel, 4%-os kamattal (a példa következő bővített részében a változó kamatozású hitelt fogunk vizsgálni).
Hamarosan új résszel folytatjuk, ahol már változó kamatozású hitellel számolunk és a MonteCarlo szimuláció lehetőségeit is használni fogjuk!
Szeretnéd az Excel lehetőségeit üzleti problémák megoldására, riport készítésre profi módon, hatékonyan használni?
Haladó üzleti excel képzés
by SonicData
Addig is a videóban bemutatott modellezési lépéseket az alábbiakban néhány pontban összefoglaljuk:
1. lépés:
hitel1 – tőke: =HAHIBA(-PRÉSZLET($D3;I$2;$E3;$C3;0;0);0)
A PRÉSZLET függvény egy annuitásos (fix törlesztő részletű) hiteltörlesztésen belül tőketörlesztés összegét számítja ki a futamidőn belül egy adott időszakra. Az adósságszolgálat ugyan fix, de a tőketörlesztés és kamatok arány változik ezen belül!
hitel1 – kamat: =HA(I3>0;HAHIBA(-RÉSZLET($D3;$E3;$C3;0;0)-I3;0);0)
A RÉSZLET() függvény az annuitásos törlesztés esetén az adott időszaki teljes adósságszolgálat értékét vagyis a kamat és tőketörlesztés összegét számítja ki. Mivel magát a tőkerészt már kiszámoltuk a PRÉSZLET() függvénnyel, ezért a kamat a RÉSZLET() és PRÉSZLET() függvényekkel számolt értékek különbözete lesz.
2. lépés:
hitel2 – tőke:
=HAHIBA(-PRÉSZLET($D7;I$2;$E7;$C7;-$C7*$F7;0);0)+(I2=$E7)*($C$7*$F$7)
A „hitel2” esetében ún. maradványértékkel vagy balloon-al is számolunk, ezért az PRÉSZLET 5. paramétereként – [jövőbeli érték] – meg kell adni, hogy mekkora a futamidő legvégén fennmaradó, egy összegben esedékes tőkerész.
hitel2 – kamat:
J7, első időszak: =C7*D7, utána a J8 cellától: =($C7-SZUM($I7:I7))*$D7
A képlet első zárójeles felében az induló hitelösszegből kivonjuk az előző időszakok tőketörlesztéseinek halmozott összegét, vagyis az adott év (kamatperiódus) induló hitelösszegét kapjuk. Ezt pedig megszorozzuk az éves kamatlábbal.
3. lépés:
hitel3 – adósságszolgálat:
=HAHIBA(-RÉSZLET($D11;$E11;$C11;0;0);0)*(I2<=$E11)
A „hitel2” esetében a teljes törlesztő részletet számoljuk ki először a RÉSZLET() függvénnyel. A képlet második zárójeles tagja egy feltételvizsgálatot jelent: csak azokban az időszakokban számolunk a törlesztéssel, amely a futamidőn belül van (a futamidő végét követően az adósságszolgálat értelemszerűen nulla).
hitel3 – kamat: ugyanúgy számoljuk mint a „hitel2” esetében
J12, első időszak: =C11*D11, utána a J8 cellától: =($C11-SZUM($I11:I11))*$D11
hitel3 – tőketörlesztés: az éves adósságszolgálat és a tárgyévi fizetett kamat különbözete
4. lépés:
Az adósságszolgálati kalkulációt követően meg kell tervezni az annak fedezeteként rendelkezésre álló jövedelmet vagy cash-flowt is. Mivel DSCR mutató számítását végezzük, ezért annak definíciójából kiindulva a nettó jövedelemmel számolunk.
Hozzá kell tenni, hogy a DSCR mutatónak több definíciója is létezik, akár lehet az alábbi képlettől eltérő is:
nettó jövedelem
DSCR = ------------------------------
adósságszolgálat
ahol a nettó jövedelem =
+ EBITDA
- amortizáció*
- fizetett kamatok
- nyereségadó
* bizonyos esetekben az éves beruházás összegét vonják le, az amortizáció helyett