Bir borç planı oluşturmak için Excel'in PMT, IPMT ve IF formüllerini kullanabiliriz. Öncelikle, bazı borç varsayımlarını girerek modeli kurmamız gerekiyor. Bu örnekte, borcun 5.000.000 $ olduğunu, ödeme süresinin 5 yıl olduğunu ve faiz oranının olduğunu varsayıyoruz. Faiz oranı, verilen herhangi bir borç türü için borç veren tarafından bir borçluya tahsil edilen tutarı ifade eder, genellikle şu şekilde ifade edilir: anaparanın bir yüzdesi. % 4,5 olacak.
1. Borç planımızdaki açılış bakiyesi 5 milyon dolarlık kredi miktarına eşittir, bu nedenle E29 hücresine varsayım girdisine bağlamak için = B25 giriyoruz . Ardından, ilk döneme ilişkin toplam ödemeyi hesaplamak için PMT formülünü kullanabiliriz = PMT ($ B $ 27, $ B $ 26, $ B $ 25) . Formül, varsayım bölümünde belirtilen kredi tutarı, vade ve faiz oranını kullanarak ödeme tutarını hesaplar.
2. E28 hücresine içinde bulunduğumuz dönemi girin, yani 1. E29 hücresine = E28 + 1 girin ve formülü sağa doldurun. Ardından, ilk dönem için faiz ödemesini bulmak için IPMT formülünü kullanın = IPMT ($ B $ 27, E28, $ B $ 26, $ B $ 25) .
3. Ana ödeme, toplam ödeme ile faiz ödemesi arasındaki farktır, = E30-E31 . Kapanış bakiyesi açılış bakiyesi artı yapılan anapara ödemesidir , bu = E29 + E32 . 2. dönem açılış bakiyesi, 1. dönem kapanış bakiyesidir, = E33 .
4. E29 hücresinden E33'e tüm formülleri sonraki sütuna kopyalayın, ardından her şeyi sağa kopyalayın. Doğru formül ve sayıların kullanıldığından emin olmak için dönem 5 = 0 kapanış bakiyesinin kontrol edin.
5. Periyot 6'dan başlayan bazı hata mesajları olduğuna dikkat edin çünkü açılış bakiyesi 0'dır. Burada hataları temizlemek için IF işlevini kullanabiliriz. E30 hücresine = IF (E29> 0, PMT ($ B $ 27, $ B $ 26, $ B $ 25), 0) yazın . Formül, açılış bakiyesinin 0'dan küçük olması durumunda toplam ödeme değerinin 0 olarak gösterileceğini belirtir.
6. 31 hücresine = EĞER (E29> 0, IPMT ($ B $ 27, E28, $ B $ 26, $ B $ 25), 0) yazın . Bu formül, açılış bakiyesinin 0'dan küçük olması durumunda faiz ödemesinin 0 olarak gösterileceğini belirten öncekine benzer.
7. E30 ve E31 hücrelerini kopyalayın, SHIFT + sağ ok tuşlarına ve ardından sağa doldurmak için CTRL + R tuşlarına basın. Tüm hata mesajlarının artık 0 olarak gösterildiğini görmelisiniz.
XNPV TARİH ve EĞER işlevlerine sahip bir XIRR
DATE ve IF işlevleriyle birlikte XNPV ve XIRR Excel işlevlerini kullanarak belirli tarihlere göre NPV ve IRR'yi hesaplayabiliriz.
8. Tarihi görüntülemek için E6 hücresine gidin ve = TARİH (E5,12,31) girin . Sağa kopyala. # DEĞER! 2021'den sonraki mesaj. EĞERHATA işlevi = EĞERHATA (TARİH (E5,12,31), ”) kullanarak bunu düzeltebiliriz .
9. Şimdi NPV ve IRR'yi hesaplamaya başlayabiliriz. İlk olarak, serbest nakit akışı tutarlarını girmemiz gerekiyor. 1'den 5'e kadar olan dönemdeki FCF tutarlarının -1,000, 500, 600, 700, 900 olduğunu varsayıyoruz. C37 hücresine% 15'lik bir indirim oranı gireceğiz. B37 hücresinde, XNPV formülü = XNPV (C37, E35: I35, E6: I6) kullanarak NPV'yi hesaplayın .
10. B38 hücresinde, XIRR formülü = XIRR (E35: I35, E6: I6) kullanarak IRR'yi hesaplayın .
XNPV ve XIRR'ye OFSET Ekleme
OFFSET işlevini kullanarak daha dinamik formüller oluşturmak için XNPV ve XIRR formüllerine geçebiliriz.
11. B42 hücresinde, formülü = XNPV (C42, E40: KAYDIRMA (E40,0, $ F $ 3-1), E6: I6) olarak değiştirin . Formül daha dinamiktir çünkü dönem sayısı artarsa, serbest nakit akışı dönemleri de artacaktır. Tahmin süresi daha uzunsa NPV formülünü değiştirmemize gerek yok. IRR işlevi için bunu = XIRR (E40: OFFSET (E40,0, $ F $ 3-1), E6: I6) olarak değiştirin .
12. Formülü dönem sayısı için ayarladıktan sonra, tarihleri kaydırmalıyız. B42 hücresinde, formülü = XNPV (C42, E40: OFFSET (E40,0, $ F $ 3-1), E6: OFFSET (E6,0, $ F $ 3-1)) olarak değiştirin . Bu, NPV ve IRR formüllerinin, dönem sayısındaki değişiklikle birlikte doğru sayıda serbest nakit akışını almasını sağlar.
Temel Borç Takvimi Formüllerinin Özeti
- Borç ödeme tutarını hesaplamak için PMT formülü: = PMT (faiz oranı, vade sayısı, bugünkü değer)
- Faiz ödemesini hesaplamak için IPMT formülü: = IPMT (faiz oranı, dönem, vade sayısı, bugünkü değer)
- Net bugünkü değeri bulmak için XNPV formülü: = XNPV (iskonto oranı, serbest nakit akışları, tarihler)
- İç getiri oranını bulmak için XIRR formülü: = XIRR (serbest nakit akışları, tarihler)
- Dinamik NPV'yi hesaplamak için OFSET formülü: = XNPV (iskonto oranı, 1. FCF: OFFSET (1. FCF, 0, # dönemler - 1), 1. tarih: OFFSET (1. tarih, 0, # dönem - 1))
- Dinamik IRR'yi hesaplamak için OFSET formülü: = XIRR (1. FCF: OFFSET (1. FCF, 0, # periyotlar - 1), 1. tarih: OFFSET (1. tarih, 0, # nokta - 1))
Diğer kaynaklar
Finansın PMT, IPMT ve IF formülleriyle Borç Takvimi hakkındaki kılavuzunu okuduğunuz için teşekkür ederiz. Öğrenmeye ve kariyerinizi ilerletmeye devam etmek için aşağıdaki Finans kaynakları yardımcı olacaktır:
- Temel Excel Formülleri Temel Excel Formülleri Temel Excel formüllerinde ustalaşmak, yeni başlayanların finansal analizde uzmanlaşması için kritik öneme sahiptir. Microsoft Excel, veri analizinde endüstri standardı yazılım parçası olarak kabul edilir. Microsoft'un elektronik tablo programı da yatırım bankacıları tarafından en çok tercih edilen yazılımlardan biri oluyor.
- En İyi Finansal Modelleme Uygulamaları En İyi Finansal Modelleme Uygulamaları Bu makale, okuyuculara en iyi finansal modelleme uygulamaları hakkında bilgi sağlamak ve bir finansal model oluşturmak için takip etmesi kolay, adım adım yol gösteren bir kılavuz sağlamak içindir.
- Excel İşlevleri İşlevleri Listesi Finansal analistler için en önemli Excel işlevlerinin listesi. Bu hile sayfası, bir Excel analisti olarak bilinmesi kritik olan 100'lerce işlevi kapsar
- Excel Kısayollarına Genel Bakış Excel Kısayollarına Genel Bakış Excel kısayolları, Excel içinde üretkenliği ve hızı artırmak için gözden kaçan bir yöntemdir. Excel kısayolları, finansal analiste güçlü bir araç sunar. Bu kısayollar birçok işlevi yerine getirebilir. formülleri doldurmak veya verileri gruplamak için elektronik tablo içinde gezinmek kadar basit.