Excel’de Yetkinleşiyoruz — Ödeme Planı Oluşturma
Ülkemizde özellikle son yıllarda ülkemizdeki vakıf üniversitelerinin sayısı bir hayli arttı. Bu yazımızda da vakıf üniversitesinde belirli bir ücret ödeyerek okuyan öğrencinin; bu ödenen parayı kaç yılda ailesine geri ödeyebileceğini ölçeceğiz. Deniz isimli bir öğrencimiz olsun.
Deniz, X isimli vakıf üniversitesinde yıllık ücreti 70.000 TL olan 5 yıllık bir bölümü %75 burs ile kazandı. Bu süreçte üniversite masrafını Deniz’in ebeveynlerini finanse ediyor. Deniz’in ailesine bu parayı geri ödemek istiyor ve ne kadar sürede ödeyebileceğini hesaplamak istedi. Yani, Deniz’in ailesine sanal bir borç hesabı açmış gibi düşünebiliriz, aslında bir finans problemi çözeceğiz.
1. VARSAYIMLAR
Net olarak bir sonuca ulaşmak için varsayımlarda bulunmak zorundayız
- Enflasyon tahmini: %12 aslında çok da önemli bir değişken değil çünkü; enflasyon ile doğru orantılı olarak maaşımız da artacak. Eğer enflasyonun %3’e kadar düşmeyecekse çok radikal bir etki etmeyecek.
- Borç Faizi: %15, genellikle enflasyonun 2–3 puan üzerinde olur.
- Başlangıç Maaşı: Mezuniyetten sonraki ilk maaşı 5000 TL olarak işe başlayacağını varsayıyoruz.(5 yıl sonrası için)
- Maaş artışının: enflasyon + 1 olacağını varsayıyoruz.
- Deniz iş hayatına başladıktan sonra maaşının %30’unu geri ödeme için ayıracağını varsayıyoruz. (%30’un üzerine çıkan bir mezunun hayat koşullarında çok zorlanır.)
Problemimiz: Deniz mezun olduğunda borcu ne kadardır ve bu borcu ailesine kaç yılda geri ödeyebilir?
Çözümümüz: Sadece cevabı veren 2 farklı sayı değil, varsayımlarımızı da değiştirsek anında çözüme ulaşabileceğimiz bir karar-destek mekanizması oluşturacağız.
Çözümümüzü 3 adımda gerçekleştireceğiz.
Çözüm hedefimiz Tablo-1’deki gibi karar-destek mekanizması oluşturabilmek. Yani, girdileri değiştirince anında çıktıların da değişmesi.
- Üst bölümdeki mavi hücreler; öğrenci okurkenki girdiler,
- Üst bölümdeki gri hücreler; öğrenci mezun olduktan sonraki girdiler,
- Çıktılar ise yeşil olarak renklendirildi.
Amacımız karar-destek sistemi kurmak: Girdileri değiştirsek de anında doğru çıktıyı alabilmek istiyoruz. Ve bunu tek bir sayfada yapacağız; biz girdileri yazacağız, arka planda yazdığımız model çalışacak ve bize çıktıları yazacak.
Karar-destek sistemini kurabilmek için önce okuldan ayrılırken -işe başlarken- toplam ne kadar borcumuz olduğunu bulalım:
2. TABLO -TOPLAM BORÇ
1.Yıl ücreti için; Okulun bir yıllık fiyatını, öğrencinin burs oranına ve KDV’ye göre hesaplıyoruz.
İlk yıl ücreti = Okul fiyatı*Burs Oranı*KDV(1.08)= 19800 TL (Yılın başında borcumuz)
Yıl sonundaki borcumuzu öğrenmek için ise borç faizi ile yılbaşındaki borcumuzu çarpıyoruz. Yani Eylül ayında 19800 TL olan borcunuz, 11 ay sonra Ağustos ayında 21735 TL’ye çıkıyor. Ayrıca sonraki yıllar için tekrar tekrar borç faizi hücresini yazıp zaman kaybetmek istemiyoruz bu yüzden, bu hücrenin içindeyken borç faizini referanslıyoruz.($D$5 yazıyoruz. Kısayolu: D5’i f4lemek)
Püf Nokta: Bazı formülleri sağa sola yukarı aşağıya kaydırarak ya da kopyalayarak çoğaltmak isteriz. Ancak formül içerisine verilen hücre referanslarının (adreslerinin) bazı durumlarda değişmesi, bazı durumlarda ise sabit durması gerekir. Bu konuda hücre referansını $ işareti (Klavye kısayolu: F4 ya da Fn + F4)
Referanslamak: Formül içerisine yazılan hücre formülü nereye kaydırırsanız kaydırın değişmez. Hem satır hem de sütun numarasının önünde dolar $ işareti bulunur. Formül üzerindeyken bir kere F4 e basarsanız dolar işareti oluşur.
İkinci Yıl ve daha sonraki borçlar için:
1.yıl ücret hücresine(C16) giriyoruz. 2. yılın fiyatı bir önceki yılın enflasyon ile çarpımı kadar artacaktır.
// Bu kez enflasyonu referansladık, çünkü sonraki yıllarda da kullanacağız ve iyi bir Excel kullanıcısı olmak için kendimizi tekrar etmek istemeyiz :)
2.yıl ücretini bulduktan sonra ise diğer yılların ücretini de yine aynı şekilde (Bir önceki yılın fiyatını, enflasyon ile çarparak) bulabiliriz. Excel’de bunu yapmanın çok daha kolay bir yolu var. 2.yıl ücreti olan C16 hücresini tıklayıp imlecinizi sağ alt köşesini getirince küçük bir “+” çıktığını fark edeceksiniz. O noktada mausenuzun sol tuşuna çift tıklayarak 3,4 ve 5. yılın ücretlerini görebileceksiniz. Böylelikle 5 yıl boyuncaki ücretleri bulduk.
İkinci yılbaşındaki borcumuzu bulmak için ise; 1.yıl sonu borcumuz(E15) ile 2. yılın ücretini(C16) topluyoruz. İkinci yıl sonu borcumuzu bulmak için; Yıl sonundaki borcumuzu öğrenmek için ise borç faizi ile yıl başındaki borcumuzu çarpıyoruz. Bu işlemi yazmamıza gerek yok çünkü biz borç faizini referanslamıştık. Bu yüzden E15 hücresine gidip sağ altta çıkan “+”yı 1 satır aşağıya kaydırıyoruz.
Worksheetimizdeki geri kalan borçları bulmak artık çok kolay. D16 ve E16 hücrelerini seçiyoruz. Yine sağ alt köşeye gidiyoruz ve “+” yı görünce çift tıklıyoruz.
Artık bütün worksheet hazır ve Deniz’in 5 yıl sonundaki borcunu 180412 TL olarak bulduk. Bu sonucu çıktılardaki Mezuniyet Borcu(D11) hücresine yazıyoruz.
Bu borcumuzun yıllık faizini bulmak için: 5 yıl sonundaki borcumuzu (180.412), borç faizi (0.15) ile çarpıyoruz. = 27061TL . Yani biz mezun olduktan sonra her yıl en az 27.061 TL ödeme yapmalıyız ki bu borcumuz artmasın.
3. ÖDEME PLANI
Gelir: Net yıllık gelir
İlk yıl gelirini; yukarıdaki tablodaki bir aylık maaşımız olan 5000 TL yi 12 ile çarparak bulabiliriz. İlk yıllık net gelirimiz 60.000TL.
Geri Ödeme: O yıl ödenecek tutar
İlk yıl geri ödemesini; yukarıdaki tabloda maaşımızın %30’unu geri ödeme için ayrılacağımızı söylemiştik. Bu yüzden yıllık net gelirimizi %30 ile çarparak o yılki ödememizi bulabiliyoruz. Ayrıca referans aldığımız için bundan sonraki yıllar da çorap söküğü gibi geliyor :)
Borcumuz:
Birinci yıl başındaki borcumuzu bulmak için direkt olarak mezuniyet sonundaki borcumuzu(E19) 180412TL’yi alıyoruz.
Yıl sonundaki borcumuzu bulmak için ise yıl başı borcumuzu %15 olan borç faizi ile çarpıyor ve borç faizini referanslıyoruz.
Ödeme vakti geldii :)
Ödeme sonrası borcumuzu bulmak için; yıl sonundaki borcumuzdan, ödeme için ayırdığımız parayı çıkartıyoruz.
//Neden borcumuz arttı?
Çünkü ilk yıl ödediğimiz tutar olan 18000 TL, borcumuzun yıllık faizinden 27061 TL’den daha düşük.
2. yıl başı borcumuz ise direkt olarak ödeme sonrası olan borcumuza(F22) eşit. Yıl sonu ve ödeme sonrası satırlarında referans aldığımız için doğrudan aşağı çekerek ikinci yıl sonu ve ödeme sonrası tutarını da elde edebiliyoruz.
İkinci yılki gelirimizi hesaplamak için ise enflasyon ve maaşa gelen zam ile çarpmamız gerekiyor. Yani geçen seneki gelirimizi enflasyon (%12) + maaş zammı(%1) ile çarpıyoruz. = 67800. Böylelikle 2. yıl sonundaki maaşımızı da bulmuş olduk. Enflasyon ve maaş zammını referans aldığımız için diğer 50 yılı tek hamleyle bulabiliriz :)
Artık her noktamız referanslı ve gerisi sadece iki tık: tık tık
Ödeme sonrasında -’ye geçtiğimiz yıl artık borcumuz kapanmıştır. Bu yüzden “countif”(eğersay) formülünü kullanarak kaçıncı sene de borcumuzun kapandığını bulabiliyoruz.
Çözümümüzü özetlemek gerekirse; İlk olarak 5 yıl boyunca oluşacak borcumuzu bulduk. Sonrasında ödemelerimiz ile birlikte borcumuzun nasıl değişeceğini gösteren bolca referans içerin bir modelleme yaptık. Son olarak ise countif (eğersay) formülü ile borcumuzun ne zaman bittiğini hesapladık.
Excel dokümanına buradan ulaşabilirsiniz, problemin videolu çözümüne buradan ulaşabilirsiniz.
Saygılarımla,
Berke Yalman
