Как рассчитать коэффициент торможения в экселе
Обновлено: 06.07.2024
Коэффициент вариации в статистике применяется для сравнения разброса двух случайных величин с разными единицами измерения относительно ожидаемого значения. В итоге можно получить сопоставимые результаты. Показатель наглядно иллюстрирует однородность временного ряда.
Коэффициент вариации используется также инвесторами при портфельном анализе в качестве количественного показателя риска, связанного с вложением средств в определенные активы. Особенно эффективен в ситуации, когда у активов разная доходность и различный уровень риска. К примеру, у одного актива высокая ожидаемая доходность, а у другого – низкий уровень риска.
Как рассчитать коэффициент вариации в Excel
Коэффициент вариации представляет собой отношение среднеквадратического отклонения к среднему арифметическому. Для расчета в статистике используется следующая формула:
- CV – коэффициент вариации;
- σ – среднеквадратическое отклонение по выборке;
- ǩ – среднеарифметическое значение разброса значений.
Коэффициент вариации позволяет сравнить риск инвестирования и доходность двух и более портфелей активов. Причем последние могут существенно отличаться. То есть показатель увязывает риск и доходность. Позволяет оценить отношение между среднеквадратическим отклонением и ожидаемой доходностью в относительном выражении. Соответственно, сопоставить полученные результаты.
При принятии инвестиционного решения необходимо учитывать следующий момент: когда ожидаемая доходность актива близка к 0, коэффициент вариации может получиться большим. Причем показатель значительно меняется при незначительном изменении доходности.
В Excel не существует встроенной функции для расчета коэффициента вариации. Но можно найти частное от стандартного отклонения и среднего арифметического значения. Рассмотрим на примере.
Доходность двух ценных бумаг за предыдущие пять лет:
Наглядно это можно продемонстрировать на графике:
Обычно показатель выражается в процентах. Поэтому для ячеек с результатами установлен процентный формат.
Значение коэффициента для компании А – 33%, что свидетельствует об относительной однородности ряда. Формула расчета коэффициента вариации в Excel:
Сравните: для компании В коэффициент вариации составил 50%: ряд не является однородным, данные значительно разбросаны относительно среднего значения.
Интерпретация результатов
Прежде чем включить в инвестиционный портфель дополнительный актив, финансовый аналитик должен обосновать свое решение. Один из способов – расчет коэффициента вариации.
Ожидаемая доходность ценных бумаг составит:
Среднеквадратическое отклонение доходности для активов компании А и В составляет:
Ценные бумаги компании В имеют более высокую ожидаемую доходность. Они превышают ожидаемую доходность компании А в 1,14 раза. Но и инвестировать в активы предприятия В рискованнее. Риск выше в 1,7 раза. Как сопоставить акции с разной ожидаемой доходностью и различным уровнем риска?
Для сопоставления активов двух компаний рассчитан коэффициент вариации доходности. Показатель для предприятия В – 50%, для предприятия А – 33%. Риск инвестирования в ценные бумаги фирмы В выше в 1,54 раза (50% / 33%). Это означает, что акции компании А имеют лучшее соотношение риск / доходность. Следовательно, предпочтительнее вложить средства именно в них.
Таким образом, коэффициент вариации показывает уровень риска, что может оказаться полезным при включении нового актива в портфель. Показатель позволяет сопоставить ожидаемую доходность и риск. То есть величины с разными единицами измерения.
Каждый раз, выполняя в Excel статистический анализ, нам приходится сталкиваться с расчётом таких значений, как дисперсия, среднеквадратичное отклонение и, разумеется, коэффициент вариации. Именно расчёту последнего стоит уделить особое внимание. Очень важно, чтобы каждый новичок, который только приступает к работе с табличным редактором, мог быстро подсчитать относительную границу разброса значений.
В этой статье мы расскажем, как автоматизировать расчеты при прогнозировании данных
Что такое коэффициент вариации и для чего он нужен?
Итак, как мне кажется, нелишним будет провести небольшой теоретический экскурс и разобраться в природе коэффициента вариации. Этот показатель необходим для отражения диапазона данных относительно среднего значения. Иными словами, он показывает отношение стандартного отклонения к среднему значению. Коэффициент вариации принято измерять в процентном выражении и отображать с его помощью однородность временного ряда.
Коэффициент вариации станет незаменимым помощником в том случае, когда вам необходимо будет сделать прогноз по данным из заданной выборки. Этот индикатор выделит главные ряды значений, которые будут наиболее полезными для последующего прогнозирования, а также очистит выборку от малозначительных факторов. Так, если вы видите, что значение коэффициента равно 0%, то с уверенностью заявляйте о том, что ряд является однородным, а значит, все значения в нём равны один с другим. В случае, если коэффициент вариации принимает значение, превышающее отметку в 33%, то это говорит о том, что вы имеете дело с неоднородным рядом, в котором отдельные значения существенно отличаются от среднего показателя выборки.
Как найти среднее квадратичное отклонение?
Поскольку для расчёта показателя вариации в Excel нам необходимо использовать среднее квадратичное отклонение, то вполне уместно будет выяснить, как нам посчитать этот параметр.
Из школьного курса алгебры мы знаем, что среднее квадратичное отклонение — это извлечённый из дисперсии квадратный корень, то есть этот показатель определяет степень отклонения конкретного показателя общей выборки от её среднего значения. С его помощью мы можем измерить абсолютную меру колебания изучаемого признака и чётко её интерпретировать.
Рассчитываем коэффициент в Экселе
К сожалению, в Excel не заложена стандартная формула, которая бы позволила рассчитать показатель вариации автоматически. Но это не значит, что вам придётся производить расчёты в уме. Отсутствие шаблона в «Строке формул» никоим образом не умаляет способностей Excel, потому вы вполне сможете заставить программу выполнить необходимый вам расчёт, прописав соответствующую команду вручную.
Вставьте формулу и укажите диапазон данных
Для того чтобы рассчитать показатель вариации в Excel, необходимо вспомнить школьный курс математики и разделить стандартное отклонение на среднее значение выборки. То есть на деле формула выглядит следующим образом — СТАНДОТКЛОН(заданный диапазон данных)/СРЗНАЧ(заданный диапазон данных). Ввести эту формулу необходимо в ту ячейку Excel, в которой вы хотите получить нужный вам расчёт.
Не забывайте и о том, что поскольку коэффициент выражается в процентах, то ячейке с формулой нужно будет задать соответствующий формат. Сделать это можно следующим образом:
- Откройте вкладку «Главная».
- Найдите в ней категорию «Формат ячеек» и выберите необходимый параметр.
Как вариант, можно задать процентный формат ячейке при помощи клика по правой кнопке мыши на активированной клеточке таблицы. В появившемся контекстном меню, аналогично вышеуказанному алгоритму нужно выбрать категорию «Формат ячейки» и задать необходимое значение.
Выберите «Процентный», а при необходимости укажите число десятичных знаков
Возможно, кому-то вышеописанный алгоритм покажется сложным. На самом же деле расчёт коэффициента так же прост, как сложение двух натуральных чисел. Единожды выполнив эту задачу в Экселе, вы больше никогда не вернётесь к утомительным многосложным решениям в тетрадке.
Всё ещё не можете сделать качественное сравнение степени разброса данных? Теряетесь в масштабах выборки? Тогда прямо сейчас принимайтесь за дело и осваивайте на практике весь теоретический материал, который был изложен выше! Пусть статистический анализ и разработка прогноза больше не вызывают у вас страха и негатива. Экономьте свои силы и время вместе с табличным редактором Excel.
Этап 1. Формирование выборки
Немаловажным условием формирования исходных данных является достоверность исходных данных. Муниципалитетом заключены договоры аренды подобных по функциональному назначению земельных участков. Информация о них получена оценщиком в муниципальном департаменте имущественно-земельных отношений. Условия сделок сопоставимы с условиями предполагаемой сделки, для которой определяется ставка: ставка годовой арендной платы y (зависимая переменная); площадь участка х (независимая переменная).
Таблица 1. Данные по прошедшим сделкам
| Площадь участка, м 2 | Ставка годовой арендной платы, руб./м 2 |
| 100 | 770 |
| 379 | 505 |
| 3 586 | 251 |
| 13 019 | 197 |
| 37 358 | 100 |
| 51 494 | 90 |
| 1 606 647 | 9 |
Этап 2. Определение подходящей зависимости
В анализе удобно использовать MS Excel 2003. В ячейки A1:B8 загружают исходные данные (табл. 1). Далее выделяют массив A1:B8 и выполняют последовательность команд: Главное меню / Вставка / Диаграмма / Точечная / Готово. В результате появляется диаграмма (рис. 1), на которой точками обозначены зависимости «Площадь/Ставка».
Достоверность полученной аппроксимации достаточно высока (наивысшая и практически недостижимая достоверность равна единице). Если в других случаях достоверность аппроксимации получена при подборе других функций, следует применять эти функции [4].
В ячейках В33:B34 находятся подобранные параметры модели: A=8,98; b=-0,43 (рис. 5).
Перед утверждением полученных данных необходимо проверить качество полученной модели.
Этап 5. Проверка качества подобранной модели
Качество преобразованной модели можно проверить стандартными средствами Excel, также представленными в полученной таблице. По критерию F= 80,6118, регрессия больше параметра «Значимость» F = 0,00028592.
Регрессионная статистика (рис. 6) показывает высокую вероятность верности предположения о наличии степенной зависимости пары «Площадь/Ставка».
Таблица 2. Сопоставление абсолютной величины коэффициента корреляции и характера связи
| Абсолютная величина коэффициента корреляции | Характер связи |
| До 0,2 | Практически отсутствует, слабая |
| От 0,2 до 0,3 | Умеренная |
| От 0,3 до 0,5 | Заметная |
| От 0,5 до 0,7 | Сильная |
| От 0,7 до 0,99 | Очень сильная |
Стандартная ошибка показывает отклонение фактических значений результирующего показателя от теоретической расчетной величины на удалении σ при распределении Гаусса, или (грубо) какой разброс данных присущ выборке.
Теснота связи параметров определяется по параметру R-квадрат (коэффициент детерминации), а для малых выборок, к которым относится и наш случай (7 наблюдений), целесообразно использовать нормированный R-квадрат (коэффициент детерминации, скорректированный на величину выборки). Эти данные свидетельствуют о тесноте связи имеющихся параметров и чем ближе его значение к единице, тем лучше модель описывает исходный ряд данных, т. е. из двух «хороших» моделей «победит» та, у которой нормированный R-квадрат выше.
Полученную модель можно считать достаточно достоверной.
Этап 7. Использование параметров в расчетах
В ячейки A37:A38 занесем площадь объекта оценки: 2350555 и 600444 м2 соответственно (рис.7). В ячейки B37:B38 запишем расчетные формулы, имея ввиду, что число e в Excel задается функцией =EXP(1).
Более точными получаются расчеты с применением Excel. Обратите внимание, что разница между данными, рассчитанными на калькуляторе и с помощью Excel, может достигать 5 %. Поэтому в отчете целесообразно указывать использованные инструменты:
2,7 8,98 ´ 2350555 -0,44 = 12 2,718281828 8,985892744 ´ 2350555 -0,437137047 = 13
Литература
3. Ковалев А.П. Оценка стоимости активной части основных фондов. М.: Финстатинформ, 1998.
4. Елисеева И.И. Практикум по эконометрике. М: Финансы и статистика, 2006.
5. Грибовский С.В., Сивец С.А. Математические методы оценки стоимости недвижимого имущества. М.: Финансы и статистка, 2008.
Заказать бесплатную консультацию
Книги Аллавердяна В.В.
Получить бесплатную консультацию и оставить заявку
Настоящая Политика конфиденциальности является составной частью Пользовательского соглашения Сайта и действует в отношении всей информации, в том числе персональных данных Пользователя, получаемых Администрацией Сайта в процессе работы Пользователя с Сайтом, исполнения Пользовательского соглашения и соглашений между Администрацией сайта и Пользователем. Использование Сайта означает безоговорочное согласие Пользователя с настоящей Политикой конфиденциальности и указанными в ней условиями обработки его персональных данных; в случае несогласия с этими условиями Пользователь должен воздержаться от использования Сайта.
Перед использованием Сайта Пользователю необходимо внимательно изучить настоящую Политику конфиденциальности.
МЕРЫ ПО ЗАЩИТЕ ПЕРСОНАЛЬНЫХ ДАННЫХ
- В своей деятельности Администрация сайта руководствуется Федеральным законом “О персональных данных” от 27.07.2006 №152-ФЗ.
- Администрация сайта принимает все разумные меры по защите персональных данных Пользователей и соблюдает права субъектов персональных данных, установленные действующим законодательством Российской Федерации.
- Защита персональных данных Пользователя осуществляется с использованием физических, технических и административных мероприятий, нацеленных на предотвращение риска потери, неправильного использования, несанкционированного доступа, нарушения конфиденциальности и изменения данных. Меры обеспечения безопасности включают в себя межсетевую защиту и шифрование данных, контроль физического доступа к центрам обработки данных, а также контроль полномочий на доступ к данным.
ИЗМЕНЕНИЕ ПОЛИТИКИ КОНФИДЕНЦИАЛЬНОСТИ
Администрация сайта оставляет за собой право в одностороннем порядке вносить любые изменения в Политику конфиденциальности без предварительного уведомления Пользователя. Актуальный текст Политики конфиденциальности размещен на данной странице.
Оценка является последним этапом, когда за отчетный период подводятся итоги, так же она необходима для возможной реструктуризации портфеля, то есть изменения долей акций в нем либо включение новых активов. Оценка позволяет определить, насколько было эффективно активное управление по сравнению с пассивным управлением, когда доли акций, входящих в портфель не изменялись. Так же можно сравнить не только эффект полученный от активного управления портфелем, но и сравнить результативность различных активных стратегий (вместо стратегий может быть результаты деятельности фондов) управления между собой. Оценка эффективности управления портфелем происходит за счет анализа различных показателей, которые, как правило, используют в своем расчете доходность.
Доходность является одним из важнейших показателей эффективности управления портфелем, свидетельствующим об эффективности управления. Но нельзя, используя только доходность, судить о качестве управленческой стратегии. Помимо доходности есть обратная сторона – риск, не учет его в оценке эффективности может исказить реальное положение вещей. Слишком позитивная доходность могла быть получена портфельным управляющим, за счет нескольких сверх рискованных сделок или вследствие того, что весь фондовый рынок находился в растущем тренде. Примером этого может послужить российский фондовый рынок, где до кризиса 2008 года почти все ПИФ (паевые инвестиционные фонды) показывали сверхдоходность в 100-200% годовых, это объяснялось тем, что рынок находился в повышательном тренде последние несколько лет, но когда рынок рухнул, за ним, и обесценились многие паевые фонды. Это свидетельствует о низком качестве управления и недооценке редких, но возможных событий, таки как кризисы.
Российский фондовый рынок характеризуется высокой степенью спекулятивных операций, информационной непрозрачностью, высокой волатильностью, низкой ликвидностью отдельных отраслей, сильное влияния макроэкономических факторов и зависимость от сырьевых регионов. Все это создает дополнительную нестабильность и неопределенность в принятии управленческих решений портфельным менеджером, поэтому оценка ккачества управления является жизненно важным на российском фондовом рынке. В итоге, з адачей портфельного менеджера становиться с одной стороны максимизация доходности портфеля, а с другой стороны минимизация риска.
Для того что бы определить эффективность управления и сделать соответствующие решения рассчитаем различные показатели эффективности управления в офисной программе Excel.
Далее рассчитаем значения среднедневной доходности по безрисковому активу. За такой актив, как правило, берут государственные ценные бумаги или банковские депозиты. Годовой процент по ним составляет 7%, тогда как дневная доходность такого актива будет равна 0,02%.
В Excel формулы расчета среднедневной доходности рынка безрискового актива, инвестиционного портфеля и изменчивости доходности инвестиционного портфеля (σр) следующие:
=СРЗНАЧ(C:C)
=0.07/360
=СРЗНАЧ(D:D)
=СТАНДОТКЛОН(D:D)
Формула коэффициента Шарпа выглядит так:
=(F3-F2)/F4
Показатели доходностей мы рассчитывали ранее, сейчас рассчитаем рыночный риск представленный коэффициентом бета (β). Формула расчета бета следующая:
Где: σpm – ковариация между доходностью инвестиционного портфеля и доходностью рынка;
σm – дисперсия доходности рынка.
Коэффициент бета показывает, как изменяется доходность инвестиционного портфеля от изменения доходности рынка в целом. Показатель бета меньше 1 свидетельствует о том, что инвестиционный портфель с такой стратегией имеет больше риска, нежели сам рынок. Это легко проверить, если рассчитать стандартные отклонения доходностей индекса РТС и доходностей инвестиционного портфеля для рынка (RTSI) стандартное отклонение составляет 1,78% , а для портфеля 2,20%, что выше, а значит и более рискованно.
И так мы разобрали все основные параметры для расчета коэффициента Трейнора.
Коэффициент бета =КОВАР(C:C;D:D)/ДИСП(C:C)
Коэффициент Трейнора =(F3-F2)/F6
Чем выше значения показателя Трейнора, тем более эффективно идет управление инвестиционным портфелем, поэтому выбираем стратегии, имеющие наибольшее значения показателя Трейнора. Как правило, этот показатель используется для построения рейтингов портфелей.
Расчет альфы Дженсена в Excel
Коэффициент альфа Дженсена был предложен Jensen в 1968 году. Это абсолютный показатель, который показывает, насколько более эффективно активное управление инвестиционным портфелем, нежели пассивное. Формула расчета следующая:
Где: αp – средняя доходность инвестиционного портфеля;
αrf – средняя доходность безрискового актива;
αrm – средняя доходность рыночного индекса (рынка);
βp –коэффициент бета (систематический риск портфеля).
Мы имеем все параметры для расчета этого коэффициента.
Альфа Дженсена =F3-(F2+(F1-F2)*F6)
Чем выше значения этого коэффициента, тем более эффективно управление инвестиционным портфелем. Отрицательное значение говорит о том, что следование за рынком (использование пассивной стратегии) более эффективно, нежели использование активной стратегии управления портфелем. Мы получили значение альфы 0,21%, то есть на 0,21% среднедневная доходность портфеля выше среднерыночной. Часто встречается критика данного показателя из-за того что его расчет строится на основе коэффициента «бета», который не стационарен и меняется со временем, поэтому эффективное управление в прошлом может не повторится.
Для расчета данного коэффициента необходимо определить уровень допустимой доходности (MAR), возьмем его равным дневной доходности безрискового актива (0,02%). Так же выделим доходности, которые были получены при нисходящей волатильности. В колонке ari по формуле отделим только отрицательные доходности:
=ЕСЛИ(A3<B3;A3;0)
После этого посчитаем разницу и квадрат между ari и MAR. Далее рассчитаем среднее значение по сумме квадратов и возьмем корень из него – это и будет знаменатель формулы.
D=(C3-B3)^2
F3 =СРЗНАЧ(D:D)
G3=КОРЕНЬ(F3)
Коэффициент Сортино =(H1-H2)/G3
Коэффициент Сортино показывает доходность инвестиционного портфеля, скорректированного на нисходящую волатильность, другими словами характеризует качество управления портфелем в периоды спада рынка.
Расчет этого коэффициента очень походит на расчет коэффициента Сортино, только вместо волатильности вниз необходимо найти волатильность вверх. Можно заметить, что при пассивном управлении этот коэффициент будет равен нулю.
Для этого расчета необходимо найти доходности инвестиционного портфеля, которые были больше среднерыночной (0,07%). Для этого в колонке ari запишем:
ari =ЕСЛИ(A2>B2;A2;0)
(ari- arm)^2=(C2-B2)^2
Информационное отношение =(H1-H2)/G3
Для расчета среднегеометрического темпа необходимо исключить убыточные месяцы торговли (колонка В).
Среднегеометрическая доходность ИП =СРГЕОМ(B:B)
Максимальная просадка (MaxDradawn) =МИН(A:A)
Коэффициент Калмара =C1/C2
Расчет коэффициента M2 в Excel
Коэффициент М2 (Индекс Модильяни), разработанный Модильяни в 1997 году, рассчитывается как умножение коэффициента Шарпа на стандартное отклонение рыночной доходности и прибавление безрисковой ставки доходности. Формула расчет коэффициента приведена ниже:
Где: arp – средняя доходность инвестиционного портфеля за выбранный временной интервал;
аrf – средняя доходность безрискового актива;
σp – риск инвестиционного портфеля, выраженный как стандартное отклонение доходностей портфеля;
σm – стандартное отклонение рыночной доходности.
Отношение стандартных отклонений называют фактором рычага.
Чем выше значения коэффициента М-квадрат, тем более большую доходность получает инвестор по сравнению с бенчмарком (безрисковый актив), при определенном уровне риска, который определен рычагом. Сравнивая полученное вознаграждение при одинаковом рычаге, позволяет выбирать наилучшие инвестиционные вложения.
Читайте также: