Çoklu KDV Ayırma ve İki Bilinmeyenli KDV Hesaplama

Tek Bir KDV Tutarını İki Farklı Orana Ayıran Excel Çözümü​

Bir fişte veya faturada toplam tutar ve toplam KDV bellidir; ancak bu KDV'nin ne kadarının yüzde 20'lik, ne kadarının yüzde 10'luk ya da yüzde 1'lik kalemlerden geldiği yazmaz. Muhasebe kaydında her oranın matrahı ve KDV'si ayrı satırda gösterilmek zorunda olduğu için bu iki bilinmeyenin bulunması gerekir. Bu dosya, genel toplam ve toplam KDV girildiğinde iki farklı KDV oranına düşen matrahları, her orana ait KDV tutarını ve KDV dahil toplamları tek satırda, formülle çözer. Hesaplamanın tamamı Excel'in yerleşik matematiksel işlemleriyle ve YUVARLA ile TOPLA fonksiyonlarıyla yapılır; makro, yardımcı sayfa veya dış bağlantı yoktur.

Dosya Ne İşe Yarar​

Dosya, iki bilinmeyenli KDV hesaplama problemini çözer. Problem şudur: KDV dahil toplam tutar (T) ve toplam KDV (K) bilinmektedir; faturada yalnız iki farklı KDV oranı (r1 ve r2) kullanılmıştır; ancak her orana ait matrah bilinmemektedir. İki bilinmeyen (birinci oranın matrahı m1 ve ikinci oranın matrahı m2) iki denklemle çözülür:

• Matrahların toplamı, KDV hariç tutara eşittir: m1 + m2 = T − K
• Her matrahın kendi oranıyla çarpımının toplamı, toplam KDV'ye eşittir: m1 × r1 + m2 × r2 = K

İlk denklemden m2 çekilip ikinciye yazıldığında birinci matrah için tek bir ifade kalır: m1 = (K − (T − K) × r2) / (r1 − r2). Dosyadaki formüller bu ifadenin birebir Excel karşılığıdır; kullanıcı denklem kurmaz, yalnız dört değeri girer.
Bu yöntem; market ve akaryakıt fişlerinde, restoran adisyonlarında, gıda ve gıda dışı ürünün bir arada satıldığı perakende faturalarında, farklı oranlı kalemleri olan e-arşiv faturalarında ve KDV beyannamesine hazırlık sırasında kullanılır. Kalem kalem dökümü olmayan belgelerde matrah ayrımı başka türlü yapılamaz.

Sayfa Yapısı ve Hücre Düzeni​

Dosyada KDV Ayırma adlı tek sayfa bulunur. Veri girişi ve sonuçlar aynı satırdadır; bloklar arasındaki dar sütunlar (A, D, G, K ve N) yalnız görsel ayırıcıdır ve hesaba katılmaz.

• Giriş bloğu (B3:F3): Genel Toplam, KDV, Oran 1 ve Oran 2. Krem zeminli başlıklar giriş alanını gösterir. Oranlar yüzde işareti olmadan, tam sayı olarak girilir (20, 10, 1 gibi).
• Matrah bloğu (H3:J3): KDV Matrahı 1, KDV Matrahı 2 ve Toplam Matrah. Mavi zeminli başlıklar formül alanını gösterir.
• KDV bloğu (L3:M3): Her orana ait KDV tutarı (KDV1 ve KDV2).
• Toplam bloğu (O3:Q3): Her oranın KDV dahil toplamı ve ikisinin toplamı olan Genel Toplam. Son hücre, girişteki genel toplamla aynı değeri vermek zorundadır; bu hücre sağlama görevi görür.

Dosyanın örnek satırı aşağıdaki gibidir. Formül hücrelerinin üzerine gelindiğinde dosyadaki formül görüntülenir:

Kullanılan Formüller ve Hesaplama Mantığı​

Sekiz formül hücresi vardır; hepsi 3. satırdadır ve yalnız aynı satırdaki giriş hücrelerine başvurur. Bu sayede satır aşağı kopyalandığında her satır kendi verisiyle çalışır.

• KDV Matrahı 1 (H3): =YUVARLA((C3-(B3-C3)*(F3/100))/((E3/100)-(F3/100));2). B3−C3 ifadesi KDV hariç toplam matrahtır. Bu matrahın tamamına ikinci oran uygulansaydı çıkacak KDV, toplam KDV'den düşülür; kalan fark iki oran arasındaki farka bölünür. Sonuç, birinci orana düşen matrahtır. YUVARLA ile iki ondalığa yuvarlanır.
• KDV Matrahı 2 (I3): =YUVARLA((C3-(B3-C3)*(E3/100))/((F3/100)-(E3/100));2). Aynı mantık oranlar yer değiştirilerek uygulanır; ikinci orana düşen matrah bulunur. İki matrah bağımsız hesaplandığı için yuvarlama farkı Toplam Matrah hücresinde görülebilir; bu durum Sağlama ve Yuvarlama bölümünde açıklanmıştır.
• Toplam Matrah (J3): =TOPLA(H3:I3). İki matrahın toplamıdır ve KDV hariç tutara (B3−C3) eşit olmalıdır.
• KDV1 (L3): =YUVARLA(H3*E3%;2). Birinci matrah ile birinci oranın çarpımıdır. E3% yazımı, 20 değerini 0,20 olarak kullanır.
• KDV2 (M3): =YUVARLA(I3*F3%;2). İkinci matrah ile ikinci oranın çarpımıdır. L3 ile M3'ün toplamı girişteki KDV'ye eşit olmalıdır.
• Toplam 1 (O3): =TOPLA(H3;L3). Birinci oranın KDV dahil tutarıdır.
• Toplam 2 (P3): =TOPLA(I3;M3). İkinci oranın KDV dahil tutarıdır.
• Genel Toplam (Q3): =TOPLA(O3:P3). İki toplamın birleşimidir; girişteki Genel Toplam ile aynı değeri vermesi beklenir.

Formüllerde yüzde değerleri 100'e bölünerek kullanıldığından oranlar tam sayı olarak girilir. 0,20 biçiminde girilen bir oran formülün paydasını sıfıra yaklaştırır ve yanlış sonuç verir.

Örnek Hesaplamanın Adım Adım Açıklaması​

Dosyadaki örnekte Genel Toplam 51.000,00 ₺, KDV 4.946,56 ₺, oranlar 20 ve 10'dur.

1. KDV hariç toplam matrah: 51.000,00 − 4.946,56 = 46.053,44 ₺.
2. Bu matrahın tamamı yüzde 10'luk olsaydı KDV 4.605,34 ₺ olurdu. Gerçek KDV bundan 341,22 ₺ fazladır; fazlalık, yüzde 20'lik kalemlerin yüzde 10'u aşan ek KDV'sinden gelir.
3. Ek KDV, oran farkına (0,20 − 0,10 = 0,10) bölünür: 341,22 / 0,10 = 3.412,16 ₺. Bu, yüzde 20'lik matrahtır.
4. İkinci matrah: 46.053,44 − 3.412,16 = 42.641,28 ₺.
5. KDV1: 3.412,16 × 0,20 = 682,43 ₺; KDV2: 42.641,28 × 0,10 = 4.264,13 ₺. Toplam 4.946,56 ₺; girişle aynıdır.
6. Toplam 1: 4.094,59 ₺; Toplam 2: 46.905,41 ₺; Genel Toplam: 51.000,00 ₺.

İkinci Örnek Yüzde Yirmi ve Yüzde Bir Oranları​

Temel gıda ile diğer ürünlerin birlikte satıldığı bir fişte Genel Toplam 12.000,00 ₺ ve KDV 1.350,00 ₺ olsun; oranlar 20 ve 1 girildiğinde dosya şu sonucu verir:
KDV hariç toplam 10.650,00 ₺'dir. Bu tutarın tamamı yüzde 1 olsaydı KDV 106,50 ₺ olurdu; gerçek KDV ile aradaki 1.243,50 ₺ fark, 0,19'luk oran farkına bölününce 6.544,74 ₺'lik yüzde 20 matrahı çıkar. Kalan 4.105,26 ₺ yüzde 1'lik matrahtır.

Sağlama ve Yuvarlama Farkları​

İki matrah da iki ondalığa yuvarlandığı için Toplam Matrah, KDV hariç tutardan bir kuruş sapabilir; aynı şekilde KDV1 ile KDV2'nin toplamı girişteki KDV'den bir kuruş farklı çıkabilir. Bu fark hesaplama hatası değil, yuvarlama sonucudur. Muhasebe kaydında fark, tutarı büyük olan satıra eklenerek kapatılır. Q3 hücresindeki Genel Toplam, girişteki Genel Toplam ile karşılaştırılarak sağlama yapılır; iki değer bir kuruştan fazla farklıysa girilen KDV tutarı veya oranlar hatalıdır.

Nasıl Kullanılır​

1. Dosyayı açın ve B3 hücresine belgenin KDV dahil genel toplamını, C3 hücresine belgedeki toplam KDV tutarını yazın.
2. E3 ve F3 hücrelerine belgede kullanılan iki KDV oranını yüzde işareti olmadan girin (20 ve 10, 20 ve 1, 10 ve 1 gibi). Büyük oranın E3'e yazılması zorunlu değildir; formüller her iki sırada da çalışır.
3. H3:Q3 aralığındaki sonuçları okuyun: her oranın matrahı, KDV'si ve KDV dahil toplamı.
4. Q3 hücresindeki Genel Toplam'ın B3 ile aynı olduğunu kontrol edin.
5. Birden fazla belge için 3. satırı seçip aşağı kopyalayın; her satır kendi girişleriyle hesaplanır. Böylece bir aylık fişler tek listede ayrıştırılabilir.

Hangi Durumlarda Çalışmaz​

• Üç ve daha fazla oran: Üç bilinmeyen için iki denklem yetmez; belge üç farklı oran içeriyorsa kalemlerden en az birinin matrahı ayrıca bilinmelidir. Bilinen kalem toplamdan ve KDV'den düşülüp kalan iki oran bu dosyayla çözülür.
• Aynı oranın iki kez girilmesi: E3 ve F3 eşitse formülün paydası sıfır olur ve hücrede #SAYI/0! hatası görünür.
• Tutarsız giriş: Girilen KDV, matrahın tamamına küçük oran uygulandığında çıkacak KDV'den az ya da büyük oran uygulandığında çıkacak KDV'den fazlaysa matrahlardan biri negatif çıkar. Bu durumda belge yeniden kontrol edilmelidir.
• KDV'siz kalemler: İstisna veya yüzde 0 oranlı kalem varsa oranlardan biri 0 olarak girilir; formül bu durumu da çözer.

KDV Ayrıştırma Dosyasından Farkı​

Sitede yer alan KDV Ayrıştırma İki Oranlı Matrah Çözme dosyası aynı problemi KDV hariç matrah toplamından yola çıkarak çözer ve çok satırlı liste düzenindedir. Bu dosya ise KDV dahil genel toplamdan başlar ve tek satırlık, formülü görünür bir öğretici düzendedir. Fişte yalnız KDV dahil toplam ve KDV yazıyorsa bu dosya; matrah toplamı ayrıca biliniyorsa diğer dosya doğrudan kullanılır. Tek oranlı belgelerde matrah ve KDV'yi bulmak için KDV Hesaplama dosyası yeterlidir.

Kimler İçin Uygun​

Mali müşavirler ve muhasebe çalışanları, farklı oranlı kalemlerin bir arada olduğu fişleri kaydeden ön muhasebe personeli, e-arşiv faturalarını kontrol eden satın alma birimleri ve KDV matrahı ayırma mantığını Excel formülüyle öğrenmek isteyenler için uygundur. Formüller açık olduğundan dosya, iki bilinmeyenli denklemin Excel'de nasıl kurulduğunu gösteren bir örnek olarak da kullanılabilir.