Vaka: kredi portföyü ve geri ödeme planı

1. Şirketin sorusu ve brif

Kim soruyor? Küçük bir finansman şirketinin risk yöneticisi. Kurul toplantısından önce portföyün 31.12.2024 itibarıyla fotoğrafını istiyor.

Hangi karar? 2025’te hangi segmentte yeni kredi verilmeye devam edilecek, hangisinde koşullar sıkılaştırılacak? Brifin soruları:

1. Her kredinin aylık taksiti ne, taksitin ne kadarı faiz, ne kadarı anapara?
2. Rapor tarihinde portföyde ne kadar bakiye var?
3. Gecikme ve takip (30+ ve 90+ gün) bakiyenin yüzde kaçı; segmentlere göre fark var mı?
4. Portföy ne getiriyor? Riskli krediler hesaba katılınca getiri ne olur?
5. Bir krediye tıklayınca açılan, o kredinin ödeme planını gösteren bir detay sayfası.

Paranın zaman değeri ve PMT fonksiyonunun mantığı Finansal raporlama: gelir tablosu, bütçe sapması, oranlar ve finansal fonksiyonlar dersinde anlatıldı; burada tekrar etmiyoruz, onu bir portföye uyguluyoruz.

2. Veri ve model

Krediler =
DATATABLE(
    "KrediNo", STRING, "Segment", STRING, "Tutar", CURRENCY, "YillikFaiz", DOUBLE,
    "Vade", INTEGER, "Baslangic", DATETIME, "GecikmeGun", INTEGER,
    {
        {"K01", "KOBİ", 400000, 0.42, 24, "2024-01-15", 0},
        {"K02", "KOBİ", 250000, 0.45, 12, "2024-06-01", 45},
        {"K03", "Bireysel", 120000, 0.48, 36, "2023-09-01", 0},
        {"K04", "Bireysel", 80000, 0.50, 24, "2024-03-01", 120},
        {"K05", "Kurumsal", 1000000, 0.38, 36, "2023-04-01", 0},
        {"K06", "Kurumsal", 600000, 0.40, 24, "2024-09-01", 0},
        {"K07", "KOBİ", 300000, 0.44, 18, "2023-12-01", 0},
        {"K08", "Bireysel", 60000, 0.52, 12, "2024-02-01", 35},
        {"K09", "KOBİ", 150000, 0.46, 24, "2024-04-01", 95},
        {"K10", "Kurumsal", 450000, 0.39, 12, "2024-07-01", 0}
    }
)

Modelde ikinci tablo ödeme planıdır: her kredi için her ay bir satır. Krediler ile ödeme planı arasında bire-çok ilişki kurulur (KrediNo), plandaki ödeme tarihi de tarih tablosuna bağlanır.

Veri sınırları:

• Sözleşme bakiyesi: bakiyeyi ödeme planına göre hesaplıyoruz, yani her taksitin zamanında ödendiğini varsayıyoruz. Gecikmedeki kredilerde gerçek bakiye daha yüksektir (ödenmeyen taksitler ve gecikme faizi). Gerçek işte tahsilat tablosu ayrıca gelir.
• Tarih tablosu kısa: uygulamanın Tarih tablosu 31.12.2024’te bitiyor; portföyün son taksiti ise Eylül 2026’da. 222 taksitin 123’ü 2024’ten sonra. Modelde ödeme planını tarih tablosuna bağlayacaksan tarih tablosunu vadelerin sonuna kadar uzat.

3. Analiz adımları

Adım 1: Ödeme planı (amortisman tablosu). Aylık faiz i = yıllık faiz ÷ 12, taksit A = PMT(i, Vade, -Tutar). k. taksitten sonra kalan bakiye için kapalı formül: B_k = P·(1+i)^k − A·((1+i)^k − 1) ÷ i. k. taksitin faizi bir önceki bakiyenin faizidir: B_k−1 × i; anapara = A − faiz. GENERATE her kredi satırı için GENERATESERIES(1, Vade) ile taksit satırlarını üretir:

Ödeme Planı =
GENERATE(
    Krediler,
    VAR i = [YillikFaiz] / 12
    VAR A = PMT(i, [Vade], -[Tutar])
    VAR P0 = [Tutar]
    VAR Bas = [Baslangic]
    RETURN
        SELECTCOLUMNS(
            GENERATESERIES(1, [Vade]),
            "TaksitNo", [Value],
            "OdemeTarihi", EDATE(Bas, [Value]),
            "Taksit", A,
            "Faiz", (P0 * POWER(1 + i, [Value] - 1) - A * (POWER(1 + i, [Value] - 1) - 1) / i) * i,
            "Kalan", P0 * POWER(1 + i, [Value]) - A * (POWER(1 + i, [Value]) - 1) / i
        )
)

Uygulamanın motorunda Krediler modelde olmadığı için aynı tabloyu sorguda değişkenle deneriz. Önce tek bir kredi (K03):

EVALUATE
VAR Krediler =
    DATATABLE(
        "KrediNo", STRING, "Tutar", CURRENCY, "YillikFaiz", DOUBLE, "Vade", INTEGER, "Baslangic", DATETIME,
        {{"K03", 120000, 0.48, 36, "2023-09-01"}}
    )
VAR Plan = GENERATE(
        Krediler,
        VAR i = [YillikFaiz] / 12
        VAR A = PMT(i, [Vade], -[Tutar])
        VAR P0 = [Tutar]
        VAR Bas = [Baslangic]
        RETURN
            SELECTCOLUMNS(
                GENERATESERIES(1, [Vade]),
                "TaksitNo", [Value],
                "OdemeTarihi", EDATE(Bas, [Value]),
                "Taksit", A,
                "Faiz", (P0 * POWER(1 + i, [Value] - 1) - A * (POWER(1 + i, [Value] - 1) - 1) / i) * i,
                "Kalan", P0 * POWER(1 + i, [Value]) - A * (POWER(1 + i, [Value]) - 1) / i
            )
    )
RETURN
    SELECTCOLUMNS(
        FILTER(Plan, [TaksitNo] <= 2 || [TaksitNo] = 15 || [TaksitNo] = 36),
        "Taksit No", [TaksitNo], "Tarih", [OdemeTarihi], "Taksit", [Taksit],
        "Faiz", [Faiz], "Anapara", [Taksit] - [Faiz], "Kalan", [Kalan]
    )

Bulgu: Taksit sabit, içeriği değişiyor. K03’ün ilk taksitinin %76’sı faiz; son taksitin %96’sı anapara. Kredi boyunca toplam 228.471 ₺ ödenecek; bunun 108.471 ₺’si faiz. Son satırda kalan bakiyenin tam 0 çıkması planın doğru kurulduğunun testidir; bu kontrolü her kredi için yap.

Adım 2: Rapor tarihinde kalan bakiye ve 2024 faiz geliri. Her kredi için rapor tarihine kadar vadesi gelen taksitleri sayar, son taksitten sonraki bakiyeyi alırız. Henüz taksiti gelmemiş kredinin bakiyesi tutarın kendisidir.

DEFINE
VAR Krediler = DATATABLE(
    "KrediNo", STRING, "Segment", STRING, "Tutar", CURRENCY, "YillikFaiz", DOUBLE,
    "Vade", INTEGER, "Baslangic", DATETIME, "GecikmeGun", INTEGER,
    {
        {"K01", "KOBİ", 400000, 0.42, 24, "2024-01-15", 0},
        {"K02", "KOBİ", 250000, 0.45, 12, "2024-06-01", 45},
        {"K03", "Bireysel", 120000, 0.48, 36, "2023-09-01", 0},
        {"K04", "Bireysel", 80000, 0.50, 24, "2024-03-01", 120},
        {"K05", "Kurumsal", 1000000, 0.38, 36, "2023-04-01", 0},
        {"K06", "Kurumsal", 600000, 0.40, 24, "2024-09-01", 0},
        {"K07", "KOBİ", 300000, 0.44, 18, "2023-12-01", 0},
        {"K08", "Bireysel", 60000, 0.52, 12, "2024-02-01", 35},
        {"K09", "KOBİ", 150000, 0.46, 24, "2024-04-01", 95},
        {"K10", "Kurumsal", 450000, 0.39, 12, "2024-07-01", 0}
    }
)
VAR Plan = GENERATE(
    Krediler,
    VAR i = [YillikFaiz] / 12
    VAR A = PMT(i, [Vade], -[Tutar])
    VAR P0 = [Tutar]
    VAR Bas = [Baslangic]
    RETURN
        SELECTCOLUMNS(
            GENERATESERIES(1, [Vade]),
            "TaksitNo", [Value],
            "OdemeTarihi", EDATE(Bas, [Value]),
            "Taksit", A,
            "Faiz", (P0 * POWER(1 + i, [Value] - 1) - A * (POWER(1 + i, [Value] - 1) - 1) / i) * i,
            "Kalan", P0 * POWER(1 + i, [Value]) - A * (POWER(1 + i, [Value]) - 1) / i
        )
)
VAR Rapor = DATE(2024, 12, 31)
EVALUATE
ADDCOLUMNS(
    Krediler,
    "Durum", SWITCH(TRUE(), [GecikmeGun] >= 90, "Takipte", [GecikmeGun] >= 30, "Gecikmede", "Düzenli"),
    "Odenen", VAR k = [KrediNo] RETURN COUNTROWS(FILTER(Plan, [KrediNo] = k && [OdemeTarihi] <= Rapor)) + 0,
    "Bakiye",
        VAR k = [KrediNo]
        VAR t = [Tutar]
        VAR p = FILTER(Plan, [KrediNo] = k && [OdemeTarihi] <= Rapor)
        RETURN IF(ISEMPTY(p), t, MINX(p, [Kalan])),
    "Faiz2024", VAR k = [KrediNo] RETURN SUMX(FILTER(Plan, [KrediNo] = k && YEAR([OdemeTarihi]) = 2024), [Faiz]),
    "Tahsilat2025", VAR k = [KrediNo] RETURN SUMX(FILTER(Plan, [KrediNo] = k && YEAR([OdemeTarihi]) = 2025), [Taksit])
)

Bulgu: 3.410.000 ₺ kullandırılmış, rapor tarihinde 2.205.272 ₺ bakiye var. 2024’te vadesi gelen taksitlerin faiz kısmı 814.747 ₺, anapara kısmı 1.063.190 ₺: tahsil edilmesi gerekenin %43’ü faiz. 2025’te planlanan tahsilat 2.189.223 ₺; bunun 350.646 ₺’si (%16) 30 gün ve üstü gecikmedeki kredilerden.

Adım 3: Segmentlere göre risk ve getiri. Gecikme oranını kredi sayısıyla değil bakiyeyle ölçeriz: küçük bir kredinin gecikmesi büyük bir kredininkiyle aynı ağırlığı taşımamalı. Getiri için iki ölçü: sözleşme getirisi (bakiye ağırlıklı faiz) ve takipteki kredileri getirisiz sayan riskten arındırılmış getiri.

DEFINE
VAR Krediler = DATATABLE(
    "KrediNo", STRING, "Segment", STRING, "Tutar", CURRENCY, "YillikFaiz", DOUBLE,
    "Vade", INTEGER, "Baslangic", DATETIME, "GecikmeGun", INTEGER,
    {
        {"K01", "KOBİ", 400000, 0.42, 24, "2024-01-15", 0},
        {"K02", "KOBİ", 250000, 0.45, 12, "2024-06-01", 45},
        {"K03", "Bireysel", 120000, 0.48, 36, "2023-09-01", 0},
        {"K04", "Bireysel", 80000, 0.50, 24, "2024-03-01", 120},
        {"K05", "Kurumsal", 1000000, 0.38, 36, "2023-04-01", 0},
        {"K06", "Kurumsal", 600000, 0.40, 24, "2024-09-01", 0},
        {"K07", "KOBİ", 300000, 0.44, 18, "2023-12-01", 0},
        {"K08", "Bireysel", 60000, 0.52, 12, "2024-02-01", 35},
        {"K09", "KOBİ", 150000, 0.46, 24, "2024-04-01", 95},
        {"K10", "Kurumsal", 450000, 0.39, 12, "2024-07-01", 0}
    }
)
VAR Plan = GENERATE(
    Krediler,
    VAR i = [YillikFaiz] / 12
    VAR A = PMT(i, [Vade], -[Tutar])
    VAR P0 = [Tutar]
    VAR Bas = [Baslangic]
    RETURN
        SELECTCOLUMNS(
            GENERATESERIES(1, [Vade]),
            "TaksitNo", [Value],
            "OdemeTarihi", EDATE(Bas, [Value]),
            "Taksit", A,
            "Faiz", (P0 * POWER(1 + i, [Value] - 1) - A * (POWER(1 + i, [Value] - 1) - 1) / i) * i,
            "Kalan", P0 * POWER(1 + i, [Value]) - A * (POWER(1 + i, [Value]) - 1) / i
        )
)
VAR Rapor = DATE(2024, 12, 31)
VAR Ozet = ADDCOLUMNS(
    Krediler,
    "Durum", SWITCH(TRUE(), [GecikmeGun] >= 90, "Takipte", [GecikmeGun] >= 30, "Gecikmede", "Düzenli"),
    "Odenen", VAR k = [KrediNo] RETURN COUNTROWS(FILTER(Plan, [KrediNo] = k && [OdemeTarihi] <= Rapor)) + 0,
    "Bakiye",
        VAR k = [KrediNo]
        VAR t = [Tutar]
        VAR p = FILTER(Plan, [KrediNo] = k && [OdemeTarihi] <= Rapor)
        RETURN IF(ISEMPTY(p), t, MINX(p, [Kalan])),
    "Faiz2024", VAR k = [KrediNo] RETURN SUMX(FILTER(Plan, [KrediNo] = k && YEAR([OdemeTarihi]) = 2024), [Faiz]),
    "Tahsilat2025", VAR k = [KrediNo] RETURN SUMX(FILTER(Plan, [KrediNo] = k && YEAR([OdemeTarihi]) = 2025), [Taksit])
)
EVALUATE
ADDCOLUMNS(
    DISTINCT(SELECTCOLUMNS(Krediler, "Segment", [Segment])),
    "Bakiye", VAR s = [Segment] RETURN SUMX(FILTER(Ozet, [Segment] = s), [Bakiye]),
    "Gecikme 30+ %", VAR s = [Segment]
        RETURN DIVIDE(SUMX(FILTER(Ozet, [Segment] = s && [GecikmeGun] >= 30), [Bakiye]), SUMX(FILTER(Ozet, [Segment] = s), [Bakiye])) + 0,
    "Takip 90+ %", VAR s = [Segment]
        RETURN DIVIDE(SUMX(FILTER(Ozet, [Segment] = s && [GecikmeGun] >= 90), [Bakiye]), SUMX(FILTER(Ozet, [Segment] = s), [Bakiye])) + 0,
    "Sözleşme Getirisi", VAR s = [Segment]
        RETURN DIVIDE(SUMX(FILTER(Ozet, [Segment] = s), [Bakiye] * [YillikFaiz]), SUMX(FILTER(Ozet, [Segment] = s), [Bakiye])),
    "Riskten Arındırılmış", VAR s = [Segment]
        RETURN DIVIDE(SUMX(FILTER(Ozet, [Segment] = s && [GecikmeGun] < 90), [Bakiye] * [YillikFaiz]), SUMX(FILTER(Ozet, [Segment] = s), [Bakiye]))
)

Bulgu: Kâğıt üzerinde en kârlı segment Bireysel (%49,0), ama takipteki K04 hesaba katılınca getirisi %30,7’ye iniyor ve en düşük segment oluyor. Kurumsal hiç gecikme olmadan %39,0 getiriyor. Yüksek faiz riskin bedelidir; risk gerçekleşince getiri kaybolur. Portföy düzeyinde takip oranı %7,8 ve bu bakiye 2024 faizinin 68.136 ₺’sini oluşturuyor; bu faiz büyük olasılıkla tahsil edilemeyecek.

Adım 4: Model ölçüleri ve kredi detay sayfası. Gerçek modelde Krediler ve Ödeme Planı iki tablodur; ölçüler ilişki üzerinden çalışır. Kalan bakiye yarı toplanabilir bir değerdir: krediler üzerinden toplanır ama aylar üzerinden toplanmaz (Yarı toplanabilir ölçüler: stok, bakiye, nüfus).

Kalan Bakiye =
VAR Rapor = MAX(Tarih[Tarih])
RETURN
    SUMX(
        VALUES(Krediler[KrediNo]),
        VAR Son = CALCULATE(MIN('Ödeme Planı'[Kalan]), REMOVEFILTERS(Tarih), 'Ödeme Planı'[OdemeTarihi] <= Rapor)
        RETURN IF(ISBLANK(Son), CALCULATE(SUM(Krediler[Tutar])), Son)
    )
Gecikme 30+ % =
DIVIDE(CALCULATE([Kalan Bakiye], Krediler[GecikmeGun] >= 30), [Kalan Bakiye])
Kredi Başlığı =
VAR No = SELECTEDVALUE(Krediler[KrediNo])
VAR Gun = SELECTEDVALUE(Krediler[GecikmeGun])
VAR Durum = SWITCH(TRUE(), Gun >= 90, "Takipte", Gun >= 30, "Gecikmede", "Düzenli")
RETURN
    IF(
        ISBLANK(No), "Bir kredi seçin",
        No & " · " & SELECTEDVALUE(Krediler[Segment]) & " · " & FORMAT(SELECTEDVALUE(Krediler[Tutar]), "#,##0") & " ₺ · yıllık %"
            & FORMAT(SELECTEDVALUE(Krediler[YillikFaiz]) * 100, "0") & " · " & Durum & IF(Gun > 0, " (" & Gun & " gün)")
    )

Portföy sayfasındaki kredi tablosunda bir satıra sağ tıklayıp Detaya git denince Kredi detayı sayfası açılır; detay alanı Krediler[KrediNo]. K04 için başlık “K04 · Bireysel · 80.000 ₺ · yıllık %50 · Takipte (120 gün)” olur. Sayfada ödeme planı tablosu (taksit no, tarih, faiz, anapara, kalan), faiz ve anaparayı yığılmış sütunla gösteren grafik ve rapor tarihini işaretleyen bir sabit çizgi bulunur.

4. Rapor sayfası taslağı

5. Bulgu özeti ve öneri

1. Portföyün %14,7’si 30 gün ve üstü gecikmede, %7,8’i takipte. Takipteki iki kredi (K04, K09) için yapılandırma ya da hukuki takip kararı ve karşılık ayrılması gerekiyor.
2. Bireysel segmentte koşulları sıkılaştır: en yüksek sözleşme faizine (%49,0) rağmen riskten arındırılmış getiri en düşük (%30,7). Faizi artırmak yerine kredi onay kriterlerini gözden geçir.
3. Kurumsal segment portföyün bel kemiği: bakiyenin %64,1’i, gecikme yok, getiri %39,0. Büyüme bu segmentte daha güvenli; ama tek bir kredinin (K05) bakiyenin %26’sı olduğu yoğunlaşma riski not edilmeli.
4. 2025 nakit planını riske göre ayır: planlanan 2.189.223 ₺ tahsilatın 350.646 ₺’si gecikmedeki kredilerden; nakit planında bu kısmı ayrı satırda ve iskontolu göster.
5. Modeli tamamla: gerçek tahsilat tablosu ve vadelerin sonuna kadar uzanan tarih tablosu olmadan bakiye sözleşme varsayımına dayanır.