Wykorzystanie Excela w wiekowaniu kredytów bankowych

Zgodnie z ustawą o rachunkowości przedsiębiorstwa sporządzające bilans zobowiązane są do wykazania w sprawozdaniu wartości zobowiązań kredytowych na dzień bilansowy w układzie czasowym krótkoterminowym i długoterminowym.

Podstawą takiej kalkulacji zwykle jest harmonogram spłaty kredytu stanowiący załącznik do umowy kredytowej. Jest to w pełni uzasadnione, o ile harmonogram spłat zakłada równe raty kapitałowe, niestety, w przypadku harmonogramu annuitetowego (równe raty kapitałowo-odsetkowe) poziom kapitału do spłaty i jego wiekowanie zależne jest od zmian stóp procentowych i ich poziomu na dzień wyceny.
Pierwszym rozwiązaniem jest przeprowadzenie odrębnej analizy dla każdej umowy kredytowej (annuitetowy rozkład ma zwykle umowa leasingu). Jednak w przypadku wielu umów czynność staje się kłopotliwa i czasochłonna. Dodatkowo sprawa komplikuje się w momencie, kiedy płatności mają charakter kwartalny.
Celem niniejszego artykułu jest przedstawienie sposobu budowy arkusza Excel, który pozwoli zautomatyzować budowanie harmonogramów spłat i analizę struktury wiekowej.
Docelowa postać arkusza będzie miała następującą postać:

W komórkach E2, E3, E4 wprowadzamy kolejno obowiązujący na dzień analizy poziom indeksów dla poszczególnych walut opisanych w kolumnie D (PLN, CHF, EUR). W komórce D6 wprowadzamy datę analizy w układzie RRRR-MM-DD.
Od wiersza 8. (nagłówek) wprowadzone są dane z systemu księgowego w układzie:

• numer konta,
• opis,
• waluta (PLN/EUR/CHF),
• marża kredytu w procentach,
• indeks dopisany według funkcji (=WYSZUKAJ.PIONOWO(E9;$D$2:$E$4;2;0)/100), gdzie dla zadanej waluty (E9) w tabeli ($D$2:$E$4) wyszukiwana jest ta waluta i zwracana wartość z drugiej kolumny,
• procent łączny jako suma kolumn G i H,
• koniec umowy, czyli data zakończenia kredytu zgodnie z umową,
• YTM, tj. liczba miesięcy do końca trwania umowy (DNI.360($D$6;I9)/30), według funkcji obliczającej liczbę dni pomiędzy datą analizy a datą końca umowy w ujęciu roku księgowego 360, podzielona przez 30,
• saldo PLN i walutowe pochodzące z systemu księgowego.

Arkusz ten o nazwie „Wiekowanie” stanowi interfejs, natomiast sama kalkulacja odbywa się w arkuszu pomocniczym „Kalkulacja”.

Pola z czerwoną czcionką, będą w wyniku kodu Visual Basic (VBA) zasilane danymi z interfejsu. Pozostałe pola stanowią model harmonogramu o długości 120 miesięcy (przy założeniu, że żaden kredyt nie będzie dłuższy niż 120 miesięcy).
Kolumna C zawiera liczbę porządkową od 0 do 120. W kolumnie D wpisano odpowiednią datę płatności. Ponieważ data analizy jest datą końca miesiąca, pierwsza płatność następować będzie w kolejnym miesiącu. Data ta została określona z wykorzystaniem funkcji (w wierszu 10.):
=EOMONTH(D9;1), czyli do każdej dat z pola powyżej pola obliczana jest data ostatniego dnia miesiąca kolejnego.
Wartość kolumny „Koszyk” określona jest w następujący sposób:

• kolejne 3 miesiące jako [1-3],
• następne 9 kolejnych jako [3-12],
• pozostałe 48 jako [12-60],
• kolejne aż do pozycji z liczbą porządkową 120 jako [>60].

Kluczową kolumną dla kalkulacji jest kolumna płatności annuitetowej (kolumna G „PMT”), opisana wartością wyliczaną w komórce G7. W każdym wierszu kolumny G wykorzystano funkcję =JEŻELI(D10<=$F$7;JEŻELI(MOD(MIESIĄC(D10);3)=0;$G$7;0);0). W pierwszym kroku sprawdzane jest, czy data płatności obliczona w kolumnie D jest mniejsza od daty końca umowy. Jeżeli warunek jest prawdziwy, to sprawdzane jest, czy dany miesiąc stanowi ostatni miesiąc kwartału (reszta z dzielenia indeksu miesiąca przez 3 powinna być równa 0). Jeżeli ten warunek jest spełniony, to w danej komórce wstawiana jest wyliczona wartość kapitału z komórki G7.
W kolejnym kroku wartość raty annuitetowej rozbijana jest na dwie części: odsetkową (kolumna H) i kapitałową (kolumna I). Część odsetkowa obliczona jest jako iloczyn zaangażowania (kolumna F) i zadanej stopy odsetkowej (komórka H7), tj. (=F9*$H$7/12).
Znając ratę odsetkową i ratę annuitetową – jako różnicę można obliczyć wartość kapitału, tj. (=G10-H10). Kapitał z kolei stanowi podstawę kalkulacji wartości zaangażowania w każdym okresie harmonogramu (=F9-I10). Analogiczne funkcje jak dla wiersza 10. wpisane są w kolejnych wierszach od 11. do 129.
W wierszu 4. znajduje się podsumowanie analizy, czyli wartość zaangażowania kapitałowego w przedziałach czasowych według funkcji (=SUMA.JEŻELI($E$9:$E$129;F3;$I$9:$I$129)), czyli na podstawie danych kolumny E9:E129, gdzie przypisane są kody koszyka analizy, wyszukiwane są kody identyczne z kodem opisanym w komórce F3 i dla tych pól sumowane są odpowiednie wartości z kolumny I9:I129. W ten sposób z kalkulacji zsumowane zostaną części kapitałowe spłat według przypisanych koszyków czasowych.
Po rozpisaniu kalkulacji harmonogramu kluczowym pozostaje obliczenie wartości annuitetowej dla każdego kredytu. Zarówno zasilenie arkusza „Kalkulacja” oraz wyliczenie wartości kapitałowo-odsetkowej wynika z kodu VBA:

Z komórki (6,4) z 6. wiersza i 4. kolumny pobierana jest wartość daty analizy i zapisywana w zmiennej data_s. Następnie otwierana jest pętla warunkowa, gdzie do czasu, aż w arkuszu „Wiekowanie” napotkany zostanie pusty wiersz do wartości 9 (x=9), będzie dodawana liczba 1, a pętla wykonywana ponownie.
Za każdym wykonaniem pętli, na podstawie wartości komórek w arkuszu „Wiekowanie”, powstają zmienne:

• data końca umowy (data_k),
• stopa procentowa (stopa),
• saldo PLN (saldo).

Wartości te przenoszone są w odpowiednie pola arkusza „Kalkulacja”. Na tej podstawie obliczana jest wartość annuitetowa w komórce G7:
Sheets("Kalkulacja").Cells(7, 9).GoalSeek Goal:=0,
ChangingCell:=Sheets("Kalkulacja").Cells(7, 7)

Powyższy kod jest odzwierciedleniem procedury Excela „Szukaj Wyniku”. Zgodnie z poleceniem w komórce (7,9) arkusza „Kalkulacja” poszukiwana jest wartość 0, na podstawie iteracyjnie zmienianej wartości w komórce G7 (7,7). W komórce (7,9) wpisana jest funkcja (=F9-SUMA(I10:I129)), czyli różnica pomiędzy wartością kredytu a sumą części kapitałowych z kolumny I.
W momencie spełnienia warunku, kiedy znaleziona została taka wartość raty kapitałowo-odsetkowej, która przy zadanych parametrach zapewnia spłatę bieżącego zaangażowania, harmonogram uznany jest za rozliczony. W tym momencie jego wyniki przenoszone są z arkusza „Kalkulacja” do arkusza „Wiekowanie”:
For a = 1 To 4
Sheets("Wiekowanie").Cells(x, 12 + a) = Sheets("Kalkulacja").Cells(4, 5 + a)
Next a

Polecenie to kończy wykonanie bloku pętli, następnie pobierane są kolejne wartości do kalkulacji z arkusza „Wiekowanie” i polecenia wykonywane dla nowych wartości.
Po opisaniu funkcji oraz zapisaniu modułu VBA pozostaje przypisanie makra do formantu przycisku:

Dzięki powyższym działaniom przygotowaliśmy arkusz kalkulacyjny, który po wpisaniu danych źródłowych z systemu księgowego pozwala wygenerować kolejne harmonogramy spłat annuitetowych i pobrać z nich zagregowane wartości zaangażowania w przedziałach czasowych. Wartości te przypisywane są następnie do właściwych umów kredytowych arkusza wynikowego.