Как рассчитать доходность инвестиций с учетом ввода/вывода средств? Формула чиствндох в excel


Как рассчитать доходность инвестиций с учетом ввода/вывода средств?

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

Для этого рассчитывают результат инвестирования с учетом вводов/выводов средств и делят его на средневзвешенную по времени величину вложенных средств. Данный метод расчета очень подробно описан на сайте УК Арсагера, поэтому здесь я его описывать не буду. Те, кто читал их статью, знают, что этот метод очень трудоемкий, все приходится считать вручную. Если вы много раз вводили и выводили деньги, то формула расчета доходности портфеля будет ооочень длинной, легко запутаться и сделать ошибку. Поэтому я объясню, как очень просто посчитать доходность инвестиций в Excel.

Как считать доходность инвестиций в Excel

В Excel для расчета доходности инвестиций с учетом ввода/вывода денег используется функция ЧИСТВНДОХ (XIRR) — это функция, которая возвращает внутреннюю ставку доходности для графика денежных потоков, которые не обязательно носят периодический характер. Как ей пользоваться? Возьмем пример из статьи Арсагеры:

  1. Инвестор купил акций на сумму 1000 рублей.
  2. Через 3 месяца он купил еще акций на 500 рублей.
  3. Еще через 4 месяца он продал часть акций на сумму 300 рублей.
  4. Через год после первоначального приобретения, стоимость акций составила 1300 рублей.

Доходность портфеля составила 8,004% годовых.

Введем эти данные в Excel. В первой колонке указываем суммы, во второй даты.

  • В первой строчке указываем начальную сумму инвестиций 1000 рублей и дату инвестирования, к примеру 01.01.2014.
  • Во второй строчке указываем ввод средств 500 рублей и дату 01.03.2014.
  • В третьей строчке указываем вывод средств со знаком минус -300 и дату 01.04.2014.
  • В четвертой строчке указываем стоимость портфеля на конец года со знаком минус -1300 и дату конец года 31.12.2014.

доходность инвестиций excelТеперь выбираем какую-нибудь пустую ячейку и жмем кнопку fx (вставить функцию). Находим функцию ЧИСТВНДОХ. Вводим значения ячеек. В строке «Значения» выбираем ячейки с суммами, в строке «Даты» — ячейки с датами. доходность портфеля

Жмем ОК, получаем доходность — 8,009% годовых.

доходность инвестиционного портфеля excel

Если бы мы считали по простой формуле, то получили бы результат (1300-1200)/1200=8,3%. Вроде бы разница небольшая, но в других примерах разница может составить несколько процентов.

Функцию в ячейку так же можно вписать руками. Для этого в пустой ячейке впишите текст: =ЧИСТВНДОХ(A1:A4;B1:B4), номера ячеек укажите свои.

Расчет доходности инвестиционного портфеля за год

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

Рыночная стоимость портфеля на 31 декабря 2004 года: 10000$20 марта 2005 года: внесение 1000$25 июня 2005 года: изъятие 500$1 октября 2005 года: внесение 1000$Рыночная стоимость портфеля на 31 декабря 2005 года: 12000$

Вносим данные в Excel:

доходность инвестиционного портфеля формула

Формулы расчетов ниже:

доходность инфестиционного портфеля

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

Как видите, все очень быстро и просто. Надеюсь теперь вы будете пользоваться этим способом для расчета доходности своих инвестиций. Я раньше рассчитывал доходность своих инвестиций по формуле процентов годовых. При этом я почти каждый месяц доношу в свой инвестиционный портфель новые деньги, а простая формула этого не учитывает, она считает грубо. На сроке в несколько лет это очень сильно исказит цифры вашей доходности. Когда я пересчитал свою доходность инвестиций с помощью способа, описанного в этой статье, доходность моего портфеля заметно изменилась. Поделиться в соцсетях Top-100 блогов инвесторов, 
трейдеров и аналитиков

activeinvestor.pro

Внутренняя норма доходности. Формула расчета IRR инвестиционного проекта.

IRRIRR — это Internal Rate of Return, что переводится на русский язык как «внутренняя норма доходности». Так называется один из двух основных методов оценки инвестиционных проектов. В интернете немало статей, представляющих собой краткое изложение данной темы по учебникам финансового анализа. Их общий минус в том, что в них слишком много математики и слишком мало объяснений.

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

1. Как пользоваться программой Excel для расчета внутренней нормы доходности?2. Как пользоваться показателем IRR для оценки инвестиционных проектов?

IRR — что это такое? Формула IRR.

IRR или внутренняя норма доходности — это ставка процента, при которой приведенная стоимость всех денежных потоков инвестиционного проекта (т.е. NPV) равна нулю. Это означает, что при такой ставке процента инвестор сможет возместить свою первоначальную инвестицию, но не более того. О том, как пользоваться показателем IRR для одобрения инвестиционных проектов рассказывается чуть дальше в этой статье. Для начала надо научиться рассчитывать величину внутренней нормы доходности IRR, или, как ее еще называют, внутренней нормы рентабельности.

Математика расчета IRR довольно простая. Лучше всего рассмотреть ее на элементарных примерах. Для расчета показателя NPV инвестиционного проекта в одной из более ранних статей на этом сайте были использованы проекты А и Б с одинаковой суммой первоначальной инвестиции (10,000), но с разными по величине притоками денежных средств в последующие 4 года. Удобно будет воспользоваться этими примерами и для изучения формулы расчета показателя IRR.

Project Cash Flows

Приведенная (к сегодняшнему моменту) стоимость всех денежных потоков для четырехлетних проектов будет вычисляться по формуле:

NPV formula CF

где NPV — чистая приведенная стоимость, CF — денежные потоки (Cash Flows), R — % ставка, стоимость капитала, 0,1,2,3,4 — количество периодов времени от сегодняшнего момента.

Если приравнять NPV к нулю, а вместо CF подставить денежные потоки, соответствующие каждому проекту, то в уравнении останется одна переменная R. Ставка процента, которая будет решением данного уравнения, т.е. при которой сумма всех слагаемых будет равна нулю, и будет называться IRR или внутренней нормой доходности.

IRR big formula

Для проекта А уравнение примет вид:

Project A formula_2

Для проекта Б можно написать аналогичную формулу для расчета IRR, только денежные потоки будут другими:

Projact B formula_2

Чтобы было еще понятнее, можно нанести денежные потоки от проекта на шкалу времени и представить дисконтирование наглядно. Скажем, для проекта А расчет внутренней нормы доходности можно представить в таком виде:

IRR calculation

В общем виде для любого инвестиционного проекта формула для расчета IRR выглядит так:

formula IRRгде CF t — денежные потоки от проекта в момент времени t , n — количество периодов времени, IRR — внутренняя норма доходности.   Обратите внимание, что понятие IRR в отличие от NPV имеет смысл только для инвестиционного проекта, т.е. когда один из денежных потоков (обычно самый первый) является отрицательным. Этот отрицательный денежный поток и будет являться первоначальной инвестицией. В противном случае мы никогда не получим NPV, равную нулю.

Расчет внутренней нормы доходности с помощью программы Excel — примеры

Вручную с помощью обычного калькулятора найти значение IRR для проектов А и Б невозможно, потому что в данном случае получается уравнение 4-й степени (в нем будет множитель IRR4 — ставка процента в четвертой степени). Проблему решения такого уравнения n-ой степени можно устранить или с помощью финансового калькулятора, или, что проще, можно воспользоваться встроенной функцией в программе Excel. Эта функция находится в разделе Формулы —> Финансовые, и называется она ВСД (внутренняя ставка доходности).IRR excel function

Для проекта А значение IRR, как видно из рисунка ниже, составит 14,48%.

Excel function IRR project A

Чтобы воспользоваться функцией ВСД, в строку «значения» нужно поставить ссылки на ячейки таблицы с суммами денежных потоков. Ячейку «предположение» можно не заполнять, этот аргумент, является необязательным. Выводимое значение 0,144888443 — это и будет искомая IRR, т.е. внутренняя норма доходности данного проекта. Если перевести эту величину в проценты, то она равна 14,48% с точностью до двух знаков после запятой.

Для проекта Б значение IRR согласно Excel равно 11,79%.

Excel function IRR project B

Приведу важные пояснения по этой функции из раздела «справка» с моими дополнениями:

  1. Значения должны содержать по крайней мере одну положительную и одну отрицательную величину. В противном случае функция ВСД возвращает значение ошибки #ЧИСЛО!. Действительно, если нет отрицательного денежного потока, то NPV не может быть равно нулю, а в этом случае IRR не существует.
  2. Для расчета функции ВСД важен порядок поступлений денежных средств. Поэтому если потоки денежных средств отличаются по величине в разные периоды, что обычно и бывает, то их необходимо внести в таблицу в соответствии со временем их возникновения.
  3. В Microsoft Excel для вычисления ВСД используется метод итераций. Функцией ВСД выполняются циклические вычисления начиная со значения аргумента «предположение», пока не будет получен результат с точностью 0,00001%. В большинстве случаев для вычислений с помощью функции ВСД нет необходимости задавать аргумент «предположение». Если он опущен, предполагается значение 0,1 (10%).

Другими словами функция ВСД программы Excel будет искать значение IRR подбором, последовательно подставляя в формулу различные величины % ставки, начиная со значения в ячейке «предположение» или с 10%. Если функция ВСД не сможет получить результат после 20 попыток, выдается значение ошибки #ЧИСЛО! Поэтому в некоторых случаях, например, если вы будете считать IRR для ежемесячных потоков за несколько лет, лучше поставить в ячейку «предположение» ожидаемую вами величину ежемесячной процентной ставки. Иначе Excel может не справиться с расчетом за 20 попыток.

Графический метод расчета IRR

До появления персональных компьютеров обычно использовался графический метод определения IRR. Ниже представлены графики изменения NPV для проектов А и Б в зависимости от ставки процента. Для построения графиков нужно найти значение NPV, подставляя в формулу NPV различные значения ставки дисконтирования. Что такое дисконтирование и как дисконтировать денежные потоки, можно прочитать в одной из моих предыдущих статей.

IRR on curveНа рисунке выше синий график — проект А, красный график — проект Б. Пересечение графиков с осью X (в этой точке NPV проекта равно нулю) как раз и даст значение IRR для этих проектов. Нетрудно видеть, что графический метод дает величину IRR, аналогичную найденным в Excel значениям внутренней нормы доходности для проектов А — 14,5% и Б — 11,8%.

Как пользоваться показателем IRR для оценки инвестиционных проектов?

Любой инвестиционный проект предполагает наличие первоначальной инвестиции (оттока денежных средств), которая приведет к поступлениям денежных средств в будущем (в идеальном случае). Что же показывает внутренняя норма доходности инвестиционного проекта? Она показывает ставку  кредита, при которой мы не получим убытка от нашей инвестиции, т.е. результатом всех денежных притоков и оттоков в сумме будет ноль — ни прибыли, ни убытка. В этом случае наша инвестиция в проект окупится будущими поступлениями денежных средств от проекта, но в конечном итоге мы ничего не заработаем.

Правило оценки инвестиционных проектов:

Если величина IRR проекта больше стоимости капитала для компании (т.е. WACC), то проект следует принять.

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

Например, если вы берете кредит в банке под 14% годовых для того, чтобы вложить средства в бизнес-проект, который принесет вам 20% годовых дохода, то вы на этом проекте заработаете. Если же ваши расчеты окажутся неверны, и внутренняя норма доходности вашего проекта будет ниже 14%, то вам придется отдать банку больше денежных средств, чем вы получите от проекта. То есть вы понесете убыток.

Сам банк поступает точно так же. Он привлекает деньги от населения, скажем, под 10% годовых (ставка по депозиту), а выдает кредиты под 20% годовых (цифра взята «с потолка»). До тех пор, пока ставка по принимаемым банком депозитам будет меньше, чем ставка по выдаваемым банком кредитам, банк будет жить на эту разницу.

Рассчитав показатель IRR, мы узнаем верхний допустимый уровень стоимости заемного капитала, который предполагается инвестировать. Если стоимость капитала (по которой компания может привлечь финансовые ресурсы) выше, чем внутренняя доходность проекта (IRR), то проект принесет убытки. Если стоимость капитала для компании ниже, чем IRR проекта, то компания в каком-то смысле будет работать как банк — жить на разницу между процентными ставками банковского кредитования и рентабельности инвестиции.

Чтобы логика расчета IRR стала еще понятнее, приведу несколько примеров из жизни, с которыми может столкнуться (и сталкивается) обычный человек.

Пример 1 — срочный вклад в Сбербанке

Допустим, у вас есть в наличии 6,000,000 рублей. Прямо сейчас можно сделать срочный вклад в Сбербанк, скажем, на три года. Сумма большая, поэтому нужен самый надежный банк в России. Сбербанк в данный момент предлагает ставку для вкладов свыше 2 млн. рублей на три года в размере 9,0 % годовых без капитализации и 10,29% годовых с ежемесячной капитализацией. Что такое капитализация вклада можно прочитать по ссылке.

sberbank ratesПоскольку мы будем снимать проценты в конце каждого года, это будет вклад без капитализации процентов, и ставка составит 9% годовых. В конце каждого года можно будет снимать сумму, равную 6,000,000*0,09 = 540,000 рублей. В конце третьего года депозит можно будет закрыть, сняв проценты за третий год и основную сумму в размере 6 миллионов рублей.

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

IRR excel calc

Внутренняя норма доходности (IRR инвестиции) в банковский депозит равна процентной ставке по этому депозиту, т.е. 9%. Если 6,000,000 рублей достались вам в наследство после уплаты налогов, то это означает, что стоимость капитала для вас равна нулю. Поэтому такой инвестиционный проект будет выгоден при любой депозитной ставке. Но взять кредит на 6 миллионов в одном банке и положить эти деньги на депозит в другой банк с прибылью не получится: ставка кредита всегда будет заведомо выше ставки инвестирования. Это принцип работы банковской системы.

Пример 2 — покупка квартиры с целью заработка на сдаче ее в аренду

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

app cost

Я выбрала в Интернете первую же попавшуюся квартиру за 6 млн. рублей на СЗ Москвы. Аренда подобной однокомнатной квартиры стоит 30,000 рублей в месяц. Налоговые последствия данных сделок для простоты не учитываются.

app leasing 3

Итак, арендная плата за год составит 30,000*12 = 360,000 рублей. Чтобы было нагляднее, денежные потоки от обоих проектов — вклад в Сбербанке и сдача 1-комнатной квартиры в аренду на северо-западе Москвы — показаны вместе в таблице ниже:

CF 2 projects

Даже без вычисления IRR видно, что сейчас банковский депозит является более доходным вариантом. Легко доказать это, если рассчитать внутреннюю норму доходности для второго проекта — она будет ниже, чем IRR по депозиту. При сдаче данной однокомнатной московской квартиры в течение трех лет при условии ее продажи в конце третьего года IRR инвестиции составит 6,0% годовых.

leasing IRR

Если у вас нет наследства в сумме 6 млн рублей, то брать эти деньги в кредит, чтобы сдавать квартиру в аренду неразумно, так как ставка кредитования сейчас заведомо выше, чем 6,0% внутренней доходности данного проекта. Причем IRR не зависит от количества лет сдачи квартиры в аренду — внутренняя норма доходности останется такой же, если вместо трех лет сдавать ее в аренду 10 лет или 15.

Если учесть ежегодное подорожание квартиры в результате инфляции, IRR данного проекта будет выше, Например, если в первый год (2015) рублевая стоимость квартиры вырастет на 10%, во второй (2016) на 9%, а в третий (2017) на 8%, то к концу третьего года ее можно будет продать за 6,000,000*1,10*1,09*1,08 = 7,769,520 рублей. Такое увеличение денежного потока в третий год проекта даст IRR, равную 14,53%. Поэтому если бы мы могли предсказать будущие рублевые цены на квартиры с большой точностью, то наш проект стал бы более реальным. Но все равно невыгодным в нынешней ситуации, когда ставка рефинансирования ЦБ равна 17%, и, соответственно, все банковские кредиты слишком дороги.

Расчет IRR при ежемесячных потоках денежных средств

С помощью функции ВСД можно рассчитать IRR инвестиционного проекта при равных промежутках времени между денежными потоками. Результатом вычислений будет процентная ставка за период — год, квартал, месяц. Например, если бы мы считали, что платежи за аренду квартиры приходят в конце каждого месяца (а не года), то надо было бы сделать таблицу Excel с 36-ю платежами по 30,000 рублей. В этом случае функция ВСД выдала бы значение внутренней нормы доходности проекта за месяц. Для нашего проекта IRR получилась равной 0,5% в месяц. Это соответствует годовой % ставке в размере 6,17% (рассчитывается как (1+0,005)12-1), что ненамного больше, чем 6,0%, рассчитанных ранее.

IRR monthly

Если вы захотите получить этот результат самостоятельно, обязательно заполните ячейку «предположение» — поставьте туда 0,03, иначе вы получите на выходе ошибку #ЧИСЛО!, потому что Excel не хватит 20 попыток, чтобы рассчитать IRR.

Расчет IRR при неравных промежутках времени между денежными потоками

Excel предоставляет возможность рассчитать внутреннюю норму доходности проекта и в том случае, если денежные потоки от проекта поступают через неравные промежутки времени. Для расчета IRR такого проекта надо использовать функцию ЧИСТВНДОХ и в качестве аргумента указать не только ячейки с денежными потоками, но и ячейки с датами их поступлений. Например, если мы перенесем срок продажи квартиры вместе с последней арендной платой на конец четвертого года (с 31.12.17 на 31.12.18), а в конце третьего года у нас не будет поступлений денежных средств, то IRR упадет с 6% до 4,53% годовых. Обратите внимание, что рассчитать внутреннюю норму доходности в данном случае можно будет только с помощью функции ЧИСТВНДОХ, потому что фукция ВСД даст тот же результат, который и был — 6%, т.е. изменение периода времени ВСД не учтет.

IRR irregular dates

«Куда идем мы с Пятачком, большой, большой секрет…»

(Мнение автора может не совпадать с мнением правительства)

Нынешняя ставка рефинансирования, равная 17%, убивает и бизнес, и банки. Потому что трудно найти инвестиционные проекты, которые бы окупались при таких ставках кредитования. Как развивать бизнес в подобных условиях? Торговля оружием и наркотиками, конечно, будут прибыльны и в этом случае, но большая часть бизнесов будут в лучшем случае выживать, а в худшем  разорятся.

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

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

В 2014 году мы неоднократно слышали, что ЦБ РФ занимается таргетированием инфляции. И делалось это с благими намерениями — чем ниже инфляция, тем легче добиться окупаемости инвестиций. Но получается, что хотели «как лучше», а получилось «как всегда». При дорогой валюте, как сейчас, в России могло бы успешно развиваться собственное производство, импортозамещение стало бы реальностью. Но нет, мы не ищем легких путей, а что хуже всего, мы не учимся на своих ошибках. И живем, как в том анекдоте:

«В прошлом году посеяли 100 га пшеницы. Все поел хомяк…В этом году собираемся засеять 200 га пшеницы. Нехай хомяк подавится!»

Не вписались в рынок — небольшая заметка о том, к чему привели высокие ставки по кредитам в 2014-15 годах. Хотя, конечно, сложно судить, что было бы лучше: еще более низкий курс рубля, но не такие высокие процентные ставки по кредитам для компаний, или то, что у нас в итоге получилось.

март 2018 «Эксперты не исключают кризиса плохих долгов в ближайшие годы. Об этом, в частности, заявил директор Центра структурных исследований РАНХиГС, экс-замминистра экономического развития Алексей Ведев. По его словам, это может случиться в ближайшие полтора-два года «с большой вероятностью».

Сейчас у банков накоплен портфель кредитов в 11 трлн рублей, размещенных под запредельные 16% годовых, с 2 триллионами рублей процентных платежей ежегодно, отметил Ведев. Это очень тяжелая нагрузка на заемщиков, на экономику. При этом основная задолженность приходится на слабо обеспеченные слои населения. «Ситуация близка к катастрофичной!», — считает он.

Другие статьи на этом сайте из рубрики «Финансы»:

Вернуться на главную страницу

msfo-dipifr.ru

Как рассчитать доходность портфеля + Excel с готовыми формулами

Для оценки эффективности вложений нужно обязательно знать, какую доходность они принесут (или принесли). А если таких вложений много? Их нужно как то сравнить. Чтобы понять — что было более выгодным. И вообще, как можно рассчитать доходность портфеля имея различные вложения (вклады в банке, облигации, акции и прочее). На разные суммы и различные сроки?

Например, что более выгодно? Вложить 57 тысяч на 3 месяца и заработать 3 тысячи. Или инвестировать 75 000 на восемь месяцев и получить 5500?

Как узнать процент эффективной доходности портфеля, если в течении года было постоянное снятие и внесение средств?

Итак, поехали!

Считаем прибыль

Самая простая и базовая формула для определения «выгодности» вложений.

Разность между конечной суммой и начальной образует чистую прибыль.

Чтобы вывести в процентном соотношении воспользуйтесь формулой:

Доходность = (чистая прибыль) / сумму вложения * 100%.

Пример.

Купили акции Газпрома на 10 000 рублей. Через год все продали за 13 000 тысяч.

Чистая прибыль составила 3 тысячи рублей (13 000 — 10 000).

Доходность вложений 30% (3 000 / 10 000) * 100%).

В этой формуле есть один существенный недостаток. Она позволяет рассчитать только абсолютную доходность. Без привязки к периоду, за который она была получена.

Мы могли заработать 30% за 1 год. А могли бы и за 5 лет.

Годовая доходность в процентах

Более правильно оценить прибыль вложений можно с помощью годовой доходности.

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

Общепринятая оценка «одинакового периода времени» — это один год. Все проценты доходности полученные за разные промежутки времени сводятся к годовой ставке доходности.

Как это выглядит на практике?

Вложили деньги в акции Сбербанка — 30 000 рублей. И в акции Газпрома — 50 000 рублей

Через полгода, после роста котировок Сбербанка, продали все за 36 тысяч рублей.

Газпром вы держали ровно год и скинули бумаги за 65 тысяч.

Итог: На Сбербанке вы заработали за полгода 6 тысяч. На Газпроме 15 тысяч, но за целый год.

Если считать по общей доходности, то чистая прибыль в процентах составила бы:

  • Сбербанк — 6 тысяч или 20%;
  • Газпром — 15 тысяч или 30%.

Для правильной оценки эффективности инвестиций нужно все перевести в годовые проценты:

Формула:

Доходность (% годовых) = (прибыль в % * 365 дней) / срок инвестиций в днях.

Доходность Сбербанка = 20% х 365 дня / 180 дней = 40% годовых

Доходность Газпрома = 30% х 365 / 365 = 30% годовых.

Более выгодными оказались инвестиции в акции Сбербанка.

Как рассчитать доходность портфеляКак рассчитать доходность портфеля

Доходность с учетом движения средств

А как вывести общий результат, например за год?

Складывать все доходности не очень удобно и трудоемко.

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

Пример. На начало года инвестор обладал капиталом в 200 тысяч рублей.  За счет выгодных инвестиций, через год его портфель оценивался в 240 тысяч.

Чистая прибыль 40 000 рублей или 20% годовых.

В этой методике расчета доходности есть один существенный минус, который будет искажать реальные цифры. А по простому, делать их совсем не правильными.

За рассматриваемый период не учитываются возможные движения средств по счету или портфелю.

Что это за движения:

  • ввод-вывод средств;
  • получения прибыли «извне». Например, купонный доход по облигациям или дивиденды по акциям.

Из примера выше. Если за месяц до окончания годового периода инвестор вносил дополнительные 40 тысяч рублей. Как это скажется на результате? В абсолютных цифрах мы также имеем +40 тысяч прибыли или 20% годовых. Но по факту результата ноль.

Другой вариант. Через 1 месяц инвестор не внес, а снял 40 тысяч. В итоге почти целый год он оперировал суммой на 20% меньше первоначальной. И все равно заработал 40 тысяч прибыли.

Или в течении года выплачивались дивиденды, купоны. Были постоянное внесение и вывод средств со счета. Как тогда? Как определить реальную доходность?

Для расчета есть специальная формула расчета процентов в зависимости от даты и суммы движения средств.  Но думаю большинство (наверняка все) не будут ее пользоваться. Она слишком сложная и громоздкая. Даже ее приводить здесь не буду.)))

Расчет доходности в Excel

Есть более простой вариант расчета процентов в таблице Эксель. Нам поможет формула ЧИСТВНДОХ.

Все что нам нужно знать — это даты и суммы движений средств.

Как заполнить таблицу?

Нам нужны 2 колонки по движению денежных средств:

  1. сумма входящих и выходящих потоков
  2. Даты движений.

Все поступления на счет должны быть со знаком плюс. Снятия и прочие расходы обязательно со знаком минус. Конечная финальная сумма (на момент которой подсчитывается доходность) на счете тоже со знаком минус.

Вот как это выглядит на примере:

Расчет доходности портфеляРасчет доходности портфеля

Как это сделать в Excel?

Вносим в таблицу собственные значения (по аналогии с примером выше).

Вызываем функцию ЧИСТВНДОХ.

Расчет дохода в ExcelРасчет дохода в Excel

В поля «Значение» и «Даты» вносим наши условия как на картинке ниже. Просто выделяя правой кнопкой мыши необходимый диапазон.

Внесение данных для расчета доходаВнесение данных для расчета дохода

Саму формулу еще нужно умножить на 100. Дабы привести к более привычному нам виду. По умолчанию показывается не в процентах, в доле от единицы. В нашем случае — 0,16.

По ссылке, есть файл Excel с уже готовыми формулами, перечисленными в статье. Подставляете свои данные. Считаете прибыль. Радуйтесь (или огорчайтесь) полученной доходности.

Удачных инвестиций!

vse-dengy.ru

Разбираем функцию EXEL «ЧИСТВНДОХ». - 9 Января 2017 - Блог

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

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

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

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

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

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

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

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

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

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

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

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

Ставка в банке 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%.

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

fondmarket.ucoz.com

Функция ЧИСТВНДОХ - ONLYOFFICE

Функция ЧИСТВНДОХ - это одна из финансовых функций. Используется для вычисления внутренней ставки доходности по ряду нерегулярных денежных потоков.

Синтаксис функции ЧИСТВНДОХ:

ЧИСТВНДОХ(значения;даты[;предположение])

где

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

даты - это массив, содержащий даты поступления или выплаты денежных средств. Даты должны быть введены с помощью функции ДАТА.

предположение - это приблизительная оценка будущей внутренней ставки доходности. Это необязательный аргумент. Если он опущен, аргумент предположение полагается равным 10%.

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

Чтобы применить функцию ЧИСТВНДОХ,

  1. выделите ячейку, в которой требуется отобразить результат,
  2. щелкните по значку Вставить функцию Значок Вставить функцию, расположенному на верхней панели инструментов, или щелкните правой кнопкой мыши по выделенной ячейке и выберите в меню команду Вставить функцию, или щелкните по значку Значок Функция перед строкой формул,
  3. выберите из списка группу функций Финансовые,
  4. щелкните по функции ЧИСТВНДОХ,
  5. введите требуемые аргументы через точку с запятой,
  6. нажмите клавишу Enter.

Результат будет отображен в выделенной ячейке.

Функция ЧИСТВНДОХ

Вернуться на предыдущую страницу

helpcenter.onlyoffice.com

Уокенбах Формулы в Excel - Стр 33

Формула массива, возвращающая одно значение

Перейдем к рассмотрению формулы массива, возвращающей одно значение. Обратимся опять к рис. 14.1. Следующая формула массива размещается в одной ячейке:

{=СУММ(А1:А5*В1:В5)}

Эту формулу можно ввести в любую ячейку. При вводе формулы не нужно использовать фигурные скобки, а после введения не забудьте использовать комбинацию клавиш <Ctrl+Shift+Enter>.

Эта формула массива возвращает общую сумму, на которую продано товаров. Важно понимать, что значение, возвращаемое этой формулой, не зависит от информации в столбце D. Фактически можно удалить столбец D,а формула будет выполняться по-прежнему.

Эта формула оперирует значениями, находящимися в двух массивах. Оба массива хранятся в ячейках. Первый — в диапазоне ячеек В2 : В7, второй — С2 : С7. Формула перемножает соответствующие значения в этих двух массивах и создает новый массив (который существует только в памяти компьютера). Функция СУММвыполняет операцию над этим новым массивом и возвращает значение суммы его элементов.

Создание массива констант

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

Для создания массива констант нужно список его элементов заключить в фигурные скобки. Вот пример горизонтального массива констант, состоящего из пяти элементов:

{ 1 ; 0 ; 1 ; 0 ; 1 >

Представленная ниже формула использует функцию СУММ, аргументом которой являетсяпредыдущий массив констант. Формула возвращает значение суммы элементов массива, равное 3. Отметим, что эта формула использует массив в качестве аргумента, но приэтом не являетсяформулой массива. Поэтому ненужно использовать клавиши <Ctrl+Shift+Enter>дляввода формулы:

=С У М М ( { 1 ; 0 ; 1 ; 0 ; 1 } )

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

Пока, возможно, трудно заметить, какие-либопреимущества использования массивов констант. Действительно, представленная далее формула возвращает точно такое же значение, как и предыдущая:

= С У М М ( 1 ; 0 ; 1 ; 0 ; 1 )

Очевидные преимущества становятся ясны при дальнейшем рассмотрении. Следующая формула использует два массива констант:

=СУММ ( { 1 ; 2 ; 3 ; 4 } * { 5 ; б ; 7 ; 8 } )

Эта формула создает в памяти новый массив, который состоит из произведения соответствующих элементов двух других массивов. Этот новый массив имеет вид:

{ 5 ; 1 2 ; 2 1 ; 3 2 >

studfiles.net

Нерегулярные денежные потоки | WordEx999

Функции ЧПС, ВСД и МВСД имеют дело с регулярными денежными потоками. Это значит, что выплаты осуществляются ежемесячно, ежеквартально, ежегодно или с использованием какого-либо другого постоянного интервала времени. Excel предоставляет в ваше распоряжение две функции, позволяющие работать с нерегулярными денежными потоками: ЧИСТНЗ и ЧМСТВНДОХОД.

Чистая приведенная стоимость

Синтаксис функции ЧИСТНЗ следующий:

=ЧИСТНЗ(ставка,плт,даты)

Различие между функциями ЧИСТНЗ и ЧПС состоит в том, что первая требует наличия последовательности дат, с которыми связаны платежи. В примере, показанном на рисунке, приведенная стоимость последовательности платежей найдена с помощью функции ЧИСТНЗ.

Формула в ячейке В17 следующая:

=ЧИСТНЗ(ВЗ;В6:В15;А6:А16)

Аналогично функции ЧПС, функция ЧИСТНЗ может быть проверена дублированием денежных потоков и связыванием результата с первым денежным потоком. Функция ЧИСТНЗ просматриваемых денежных потоков должна иметь нулевой результат.

Примечание

В отличие от функции ЧПС, функция ЧИСТНЗ предполагает, что денежные потоки связаны с началом каждого периода, а не с его концом. Работая с функцией ЧПС, я исключал первый платеж из аргументов и добавлял его к результату функции. Работа с функцией ЧИСТНЗ не требует использования этого приема.

Внутренняя ставка доходности

Синтаксис функции ЧИСТВНДОХ следующий:

=ЧИСТВНДОХ(сумма,даты,предположение)

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

Формула в ячейке В15 следующая:

=ЧИСТВНДОХ(В4:В13;А4:А13)

Предупреждение

Функция ЧИСТВНДОХ имеет те же проблемы при работе с несколькими ставками, что и функция ВСД, – она ожидает, что смена знака денежных потоков будет однократной: с положительных значений на отрицательные или в обратном направлении. Если знак изменяется несколько раз, результат функции следует подставить в функцию ЧИСТНЗ и проверить, возвращает ли она нуль. На рисунке выше продемонстрирована такая проверка (в рассматриваемом примере знак изменяется всего один раз).

В начало

Полезное

wordex999.ru