Excel'de kredi amortisman çizelgesi oluşturun – Adım adım öğretici

Excel'de bir kredi amortisman çizelgesi oluşturmak, kredilerinizi etkili bir şekilde görselleştirmenize ve yönetmenize olanak tanıyan değerli bir beceri olabilir. Amortisman çizelgesi, genellikle bir ipotek veya otomobil kredisi olan bir amortisman kredisinin her dönem ödemesini detaylandıran bir tablodur. Her ödemenin faiz ve ana para bileşenlerine ayrıştırır ve her ödeme sonrası kalan bakiyeyi gösterir. Şimdi Excel'de böyle bir çizelge oluşturmak için adım adım bir kılavuza dalalım.

Amortisman çizelgesi nedir?

Excel'de amortisman çizelgesi oluşturun

Değişken dönem sayısı için amortisman çizelgesi oluşturun

Ekstra ödemeler içeren amortisman çizelgesi oluşturun

Excel şablonunu kullanarak (ekstra ödemelerle) amortisman çizelgesi oluşturun

Amortisman çizelgesi nedir?

Amortisman çizelgesi, bir krediyi zaman içinde geri ödeme sürecini gösteren ve kredi hesaplamalarında kullanılan ayrıntılı bir tablodur. Amortisman çizelgeleri genellikle sabit oranlı kredilerde kullanılır; bu krediler arasında ev kredileri, otomobil kredileri ve kişisel krediler bulunur. Bu tür kredilerde ödeme tutarı kredi süresince sabit kalırken, faiz ve ana para oranları zamanla değişir.

Excel'de bir kredi amortisman çizelgesi oluşturmak için yerleşik fonksiyonlar olan PMT, PPMT ve IPMT gerçekten önemlidir. Şimdi her bir fonksiyonun ne işe yaradığını anlamaya çalışalım:

• PMT Fonksiyonu: Bu fonksiyon, sabit ödemeler ve sabit faiz oranı temelinde bir kredi için her dönem toplam ödemesini hesaplamak üzere kullanılır.
• IPMT Fonksiyonu: Bu fonksiyon, belirli bir dönem için ödemenin faiz kısmını hesaplar.
• PPMT Fonksiyonu: Bu fonksiyon, belirli bir dönem için ödemenin ana para kısmını hesaplamak üzere kullanılır.

Excel'de bu fonksiyonları kullanarak, her ödemenin faiz ve ana para bileşenlerini ve her ödeme sonrası kalan kredi bakiyesini gösteren detaylı bir amortisman çizelgesi oluşturabilirsiniz.

Excel'de amortisman çizelgesi oluşturun

Bu bölümde, Excel'de amortisman çizelgesi oluşturmak için iki farklı yöntem tanıtacağız. Bu yöntemler, farklı kullanıcı tercihlerine ve beceri seviyelerine hitap eder, böylece Excel konusundaki yetkinlikleri ne olursa olsun herkes kendi kredisi için detaylı ve doğru bir amortisman çizelgesi oluşturabilir.

Formüller, temel hesaplamaların daha derinlemesine anlaşılmasını sağlar ve çizelgeyi özel gereksinimlere göre özelleştirme esnekliği sunar. Bu yaklaşım, her ödemenin ana para ve faiz bileşenlerine nasıl bölündüğünü net bir şekilde görmek isteyenler için idealdir. Şimdi Excel'de amortisman çizelgesi oluşturmayı adım adım inceleyelim:

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

1. Yıllık faiz oranı, kredi vadesi (yıl cinsinden), yıllık ödeme sayısı ve kredi miktarı gibi ilgili kredi bilgilerini aşağıdaki ekran görüntüsünde gösterildiği gibi hücrelere girin:
   
2. Ardından, A7:E7 hücrelerinde Dönem, Ödeme, Faiz, Ana Para, Kalan Bakiye gibi belirtilen etiketlerle bir amortisman tablosu oluşturun.
3. Dönem sütununda, dönem numaralarını girin. Bu örnekte, toplam ödeme sayısı 24 aydır (2 yıl), bu nedenle Dönem sütununa 1'den 24'e kadar sayıları gireceksiniz. Ekran görüntüsüne bakın:
   
4. Etiketler ve dönem numaralarıyla tabloyu ayarladıktan sonra, kredinizin özelliklerine göre Ödeme, Faiz, Ana Para ve Bakiye sütunları için formülleri ve değerleri girebilirsiniz.

⭐️ Adım 2: PMT fonksiyonunu kullanarak toplam ödeme miktarını hesaplayın

PMT fonksiyonunun sözdizimi şu şekildedir:

Aşağıdaki formülü B7 hücresine girin ve ardından formülü diğer hücrelere doldurmak için doldurma tutamağını aşağı çekin; böylece tüm dönemler için sabit bir ödeme miktarı 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 fonksiyonunu kullanarak faizi hesaplayın

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

Aşağıdaki formülü C7 hücresine girin ve ardından formülü sütun boyunca doldurmak için doldurma tutamağını aşağı çekin ve her dönem için faizi elde edin.

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

⭐️ Adım 4: PPMT fonksiyonunu kullanarak anaparayı hesaplayın

Her dönem için faizi hesapladıktan sonra, amortisman çizelgesi oluşturmanın bir sonraki adımı, her ödemenin ana para kısmını hesaplamaktır. Bu işlem, sabit ödemeler ve sabit faiz oranı temelinde belirli bir dönem için ödemenin ana para kısmını belirlemek üzere tasarlanmış PPMT fonksiyonu kullanılarak yapılır.

IPMT fonksiyonunun sözdizimi şu şekildedir:

PPMT formülü için sözdizimi ve parametreler, 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 formülü her dönemin ana parasını doldurmak için sütun boyunca doldurma tutamağını aşağı çekin. 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 ana parasını hesapladıktan sonra, amortisman çizelgenizin bir sonraki adımı, her ödeme sonrası kalan kredi bakiyesini hesaplamaktır. Bu, çizelgenin önemli bir parçasıdır çünkü kredi bakiyesinin zaman içinde nasıl azaldığını gösterir.

1. Bakiye sütununun ilk hücresinde – E7, aşağıdaki formülü girin; bu, kalan bakiyenin orijinal kredi miktarından ilk ödemenin ana para kısmının çıkarılması anlamına gelir:

   =B4-D7

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

   =E7-D8

    Not: Bakiye hücresine yapılan referans göreceli olmalıdır, böylece formülü aşağı çektiğinizde güncellenir.
   
3. Ardından, doldurma tutamağını sütun boyunca aşağı çekin. Gördüğünüz gibi, her hücre, güncellenmiş ana para ödemelerine göre otomatik olarak kalan bakiyeyi hesaplayacaktır.
   

⭐️ Adım 6: Kredi özeti oluşturun

Detaylı amortisman çizelgenizi oluşturduktan sonra, bir kredi özeti oluşturmak, kredinizin temel yönleri hakkında hızlı bir genel bakış sağlayabilir. Bu özette genellikle toplam kredi maliyeti ve toplam ödenen faiz yer alır.

● Toplam ödemeleri hesaplamak için:

=SUM(B7:B30)

● Toplam faizi hesaplamak için:

=SUM(C7:C30)

⭐️ Sonuç:

Şimdi, basit ama kapsamlı bir kredi amortisman çizelgesi başarıyla oluşturulmuştur. Ekran görüntüsüne bakın:

Kutools AI ile Excel Sihirini Keşfedin

• Akıllı Yürütme: Hücre işlemleri gerçekleştirin, verileri analiz edin ve grafikler oluşturun—tümü basit komutlarla sürülür.
• Özel Formüller: İş akışlarınızı hızlandırmak için özel formüller oluşturun.
• VBA Kodlama: VBA kodunu kolayca yazın ve uygulayın.
• Formül Yorumlama: Karmaşık formülleri kolayca anlayın.
• Metin Çevirisi: Elektronik tablolarınız içindeki dil engellerini aşın.

Yapay zeka destekli araçlarla Excel yeteneklerinizi geliştirin. Şimdi İndirin ve daha önce hiç olmadığı gibi bir verimlilik deneyimi yaşayın!

Değişken dönem sayısı için amortisman çizelgesi oluşturun

Önceki örnekte, sabit sayıda ödeme için bir kredi geri ödeme çizelgesi oluşturduk. Bu yaklaşım, şartların değişmediği belirli bir kredi veya ipotek işleme için mükemmeldir.

Ancak, farklı dönemlere sahip krediler için tekrar kullanılabilen esnek bir amortisman çizelgesi oluşturmak istiyorsanız ve farklı kredi senaryoları için ödeme sayısını gerektiği gibi değiştirebilmek istiyorsanız, daha detaylı bir yöntemi takip etmeniz gerekecek.

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

1. Yıllık faiz oranı, kredi vadesi (yıl cinsinden), yıllık ödeme sayısı ve kredi miktarı gibi ilgili kredi bilgilerini aşağıdaki ekran görüntüsünde gösterildiği gibi hücrelere girin:
   
2. Ardından, A7:E7 hücrelerinde Dönem, Ödeme, Faiz, Ana Para, Kalan Bakiye gibi belirtilen etiketlerle bir amortisman tablosu oluşturun.
3. Dönem sütununda, 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ı doldurun. Bu, aylık ödemeler yapıyorsanız standart 30 yıllık bir krediyi kapsayabilir.
   

⭐️ Adım 2: IF fonksiyonuyla ödeme, faiz ve ana para formüllerini değiştirin

Aşağıdaki formülleri ilgili hücrelere girin ve ardından bu formülleri daha önce ayarladığınız maksimum ödeme dönemine kadar uzatmak için doldurma tutamağını aşağı çekin.

● Ödeme formülü:

Normalde, ödeme hesaplamak için PMT fonksiyonunu kullanırsınız. Bir IF ifadesini dahil etmek için sözdizimi formülü şu şekildedir:

Yani formül şu şekildedir:

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

● Faiz formülü:

Sözdizimi formülü şu şekildedir:

Yani formül şu şekildedir:

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

● Ana para formülü:

Sözdizimi formülü şu şekildedir:

Yani formül şu şekildedir:

=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 önceki bakiyeden anaparayı çıkarırsınız. Bir IF ifadesiyle bunu şu şekilde değiştirin:

● İlk bakiye hücresi:(E7)

=B4-D7

● İkinci bakiye hücresi:(E8)

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

⭐️ Adım 4: Kredi özeti oluşturun

Değiştirilmiş formüllerle amortisman çizelgesini ayarladıktan sonra, bir 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ç:

Şimdi, Excel'de kapsamlı ve dinamik bir amortisman çizelgesine sahipsiniz; ayrıca detaylı bir kredi özeti de bulunmaktadır. Ödeme dönemi terimini her değiştirdiğinizde, tüm amortisman çizelgesi bu değişiklikleri yansıtacak şekilde otomatik olarak güncellenir. Aşağıdaki demoyu izleyin:

Ekstra ödemeler içeren amortisman çizelgesi oluşturun

Planlanan ödemelerin ötesinde ekstra ödemeler yaparak, bir krediyi daha hızlı bir şekilde geri ödeyebilirsiniz. Excel'de ekstra ödemeler içeren bir amortisman çizelgesi, bu ek ödemelerin kredinin geri ödeme hızını nasıl artırdığını ve toplam ödenen faizi nasıl azalttığını gösterir. İşte bunu nasıl kuracağınız:

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

1. Yıllık faiz oranı, kredi vadesi (yıl cinsinden), yıllık ödeme sayısı, kredi miktarı ve ek ödeme gibi ilgili kredi bilgilerini aşağıdaki ekran görüntüsünde gösterildiği gibi hücrelere girin:
   
2. Ardından, planlanan ödemeni hesaplayın.
   Giriş hücrelerine ek olarak, sonraki hesaplamalarımız için bir başka önceden tanımlanmış hücreye ihtiyacımız var - planlanan ödeme miktarı. Bu, ek ödeme yapılmadığı varsayıldığında bir kredideki düzenli ödeme miktarı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, Planlanan Ödeme, Ek Ödeme, Toplam Ödeme, Faiz, Ana Para, Kalan Bakiye gibi belirtilen etiketleri ayarlayın;
   • Dönem sütununda, 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. Bu, aylık ödemeler yapıyorsanız standart 30 yıllık bir krediyi kapsayabilir;
   • Dönem 0 için (bizim durumumuzda 9. satır), bu formülü kullanarak Bakiye değerini alın =B4, bu da başlangıç kredi miktarına karşılık gelir. Bu satırdaki tüm diğer hücreler boş bırakılmalıdır.
   • 

⭐️ Adım 2: Ekstra ödemeler içeren amortisman çizelgesi için formülleri oluşturun

Aşağıdaki formülleri ilgili hücrelere tek tek girin. Hata işleme yeteneğini artırmak için bu ve tüm gelecek formülleri IFERROR fonksiyonu içine alıyoruz. Bu yaklaşım, giriş hücrelerinin boş bırakılması veya yanlış değerler içermesi durumunda ortaya çıkabilecek birden fazla potansiyel hatayı önlemeye yardımcı olur.

● Planlanan ödemeni hesaplayın:

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

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

● Ek ödemeni hesaplayın:

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

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

● Toplam ödemeni 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), "")

Her bir formülü tamamladıktan sonra, B10:G10 hücre aralığını seçin ve bu formülleri tüm ödeme dönemleri boyunca uzatmak için doldurma tutamağını kullanın. 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 oluşturun

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

=B2:B3

● Gerçekleşen ödeme sayısını alın:

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

● Toplam ekstra ödemeleri alın:

=SUM(C10:C369)

● Toplam faizi alın:

=SUM(F10:F369)

⭐️ Sonuç:

Bu adımları izleyerek, ekstra ödemeleri hesaba katan dinamik bir amortisman çizelgesi oluşturursunuz.

Excel şablonunu kullanarak amortisman çizelgesi oluşturun

Excel'de şablon kullanarak amortisman çizelgesi oluşturmak, basit ve zaman açısından verimli bir yöntemdir. Excel, her ödemenin faizini, ana parasını ve kalan bakiyesini otomatik olarak hesaplayan yerleşik şablonlar sunar. İşte bir Excel şablonu kullanarak amortisman çizelgesi oluşturmanın yolu:

1. Tıklayın Dosya > Yeni, arama kutusuna yazın amortisman çizelgesi, ve basın Enter tuşuna basın. Ardından, ihtiyaçlarınıza en uygun şablonu seçmek için üzerine tıklayın. Örneğin, burada Basit kredi hesaplayıcı şablonunu seçeceğim. Ekran görüntüsüne bakın:
   
2. Bir şablon seçtikten sonra, onu yeni bir çalışma kitabı olarak açmak için Oluştur düğmesine tıklayın.
3. Ardından, kendi kredi bilgilerinizi girin; şablon, girişlerinize göre çizelgeyi otomatik olarak hesaplayıp doldurmalıdır.
4. Son olarak, yeni amortisman çizelgesi çalışma kitabınızı kaydedin.