YENİ BLOG ADRESİ

arkadaşlar merhaba,

yeni blog adresim

http://excelyardim.wordpress.com/

artık güncel yazılara buradan ulaşabilirsiniz,

sevgiler,

duygu

Banka Kredi/Mortgage Ödeme Hesaplama - DEVRESEL_ÖDEME- PMT Fonksiyonu

Banka Kredi/Mortgage Ödeme Hesaplama - DEVRESEL_ÖDEME- PMT Fonksiyonu

FAİZ TUTARI -IPMT FONKSİYONU

ANA_PARA_ÖDEMESİ - PPMT FONKSİYONU

Evet bu bölümde biraz finansal fonksiyonlardan bahsedeceğiz. Özellikle bankaların sitelerinde gördüğümüz kredi hesaplama araçlarının mantığını anlamaya çalışıp hem de kendi hesaplama aracımızı oluşturacağız.

Öncelikle ufak birkaç bilgiyle konumuza giriş yapalım. Kredi kullanırken bankaların müşteriye sunduğu faiz oranı bileşik faizdir. Aşağıda kullanacağımız formül belirli vadeli ve aylık sabit taksitli kredi hesaplaması için kullanılır.

3 adet formül üzerinde duracağız:

1) DEVRESEL_ÖDEME(oran; dönemsayısı; bd)

 

 

Oran: Aylık faiz oranını temsil eder, eğer elimizdeki oran yıllık ise formüle girerken yıllık faiz oranı/12 şeklinde kullanmalıyız.

Dönem sayısı : Krediyi kaç ay vadeli kullanacağımız, (birimi: ay)

Bd: Bugünkü değer yani kullanmak istediğimiz kredi tutarı, formül sonucu (–) negatif döndürür pozitife çevirmek için ana parayı eksi gireceğiz.

	A	B	C
1	vade sayısı	9
2	alınacak kredi tutarı	10.000
3	aylık faiz	1,50%
4
5	aylık ödeme	1.196,10 TL	=DEVRESEL_ÖDEME(B3;B1;-B2)
6	toplam ödeme	10.764,88 TL	=B5*24
7	toplam faiz	764,88 TL	=+B6-B2
8
9	yıl	20
10	alınacak kredi tutarı	100.000
11	yıllık faiz	5,00%
12
13	aylık ödeme	659,96 TL	=DEVRESEL_ÖDEME(B11/12;B9*12;-B10)
14	toplam ödeme	158.389,38 TL	=B13*B9*12
15	toplam faiz	58.389	=+B14-B10

DEVRESEL_ÖDEME(oran; dönem sayısı; bd) : ilk örnek için oran 1,5%, vade 9 ay, tutar 10.000

DEVRESEL_ÖDEME(0,015; 9; -10000) şeklinde formülü girdik aylık ödememiz gereken tutar 1.196 TL çıktı.

9 ay ödeme yapacağımız için toplam ödeyeceğimiz tutarı aylık tutar * vade sayısı olarak 10.764 TL bulduk

Alınan krediden bu rakamı çıkararak ödeyeceğimiz faiz tutarına 764TL ulaşmış olduk.

DEVRESEL_ÖDEME(oran; dönemsayısı; bd) : ikinci örnek için oran yıllık 5%, vade 20 yıl, tutar 100.000

Oranı 12 ye bölerek aylığa, vadeyi de 12 ile çarparak aylığa dönüştürmeliyiz. Böylelikle

DEVRESEL_ÖDEME(0,05/12; 20*12 ; -100000) şeklinde formülü girdik

ANA_PARA_ÖDEMESİ (oran; dönem; dönem_sayısı; bd)

İkinci kullanılacak formül ise aylık ödeme içerisindeki ana para tutarını bulmak,

Her ay ödediğimiz taksitlerin içindeki ana para tutarını hesaplar.

Burada formülde geçen "dönem" sorguladığımız ayı ifade eder. yani 3. ay taksidimin içindeki ana para tutarı nedir dediğimizde dönem=3 olacaktır.

FAİZTUTARI(oran; dönem; dönem_sayısı; bd)

Üçüncü ve son formülümüz ise Ödenen taksitler içerisindeki faiz tutarını bulmamızı sağlar.

Burada formülde geçen "dönem" sorguladığımız ayı ifade eder. yani 3. ay taksidimin içindeki faiz tutarı nedir dediğimizde dönem=3 olacaktır.

	A	B	C
1	vade sayısı	9
2	alınacak kredi tutarı	10.000
3	aylık faiz	1,50%
4
5	aylık ödeme	1.196,10 TL	=DEVRESEL_ÖDEME(B3;B1;-B2)
6	Ana para ödemesi	1.046,10 TL	=ANA_PARA_ÖDEMESİ(B3;1;B1;-B2)
7	Faiz Tutarı	150,00 TL	=FAİZTUTARI(B3;1;B1;-10000)
8

= ANA_PARA_ÖDEMESİ (oran; dönem; dönem_sayısı; bd) = ANA_PARA_ÖDEMESİ(B3;1;B1;-B2)

oran = B3: oran %1,5

dönem = 1: dönem ilk ay taksidi içerisindeki ana parayı bulalım

vade = B1: Vade sayısı

bd = -B2: kredi tutarı

= FAİZTUTARI(oran; dönem; dönem_sayısı; bd) = FAİZTUTARI(B3;1;B1;-10000)

oran = B3: oran %1,5

dönem = 1: dönem ilk ay taksidi içerisindeki faizi bulalım

vade = B1: Vade sayısı

bd = -B2: kredi tutarı

*yani ilk ay ödediğim 1.196  TL nin 1.046  TL si ana para 150 TL si faiz olarak ödenmiştir.

elimizde aylık taksit tutarı ve ödenen ana para tutarı var ise faiz tutarını taksit - anapara olarak da bulabiliriz.

NOT :Her ay kalan para azalacağı için ödenen ana para ve faiz tutarları değişiklik gösterecektir. Şimdi 9 aylık kredi ödeme tablomu oluşturmaya çalışalım:

Yukarıdaki formüle ek olarak her ayın ana para ve faizini hesaplarken dönem kısmına 1-9 (9 AYLIK VADE OLDUĞU İÇİN) arası değerleri tek tek gireceğiz.

Hedef Ara (GOAL SEEK)

Hedef Ara (GOAL SEEK) özelliği belirli girdi ve çıktı değerlerinden oluşan bir data setinde istenilen hedef sonucuna ulaşmak için girdilerin nasıl değişmesi gerektiğini hesaplamamızı sağlar,

	A	B	C	D	E	F	G	H
1
2		ürün
3	satış adedi	6
4	ürün fiyatı	80 TL
5	indirim oranı	10%
6	toplam kazanç	432	=B4*(1-B5)*B3
7

Örneğin yukarıda satış adedi, ürün fiyatı ve indirim oranı verilmiş ve toplam kazanç hesaplaması yapılmış, burada ilk 3 girdiyi değiştirerek yeni toplam satış rakamına formül yardımıyla kolayca ulaşabiliriz,

Peki 800 TL kazanç değerine ulaşmak için gerekli olan satış adedi sorulursa formülde nasıl geriye gideceğiz? İşte tam bu noktada Hedef Ara (GOAL SEEK) işlevinden yararlanacağız.

 

veri=>durum çözümlemesi=>hedef ara kısmına gidiyoruz.

 

 

 

Yani;

Ayarlanacak hücre: kazanç hücresini ayarlamak istiyoruz.

Sonuç: 800 tl olmasını istiyoruz.

Değişecek hücre: 800 Tl ye ulaşmak için kaç adet satmalıyım? Yani satış adedi hücresi.

*Aynı şekilde değişecek hücreyi ürün fiyatı olarak verebiliriz yani 800 TL kazanç için ürün fiyatım ne olmalı?