Esnek Kredi Hesabı

[shhnyldrr]

Merhaba Excel Web TR ailesi,

Ekran görüntülerinin yer aldığı bir şekilde kredi hesabı yapmak istiyorum. İlk ekran görüntüsünde sabit bir ödeme yapmadan halini ikinci ekran görüntüsünde çıkan taksit tutarı değilde eline daha fazla para geçen borçlunun daha fazla para ödediği sabit ödemeli bir sistem kuruduğunda taksitlerin ikinc iekran görüntüsündeki gibi değişeceği bir sistem oluşturmaya çalışıyorum.

İlk hesapta 24 bin civarı çıkan taksit, ikinci sabit tutar eklendiği zaman 22 bin civarına düşüyor. Teknik olarak( kredi hesaplama bilgileri) yeterli bilgiye sahip olamadığım için 22 bine düşme anını formülize edemedim.

Ekran görüntüleri ve dosya ektedir.

Desteklerinizi rica ederim

 

[shhnyldrr]

Hesaplama yapılan sitenin linki:

Esnek kredi hesaplama aracı & En uygun krediler

Sağ taraftaki seçeneklerden > Esnek

 

[shhnyldrr]

Desteklerinizi rica ederim

 

[shhnyldrr]

Desteklerinizi rica ederim

 

[Haluk]

Eklediğiniz resimlere göre tuhaf bir durum var;

1.alternatifteki şartlarda aylık ödemeler 24.649,11 TL olarak Excel hesaplıyor, onda sorun yok. Toplam olarak da, 24.649,11 TL X 13 = 320.438,38 TL yapar.

Ancak 2. alternatifteki hesapta bir anormallik var ....

Şöyle düşünün ki; 2. ayda yapılan ek ödeme olan 50.000 TL yerine, diğer aylardaki gibi 22.092,88 TL ödendiğini düşünün. Bu durumda; 13 ay boyunca aylık 3,75% faizle aylık 22.092,88 TL ödemenin, 13 aylık vade sonundaki değeri 361.606,75 TL olur. Sadece tek başına bu değer bile, yukarıdaki 1. alternatifte hesaplanan toplam 320.438,38 TL'nin üzerinde bir rakkam. Bunun üzerine bir de, 2. ayda 22.092,88 TL yerine 50.000 TL ödendiği için aradaki farkın (27.907,12 TL) 13. ay sonunda yani, 12 aylık getirisini hesaplarsak, o da 43.408,25 TL yapar. Bu iki rakkamı toplarsak; 361.606,75 TL + 43.408,25 TL = 405.015,01 TL yapar.

Yani; 250.000 TL tutarındaki kredinin geri ödemesi için önerilen 1. alternatifteki 320.438,38 TL ile 2. alternatifteki 405.015,01 TL arasında çok büyük bir fark var......


.

 

[shhnyldrr]

Eklediğiniz resimlere göre tuhaf bir durum var;

1.alternatifteki şartlarda aylık ödemeler 24.649,11 TL olarak Excel hesaplıyor, onda sorun yok. Toplam olarak da, 24.649,11 TL X 13 = 320.438,38 TL yapar.

Ancak 2. alternatifteki hesapta bir anormallik var ....

Şöyle düşünün ki; 2. ayda yapılan ek ödeme olan 50.000 TL yerine, diğer aylardaki gibi 22.092,88 TL ödendiğini düşünün. Bu durumda; 13 ay boyunca aylık 3,75% faizle aylık 22.092,88 TL ödemenin, 13 aylık vade sonundaki değeri 361.606,75 TL olur. Sadece tek başına bu değer bile, yukarıdaki 1. alternatifte hesaplanan toplam 320.438,38 TL'nin üzerinde bir rakkam. Bunun üzerine bir de, 2. ayda 22.092,88 TL yerine 50.000 TL ödendiği için aradaki farkın (27.907,12 TL) 13. ay sonunda yani, 12 aylık getirisini hesaplarsak, o da 43.408,25 TL yapar. Bu iki rakkamı toplarsak; 361.606,75 TL + 43.408,25 TL = 405.015,01 TL yapar.

Yani; 250.000 TL tutarındaki kredinin geri ödemesi için önerilen 1. alternatifteki 320.438,38 TL ile 2. alternatifteki 405.015,01 TL arasında çok büyük bir fark var......


.

Evet size 100% katılıyorum. Hatta ilk mesajımda da bahsettiğim gibi bu işlemin teknik anlamda uzmanı olmasamda kafa yaromama rağmen anlayamamış olmam.

Ama şu halen bende merak konusu olmaya devam edecek. 50.000 sabit tutar yatırılan hesaplamada nasıl bir işlem yaptığı arka planda

 

[Haluk]

Ben kendime göre bir çalışma yaptım ekli dosyada.... Buna göre 2.alternatifte (2. ayda 50.000TL ek ödemeli) aylık taksitler 21.986,95 TL çıkıyor.

.

 

[shhnyldrr]

Ben kendime göre bir çalışma yaptım ekli dosyada.... Buna göre 2.alternatifte (2. ayda 50.000TL ek ödemeli) aylık taksitler 21.986,95 TL çıkıyor.

.

çok teşekkürler inceleyeceğim emeğinize sağlık

 

[shhnyldrr]

Ben kendime göre bir çalışma yaptım ekli dosyada.... Buna göre 2.alternatifte (2. ayda 50.000TL ek ödemeli) aylık taksitler 21.986,95 TL çıkıyor.

.

Evet sanırım mantık bu şekilde; peki benim ilk paylaştığım tabloya nasıl uyarlanır bu

 

[Haluk]

Ara ödemeli kredi geri ödeme tablosu, söz konusu web sitesindeki hesaplama aracıyla aynı sonuçları üretecek şekilde yapıldı...

Ara ödeme olunca iş enteresanlaştığı için, çözümü bulmak biraz vakit aldı....





.

 

[shhnyldrr]

Ara ödemeli kredi geri ödeme tablosu, söz konusu web sitesindeki hesaplama aracıyla aynı sonuçları üretecek şekilde yapıldı...

Ara ödeme olunca iş enterasanlaştığı için, çözümü bulmak biraz vakit aldı....



Ekli dosyayı görüntüle 250292

.

çok teşekkür ederim fakat dosyayı göremedim rica edersem ekler misiniz

 

[Haluk]

Aslında 10 No'lu mesajda görselini eklediğim tablonun mantığı basit ama sonuca ulaşmak yani, aylık eşit taksitlerin değerini bulmak için iterasyon yapmak gerekiyor. Elle tahminde bulunarak çözüme ulaşmak zaman alacağı için ben Excel'in "Goal Seek" (Hedef Bulma) özelliğini kullandım.

Yaptığım işler şu şekilde;

- Vergiler (BSMV ve KKDF) dahil aylık kredi faizini bulmak için B7 hücresine yazdığım formül

=B4*(1+ B5 + B6)

- Çok önemli değil ama, sırf merak gidermek için ..... B2 hücresinde yazılı olan kredi tutarının vade sonundaki değerini bulmak için B10 hücresine yazdığım formül;

=-FV(B4;B3;;B2;1)

- Bu çalışmanın amacı olan aylık taksitlerin hesaplanacağı B11 hücresini ileriki adımlarda "Goal Seek" (Hedef Bulma) metodunda kullanacağım için, başlangıç olarak KrediTutarı/TaksitSayısı gibi bir değer girildi (örneğin, 20000)

- Tabloda "Ödeme No: 0" satırında I11 hücresine, alınan kredi tutarını borç olarak kaydettim. Bunun için I11 hücresine yazdığım formül;

=B2

- Tablonun "Ara Ödemeler" sütununda, sadece 2. ödemede yapıldığı için F13 hücresine 50.000 girdim, diğer dönemlerde ara ödeme olmadığı için onları boş bıraktım.

- Tablonun geri kalanının mantığı ise şu şekilde........ Her ödeme dönemindeki "Bakiye Borç" verisini bulmak için, bir önceki dönem bakiyesini ve ona ait faizi toplayıp, cari dönemdeki aylık ödeme miktarını düştükten sonra, o cari döneme ait yeni bakiye borç bilgisini hesaplayarak "Bakiye Borç" sütununda ilgili hücresine yazdırdım. Bu işi alt alta satırlarda her dönem için yaptıktan sonra, en son dönemde "Bakiye Borç" değerinin 0,00 TL olması gerikir ki, başlangıçta aldığımız kredi tutarının geri ödemeleri tamamlanabilsin.

İşe ilk ödeme döneminden başlayarak, 1. dönemin faizini hesaplayıp tabloda "Faiz" sütununa yazdırdım. Bunun için ilk ödemeye ait G12 hücresinde kullandığım formül;

=I11*B$7

- 1. Ödeme dönemine ait "Aylık Ödeme" olarak, yukarıda B11 hücresine girdiğim "aylık ortalama taksit" verisini ya da varsa o döneme ait ara ödemeyi H12 hücresine yazdığım aşağıdaki formülle getirdim;

=IF(F12="";B$11;F12)

Görüldüğü gibi bu formül, o döneme ait ara ödeme varsa onu, yoksa başlangıçta varsayılan sabit değeri getiriyor.

- Daha sonra 1. döneme ait "Bakiye Borç" verisini elde etmek için bir önceki döneme ait "Bakiye Borç" verisinin üzerine, 1.dönem faizini ekleyip, bu döneme yani, 1. döneme ait "Aylık Ödeme" miktarını düştüm.

Bu işleri 1. dönem için yapmak üzere, I12 hücresine aşağıdaki formülü girdim;

=I11+G12-H12

- Böylece, tabloda 1. dönem için gerekli bütün parametreleri elde ettim. Bundan sonra, G12:I12 hücre aralığını seçip, aşağıya doğru 13 dönem için çoğaltmak üzere sürükledim.

- Tablonun hazırlanmasıyla ilgili son olarak da, "Ara Ödeme, "Faiz" ve "Aylık Ödeme" sütun toplamlarını hesaplamak üzere, F25 hücresine aşağıdaki formülü girip, sağa doğru sürükledim.

=SUM(F12:F24)

- Eğer herşey yolunda gitse, tablonun 13. dönemine ait bakiye borç 0,00 TL olur ama bu tabii tamamen başlangıçta B11 hücresine girdiğimiz tahmini aylık ortalama taksit tutarını doğru tahmin edemeyeceğimizden dolayı 0,00 TL'den farklı bir değer çıkacaktır.

- Dolayısıyle, aylık ortalama taksit tutarını manuel olarak B11 hücresinde değiştirerek, son dönem bakiye borcu sıfırlamaya çalışabiliriz...... ama bu biraz vakit alır.

- Ben de, Excel'in "Data" (Veri) menüsünde bu tip işler için ideal bir araç olan "Goal Seek" (Hedef Bulma) özelliğini kullandım.
Bunun için, benim kullandığım Office 2010 versiyonunda "Data" sekmesinde "What-If Anaysis" menüsü altında yer alan "Goal Seek" aracını çalıştırdım.

Ekrana gelen pencerede "Set Cell" kutusunun içine tıklayıp, fare ile sayfadaki I24 hücresini yani, yon son döneme ait bakiye borcun olduğu hücreyi seçtim. Daha sonra, "To value" kutusuna 0 değerini girdim (rakkamla sıfır). Ardından, "By changing cell" kutusuna tıklayıp, fare ile sayfadaki B11 hücresine seçtikten sonra "OK" tuşuna bastım. Excel bir kaç saniyede gerekli iterasyonları yaparak, bulduğu en doğru sonucu B11 hücresine yazacaktır.

"Goal Seek" aracının burada yaptığı iş; I24 hücresindeki değer 0 (sıfır) olana kadar B11 hücresine çeşitli değerler girmek. Biz de, bu değeri zaten tablodaki formüllerde kullandığımız için, amacımıza ulaşmış oluyoruz.

- Tabloyu bir kez oluşturduktan sonra, başka dönemlere ait değişik miktarlarda ara ödeme değerleri girdikten sonra yine benzer şekilde "Goal Seek" metodunu kullanarak, size en uygun geri ödeme planı oluşturabilirsiniz.

Ben, kendime kolaylık olması açısından "Goal Seek" metodunu kullanmak üzere, aşağıdaki ufak makroyu hazırlayıp, sayfadaki "HESAPLA" etiketli butona bağladım. Gayet pratik oldu. İlgili kod;

Sub Test()
    Range("B11") = Range("B2") / Range("B3")
    Range("I24").GoalSeek Goal:=0, ChangingCell:=Range("B11")
End Sub

Kolay gelsin....


.

 

[shhnyldrr]

Aslında 10 No'lu mesajda görselini eklediğim tablonun mantığı basit ama sonuca ulaşmak yani, aylık eşit taksitlerin değerini bulmak için iterasyon yapmak gerekiyor. Elle tahminde bulunarak çözüme ulaşmak zaman alacağı için ben Excel'in "Goal Seek" (Hedef Bulma) özelliğini kullandım.

Yaptığım işler şu şekilde;

- Vergiler (BSMV ve KKDF) dahil aylık kredi faizini bulmak için B7 hücresine yazdığım formül

=B4*(1+ B5 + B6)

- Çok önemli değil ama, sırf merak gidermek için ..... B2 hücresinde yazılı olan kredi tutarının vade sonundaki değerini bulmak için B10 hücresine yazdığım formül;

=-FV(B4;B3;;B2;1)

- Bu çalışmanın amacı olan aylık taksitlerin hesaplanacağı B11 hücresini ileriki adımlarda "Goal Seek" (Hedef Bulma) metodunda kullanacağım için, başlangıç olarak KrediTutarı/TaksitSayısı gibi bir değer girildi (örneğin, 20000)

- Tabloda "Ödeme No: 0" satırında I11 hücresine, alınan kredi tutarını borç olarak kaydettim. Bunun için I11 hücresine yazdığım formül;

=B2

- Tablonun "Ara Ödemeler" sütununda, sadece 2. ödemede yapıldığı için F13 hücresine 50.000 girdim, diğer dönemlerde ara ödeme olmadığı için onları boş bıraktım.

- Tablonun geri kalanının mantığı ise şu şekilde........ Her ödeme dönemindeki "Bakiye Borç" verisini bulmak için, bir önceki dönem bakiyesini ve ona ait faizi toplayıp, cari dönemdeki aylık ödeme miktarını düştükten sonra, o cari döneme ait yeni bakiye borç bilgisini hesaplayarak "Bakiye Borç" sütununda ilgili hücresine yazdırdım. Bu işi alt alta satırlarda her dönem için yaptıktan sonra, en son dönemde "Bakiye Borç" değerinin 0,00 TL olması gerikir ki, başlangıçta aldığımız kredi tutarının geri ödemeleri tamamlanabilsin.

İşe ilk ödeme döneminden başlayarak, 1. dönemin faizini hesaplayıp tabloda "Faiz" sütununa yazdırdım. Bunun için ilk ödemeye ait G12 hücresinde kullandığım formül;

=I11*B$7

- 1. Ödeme dönemine ait "Aylık Ödeme" olarak, yukarıda B11 hücresine girdiğim "aylık ortalama taksit" verisini ya da varsa o döneme ait ara ödemeyi H12 hücresine yazdığım aşağıdaki formülle getirdim;

=IF(F12="";B$11;F12)

Görüldüğü gibi bu formül, o döneme ait ara ödeme varsa onu, yoksa başlangıçta varsayılan sabit değeri getiriyor.

- Daha sonra 1. döneme ait "Bakiye Borç" verisini elde etmek için bir önceki döneme ait "Bakiye Borç" verisinin üzerine, 1.dönem faizini ekleyip, bu döneme yani, 1. döneme ait "Aylık Ödeme" miktarını düştüm.

Bu işleri 1. dönem için yapmak üzere, I12 hücresine aşağıdaki formülü girdim;

=I11+G12-H12

- Böylece, tabloda 1. dönem için gerekli bütün parametreleri elde ettim. Bundan sonra, G12:I12 hücre aralığını seçip, aşağıya doğru 13 dönem için çoğaltmak üzere sürükledim.

- Tablonun hazırlanmasıyla ilgili son olarak da, "Ara Ödeme, "Faiz" ve "Aylık Ödeme" sütun toplamlarını hesaplamak üzere, F25 hücresine aşağıdaki formülü girip, sağa doğru sürükledim.

=SUM(F12:F24)

- Eğer herşey yolunda gitse, tablonun 13. dönemine ait bakiye borç 0,00 TL olur ama bu tabii tamamen başlangıçta B11 hücresine girdiğimiz tahmini aylık ortalama taksit tutarını doğru tahmin edemeyeceğimizden dolayı 0,00 TL'den farklı bir değer çıkacaktır.

- Dolayısıyle, aylık ortalama taksit tutarını manuel olarak B11 hücresinde değiştirerek, son dönem bakiye borcu sıfırlamaya çalışabiliriz...... ama bu biraz vakit alır.

- Ben de, Excel'in "Data" (Veri) menüsünde bu tip işler için ideal bir araç olan "Goal Seek" (Hedef Bulma) özelliğini kullandım.
Bunun için, benim kullandığım Office 2010 versiyonunda "Data" sekmesinde "What-If Anaysis" menüsü altında yer alan "Goal Seek" aracını çalıştırdım.

Ekrana gelen pencerede "Set Cell" kutusunun içine tıklayıp, fare ile sayfadaki I24 hücresini yani, yon son döneme ait bakiye borcun olduğu hücreyi seçtim. Daha sonra, "To value" kutusuna 0 değerini girdim (rakkamla sıfır). Ardından, "By changing cell" kutusuna tıklayıp, fare ile sayfadaki B11 hücresine seçtikten sonra "OK" tuşuna bastım. Excel bir kaç saniyede gerekli iterasyonları yaparak, bulduğu en doğru sonucu B11 hücresine yazacaktır.

"Goal Seek" aracının burada yaptığı iş; I24 hücresindeki değer 0 (sıfır) olana kadar B11 hücresine çeşitli değerler girmek. Biz de, bu değeri zaten tablodaki formüllerde kullandığımız için, amacımıza ulaşmış oluyoruz.

- Tabloyu bir kez oluşturduktan sonra, başka dönemlere ait değişik miktarlarda ara ödeme değerleri girdikten sonra yine benzer şekilde "Goal Seek" metodunu kullanarak, size en uygun geri ödeme planı oluşturabilirsiniz.

Ben, kendime kolaylık olması açısından "Goal Seek" metodunu kullanmak üzere, aşağıdaki ufak makroyu hazırlayıp, sayfadaki "HESAPLA" etiketli butona bağladım. Gayet pratik oldu. İlgili kod;

Sub Test()
    Range("B11") = Range("B2") / Range("B3")
    Range("I24").GoalSeek Goal:=0, ChangingCell:=Range("B11")
End Sub

Kolay gelsin....


.

teşekkürler