Optimalizace investičního portfolia v Excelu

Příklad

V jednom z minulých dílů jsme si popsali, jak funguje Řešitel / Solver.
Pojďme si jej dnes procvičit na praktičtějším příkladu. V tomto článku není vysvětlené všechno - takže kdyby něco nebylo jasné, můžete si projít původní vysvětlení.
Představte si, že máte určitou částku peněz, a tu chcete investovat tak, aby to pro vás bylo co nejvýhodnější. To znamená, že chcete vydělat co nejvíce peněz, na druhou stranu nechcete, aby investování bylo příliš rizikové.
V mém případě tedy chci maximalizovat celkový výnos při zadaném limitu rizikovosti
Mám k dispozici tyto investice, ze kterých si mohu vybrat libovolné množství - třeba i všechny nebo žádnou.

Řekněme, že mám k dispozici jeden milion a že nechci nést celkové riziko větší než 5%.
Dopočítám si do tabulky vážené výnosy a vážené riziko - tedy výnosnost a ztrátu násobenou jejich
pravděpodobností.

Řešení

V tabulce teď jsou žluté buňky, které vyplňuje uživatel, a ostatní buňky, které se počítají samy.
Ve sloupci "Zařazeno v portfoliu" budou jedničky u všech položek, které budou zařazeny do portfolia. Zatím jsou jedničky u všech - ale já chci, aby Řešitel dal nuly k těm, které do portfolia zařazené nebudou.
V horní části tabulky jsou zatím buňky vyplněné tak, jako bych investoval do všeho. To ale nemohu, protože nemám dost peněz. Po skončení řešitele už se do své částky vejdu a současně bude pořád splněná podmínka s minimální výnosností.

Teď mohu spustit Řešitele, a ten bude vypadat takto:

Všimněte si, že:

• Maximalizuji hodnotu buňky B6 - to je buňka, kde je můj celkový výnos
• Měním buňky B9 až B22 - resp. nechám Excel, aby tyto buňky měnil
• První podmínka říká, že to, kolik investuji, nesmí být výše, než to, kolik jsem se rozhodl investovat
• Druhá podmínka říká, že celková míra rizika nesmí být větší než určitá mez
• Třetí podmínka říká, že ve sloupečku "Zařazeno v portfoliu" nesmí být nic jiného než jedničky a nuly

Metodu řešení jsem vybral Evolutionary - je nejuniverzálnější (i když výpočet trvá déle) a nemělo by se stát, že Excel najde špatné řešení (někdy totiž najde lokální, ale ne absolutní maxima výnosové/nákladové funkce).
A teď už dám pouze Řešit a čekám na výsledek...

Vidím, že ve finální tabulce jsou (s jedničkou) vybrané položky, které mám do portfolia zařadit.
Vidím, že z jednoho milionu mohu při limitu rizikovosti 5% vydělat maximálně 87 854 Kč. Mohu libovolně měnit všechny parametry modelu (žluté buňky) a dalším přepočtem počítat nové varianty investice
Ke stažení:
https://www.dropbox.com/s/ofk7qn0lc90fhs9/resitel_optimalizace_portfolia.xlsx