Fulya Ergeç
Excel Rehberi
Excel’de Hisse Senedi Portföyü Yönetimi
Buraya tıklayarak konuyla ilgili Excel dosyasını görüntüleyebilirsiniz.
Portföy Hazırlama
Excel’de Tools*Solver (Araçlar*Çözücü) komutları kullanılarak, hisse senetlerinden oluşan bir portföyden elde edilecek karı maksimize edecek portföy içindeki hisse senedi oranlarını hesaplayabiliriz. Diyelim ki, 5 milyar değerinde bir hisse senedi portföyü oluşturulmak isteniyor. Portföye alınmasına karar verilen hisse senetlerine ait bilgiler bir tablo halinde düzenliyoruz. Belirlenen hisse senetlerinin yıllık getirilerine ve sizin belirleyeceğiniz bazı kriterlere göre bu hisse senetlerinin portföy içindeki oranlarını hesaplayacağımızı varsayalım. Böylece, bulunacak portföydeki hisse senedi oranları ile portföyden maksimum getiri elde edebiliriz.
Excel’de Tools (Araçlar) menüsünde Solver (Çözücü) komutu görünmüyorsa, Tools*Add-Ins (Araçlar*İçine-Ekle) komutunu çalıştırın. Ekrana gelen Add-Ins (İçine-Ekle) diyalog kutusunda Solver Add-In (Çözücü İçine Ekle’si) seçeneğinin onay kutusunı işaretleyip ardından OK (Tamam) düğmesine tıklayın. Böylece Tools (Araçlar) menüsünde Solver (Çözücü) komutunu görebileceksiniz.
Hisse portföyüne toplam 5 milyar TL yatırım yapacağımızı belirtmiştik. Belirlediğimiz kriterler de şöyle olsun: Bu paranın 1.5 milyarı ile Elektrik sektöründen T hisselerinden satın alacağız. Bankacılık sektörüne yapacağımız yatırım Gıda sektörüne yapacağımız yatırım miktarını aşmayacaktır. Belirlenen hisselerden en az 100 milyon TL’lık alınıp portföye konulacaktır.
Bu koşullar altında 5 milyar tutarındaki hisse senedi portföyünün getirisini maksimize edecek hisse senedi yatırım tutarları Excel ile kolaylıkla bulunabilir. Bu yatırım kararının amacını ve yatırım kararını etkileyecek olan koşulları, Excel programına tanımlamadan önce denklemler halinde ifade etmek, yatırımı matematik olarak daha anlaşılır olmasını sağlayacaktır. Portföy yatırımının amacı hisse senetlerinden elde edilecek getiriyi maksimize etmektir.
Amaç fonksiyonu:
0.90A + 0.85D + 0.90P + 1.30T + 1.15V + 0.95Y = Zmax
Koşullar
( 1 ) Ş A + D + P + T + V + Y = 5 milyar
( 2 ) Ş T = 1.5 milyar
( 3 ) Ş A + D - P < = 0 veya A + D < = P
( 4 ) Ş A >= 100 milyon
( 5 ) Ş D >= 100 milyon
( 6 ) Ş P >= 100 milyon
( 7 ) Ş T >= 100 milyon
( 8 ) Ş V >= 100 milyon
( 9 ) Ş Y >= 100 milyon
Burada, (4), (5), (6), (7), (8), (9) no’lu koşullar ile portföyde olması istenen hisse senedi miktarlarının 100 milyona eşit veya büyük olması isteniyor. Böylece hisse senetlerini ifade eden A, D, P, T, V, Y değişkenlerinin hem negatif değer alması önlenmiş olur, hem de hisse senetlerinin getirilerinin maksimizasyonu sonucunda portföye giremeyen favori hisse senetlerindeki artıştan az da olsa yararlanılmış olunur. Kısacası, portföye giremeyen hisselerin artışından yararlanmak için, favori olarak belirlediğiniz bu 6 hisseden en azından 100 milyon tutarında alıp portföyünüze koymuş olursunuz.
Belirlediğiniz hisse senetlerine göre getirinizi (karınızı) maksimize edecek miktarları Excel’de bulabilmek için portföy hazırlamadaki amacımızı ve portföyün bileşimini etkileyecek koşulları programa tanımlamak gerekir. Bunun için önce B3:B8 alanında görüldüğü gibi hisse senetlerinin isimleri Excel’de çalışma sayfasına yazın. Değerleri hesaplanacak olan hisse senedi tutarlarının yazılacağı alanı (C3:C8) belirleyin. Bu alandaki hücre değerlerine göre portföyün amaç denklemini hesaplayın.
Değeri, Excel’in Solver özelliği ile maksimize edilecek amaç denklemi boş bir hücreye (D3) yazın. Seçilen portföy için getiri maksimizasyonundan sonra bu hücrede portföydeki seçilen hisse senetlerinin tutarlarına göre portföyün ulaşacağı maksimum değeri hesaplayın. Hisse senedi portföyünün amacını etkileyecek koşulları denklemler halinde E3:E11 alanına yazın.
Portföydeki hisse senedi tutarlarının hesaplanacağı çalışma sayfasındaki alana (C3:C8) karar verildikten sonra çalışma sayfasında, portföy için belirlenmiş amaç denklemi (D3) ve koşulları (E3:E11) alanına yazılmıştır.
Portföydeki hisse senedi tutarlarının ve bu değerlere bağlı olarak maksimum portföy değerinin hesaplanması için Excel’de Tools*Solver (Araçlar*Çözücü) komutunu çalıştırın. Ekrana gelen Solver Parameters (Çözücü Parametreleri) diyalog kutusunda gerekli tanımlamaları yapın. Öncelikle Set Target Cell (Hedef Hücre) isimli metin kutusuna, amaç denkleminin bulunduğu hücrenin adresini (D3) yazın. Amaç denklemi maksimize edilecekse, diğer bir deyişle, koşulları sağlayacak amaç denklemindeki değişkenlerin alabileceği en yüksek değerler aranıyorsa, Max (En Büyük) seçeneğini işaretleyin. Ardından aynı diyalog kutusunda By Changing Cells (Değişen Hücreler) isimli metin kutusuna, değerleri Çözücü hesaplamaları ile değiştirilecek hücrelerin adreslerini (C3:C8) girin. Hisse senedi tutarını gösteren bu değerler başlangıçta boş bırakılmıştır. Çözücü ile bu değerler, belirlenen tüm koşulları sağlayacak ve amacımızı maksimum edecek değerler olarak bulunur.
Koşullar, Subject to the Constraints (Kısıtlama Etkisi) isimli kısma girilmelidir. Bunun için Add (Ekle) düğmesine tıklayın. Bir maksimizasyon veya bir minimizasyon - kısaca bir optimizasyon - probleminde en çok 100 adet koşul, diğer bir adıyla kısıt, tanımlanabilmektedir. Portföy getirisinin maksimizasyon değerini etkileyecek olan koşullar, Add (Ekle) düğmesi tıklandığında ekrana gelecek olan Add Constraint (Kısıtlama Ekle) diyalog kutusunda tanımlanır. Hisse senedi portföyü için yapılacak olan yatırım tutarının değeri 5 milyar olarak belirlenmiştir. C9 hücresinde, portföydeki hisse senedi tutarlarının toplamı hesaplanmaktadır ve bu değerin 5 milyara eşit olması istenmektedir.
(2). Koşullun tanımlanması için (1). koşul girildikten sonra Add (Ekle) düğmesine tıklayın. (2) ile T hissesinden kesinlikle 1,5 milyar tutarında alınıp, portföye konması belirlenmektedir. (3). Koşul için yine Add (Ekle) düğmesi tıklanmalıdır.
Ekrana tekrar boş bir Add Constraint (Kısıtlama Ekle) diyalog kutusu gelecektir. Burada (3). Koşul olarak Bankacılık sektörüne yapılan yatırımın, Gıda sektörüne yapılacak yatırım miktarını aşmayacağı tanımlanır. E5 hücresinde Bankacılık sektörüne ait A ve D hisselerinin portföydeki tutarları toplanmaktadır. Add Constraint (Kısıtlama Ekle) diyalog kutusunun Cell Reference (Hücre Başvurusu) kısmına, A ve D hisse tutarlarının toplamı =C3+C4 olarak yazıldığında, Excel bu tanımlamayı bir koşul olarak kabul etmektedir. Bu nedenle (3). koşulun tanımlanması için E5 yardımcı hücresi kullanılmıştır. Burada (3). koşul için gerekli tanımlamalar yapıldıktan sonra diğer koşulları tanımlamak için Add (Ekle) düğmesi tıklanır.
Belirlenen hisselerden en az 100 milyon TL’lık alınıp portföye konulacağını ifade eden (4), (5), (6), (7), (8), (9) koşulları bir koşul ekleme diyalog kutusunda bir kerede tanımlanır. (4)-(9) koşullarında bulunan hisse senedi tutarlarının bulunacağı alan C3:C8 olarak Cell Reference (Hücre Başvurusu) kısmında tanımlanır.
Bu girdilerden sonra, koşullar tanımlanmış olarak tekrar Solver Parameters (Çözücü Parametreleri) diyalog kutusuna geri dönün. Portföyün getiri maksimizasyonu için gerekli tanımlamaları artık yaptınız. C3:C8 alanında portföy hisselerinin değerlerini ve maksimizasyondan sonra D3 hücresinde portföyün değerini görmek için Solve (Çöz) düğmesine tıklayıp optimizasyonu başlatın. Koşullara ilişkin değişiklik yapacaksanız Change (Değiştir) düğmesine tıklamalısınız. Tanımlanmış olan koşulları ve tanımlanmış diğer adresleri iptal etmek istiyorsanız, Reset All (Tümünü Sıfırla) düğmesine tıklayın. Portföyün D3 hücresinde hesaplanan değerini sabit değere göre optimize edecekseniz, Value of (Değer) bölmesine belirlediğiniz değeri yazmanız gerekir. Hesaplatma işlemini başlatmak için Solve (Çöz) düğmesine tıklayın.
Çalışma sayfasında, sonuçların ilgili hücrelerde görüntülenmesi için çeşitli seçeneklerin bulunduğu Solver Results (Çözücü Sonuçları) diyalog kutusu ekrana gelir. Buradan Keep Solver Solution (Çözümü Alıkoy) seçeneği tıklanırsa, ilgili hücrelerde bulunan sonuçlar muhafaza edilerek çalışma sayfası görüntülenir.
Solver Results (Çözücü Sonuçları) diyalog kutusunda maksimizasyon sonuçlarını (Answer/Yanıt), duyarlılığını (Sensitivity), sınırlarını (Limits) rapor halinde görüntüleyecek seçenekler bulunmaktadır. Aynı zamanda, bu diyalog kutusundaki Save Scenario (Kaydet) düğmesine tıklayarak belirlenen getiri değerleri için bulunan portföy maksimizasyon değerlerini bir senaryo çalışması olarak saklayabilirsiniz. Solver Results diyalog kutusundan kurtulup çalışma sayfasına dönmek için OK düğmesine tıklayabilirsiniz.
Ekrana gelen portföy maksimizasyonu sonuçlarına göre A hissesinden 100 milyon, D hissesinden 100 milyon, P hissesinden 200 milyon, T hissesinden 1.5 milyar, V hissesinden 3 milyar, Y hissesinden 100 milyon tutarında alınarak 5 milyar değerinde portföy yatırımı yapılmıştır. Hisse senetlerinden elde edilecek getiri oranları da dikkate alındığında başlangıçta 5 milyar olan portföy değeri bir yıl sonunda 5. 850.000.000 değerine ulaşacak ve sahibine 850.000.000 kazandıracaktır. Hisse senetleri ve onların getiri oranları değiştikçe yıl sonunda portföyden elde edilecek getiri miktarı da değişecektir.
Sharpe Tek İndeks Modeli İle Portföy Seçimi
Finans alanında kısaca Sharpe modeli olarak bilinen portföy seçim modelinin amacı, verilen bir risk düzeyinde en iyi getiriyi sağlayan menkul kıymet oranlarını bulmaktır. Sharpe modelinin esası, hisse senetleri getirileri arasındaki doğrusal ilişkiyi ortadan kaldırıp, bu ilişkiyi sadece pazar indeksi getirisine, diğer bir deyişle borsa indeksindeki değişmeye bağımlı hale getirmesidir.
Sharpe portföy seçim modelinin uygulandığı bir örnek çalışma Excel ile birlikte hazır olarak verilmiştir. Etkin hisse senedi portföyünün seçildiği çalışma, İngilizce Excel 95’de SOLVSAMP.XLS isimli dosyada bulunmaktadır. Bu dosyayı C:\EXCEL\LIBRARY\SOLVER dizininde yada C:\EXCEL\EXAMPLES\SOLVER dizininde bulabilirsiniz. Bu dosyayı açıp Insurance Portfolio (Sigorta Portföyleri) isimli çalışma sayfasına giderseniz, ekrana Efficient Stock Portfolio (Etkin Stok Portfolio) isimli bir tablo gelir.
A6:G18 alanında bulunan etkin portföy bileşimini hesaplayan tablonun, İngilizce Excel kullananlar tarafından anlaşılması için bu alandaki İngilizce terimler Türkçeleştirilmiştir. Tools*Solver (Araçlar*Çözücü) komutu ile E10:E14 alanındaki hisse senetleri oranları en fazla getiriyi sağlayacak şekilde diğer bir deyişle etkin portföy oluşturacak şekilde hesaplanır.
Portföy hisse senetleri ve hazine bonosunda oluşmaktadır. Başlangıçta bu menkul değerlerin portföye iştirak oranları eşit alınmıştır. E10:E14 alanındaki değerler bize portföyün bileşiminin Hisse A 0.20, Hisse B 0.20, Hisse C 0.20, Hisse D 0.20, Hazine Bonosu 0.20 oranlarında olduğunu göstermektedir. Bu oranlara göre E18 hücresinde hesaplanan portföy getirisi maksimum getiri değildir. Ancak bu beş menkul değerin portföyde eşit oranlarda (0.20) bulunduğu zaman G18 hücresinde hesaplanan portföy varyansı, diğer bir deyişle riski, minimum alınmış ve bir koşul olarak belirlenmiştir. G18 hücresindeki varyans değeri, portföyün sistematik ve sistematik olamayan riskini ifade etmektedir.
Bu tablodaki diğer finansal terimlerin kısaca açıklanması Solver ile yapılan portföy getirisi maksimizasyonunun anlaşılmasını kolaylaştıracaktır. Risksiz faiz oranı, yıllık banka faiz oranı olarak alınabilir. A6:G18 alanındaki değerler güncel değerler değildir, bu tablodaki değerler SOLVSAMP.XLS dosyasındaki çalışmada kullanılan orijinal değerlerdir. Pazar İndeksinin Getirisi, belli bir zaman aralığında borsa indeksindeki değişme oranı olarak tanımlanmaktadır. Varyans terimi istatistikten hatırlanacağı gibi bir değişkenin değerlerinin ortalama değerden sapmalarının karesi olarak bilinmektedir. Beta katsayısı, menkul değerin piyasa ile olan ilişkisini ifade etmektedir. Bu tanımlamalara göre A6:G18 alanında görüntülenen formüllerin ne amaçla kullanıldığı anlaşılabilir.
Solver (Çözücü) özelliğinin uygulanacağı tablo formül ve düzenleme açısından kullanıma hazır hale getirilmiştir.
Belirlenen koşullar altında, E18 hücresini, E10:E14 alanındaki değerlere göre maksimize etmek için Tools*Solver (Araçlar*Çözücü) komutu tıklanır. Ekrana gelen Solver Parameters (Çözücü Parametreleri) diyalog kutusunda gerekli tanımlamalar yapılır ve koşullar eklenir
Bu tabloda üç tane koşul tanımlanmıştır. Bu koşullardan birincisi E10:E14 alanında bulunan portföy oranlarının sıfırdan büyük olması gerektiğini gösterir. İkinci koşul, E16 hücresindeki değerin 1 sayısına eşit olacağını gösterir. E16 hücresinde portföydeki menkul kıymet oranlarının toplamı 1 olmalıdır. Üçüncü koşul ile, portföyün toplam riskini gösteren G18 hücresinin değeri %7.1’e eşit ve küçük olacağı tanımlanır. Gerekli tanımlamalar yapıldıktan sonra Solve (Çöz) düğmesi fare ile tıklanır. Çalışma sayfasında E10:E14 alanında etkin portföy oranları ve E18’de yıllık maksimum portföy getirisi hesaplanır.
Tanımlanan koşullar altında en iyi getiriyi sağlayan portföy bileşim oranları E10:E14 alanında yer almaktadır. Bu oranlara göre % 17.1 oranında maksimum getiri elde edilmektedir.
|