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

Excel'de bir kredi amortisman programı oluşturmak, kredi geri ödemelerinizi etkili şekilde görselleştirmenize ve yönetmenize olanak tanıyan son derece değerli bir beceridir. Amortisman programı, genellikle konut veya taşıt kredisi gibi amortismanlı bir kredi için her dönem ödemesini ayrıntılı olarak gösteren bir tablodur. Bu tablo, her ödemenin faiz ve anapara bileşenlerini net bir şekilde ayırır ve her ödeme sonrası kalan bakiyeyi günceller. Şimdi, Excel'de böyle bir program oluşturmak için adım adım ilerleyelim.

Amortisman planı nedir?

Excel'de amortisman planı oluşturun

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

Ek ödemelerle amortisman planı oluşturun

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

Amortisman planı nedir?

Amortisman programı, bir kredinin zaman içinde nasıl ödeneceğini gösteren, kredi hesaplamalarında kullanılan ayrıntılı bir tablodur. Amortisman programları, ödeme tutarının kredi süresi boyunca sabit kaldığı ancak faiz ve anapara ödemelerinin oranı zamanla değişen konut, taşıt ve kişisel krediler gibi sabit faizli krediler için Sık Kullanılan'dir.

Excel'de bir kredi amortisman programı oluştururken yerleşik PMT, PPMT ve IPMT işlevleri gerçekten büyük önem taşır. Şimdi bu işlevlerin her birinin ne işe yaradığına yakından bakalım:

• PMT İşlevi: Bu işlev, sabit ödemeler ve sabit faiz oranına dayalı olarak bir kredinin her dönem için toplam ödemesini hesaplamakta kullanılır.
• IPMT İşlevi: Bu işlev, belirli bir dönem için ödemenin faiz kısmını hesaplar.
• PPMT İşlevi: Belirli bir dönem için ödemenin anapara kısmını hesaplamak istediğinizde bu işlevi kullanın!

Excel'de bu işlevleri kullanarak, her ödemenin faiz ve anapara bileşenlerini ile ödeme sonrası kredinin kalan bakiyesini gösteren ayrıntılı bir amortisman tablosu oluşturabilirsiniz.

Excel'de amortisman planı oluşturun

Bu bölümde, Excel’de amortisman programı oluşturmak için iki farklı yöntem sunuyoruz. Bu yaklaşımlar, farklı kullanıcı tercihlerini ve yetkinlik düzeylerini karşılayarak, Excel bilgi seviyeniz ne olursa olsun, kredinizle ilgili ayrıntılı ve doğru bir amortisman programı hazırlamanızı sağlar.

Formüller, temel hesaplamaları daha derinlemesine anlamanıza olanak tanır ve programı özel ihtiyaçlarınıza göre özelleştirmeniz için esneklik sağlar. Bu yaklaşım, uygulamalı deneyim kazanmak ve her ödemenin anapara ile faize nasıl ayrıldığını net bir şekilde görmek isteyenler için idealdir. Şimdi, Excel’de amortisman programı 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 süresi, yılda ödeme sayısı ve kredi tutarı gibi ilgili kredi bilgilerini aşağıdaki ekran görüntüsünde gösterildiği gibi hücrelere girin:
   
2. Ardından, Excel'de hücreler A7:E7'ye Dönem, Ödeme, Faiz, Anapara ve Kalan Bakiye gibi etiketleri ekleyerek 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 olan sayıları gireceksiniz. Ekran görüntüsüne göz atın:
   
4. Etiketleri ve dönem numaralarını tabloya yerleştirdikten sonra, kredinizin özelliklerine uygun olarak Ödeme, Faiz, Anapara ve Bakiye sütunlarına formüller ve değerler girmeye devam edebilirsiniz.

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

PMT işlevinin söz dizimi şöyledir:

B7 hücresine aşağıdaki formülü girin ve ardından formülü diğer hücrelere uygulamak için dolgu tutamacını aşağı doğru sürükleyin; böylece 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, her ödeme dönemi için faiz tutarını Excel’in IPMT işleviyle hesaplayacaksınız.

C7 hücresine aşağıdaki formülü girin, ardından dolgu tutamacını aşağı doğru sürükleyerek formülü sütun boyunca doldurun ve her dönem için faiz miktarını alın.

=-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 programı oluşturmada bir sonraki adım, her ödemenin anapara kısmını belirlemektir. Bu işlem, sabit ödemeler ve sabit bir faiz oranına dayanarak belirli bir dönem için ödemenin anapara tutarını hesaplamak üzere tasarlanmış PPMT işleviyle gerçekleştirilir.

IPMT işlevinin söz dizimi şöyledir:

PPMT formülünün söz dizimi ve parametreleri, daha önce açıklanan IPMT formülüyle tamamen aynıdır.

D7 hücresine aşağıdaki formülü girin, ardından her dönem için anaparayı doldurmak üzere dolgu tutamacını sütun boyunca aşağı doğru sürükleyin. Ekran görüntüsüne göz atı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 programınızda bir sonraki adım, her ödeme sonrası kredinin kalan bakiyesini belirlemektir. Zira bu, bakiyenin zaman içinde nasıl azaldığını net bir şekilde ortaya koyar ve programın kritik bir bileşenidir.

1. Bakiye sütununuzun ilk hücresine – E7 – aşağıdaki formülü girin; bu, kalan bakiyenin orijinal kredi tutarından ilk ödemenin anapara 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 anapara ödemesini çıkararak hesaplayın. Lütfen aşağıdaki formülü E8 hücresine uygulayın:

   =E7-D8

   Not: Bakiye hücresine yapılan başvuru göreli olmalıdır; böylece formülü aşağı doğru sürüklediğinizde otomatik olarak güncellenir.
   
3. Ardından, dolgu tutamacını sütunun aşağısına doğru sürükleyin. Gördüğünüz gibi, her hücre kalan bakiyeyi otomatik olarak güncellenmiş anapara ödemelerine göre hesaplayacaktır.
   

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

Ayrıntılı amortisman programınızı ayarladıktan sonra, kredinizin temel özelliklerine hızlı bir genel bakış sunan bir kredi özeti oluşturabilirsiniz. Bu özet genellikle kredinin toplam maliyetini ve ödenen toplam faiz miktarını içerir.

● 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 programı başarıyla hazır! Ekran görüntüsüne göz atın:

KUTOOLS AI ile Excel'in Sihirli Gücünü Keşfedin

• Akıllı Yürütme: Hücre işlemlerini gerçekleştirin, verileri analiz edin ve grafikler oluşturun—tümü basit komutlarla tek tuşla yönetilir.
• Özel formüller: İş akışlarınızı kolaylaştırmak için özelleştirilmiş formüller oluşturun.
• VBA Kodlama: VBA kodlarını zahmetsizce yazın ve anında uygulayın.
• Formül Yorumlama: Karmaşık formülleri kolayca anlayın.
• Metin Çevirisi: Elektronik tablolarınızda dil bariyerlerini aşın.

Yapay zekâ destekli araçlarla Excel becerilerinizi geliştirin.Şimdi İndirinve verimliliği hiç olmadığı gibi deneyimleyin!

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

Önceki örnekte, sabit sayıda ödeme için bir kredi geri ödeme programı oluşturduk. Bu yaklaşım, şartların değişmediği belirli bir kredi ya da konut kredisiyle çalışmak için mükemmeldir.

Ancak, farklı vadelerdeki krediler için tekrar tekrar kullanabileceğiniz ve çeşitli kredi senaryolarına göre ödeme sayısını esnekçe ayarlamanıza olanak tanıyan bir amortisman planı oluşturmak istiyorsanız, daha ayrıntılı bir yöntemi benimsemelisiniz.

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

1. Yıllık faiz oranı, yıl cinsinden kredi süresi, yılda ödeme sayısı ve kredi tutarı gibi ilgili kredi bilgilerini aşağıdaki ekran görüntüsünde gösterildiği gibi hücrelere girin:
   
2. Ardından, Excel’de Dönem, Ödeme, Faiz, Anapara ve Kalan Bakiye gibi etiketleri içeren bir amortisman tablosu oluşturun (hücreler A7:E7).
3. Dönem sütununa, herhangi bir kredi için düşünebileceğiniz en yüksek ödeme sayısını girin; örneğin, 1 ile 360 arasında değerler kullanın. Bu aralık, aylık ödeme yapıyorsanız standart 30 yıllık bir krediyi rahatlıkla kapsar.
   

⭐️ Adım 2: Ödeme, faiz ve anapara formüllerini EĞER (IF) işleviyle düzenleyin

İlgili hücrelere aşağıdaki formülleri girin ve ardından bu formülleri belirlediğiniz maksimum ödeme dönemine kadar doldurma tutamacıyla aşağı doğru genişletin.

● Ödeme formülü:

Normalde ödemenin hesaplanması için PMT işlevini kullanırsınız. Bir EĞER ifadesi eklemek için sözdizimi şu şekildedir:

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 şu şekildedir:

Formül şu şekildedir:

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

● Anapara formülü:

Sözdizimi şu şekildedir:

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 anaparayı önceki bakiyeden çıkarırsınız. Bunu bir EĞER ifadesiyle ş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: Bir kredi özeti oluşturun

Amortisman planınızı değiştirilmiş formüllerle ayarladıktan sonra bir sonraki adım, kredi özeti oluşturmak olur.

● Toplam ödemeleri hesaplamak için:

=SUM(B7:B366)

● Toplam faizi hesaplamak için:

=SUM(C7:C366)

⭐️ Sonuç:

Artık Excel’de, detaylı bir kredi özetini de içeren kapsamlı ve dinamik bir amortisman planınız var. Ödeme vadesini ne zaman değiştirirseniz, tüm amortisman planı bu değişiklikleri otomatik olarak yansıtmak üzere güncellenir. Aşağıdaki demoyu inceleyin:

Ek ödemelerle amortisman planı oluşturun

Planlanan ödemelerin ötesinde ek ödemeler yaparak bir krediyi daha hızlı kapatabilirsiniz. Excel’de hazırlanan ve bu ek ödemeleri içeren bir amortisman planı, ilave ödemelerin kredinin kapanışını nasıl hızlandırdığını ve ödenen toplam faizi nasıl azalttığını net bir şekilde gösterir. Kurulum şu şekildedir:

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

1. Yıllık faiz oranı, yıl cinsinden kredi süresi, yılda ödeme sayısı, kredi tutarı 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 ödemenin hesaplanmasını yapın.
   Giriş hücrelerine ek olarak, sonraki hesaplamalarımız için önceden tanımlanmış bir başka hücreye daha ihtiyacımız var: planlanan ödeme tutarı. Bu, ek ödemeler yapılmadığı varsayımıyla bir kredinin düzenli ödeme tutarıdır. Lütfen aşağıdaki formülü B6 hücresine girin:

   =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ücrelerine Dönem, Ödeme Planı, Ek Ödeme, Toplam Ödeme, Faiz, Ana Para, Kalan Bakiye gibi belirtilen etiketleri girin;
   • Dönem sütununa, herhangi bir kredi için düşünebileceğiniz en yüksek ödeme sayısını girin; örneğin, 0 ile 360 arasında bir değer. Bu aralık, aylık ödemelerle standart 30 yıllık bir krediyi kapsayabilir.
   • Dönem 0 için (9 numaralı satır), Bakiye değerini aşağıdaki formülle alın: =B4. Bu formül, başlangıç 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: Ek ödemeli amortisman planı için formülleri oluşturun

Aşağıdaki formülleri ilgili hücrelere sırayla girin. Hata işleme yeteneğinizi artırmak için bu formülü ve gelecekteki tüm formülleri HATAOLURSA (IFERROR) işlevi içine alın; böylece giriş hücrelerinden herhangi biri boş bırakıldığında veya geçersiz değerler içerdiğinde oluşabilecek olası hatalar önlenebilir.

● Planlanan ödemenin hesaplanması:

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

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

● Ek ödemenin hesaplanması:

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

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

● Toplam ödemenin hesaplanması:

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

=IFERROR(B10+C10, "")

● Anaparanın hesaplanması:

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

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

● Faizin hesaplanması:

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

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

● Kalan bakiyenin hesaplanması

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

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

Tüm formülleri tamamladıktan sonra B10:G10 hücre aralığını seçin ve bu formülleri doldurma tutamacıyla tüm ödeme dönemlerine kadar aşağı doğru genişletin. Kullanılmayan dönemlerdeki hücreler 0 değerini gösterecektir. Ekran görüntüsüne bakın:

⭐️ Adım 3: Bir 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 ek ödemeyi alın:

=SUM(C10:C369)

● Toplam faizi alın:

=SUM(F10:F369)

⭐️ Sonuç:

Bu adımları izleyerek Excel’de ek ödemeleri de hesaba katan dinamik bir amortisman planı oluşturun.

Excel şablonu kullanarak bir amortisman planı oluşturun

Excel’de bir şablon kullanarak amortisman planı oluşturmak, hem son derece kolay hem de zaman açısından son derece verimli bir yöntemdir. Excel, her ödeme için faiz, anapara ve kalan bakiyeyi otomatik olarak hesaplayan yerleşik şablonlar sunar. Excel şablonuyla amortisman planı oluşturmak için şu adımları izleyin:

1. Dosya > Yeni’ye tıklayın, arama kutusuna amortisman programı yazın ve 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ısı şablonunu seçeceğim. Ekran görüntüsüne bakın:
   
2. Bir şablon seçtikten sonra, bunu bir Yeni Çalışma Kitabı olarak açmak için Oluştur düğmesine tıklayın.
3. Ardından kendi kredi bilgilerinizi girin; şablon, girdilerinize göre programı otomatik olarak hesaplayıp dolduracaktır.
4. En İyi Ofis Verimlilik Araçları