Excel'de bir kredi amortisman planı oluşturmak, kredi geri ödemelerinizi etkili bir şekilde görselleştirmenize ve yönetmenize olanak tanıyan değerli bir beceri olabilir. Amortisman planı, amortisman kredisine (tipik olarak bir ipotek veya araba kredisi) ilişkin her periyodik ödemenin ayrıntılarını veren bir tablodur. Her ödemeyi faiz ve anapara bileşenlerine ayırarak her ödemeden sonra kalan bakiyeyi gösterir. Excel'de böyle bir program oluşturmak için adım adım kılavuza bakalım.

Bir amortisman programı nedir?

Excel'de amortisman planı oluşturma

Değişken sayıda dönem için amortisman planı oluşturun

Ekstra ödemelerle bir amortisman planı oluşturun

Excel şablonunu kullanarak bir amortisman planı (ek ödemelerle birlikte) oluşturun

Bir amortisman programı nedir?

Amortisman planı, kredi hesaplamalarında kullanılan ve bir kredinin zaman içinde ödenmesi sürecini gösteren ayrıntılı bir tablodur. Amortisman planları genellikle ipotek, araba kredileri ve bireysel krediler gibi sabit faizli krediler için kullanılır; burada ödeme tutarı kredi vadesi boyunca sabit kalır, ancak ödemenin anaparaya karşı faize oranı zamanla değişir.

Excel'de bir kredi amortisman planı oluşturmak için aslında yerleşik PMT, PPMT ve IPMT işlevleri çok önemlidir. Her fonksiyonun ne yaptığını anlayalım:

• PMT İşlevi: Bu fonksiyon, sabit ödemeler ve sabit faiz oranına göre bir kredinin dönem başına toplam ödemesini hesaplamak için kullanılır.
• IPMT İşlevi: Bu işlev, belirli bir dönem için ödemenin faiz kısmını hesaplar.
• PPMT İşlevi: Bu fonksiyon, belirli bir döneme ait ödemenin anapara kısmını hesaplamak için kullanılır.

Excel'deki bu işlevleri kullanarak, her ödemenin faiz ve anapara bileşenlerini ve her ödemeden sonra kalan kredi bakiyesini gösteren ayrıntılı bir amortisman planı oluşturabilirsiniz.

Excel'de amortisman planı oluşturma

Bu bölümde Excel'de amortisman planı oluşturmak için iki farklı yöntem tanıtacağız. Bu yöntemler, farklı kullanıcı tercihlerine ve beceri düzeylerine hitap ederek, Excel konusundaki yeterliliklerine bakılmaksızın herkesin, kredisi için başarılı bir şekilde ayrıntılı ve doğru bir amortisman planı oluşturabilmesini sağlar.

Formüller, temel hesaplamaların daha iyi anlaşılmasını sağlar ve programın belirli gereksinimlere göre özelleştirilmesi için esneklik sağlar. Bu yaklaşım, uygulamalı bir deneyime sahip olmak ve her ödemenin anapara ve faiz bileşenlerine nasıl bölündüğüne dair net bir fikir edinmek isteyenler için idealdir. Şimdi Excel'de amortisman planı oluşturma sürecini adım adım inceleyelim:

⭐️ Adım 1: Kredi bilgilerini ve amortisman tablosunu ayarlayın

1. Yıllık faiz oranı, yıl cinsinden kredi vadesi, yıllık ödeme sayısı ve kredi tutarı gibi ilgili kredi bilgilerini aşağıdaki ekran görüntüsü gibi hücrelere girin:
   
2. Daha sonra Excel'de A7:E7 hücrelerinde Dönem, Ödeme, Faiz, Anapara, Kalan Bakiye gibi belirtilen etiketlerle bir amortisman tablosu oluşturun.
3. Dönem sütununa dönem numaralarını girin. Bu örnekte toplam ödeme sayısı 24 aydır (2 yıl), dolayısıyla Dönem sütununa 1'den 24'e kadar sayıları gireceksiniz. Ekran görüntüsüne bakın:
   
4. Etiketlerin ve dönem numaralarının yer aldığı tabloyu oluşturduktan sonra, kredinizin özelliklerine göre Ödeme, Faiz, Anapara ve Bakiye sütunları için formüller ve değerler girmeye devam edebilirsiniz.

⭐️ Adım 2: PMT işlevini kullanarak toplam ödeme tutarını hesaplayın

PMT'nin sözdizimi şöyledir:

Aşağıdaki formülü B7 hücresine girin ve doldurma tutamacını aşağı sürükleyerek bu formülü diğer hücrelere doldurun, tüm dönemler için sabit bir ödeme tutarı göreceksiniz. Ekran görüntüsüne bakın:

= -PMT($B$1/$B$3, $B$2*$B$3, $B$4)

⭐️ Adım 3: IPMT işlevini kullanarak faizi hesaplayın

Bu adımda Excel'in IPMT işlevini kullanarak her ödeme dönemi için faizi hesaplayacaksınız.

Aşağıdaki formülü C7 hücresine girin ve ardından bu formülü doldurmak ve her dönemin faizini almak için doldurma tutamacını sütunda aşağı sürükleyin.

=-IPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4)

⭐️ Adım 4: PPMT işlevini kullanarak anaparayı hesaplayın

Her dönem için faiz hesaplandıktan sonra, amortisman planı oluşturmanın bir sonraki adımı, her ödemenin anapara kısmını hesaplamaktır. Bu, sabit ödemelere ve sabit faiz oranına dayalı olarak belirli bir dönem için ödemenin ana kısmını belirlemek üzere tasarlanmış PPMT işlevi kullanılarak yapılır.

IPMT'nin sözdizimi şöyledir:

PPMT formülünün sözdizimi ve parametreleri, daha önce tartışılan IPMT formülünde kullanılanlarla aynıdır.

Aşağıdaki formülü D7 hücresine girin ve ardından her dönemin anaparasını doldurmak için doldurma tutamacını sütunda aşağı doğru sürükleyin. Ekran görüntüsüne bakın:

=-PPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4)

⭐️ Adım 5: Kalan bakiyeyi hesaplayın

Her ödemenin hem faizini hem de anaparasını hesapladıktan sonra amortisman planınızdaki bir sonraki adım, her ödemeden sonra kredinin kalan bakiyesini hesaplamaktır. Bu, kredi bakiyesinin zaman içinde nasıl azaldığını gösterdiği için programın önemli bir parçasıdır.

1. Bakiye sütununuzun (E7) ilk hücresine aşağıdaki formülü girin; bu, kalan bakiyenin orijinal kredi tutarından ilk ödemenin anapara kısmının düşülmesi anlamına gelir:

   =B4-D7

   
2. İkinci ve sonraki tüm dönemler için, bir önceki dönemin bakiyesinden cari dönemin anapara ödemesini çıkararak kalan bakiyeyi hesaplayınız. Lütfen aşağıdaki formülü E8 hücresine uygulayın:

   =E7-D8

    not: Denge hücresinin referansı göreceli olmalıdır, böylece siz formülü aşağı sürükledikçe güncellenir.
   
3. Daha sonra doldurma tutamacını sütuna doğru sürükleyin. Gördüğünüz gibi her hücre, güncellenen anapara ödemelerine göre kalan bakiyeyi hesaplamak için otomatik olarak ayarlanacaktır.
   

⭐️ Adım 6: Kredi özeti yapın

Ayrıntılı amortisman planınızı oluşturduktan sonra bir kredi özeti oluşturmak, kredinizin önemli yönlerine hızlı bir genel bakış sağlayabilir. Bu özet genellikle kredinin toplam maliyetini ve ödenen toplam faizi içerecektir.

● Toplam ödemeleri hesaplamak için:

=SUM(B7:B30)

● Toplam faizi hesaplamak için:

=SUM(C7:C30)

⭐️ Sonuç:

Artık basit ama kapsamlı bir kredi amortisman planı başarıyla oluşturuldu. ekran görüntüsüne bakın:

Değişken sayıda dönem için amortisman planı oluşturun

Önceki örnekte, sabit sayıda ödeme için bir kredi geri ödeme planı oluşturuyoruz. Bu yaklaşım, koşulların değişmediği belirli bir krediyi veya ipoteği yönetmek için mükemmeldir.

Ancak, farklı dönemlere sahip krediler için tekrar tekrar kullanılabilen, ödeme sayısını farklı kredi senaryoları için gerektiği gibi değiştirmenize olanak tanıyan esnek bir amortisman planı oluşturmak istiyorsanız, daha ayrıntılı bir yöntem izlemeniz gerekecektir.

⭐️ Adım 1: Kredi bilgilerini ve amortisman tablosunu ayarlayın

1. Yıllık faiz oranı, yıl cinsinden kredi vadesi, yıllık ödeme sayısı ve kredi tutarı gibi ilgili kredi bilgilerini aşağıdaki ekran görüntüsü gibi hücrelere girin:
   
2. Daha sonra Excel'de A7:E7 hücrelerinde Dönem, Ödeme, Faiz, Anapara, Kalan Bakiye gibi belirtilen etiketlerle bir amortisman tablosu oluşturun.
3. Dönem sütununa herhangi bir kredi için düşünebileceğiniz en yüksek ödeme sayısını girin, örneğin 1'den 360'a kadar olan sayıları girin. Aylık ödeme yapıyorsanız bu, standart 30 yıllık bir krediyi kapsayabilir.
   

⭐️ Adım 2: Ödeme, faiz ve anapara formüllerini IF işleviyle değiştirin

Aşağıdaki formülleri ilgili hücrelere girin ve ardından bu formülleri, ayarladığınız maksimum ödeme dönemi sayısına kadar genişletmek için doldurma tutamacını sürükleyin.

● Ödeme formülü:

Normalde ödemeyi hesaplamak için PMT işlevini kullanırsınız. Bir IF ifadesini dahil etmek için sözdizimi formülü şöyledir:

Yani formüller şu:

=IF(A7<=$B$2*$B$3, -PMT($B$1/$B$3, $B$2*$B$3, $B$4), "")

● Faiz formülü:

Sözdizimi formülü şöyledir:

Yani formüller şu:

=IF(A7<=$B$2*$B$3,-IPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4), "")

● Prensip formülü:

Sözdizimi formülü şöyledir:

Yani formüller şu:

=IF(A7<=$B$2*$B$3,-PPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4), "")

⭐️ Adım 3: Kalan bakiye formülünü ayarlayın

Kalan bakiye için genellikle anaparayı önceki bakiyeden çıkarabilirsiniz. Bir IF deyimiyle onu şu şekilde değiştirin:

● Birinci denge hücresi:(E7)

=B4-D7

● İkinci denge hücresi:(E8)

=IF(A8<=$B$2*$B$3, E7-D8, "") 

⭐️ Adım 4: Kredi özeti yapın

Değiştirilen formüllerle amortisman planını ayarladıktan sonraki adım, bir kredi özeti oluşturmaktır.

● Toplam ödemeleri hesaplamak için:

=SUM(B7:B366)

● Toplam faizi hesaplamak için:

=SUM(C7:C366)

⭐️ Sonuç:

Artık Excel'de ayrıntılı bir kredi özetiyle birlikte kapsamlı ve dinamik bir amortisman planınız var. Ödeme dönemi süresini ayarladığınızda, amortisman planının tamamı bu değişiklikleri yansıtacak şekilde otomatik olarak güncellenecektir. Aşağıdaki demoya bakın:

Ekstra ödemelerle bir amortisman planı oluşturun

Planlanan ödemelerin ötesinde ek ödemeler yapılarak kredinin daha hızlı ödenmesi sağlanabilir. Ekstra ödemeleri içeren bir amortisman planı Excel'de oluşturulduğunda, bu ek ödemelerin kredinin getirisini nasıl hızlandırabileceğini ve ödenen toplam faizi nasıl azaltabileceğini gösterir. Bunu nasıl ayarlayabileceğiniz aşağıda açıklanmıştır:

⭐️ Adım 1: Kredi bilgilerini ve amortisman tablosunu ayarlayın

1. Yıllık faiz oranı, yıl cinsinden kredi vadesi, yıllık ödeme sayısı, kredi tutarı ve ekstra ödeme gibi ilgili kredi bilgilerini aşağıdaki ekran görüntüsü gibi hücrelere girin:
   
2. Daha sonra planlanan ödemeyi hesaplayın.
   Giriş hücrelerine ek olarak, sonraki hesaplamalarımız için önceden tanımlanmış başka bir hücreye (planlanan ödeme tutarı) ihtiyaç vardır. Bu, herhangi bir ek ödeme yapılmadığı varsayılarak bir kredinin düzenli ödeme tutarıdır. Lütfen aşağıdaki formülü B6 hücresine uygulayın:

   =IFERROR(-PMT($B$1/$B$3, $B$2*$B$3, $B$4),"")

   
   
3. Ardından Excel'de bir amortisman tablosu oluşturun:
   • A8:G8 hücrelerinde Dönem, Ödeme Planla, Ek Ödeme, Toplam Ödeme, Faiz, Anapara, Kalan Bakiye gibi belirtilen etiketleri ayarlayın;
   • Dönem sütununa herhangi bir kredi için düşünebileceğiniz en yüksek ödeme sayısını girin. Örneğin, 0'dan 360'a kadar olan sayıları doldurun. Aylık ödeme yapıyorsanız bu, standart 30 yıllık bir krediyi kapsayabilir;
   • Dönem 0 için (bizim durumumuzda satır 9), bu formülle Bakiye değerini alın = B4Bu, başlangıçtaki kredi tutarına karşılık gelir. Bu satırdaki diğer tüm hücreler boş bırakılmalıdır.
   • 

⭐️ Adım 2: Ekstra ödemelerle amortisman planı formüllerini girin

Aşağıdaki formülleri ilgili hücrelere tek tek girin. Hata işlemeyi geliştirmek için, bunu ve gelecekteki tüm formülleri EĞERHATA işlevi içine alıyoruz. Bu yaklaşım, giriş hücrelerinden herhangi birinin boş bırakılması veya yanlış değerlerin tutulması durumunda ortaya çıkabilecek çoklu potansiyel hataların önlenmesine yardımcı olur.

● Planlanan ödemeyi hesaplayın:

Aşağıdaki formülü B10 hücresine girin:

=IFERROR(IF($B$6<=G9, $B$6, G9+G9*$B$1/$B$3), "")

● Ekstra ödemeyi hesaplayın:

Aşağıdaki formülü C10 hücresine girin:

=IFERROR(IF($B$5<G9-E10,$B$5, G9-E10), "")

● Toplam ödemeyi hesaplayın:

Aşağıdaki formülü D10 hücresine girin:

=IFERROR(B10+C10, "")

● Anaparayı hesaplayın:

Aşağıdaki formülü E10 hücresine girin:

=IFERROR(IF(B10>0, MIN(B10-F10, G9), 0), "")

● Faizi hesaplayın:

Aşağıdaki formülü F10 hücresine girin:

=IFERROR(IF(B10>0, $B$1/$B$3*G9, 0), "")

● Kalan bakiyeyi hesaplayın

Aşağıdaki formülü G10 hücresine girin:

=IFERROR(IF(G9 >0, G9-E10-C10, 0), "")

Formüllerin her birini tamamladıktan sonra B10:G10 hücre aralığını seçin ve doldurma tutamacını kullanarak bu formülleri tüm ödeme dönemleri kümesi boyunca sürükleyip genişletin. Kullanılmayan herhangi bir dönem için hücreler 0'ları gösterecektir. Ekran görüntüsüne bakın:

⭐️ Adım 3: Kredi özeti yapın

● Planlanan ödeme sayısını alın:

=B2:B3

● Gerçek ödeme sayısını alın:

=COUNTIF(D10:D369,">"&0)

● Toplam ekstra ödeme alın:

=SUM(C10:C369)

● Toplam ilgiyi alın:

=SUM(F10:F369)

⭐️ Sonuç:

Bu adımları izleyerek Excel'de ekstra ödemeleri hesaba katan dinamik bir amortisman planı oluşturursunuz.

Excel şablonunu kullanarak amortisman planı oluşturma

Bir şablon kullanarak Excel'de amortisman planı oluşturmak basit ve zaman tasarrufu sağlayan bir yöntemdir. Excel, her ödemenin faizini, anaparasını ve kalan bakiyesini otomatik olarak hesaplayan yerleşik şablonlar sağlar. Excel şablonunu kullanarak amortisman planını nasıl oluşturacağınız aşağıda açıklanmıştır:

1. Tıkla fileto > yeni, arama kutusuna yazın amortisman programı, ve bas Keşfet anahtar. Daha sonra ihtiyaçlarınıza en uygun şablonu tıklayarak seçin. Örnek olarak burada Basit kredi hesaplayıcı şablonunu seçeceğim. Ekran görüntüsüne bakın:
   
2. Bir şablon seçtikten sonra, oluşturmak Yeni bir çalışma kitabı olarak açmak için düğmeyi tıklayın.
3. Ardından, kendi kredi ayrıntılarınızı girin; şablon, girişlerinize göre programı otomatik olarak hesaplamalı ve doldurmalıdır.
4. Sonunda yeni amortisman planı çalışma kitabınızı kaydedin.