XIRR, демистифицирана с изходен код на C#

Нека започнем с въпрос: имаш ли инвестиция, която:

• Има плаваща възвръщаемост?
• Има нерегулярен интервал или сума за инвестиране?
• Твоите тегления са също нерегулярни като време и сума?

Ако имаш поне един отговор „Да“ и ако не знаеш как да измериш твоята възвръщаемост, тогава тази статия е за теб.  Ще обясня как да изчислиш възвръщаемостта на една инвестиция, където нищо не е регулярно – като например акции, злато или peer-2-peer инвестиции – използвайки обикновена екселска формула.

Както най-вероятно се досещаш от заглавието на статията, магическата екселска формула е XIRR. Ето какво казва Microsoft Office документацията за нея: „Връща вътрешна норма на печалба за разписание на парични потоци, които не са непременно периодични.“ Като входящи данни формулата получава два списъка: един с дати и друг със съответстващи суми. Същата функционалност съществува в други популярни електронни таблици като – Google Docs, Open Office.

Ако си запознат с XIRR и проблемите, които решава, можеш да прескочиш директно към втората част на статията. Там ще представя C# модул, който изпълнява същата формула (изходният код е в Github).

Финансов преглед

Първо, ще разгледаме най-простия възможен пример. Представи си, че имаш фиксиран срочен депозит с фиксиран годишен лихвен процент 1%. Ако инвестираш 1000 лв., ще получиш 1010 лв. след една година. Тук интуитивният отговор е, че възвръщаемостта е 1%, и той е правилен. Ако сложим тези числа в Excel, ще получим същия резултат. Забележи, че когато инвестираш, знакът на сумата е отрицателен.

Какво ще се случи обаче, ако усложним примера с депозита и след 6 месеца изтеглим половината сума (предполагайки, че банката няма да ни начисли такса за нарушаване на договора)? В този случай ще получим лихва 1% за цялата сума за 6 месеца (0.5 години), т.е.

и 1% лихва за 500 лв. (половината сума) за 6 месеца (0.5 години), т.е.

или да обобщим

Когато вкарваме стойностите в Excel, виждаме, че резултатът е почти същият, но има малка разлика след 5-ия знак.

Няма да те занимавам с тънкостите на различните бази за изчисляване на лихва (360/365 дни за година, 30 дни на месец/реален брой дни), които са още една причина за несъответствието в последния пример. С него просто искам да отбележа, че дори простата нерегулярност засяга възвръщаемостта.

Сега нека да продължим с нещо по-реално. Представи си , че инвестираш в акции от някаква компания. Нека предположим, че в началото на всеки месец купуваш акции за 500 лв. в продължение на една година (за 12 месеца инвестираш 6000 лв.). Промяната в цената на акциите е показана в следната таблица.

Броят акции, които купуваш с твоите инвестициите си, са показани по-долу.

Което означава, че след една година инвестиции (1.1. 2018 г.) ти притежаваш 81.81344 акции с цена 80 лв. всяка. Така, ако ги продадеш, ще вземеш:

81.81344 * 80 лв. = 6545.08 лв.

В края на инвестирането имаш сума, която е 9.08% по-голяма от инвестираната. Но това ли е реалната възвръщаемост? Разбира се, че не. Не цялата сума е била инвестирана за целия период. Отново, начинът да се калкулира възвръщаемостта е XIRR.  

И реалната възвръщаемост е 17.12%. Много по-добре, нали?

Ако продадеш някои акции през годината, просто трябва да добавиш ред в горната таблица с точната дата и сума (с положителен знак). И това е всичко.

В заключение, ако имаш инвестиция с нерегулярни парични потоци, XIRR е най-добрият ти приятел. Използвайки я, може да сравниш доходността от всяка инвестиция и да реагираш подобаващо.

Как да изчислим XIRR сами?

Може да се чудиш защо някой ще иска да възпроизведе Excel функционалност? И отговорът е прост – ако разработваш софтуер, който трябва да изчислява XIRR, нямаш друг избор. Не може просто да вмъкнеш Excel в приложението си. За да съм честен, няма техническо ограничение, за да го направиш, но трябва да платиш цената. И цената не е само финансова. Excel не е направен за паралелна работа – затова, ако планираш да направиш някои изчисления с него на Web сървър, може да срещнеш сериозни проблеми с производителността.

Обратно към темата, за да изчислиш XIRR, трябва да направиш едно нещо – да решиш това уравнение:

където:

• d_i = i-тата или последната дата на плащане
• d₁ = 0-та (нулевата) дата на плащане
• P_i = i-тото или последното плащане

Има много алгоритми да се реши такъв тип уравнение, но аз ще обясня този, чиито резултати най-много се доближават до тези в Excel. Нарича се метод на бисекцията. Идеята е проста. Започваме с минималните и максималните възможни стойности и точността, която искаме. Не можеш да изгубиш повече от 100% от инвестициите ти, така че минималната стойност е -100%. Има малък технически детайл – уравнението по-горе не може да бъде решено, ако процентът е -1(-100%), заради което минималната стойност трябва да е малко по-голяма. Максималната стойност е определена според бизнес случая – освен ако не инвестираш в някоя схема на Понци, възвръщаемостта не би могла да надвишава 100%, но за да бъдем сигурни, можем да започнем с 1000% като максимална стойност.

Първо трябва да провериш дали има положителен и отрицателни парични потоци. Без положителни парични потоци означава, че загубата е 100% – инвестиция без възвръщаемост – което е граничният случай на процент = -1. От друга страна, имайки само положителни парични потоци, означава безкрайна възвръщаемост – получаваш нещо, без да инвестираш в него. Тогава стъпките са:

1. Решаваш уравнението по-горе за минимална и максимална стойност. Ако знаците на резултатите са различни, има решение, което е между тях.
2. След това изчисляваш уравнението със стойност, която е средната от минималната и максималната – нека да я наречем V1.
3. Ако знакът на резултата е равен на минималната стойност, я заместваш с V1. Ако знакът на V1 е равен на максималната сума, то той става новата максимална сума.
4. Ако разликата между нова минимална и максимална стойност е по-малка или равна на необходимата точност, резултатът е тяхната средна стойност. В противен случай започни със стъпка 1, използвайки нови минимални и максимални стойности.

Както се казва, една картина струва повече от хиляда думи. В следващата снимка можеш да видиш същия алгоритъм, описан като блокова схема.

На графиката долу може да видиш визуално как работи методът.

Ако не използваш метода на бисекцията, тогава просто повтори от минимална до максимална стойност, увеличавайки минималната с точността, и наблюдавай кога знакът ще се промени. Както може да очакваш, този подход може да доведе до голям брой цикли, или, за да бъда по-точен, в най-лошия случай ще бъде:

Което, ако искаш точност до 0.0001, се свежда до

Сравнително много, очевидно. С метода на бисекцията намаляваш максималния брой цикли до двоичния логаритъм на по-горния резултат:

Или в цифри

Много по-добре, не мислиш ли?

И сега C# реализацията

Нашата крайна цел е да създадем метод, който получава парични потоци и евентуално точност и максимален възможен процент и връща изчислен XIRR, ако е възможно. Той изглежда така:

public static double XIRR(List<CashFlowDates> cashflows, int decimals = 4, double maxRate = 1000000)

Типът CashFlowDates е просто носител на данни:

public double Amount { get; set; }

public double Years { get; set; }

За да се ускори процесът, този тип е преизчислен вътрешно до CashFlowFractionOfYear, който вместо DateTime, го съхранява като коефициент, изчислен като времево разстояние между първата позиция от паричния поток в списъка и датата в самата позиция в години. Всъщност прави следното изчисление от формулата XIRR по-горе:

И структурата е:

public double Amount { get; set; }

public double Years { get; set; }

Методът, който конвертира CashFlowDates в CashFlowFractionOfYear, е този:

private static List<CashFlowFractionOfYear> ToFractionOfYears(List<CashFlowDates> cashflows)

        {

            var firstDate = cashflows.Min(x => x.Date);

            return cashflows

                .Select(x => new CashFlowFractionOfYear(x.Amount, ((double)x.Date.Subtract(firstDate).Days) / 365))

                .ToList();

        }

След това имаме метод, който изчислява уравнението, което трябва да бъде решено за даден процент.

private double CalcEquation(List<CashFlowFractionOfYear> cashflows, double interestRate)

        {

            return cashflows.Select(x => (x.Amount / (Math.Pow((1 + interestRate), x.Years)))).Sum(x => x);

        }

След това се намира коренът на уравнението, използвайки рекурсия:

public double Calculate(double precision, int decimals)

        {

            if (Math.Sign(LowResult) == Math.Sign(HighResult))

            {

                throw new Exception("Value cannot be calculated");

            }

 

            var middleRate = (LowRate + HighRate) / 2;

            var middleResult = CalcEquation(CashFlow, middleRate);

            if (Math.Sign(middleResult) == Math.Sign(LowResult))

            {

                LowRate = middleRate;

                LowResult = middleResult;

            }

            else

            {

                HighRate = middleRate;

                HighResult = middleResult;

            }

            if (Math.Abs(middleResult) > precision)

            {

                return Calculate(precision, decimals);

            }

            else

            {

                return Math.Round((HighRate + LowRate) / 2, decimals);

            }

 

        }

Сега, когато имаме всички съставки, можем да съберем всичко в една функция: 

public static double XIRR(List<CashFlowDates> cashflows, int decimals = 4, double maxRate = 1000000)

        {

            if (cashflows.Where(x=> x.Amount > 0).Count() == 0)

            {

                throw new IncosistentCashFlowException();

            }

            if (cashflows.Where(x => x.Amount < 0).Count() == 0)

            {

                throw new IncosistentCashFlowException();

            }

 

            var precision = Math.Pow(10, -decimals);

            var minRate = -(1 - precision);

            return (new XIRRCalculator(minRate, maxRate, cashflows).Calculate(precision, decimals));

        }

Тук приемаме, че точността е функция на десетичните числа – ако искаш, 4 знака, тогава точността е 0.0001. Има предпазно условие, за да се избегнат изчисленията, ако всички парични потоци са с един и същ знак. Моля, имай предвид отново, че първоначалната минимална стойност не е точно -1 – и причината е много проста – ако заместиш „Rate“ с -1 в уравнението XIRR, ще има грешка за делене на нула.

В изходния код можете да намерите Unit тестове, обхващащи описаните в първата част случаи.

Тук е моментът да направя уточнението – има малка разлика в резултатите в сравнение с Excel – обикновено след 6-тия десетичен знак.

Както казах по-рано, изходният код на цялото решение се намира в GitHub под MIT лиценз. Чувствайте се свободни да го използвате и подобрявате както намерите за добре.