Форма входа

Корзина

Ваша корзина пуста

Поиск

Календарь

«  Январь 2017  »
ПнВтСрЧтПтСбВс
      1
2345678
9101112131415
16171819202122
23242526272829
3031

Наш опрос

Оцените мой сайт
Всего ответов: 97

Статистика


Онлайн всего: 1
Гостей: 1
Пользователей: 0




Пятница, 26.04.2019, 11:23
Приветствую Вас Гость | RSS
Фондовый рынок
Главная | Регистрация | Вход
Блог


Главная » 2017 » Январь » 9 » Разбираем функцию EXEL «ЧИСТВНДОХ».
20:38
Разбираем функцию EXEL «ЧИСТВНДОХ».

Измерение доходности неравномерно поступающих платежей, как и способы их расчёта,  вызывают сложности. Поэтому  имеет смысл подробно разобрать и размять эту тему.

Мне это будет полезно, так как способность что-либо просто  описать и объяснить,   является пониманием предмета.

А тем, кто удосужится прочитать и разобраться, поможет начать использовать столь удобный инструмент.

Сначала об истории вопроса.

Понятие деньги,  исторически, претерпевало изменение и усложнение, что   создаёт проблему восприятия. Ещё пару сотен лет назад это было некое количество единиц, допустим монет, привязанных к объёму золота.

Но подобная денежная система оказалась нежизнеспособной. Деньги исчезали из оборота и постоянно дорожали. Их начинали накапливать. Значительная часть истории человечества это грабежи  и битвы  за кусочки металла.

Чтобы  решить эту проблему и были созданы кредитные деньги и   банковская система.

Кредитные деньги не привязаны к каким–либо материальным объектам,

если они исчезают из оборота и дорожают, то их просто подпечатывают, до создания некого равновесия в денежной системе,  за которой следит регулятор.

Т.о., деньги стали увеличиваться в числе и уменьшаться в стоимости, распадаться во времени.

Следовательно, и измеряться они стали не через количество единиц, а через скорость их изменения во времени, или через время.

На  сегодня, мы измеряем деньги в виде процента их приращения или через время их полураспада. Например.

Ставка в банке 10% за год. Это значит, что стоимость  денег равна приращению на 10% за год к первоначальной сумме. 100р +10р = 110р.

Но можно это измерить и через время их удвоения. 100р  /  10р = 10 лет.

Это означает, что при простой доходности в 10% через 10 лет у нас станет 200р.

На этом проблемы не заканчиваются. Потому, что доходность может быть не только простой, но и сложной.

Мы можем постоянно реинвестировать получаемые 10р, увеличивая сумму вложений.

При таком расчёте доходности   время полураспада или удвоения  превращается в логарифм.    LOG  (2) по основанию (1,1)  = 7,27 лет. Это = 100р * (1,1)^7,27 =200р.

Естественно, всё это влечёт сложности подсчёта и восприятия.

Особенно эти сложности очевидны, когда у нас неравномерный, по срокам и количеству, поток входящих и исходящих платежей.

Например.

Срок инвестирования 300 дней.

Входящая сумма = 100р.

Через 100 дней мы забираем 30р.

Ещё через 100 дней мы вкладываем  10р.

Через 300 дней у нас на счёте 120р.

Какая у нас доходность?

Многие, конечно, не заморачиваются  на эту тему, но тогда непонятно, как сравнивать между собой инвестиционные инструменты, и их результаты, и как принимать решения.

 Как же решают эту проблему? По-разному. Очень многие  по старинке, через средневзвешенную сумму на счёте.

Возвращаемся к примеру.

Доход в рублях =  Исходящая (результирующая) сумма +вывод средств – ввод средств - входящая сумма

= 120р+30р-10р-100р=40р.

Но к какой сумме его относить?

100 дней было 100р.

Ещё 100дней было 100р-30р=70р.

А ещё 100дней было 70р+10р=80р.

Вычисляют средневзвешенную сумму, через геометрические представления,

Рис.1.

по следующей формуле.

100р *100дн +70р *100дн + 80р *100дн = «Х»р * 300дн.,

далее    это преобразуется,

100р *100дн / 300дн +70р *100дн / 300дн +80р *100дн / 300дн = «Х»р

Дни делятся на дни, по правилам физики, и в левой части остаются рубли, которые можно сложить и получить, «Х»р, или средневзвешенную сумму.

«Х»р = 83,33 р.

Осталось отнести наш доход 40р к 83,33р =  40р / 83,33р  = 48 % .

Но это ещё не всё.  Это доход за  300 дней. А нам надо за 365дн.

=> 40р / 83,33р / 300дней *365дней = 58,4 %/год.

Видим, сколько геморроя по достаточно простой операции.

Хотя в exel эту процедуру несложно организовать.

Теперь, как этот процесс подсчёта доходности реализован в exel через функцию ЧИСТВНДОХ.

В данном случае используется сложная доходность.

Чтобы это было понятно, надо разобрать, подробно и наглядно, что такое сложная и простая доходность.

Начнём с простой.

У нас есть 1 р.

Доходность это изменение, и она равна, допустим, 10% или 1/10.

=> через единицу измерения времени = год, мы получим  ещё = 1/10р.  

Ещё через год опять  = 1/10р и т.д.

Таким образом, сумма наших денег будет прирастать линейно:

=1+1/10+1/10+1/10+1/10

Рис.2.

И за 10 лет превратится в 2р, удвоившись.

Теперь сложная доходность.

У нас есть 1 р.

Через единицу измерения времени = год, мы получим  ещё = 1/10р.  

Далее мы не забираем эти деньги, а вкладываем их вновь.

=1р+1/10.

И, последующие 10%, уже идут на новую сумму.

=(1р+1/10)* 1/10 и т.д.

Как будет выглядеть изменение наших денег во времени?

  1. 1р+1/10
  2. (1р+1/10)+ (1р+1/10)* 1/10= (1р+1/10) (1+1/10)= (1р+1/10)^2
  3. (1р+1/10)^2 + (1р+1/10)^2*1/10 = (1р+1/10)^2 * (1р+1/10) = (1р+1/10)^3

И т.д.

Т.о., мы получили геометрическую прогрессию в виде степенной функции.

Рис.3.

На рис.3 видим, как отличаются по цифрам простая и сложная доходность при той же процентной ставке.

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

LOG  (2) по основанию ( 1+1/10) = 7,27 лет.

Деньги стали логарифмическим временем.

Переходим к тому, зачем это нужно, или к методу работы формулы exel.

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

Например, взять за начало отсчёта  год0, как на рис.3.

Обозначим сумму денег в год0  = S0

Тогда сумма денег в год1 =  S1, и её можно выразить через S0.

S1 = S0 * (1р+1/10)

S2 = S0 * (1р+1/10) ^ 2

S3 = S0 * (1р+1/10) ^ 3

Или в общем виде

S(n) = S0 * (1р+1/X) ^ N

Но можно поступить и так:

1. S0 = S1 / (1р+1/10) ^ 1

2. S0 = S2 / (1р+1/10) ^ 2

3. S0 = S2 / (1р+1/10) ^ 3

S0 = S1 / (1р+1/10) ^ 1 = S2 / (1р+1/10) ^ 2 = S3 / (1р+1/10) ^ 3

Или  в общем виде, 

 S0 = S (n) / (1+1/X) ^ N

Что мы получили в последнем случае?

Одни и те же деньги выраженные через время в точке начала отсчёта = г0.

S (n) -  это сумма денег в рублях.

(1р+1/X) ^ N – это коэффициент геометрической прогрессии, содержащий внутри себя искомую доходность.  

Как этим можно воспользоваться и что делает функция EXEL «ЧИСТВНДОХ»?

Функция приводит все деньги к одному времени и приравнивает входящие и исходящие платежи друг к другу, считая, что это одни и те же деньги.

А чтобы это равенство выполнялось, подбирает соответствующую доходность методом «научного тыка» или обычным перебором. 

Переходим, к примеру, данному выше.

Разделяем  наши  деньги на входящие и исходящие.

Сначала входящие:

1. Входящая сумма =100 р. Время = 0.

Поэтому,  S0(входящие1) =  100 / ( 1+1/ Х ) ^ 0,   => S0 = 100 / 1 = 100р.

2. Входящая сумма =10р. Время = 200дн. Мы через 200 дней внесли сумму в 10р.

Т.к. за единицу измерения принимается 1 год = 365дней, то наша степень «N», будет выглядеть в виде дроби = 200 / 365.  

S0(входящие2)  =  10 / ( 1+1/ Х) ^ (200 / 365)

Теперь исходящие:

1. Исходящая сумма =30р. Время = 100дн.

S0(исходящие1)  =  30 / ( 1+1/ Х) ^ (100 / 365)

2. Исходящая сумма =120р. Время = 300дн.

S0(исходящие2)  =  120 / ( 1+1/ Х) ^ (300 / 365)

Далее, приравниваем входящие и исходящие суммы.

S0(входящие1) + S0(входящие2)  = S0(исходящие1)  + S0(исходящие2)

Это одни и те же деньги в точке  S0.

Записываем развёрнуто:

100 / 1 + 10 / (1+1/ Х) ^ (200 / 365) = 30 / ( 1+1/ Х) ^ (100 / 365) +120 / ( 1+1/ Х) ^ (300 / 365)

Неизвестное в этой формуле только «Х», который и является искомой доходностью.

Мы можем его подобрать, подставляя различные значения так, чтобы равенство было максимально близко к истине.

 Этим и занимается  EXEL, начиная с доходности 1/10 и далее 100 раз (итераций).

Только EXEL переносит всё в   правую или левую часть равенства, чтобы итог = 0.

100 / 1 + 10 / ( 1+1/ Х) ^ (200 / 365) - 30 / ( 1+1/ Х) ^ (100 / 365) -120 / ( 1+1/ Х) ^ (300 / 365) =0

Именно поэтому всё равно, под каким знаком будут входящие и исходящие платежи.

Их можно перенести справа налево, или слева направо,  знак значения не имеет.

Важно, чтобы они просто были разделены по знаку.

При этом проделать эту нехитрую операцию подбора искомой доходности можно и самостоятельно, вручную, без функции  «ЧИСТВНДОХ».

Например, ниже, я скопировал из EXEL разбираемый пример:

               
               

 

 

 

01.01.2016

-100

 

 

 

 

 

 

10.04.2016

30

 

 

 

 

 

 

19.07.2016

-10

 

 

 

 

 

 

27.10.2016

120

 

 

 

 

 

 

 

 

 

 

 

 

 

 

0,6044313

 

 

 

 

 

 

 

 

 

 

 

 

 

-100

30

-10

120

 

Сумма

 

 

1

2

3

4

5

6

 

 

-100

26,3573

-7,718968

81,38046

 

0,01879

 

 

 

 

 

 

 

 

 

 

 

 

0,604

 

 

 

 

               
               
               

 

Сверху стандартный подсчёт с помощью формулы.

А снизу я подгоняю всё это вручную. Записываю в каждой ячейке дисконтированный(приведённый) денежный поток, отнесённый к коэффициенту, внутри которого доходность «Х». Вместо «Х» ссылаюсь на  общую ячейку с цифрой подбираемой доходности. Нижнее число в последней табличке. Это число изменяю так, чтобы сумма всех ячеек в столбце 6 стремилась к нулю.

Как видим, результат почти точно такой же, как и при расчёте через функцию.

Заодно можно сравнить результат, с тем, который получился при расчёте через средневзвешенную сумму.

Итог отличается почти на 2%.

Надеюсь, что после того как я раздраконил эту непростую в понимании, но очень простую в применении формулу на съедобные части,  у всех хватит желания это осмыслить, осилить и в дальнейшем использовать.

Просмотров: 1499 | Добавил: Воронноров | Рейтинг: 5.0/4
Всего комментариев: 15
14 bobez82  
Добрый день Воронноров мне удалось решить мою проблему с ЧИСТВНДОХ (спасибо опытным людям). Возможно Вам будет интересно http://www.excelworld.ru/forum/2-34340-1

0
15 Воронноров  
Да, спасибо, я понял. Это очень важное замечание.На самом деле, математически задачу можно решить, только очень муторно и долго.
Действительно, вся проблема в том, что по умолчанию итерации начинаются с доходности 0,1.
Так как это очень далеко от реальной доходности, то функция начинает косячить.
Ей просто не добраться до реальных значений.
При этом она,видимо, не умеет перескакивать через 0.
 Поэтому, чтобы данные были максимально достоверны надо примерно знать знак результата и  просчитывать несколько раз,  подставляя в предполагаемую доходность прошлый результат.
Ещё раз спасибо за настойчивость в решении проблемы. Классно.

9 bobez82  
8 комент почему обрезался, начало утеряно. Вот начало. Добры день Воронноров, вычислите пожалуйста ряд значений из комента № 8 с помощью ЧИСТВНДОХ. У меня расчет выдает 2,98023E-09........

8 bobez82  
2,98023E-09, хоть ты тресни, уже и на других ПК пробовал и всё одно 2,98023E-09) 3 630,00 2 419,80 12 501,00 13 600,00 16 135,00 21 342,10 14 765,00 15 455,00 13 668,00 13 446,00 13 299,00 12 785,00 -130 832,70 11.02.2014 11.02.2014 24.03.2014 23.03.2015 20.04.2016 29.05.2016 25.01.2017 10.02.2017 22.02.2017 03.03.2017 07.03.2017 31.03.2017 07.06.2017

0
10 Воронноров  
Да, у меня то же выдаёт. Вообще, по идее, это убыток.

12 bobez82  
Да, это точно убыток. Формат менял, не помогает. Буду писать в тех. поддержку. Спасибо, что попробовали на Вашем экселе.

0
13 Воронноров  
Я поменял (поиграл) форматом последнего числа. Сделал процентным, а потом вернул обратно.
Вышло: -7,45058Е-10
Расчёт по формуле, вообще говоря, не 100%. В трудных случаях может лажать.

0
11 Воронноров  
Попытайтесь поменять формат ввода числа, например процентный.Видимо, после какого-то приближения эксель лажает. Вокруг этого числа
показывает,  похожее на правду.

4 osutkak  
Здравствуйте. Пожалуйста, помогите разобраться. Получается какая-то белиберда с расчетом доходности. Доходность ноль, а налог заплатил.
На 1 января 2016 г имею на счете 853608 руб. На самом деле 899113, но 16 января списали налог за предыдущий год в размере 45505, поэтому, чтобы не запутаться, сразу пишу цифру без налога. Составляю таблицу и используя функцию ЧИСТВНДОХ считаю доходность, получаю цифру близкую к нулю. Получается, я целый год мучался с этой биржей, а в итоге ноль? Или таблица составлена неправильно? Кроме того, не вошли в таблицу дивиденды и купоны (в мае 30928 и в июле 34393 - дивиденды, 577 купоны в декабре), не знаю как их учитывать. Они не поступали на счет, т.е. не работали, а сразу приходили на карту.

01.01.2016 853608 Сумма на начало года
13.01.2016 200000 пополнение
01.02.2016 -16017 вывод 13000+налог
12.02.2016 400000 пополнение
01.03.2016 -60000 вывод 50625 + налог
05.04.2016 -37900 вывод 30000 + налог
15.06.2016 400000 пополнение
16.06.2016 120000 пополнение
09.09.2016 -110150 вывод 100000 + налог
31.12.2016 1806413 Остаток на конец года
Доход     2,98E-09

0
5 Воронноров  
Да, у вас ошибка.
Последнюю цифру 1806413 надо записать со знаком минус.
В смысле данного расчёта это деньги,  которые вы получили на выходе.
Тогда доход получится =3,65%.
Дивиденды и купоны также надо вставить в таблицу в дату получения.
В вашем варианте со знаком «-».
Тогда у вас примерно= 8,1% получится.

6 osutkak  
Спасибо за помощь. Теперь у меня открыты глаза.
Завтра закрою счет и положу все на депозит под 9,25%. Будет выгоднее.

0
7 Воронноров  
Да, биржа дело не простое. Если вы профессионально этим не занимаетесь, что тоже не гарантирует успеха, то лучше просто держать деньги в банке.

3 bobez82  
Спасибо за разъяснения.

1 bobez82  
Добрый день Воронноров, попробовал использовать функцию "ЧИСТВНДОХ" для расчета доходности. Но получил странный результат, если не затруднит подскажите пожалуйста в чем моя ошибка? Вот входные данные: ввод ден. средств 23.12.2016 9676 руб., вывод ден. средств 13.01.2017 10222 руб. Вывод ден. средств в формуле "ЧИСТВНДОХ" указывал со знаком минус. Итого получилась доходность 159,63%.

0
2 Воронноров  
Результат совсем не странный, а такой, какой и должен быть.
Вы получили доход = 10222- 9676= 546 рублей.Это = 546 / 9676 = 5,64 % от вложенной суммы.
Эти проценты вы получили не за год, а за 21 день.Соответственно, ваша простая доходность в годовых % =
=5,64/21 *365 = 98 %.
А сложная доходность, предполагающая, что вы и дальше, каждые21 день будете  зарабатывать по 5,64% =5,64/100+1=
=1,0564 ^ (365/21)= 2,596 раза или =
=2,596 -1 = 1,596 *100% =159,6 %.
Это тоже самое, что вы по формуле получили.

Добавлять комментарии могут только зарегистрированные пользователи.
[ Регистрация | Вход ]

Copyright MyCorp © 2019