Как построить график доходности в excel
Для привлечения и вложения средств в какое-либо дело инвестору необходимо тщательно изучить внешний и внутренний рынок.
На основании полученных данных составить смету проекта, инвестиционный план, спрогнозировать выручку, сформировать отчет о движении денежных средств. Наиболее полно всю нужную информацию можно представить в виде финансовой модели.
Финансовая модель инвестиционного проекта в Excel
Составляется на прогнозируемый период окупаемости.
- описание макроэкономического окружения (темпы инфляции, проценты по налогам и сборам, требуемая норма доходности);
- прогнозируемый объем продаж;
- прогнозируемые затраты на привлечение и обучение персонала, аренду площадей, закупку сырья и материалов и т.п.;
- анализ оборотного капитала, активов и основных средств;
- источники финансирования;
- анализ рисков;
- прогнозные отчеты (окупаемость, ликвидность, платежеспособность, финансовая устойчивость и т.д.).
Чтобы проект вызывал доверие, все данные должны быть подтверждены. Если у предприятия несколько статей доходов, то прогноз составляется отдельно по каждой.
Финансовая модель – это план снижения рисков при инвестировании. Детализация и реалистичность – обязательные условия. При составлении проекта в программе Microsoft Excel соблюдают правила:
- исходные данные, расчеты и результаты находятся на разных листах;
- структура расчетов логичная и «прозрачная» (никаких скрытых формул, ячеек, цикличных ссылок, ограниченное количество имен массивов);
- столбцы соответствуют друг другу;
- в одной строке – однотипные формулы.
Расчет экономической эффективности инвестиционного проекта в Excel
Для оценки эффективности инвестиций применяются две группы методов:
- статистические (PP, ARR);
- динамические (NPV, IRR, PI, DPP).
Коэффициент PP (период окупаемости) показывает временной отрезок, за который окупятся первоначальные вложения в проект (когда вернутся инвестированные деньги).
Экономическая формула расчета срока окупаемости:
где IC – первоначальные вложения инвестора (все издержки),
CF – денежный поток, или чистая прибыль (за определенный период).
Расчет окупаемости инвестиционного проекта в Excel:
- Составим таблицу с исходными данными. Стоимость первоначальных инвестиций – 160000 рублей. Ежемесячно поступает 56000 рублей. Для расчета денежного потока нарастающим итогом была использована формула: =C4+$C$2.
- Рассчитаем срок окупаемости инвестированных средств. Использовали формулу: =B4/C2 (сумма первоначальных инвестиций / сумма ежемесячных поступлений).
Так как у нас дискретный период, то срок окупаемости составит 3 месяца.
Данная формула позволяет быстро найти показатель срока окупаемости проекта. Но использовать ее крайне сложно, т.к. ежемесячные денежные поступления в реальной жизни редко являются равными суммами. Более того, не учитывается инфляция. Поэтому показатель применяется вкупе с другими критериями оценки эффективности.
Рентабельность инвестиций
ARR, ROI – коэффициенты рентабельности, показывающие прибыльность проекта без учета дисконтирования.
где CFср. – средний показатель чистой прибыли за определенный период;
IC – первоначальные вложения инвестора.
Пример расчета в Excel:
- Изменим входные данные. Первоначальные вложения в размере 160 000 рублей вносятся только один раз, на старте проекта. Ежемесячные платежи – разные суммы.
- Рассчитаем средние поступления по месяцам и найдем рентабельность проекта. Используем формулу: =СРЗНАЧ(C23:C32)/B23. Формат ячейки с результатом процентный.
Чем выше коэффициент рентабельности, тем привлекательнее проект. Главный недостаток данной формулы – сложно спрогнозировать будущие поступления. Поэтому показатель часто применяется для анализа существующего предприятия.
Примеры инвестиционне6ого проекта с расчетами в Excel:
Статистические методы не учитывают дисконтирование. Зато позволяют быстро и просто найти необходимые показатели.
9.) Посчитаем коэффициенты Шарпа и Сортино. Эти коэффициенты оценивают риски, связанные с волатильностью доходности системы, и соотносят рисковую доходность системы с безрисковой доходностью (например, по облигациям или по банковскому вкладу). Таким образом, коэффициенты Шарпа и Сортино позволяют оценить финансовую целесообразность системы. Ключевое различие между коэффициентами в том, что коэффициент Шарпа не делает различий между колебаниями доходности вверх и колебаниями доходности вниз, то есть резкое увеличение прибыли он оценивает так же негативно, как и резкое увеличение убытков (что может негативно сказаться на оценке классических трендовых систем, рассчитанных на ловлю больших движений и демонстрирующих крайне низкий процент прибыльных сделок). А коэффициент Сортино считает рисковой только ту доходность, которая отличается от безрискойвой доходности по ставке в худшую сторону.
Шарп рассчитывается так: из средней доходности торговой системы за определённый период вычитается средняя доходность по безрисковой ставке и полученный результат делится на стандартное отклонение доходности. В нашем тесте мы рассчитаем доходность системы за каждый месяц. Для рассчёта коэффициента будем использовать среднемесячную доходность. За безрисковую ставку мы возьмём 12% годовых — доходность, которую можно получить по банковскому вкладу и которую легко посчитать по месяцам, просто поделив на 12. Для расчёта доходности по месяцам мы используем 3 столбца: AL, AM и AN. Нам необходимо определить величину депозита на начало каждого месяца и величину депозита на конец каждого месяца. Затем, посчитав разность между вторым и первым и выразив её в процентах, мы и получим искомую доходность. В первом столбце (AL) мы отметим начало каждого месяца. В ячейке AL3 формула:
Она отсылает нас к столбцу C, где указан порядковый номер в году каждого месяца. «Если номер в текущей ячейке столбца С равен номеру в предыдущей ячейке, значит месяц продолжается — в текущуюю ячейку столбца AL ставится 0; в ином случае ставится единица, отмечая начало нового месяца».
В столбце AM мы определим величину депозита на начало и конец каждого месяца. Используем самый простой вариант: размер депозита на начало последуещего месяца одновременно будет являться размером депозита на конец предыдущего. Кроме того мы будем учитывать только закрытые позиции: если на начало месяца остаётся позиция, открытая в прошлом месяце или раньше, её объём на тот момент не будет учитываться как объём депозита. Пишем формулу в ячейку AM4:
«Если текущая ячейка AL указывает на начало месяца, в текущую ячейку AM заносится текущее значение депозита из столбца AB; в ином случае значение текущей ячейки AM равно предыдущему значению ячейки AM».
Наконец в столбце AN, начиная с ячейки AN4, посчитаем доходность каждого месяца в процентах:
«Если текущая ячейка AL указывает на начало месяца, из текущего значения ячейки AM вычитается предыдущее и полученный результат выражается в процентах; в ином случае ячейка остаётся пустой».
Не забываем полученные формулы провести по столбцам.
Вот что получаем:
В столбцах AO, AP, AQ и AR будут размещены отрицательная доходность, а также будут рассчитаны формулы необходимые для получения коэффициента Сортино. Их пока пропустим.
В ячейке AS2 посчитаем среднемесячную доходность (среднее значение диапазона столбца AN):
В ячейке AT2 посчитаем коэффициент Шарпа — из среднемесячной доходности нашей системы вычтем среднемесячную доходность по безрисковой ставке (её мы взяли за единицу) и полученный результат разделим на стандартное отклонение среднемесячной доходности (дипазона столбца AN). Стандартное отклонение мы будем считать по генеральной совокупности (функция СТАНДОТКЛОНП):
Теперь посчитаем коэффициент Сортино. Для его расчёта нам понадобятся только отрицательные значения доходности по месяцам. Выделим их в отдельный столбец AO с помощью формулы в ячейке AO4:
(Напоминаю, что 1 — это среднемесячная безрисковая доходность).
Формула читается так: «Если значение доходности ниже доходности, полученной по безрисковой ставке, это значение указывается в столбце AO; если значение доходности не указано, то ячейка в столбце AO остаётся пустой; в ином случае — если значение доходности указано и оно выше, либо равняется доходности по безрисковой ставке, в столбце AO пишется 0».
Обратите внимание, что положительная доходность из расчётов не исключается — просто ей присваивается нулевое значение.
Проводим формулу через столбец.
Самый простой способ получить очень грубый аналог коэффициента Сортино — посчитать коэффициент Шарпа со стандартным отклонением только по отрицательной доходности. Но для корректного расчёта нужно произвести нижеописанные действия.
Числитель коэффициента Сортино соответствует числителю коэффициента Шарпа. А вот числитель отличается тем, что в его основе не отклонение текущего значения доходности от средней доходности, а отклонение отрицательной доходности от доходности по безрисковой ставке. Мы посчитаем знаменатель в несколько этапов: 1. В столбце AP посчитаем разность между отрицательной доходностью и ставкой и переведём результат из процентов в десятичную дробь (разделим на 100). В ячейке AP4 пишем формулу:
Отклонение положительной доходности от ставки рассчитывать не нужно, но ей должно быть присвоено нулевое значение.
Проводим формулу через столбец.
2. В столбце AQ посчитаем квадрат полученной разности:
3. Наконец в столбце AR переведём полученный результат снова в проценты:
Вот что получается:
В отдельных ячейках столбца AS рассчитаем оставшиеся значения.
В ячейку AS4 пишем формулу:
Получаем среднее значение нашего квадрата разности.
В ячейку AS6 переведём среднее значение в дробь:
В AS8 вычислим корень из дроби:
В ячейке AS10 переведём результат в проценты:
В ячейке AU2 посчитаем коэффициент Сортино (числитель будет тот же, что и у коэффициента Шарпа):
Дополнительно в ячейке AU4 посчитаем огрублённый вариант Сортино (в знаменателе — стандартное отклонение по ген. совокупности столбца AO).
Вот что у нас получается в итоге:
Теперь, когда все основные параметры для оценки системы заданы, потестируем систему на новейших данных. (С января 2012 до конца сентября 2015).
Удалим данные из первых столбцов, где указаны параметры котировок и вместо них экспортируем данные за указанный период. Проделываем ту же процедуру, что и в прошлый раз. Не забываем так же разделить столбец «DATA» на три: Год, Месяц, День. Вот что получаем в итоге:
Поскольку мы получили меньше данных, чем при тесте первого ценового ряда, нам необходимо удалить те строки в конце таблицы, продолжает производиться расчёт параметров системы, иначе наши коэффициенты будут учитывать значения этих пустых ячеек. Выделяем лишние строки и нажимаем «Удалить» (не «Очистить содержимое», а именно «Удалить», чтобы нам не пришлось вручную вносить изменения в формулы, которые считают значения диапазонов).
Теперь мы можем оценить нашу систему на свежих данных. Вот что имеем:
Низкий профит-фактор. Высокая МаксПросадка. Коэффициенты Шарпа и Сортино показыват отрицательные значения.
Собственный тест на Велс-Лабе провёл tim (см. комментарии к предыдущей части). Вот его результаты:
Сравним с нашим графиком:
Опять видим заметные расхождения. Также обратим внимание на различия в проценте прибыльных сделок и различия профит-фактора:
Для большей наглядности я построил в Экселе график того же типа, что и в Велс-Лабе, то есть график, показывающий накопленную прибыль/убыток. Через столбец AV я протянул следующую формулу — в ячейке AV3:
«К предыдущему значению столбца прибавлется разница между текущим и предыдущим размером депозита».
Благодаря этому графику становится видно, насколько велика разница между результатом, полученным в Экселе и результатом, полученным в Велс-Лабе:
По всей видимости, tim просто использовал несколько иные параметры системы, поскольку описанные ниже небольшие исследования свидетельствуют, что в Экселе ошибок нет.
Я снова провёл проверочный тест в Метастоке и получил результат в целом аналогичный результату Экселя:
Но ещё более существенным фактом является различие в количестве сделок: в тесте tim была совершена 141 сделка, в моём тесте в Экселе -154 сделки, в Метастоке — 160. То есть различие между экселем и Велс-Лабом — почти 10%, что вряд ли можно считать статистической погрешностью. Ошибок в моей таблице в Эксель нет. Все условия заданы чётко и Эксель их исправно выполняет. Поэтому я решил провести небольшое исследование — сравнить Боллинджер, полученный в Экселе с Боллинджером, взятым из Квика. Я взял ближайший контрак Ри (RiZ5), нанёс на него стандартный Боллинджер (Simple-20-2-Close), после чего экспортировал два полученных массива данных в Эксель. Параллельно квиковскому Боллинджеру я рассчитал индикатор вручную как два стандартных отклонения от 20-ти периодной скользящей средней (как и в прошлый раз я использовал функция СТАНДОТКЛОН). Затем при помощи функции КОРРЕЛ я посчитал корреляцию между двумя Боллинджерами и по отдельности корреляции между верхними полосами и нижними. Вот что получилось:
Как видим, корреляция стремится к 100 процентам, однако полного сходства не наблюдается. Для наглядности я взял наугад один фрагмент двух Боллинджеров. Слева три столбца — Боллинджер из Квика, справа — посчитанный вручную:
Расхождения несущественны, но они есть. Я полез в мануал Квика, посмотреть, по какой формуле там рассчитывается Боллинджер. Оказалось, что стандартное отклонение там рассчитывается за N периодов, то есть по генеральной совокупности, а не по выборке. Это значит, что для рассчёта Боллинджера в Экселе лучше использовать функцию СТАНДОТКЛОНП, а не СТАНДОТКЛОН. Я заново рассчитал Боллинджер, на этот раз по функции СТАНДОТКЛОНП и вот что получил:
Также я поменял формулу Боллинджера в рассчётах при тестировании системы. Но, как видим, на итоговый результат это не повлияло. Сверху график эквити, полученный при формуле Боллинджера, рассчитанной по СТАНДОТКЛОН, снизу — по СТАНДОТКЛОНП:
Таким образом, можно сделать вывод, что в Экселе ошибки нет: Эксель показывает ровно тот результат, который может быть получен при торговле на российской бирже через Квик.
К сегодняшнему дню я подготовил материал, а точнее получилась подробная инструкция для работы с программой Excel. Или как посчитать, и построить процентный и рублевый график доходностей.
Для начала необходимо заполнить необходимые поля в таблице. Сразу оговорюсь, что применяемые навыки можно использовать как для инвестиций, так и для спекуляций. Соответственно зная базовые навыки из данной статьи, вы сможете самостоятельно построить графики как годовой доходности, так и месячной/дневной и т. д.
Определитесь, за какие дни/месяцы вам нужны данные. Я использовал данные на примере неполного месяца августа. Вы можете выбрать свой интервал. Моя задача передать принцип работы.
Колонка В – Месяц, Колонка С – Число, D – Сумма на начало дня, Е – Сумма наконец дня, сразу можете создать колонку % и Комментарий. Обращаю ваше внимание на то, что колонки Начало и Конец дня отображаются оценку портфеля на плановую дату. То есть с учетом нерассчитанных сделок Т+. Эта оценка включает в себя и деньги и активы. Вы можете использовать и свои данные, но такой подход будет более точным.
Для чего обозначены 2 поля зеленым цветом? Для того, чтобы учесть такие ситуации как зачисление денег (докапитализация) и начисление доходов.
Рассмотрим ситуацию с дозачислением денег. В нашем примере мы зачисляли 50 000 рублей.
Для того, чтобы % считался верно, вам НЕОБХОДИМО в день зачисления прибавить зачисленную сумму к концу предыдущего дня. В нашем случае 101635 (конец 14.08) + 50 000 = 151 635 рублей. Деньги мы зачисляли 15 числа, поэтому на начало 15 числа мы помещаем значение 151 635 на начало дня.
При начислении дохода (дивиденды, купоны и т.д.) лично я закладываю значение начисленной суммы в результат дня. Вы можете поступать иначе. В данном примере нам начислили дивиденды в размере 4 000 рублей, и наш портфель увеличился на 3 000 рублей за день, таким образом, сумма на начало дня 152 550+4 000+3 000=157 550. Для эксперимента или верности расчетов можете закладывать сторонний доход как зачисление денег. Учет будет вестись как с примером 50 000 рублей.
IRR (Internal Rate of Return), или ВНД – показатель внутренней нормы доходности инвестиционного проекта. Часто применяется для сопоставления различных предложений по перспективе роста и доходности. Чем выше IRR, тем большие перспективы роста у данного проекта. Рассчитаем процентную ставку ВНД в Excel.
Экономический смысл показателя
Другие наименования: внутренняя норма рентабельности (прибыли, дисконта), внутренний коэффициент окупаемости (эффективности), внутренняя норма.
Коэффициент IRR показывает минимальный уровень доходности инвестиционного проекта. По-другому: это процентная ставка, при которой чистый дисконтированный доход равен нулю.
Формула для расчета показателя вручную:
- CFt – денежный поток за определенный промежуток времени t;
- IC – вложения в проект на этапе вступления (запуска);
- t – временной период.
На практике нередко коэффициент IRR сравнивают со средневзвешенной стоимостью капитала:
- ВНД выше – следует внимательно рассмотреть данный проект.
- ВНД ниже – нецелесообразно вкладывать средства в развитие проекта.
- Показатели равны – минимально допустимый уровень (предприятие нуждается в корректировке движения денежных средств).
Часто IRR сравнивают в процентами по банковскому депозиту. Если проценты по вкладу выше, то лучше поискать другой инвестиционный проект.
Пример расчета IRR в Excel
Быстро рассчитать IRR можно с помощью встроенной функции ВСД. Синтаксис:
- диапазон значений – ссылка на ячейки с числовыми аргументами, для которых нужно посчитать внутреннюю ставку доходности (хотя бы один денежный поток должен иметь отрицательное значение);
- предположение – величина, которая предположительно близка к значению ВСД (аргумент необязательный; но если функция выдает ошибку, аргумент нужно задать).
Возьмем условные цифры:
Первоначальные затраты составили 150 000, поэтому это числовое значение вошло в таблицу со знаком «минус». Теперь найдем IRR. Формула расчета в Excel:
Расчеты показали, что внутренняя норма доходности инвестиционного проекта составляет 11%. Для дальнейшего анализа значение сравнивается с процентной ставкой банковского вклада, или стоимостью капитала данного проекта, или ВНД другого инвестиционного проекта.
Мы рассчитали ВНД для регулярных поступлений денежных средств. При несистематических поступлениях использовать функцию ВСД невозможно, т.к. ставка дисконтирования для каждого денежного потока будет меняться. Решим задачу с помощью функции ЧИСТВНДОХ.
Модифицируем таблицу с исходными данными для примера:
Обязательные аргументы функции ЧИСТВНДОХ:
- значения – денежные потоки;
- даты – массив дат в соответствующем формате.
Формула расчета IRR для несистематических платежей:
Существенный недостаток двух предыдущих функций – нереалистичное предположение о ставке реинвестирования. Для корректного учета предположения о реинвестировании рекомендуется использовать функцию МВСД.
- значения – платежи;
- ставка финансирования – проценты, выплачиваемые за средства в обороте;
- ставка реинвестирования.
Предположим, что норма дисконта – 10%. Имеется возможность реинвестирования получаемых доходов по ставке 7% годовых. Рассчитаем модифицированную внутреннюю норму доходности:
Полученная норма прибыли в три раза меньше предыдущего результата. И ниже ставки финансирования. Поэтому прибыльность данного проекта сомнительна.
Графический метод расчета IRR в Excel
Значение IRR можно найти графическим способом, построив график зависимости чистой приведенной стоимости (NPV) от ставки дисконтирования. NPV – один из методов оценки инвестиционного проекта, который основывается на методологии дисконтирования денежных потоков.
Для примера возьмем проект со следующей структурой денежных потоков:
Для расчета NPV в Excel можно использовать функцию ЧПС:
Так как первый денежный поток происходил в нулевом периоде, то в массив значений он не должен войти. Первоначальную инвестицию нужно прибавить к значению, рассчитанному функцией ЧПС.
Функция дисконтировала денежные потоки 1-4 периодов по ставке 10% (0,10). При анализе нового инвестиционного проекта точно определить ставку дисконтирования и все денежные потоки невозможно. Имеет смысл посмотреть зависимость NPV от этих показателей. В частности, от стоимости капитала (ставки дисконта).
Рассчитаем NPV для разных ставок дисконтирования:
Посмотрим результаты на графике:
Напомним, что IRR – это ставка дисконтирования, при которой NPV анализируемого проекта равняется нулю. Следовательно, точка пересечения графика NPV с осью абсцисс и есть внутренняя доходность предприятия.
График позволяет визуально оценить зависимость данных от определенных показателей или их динамику. Эти объекты используются и в научных или исследовательских работах, и в презентациях. Давайте рассмотрим, как построить график в программе Microsoft Excel.
Создание графиков в Excel
Каждый пользователь, желая более наглядно продемонстрировать какую-то числовую информацию в виде динамики, может создать график. Этот процесс несложен и подразумевает наличие таблицы, которая будет использоваться за базу. По своему усмотрению объект можно видоизменять, чтобы он лучше выглядел и отвечал всем требованиям. Разберем, как создавать различные виды графиков в Эксель.
Построение обычного графика
Рисовать график в Excel можно только после того, как готова таблица с данными, на основе которой он будет строиться.
- Находясь на вкладке «Вставка», выделяем табличную область, где расположены расчетные данные, которые мы желаем видеть в графике. Затем на ленте в блоке инструментов «Диаграммы» кликаем по кнопке «График».
- После этого открывается список, в котором представлено семь видов графиков:
- Обычный;
- С накоплением;
- Нормированный с накоплением;
- С маркерами;
- С маркерами и накоплением;
- Нормированный с маркерами и накоплением;
- Объемный.
Редактирование графика
После построения графика можно выполнить его редактирование для придания объекту более презентабельного вида и облегчения понимания материала, который он отображает.
-
Чтобы подписать график, переходим на вкладку «Макет» мастера работы с диаграммами. Кликаем по кнопке на ленте с наименованием «Название диаграммы». В открывшемся списке указываем, где будет размещаться имя: по центру или над графиком. Второй вариант обычно более уместен, поэтому мы в качестве примера используем «Над диаграммой». В результате появляется название, которое можно заменить или отредактировать на свое усмотрение, просто нажав по нему и введя нужные символы с клавиатуры.
Построение графика со вспомогательной осью
Существуют случаи, когда нужно разместить несколько графиков на одной плоскости. Если они имеют одинаковые меры исчисления, то это делается точно так же, как описано выше. Но что делать, если меры разные?
- Находясь на вкладке «Вставка», как и в прошлый раз, выделяем значения таблицы. Далее жмем на кнопку «График» и выбираем наиболее подходящий вариант.
Построение графика функции
Теперь давайте разберемся, как построить график по заданной функции.
-
Допустим, мы имеем функцию Y=X^2-2 . Шаг будет равен 2. Прежде всего построим таблицу. В левой части заполняем значения X с шагом 2, то есть 2, 4, 6, 8, 10 и т.д. В правой части вбиваем формулу.
Как видим, Microsoft Excel предлагает возможность построения различных типов графиков. Основным условием для этого является создание таблицы с данными. Созданный график можно изменять и корректировать согласно целевому назначению.
Мы рады, что смогли помочь Вам в решении проблемы.
Отблагодарите автора, поделитесь статьей в социальных сетях.
Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.
Помогла ли вам эта статья?
Еще статьи по данной теме:
Ну у вас примеры не такие, какие меня интересуют. Как быть, когда имеются эмпирические данные, где шаг изменения значения переменной Х в ряду не равный? Например ряд значений Х: 2, 5. 10, 20…которым соответствует свое значение функции Y… Ведь в Экселе по оси Х график отражается при равных по длине делениях и график будет искажен (как бы сжат). Как все же в Экселе построить реальный график?
Здравствуйте! У меня есть таблица движения материальной точки по плоскости. Цифровые данные имеются по осям Х и Y. Допустим кривая движения точки похожа на эллипс. То есть кривая загибается в обратном направлении. При попытке создания этого графика в Эксель обратный загиб не получается.
Вопрос. Можно ли в Эксель получить график траектории движения точки на плоскости?
Задайте вопрос или оставьте свое мнение Отменить комментарий
Читайте также: