Как сделать кредитный портфель организации в экселе

Обновлено: 04.07.2024

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

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

Скачайте бесплатно простое решение по учету и анализу денежных потоков в Excel. В файле Вы увидите готовую таблицу для учета и анализа денежных потоков ООО, находящегося на УСН.

Любые изменения в статьи доходов и расходов Вы можете или внести сами, или обратиться за помощью к эксперту по Excel.

Если ваша ожидаемая доходность от отдельных инвестиций в ваш портфель известна или может быть ожидаема, вы можете рассчитать общую норму доходности портфеля с помощью Microsoft Excel.

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

Расчет общей ожидаемой доходности в Excel

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

Ключевые выводы

Введите текущую стоимость и ожидаемую доходность для каждой инвестиции.
Укажите вес каждой инвестиции.
Рассчитайте общую доходность портфеля.

В ячейке A2 введите стоимость вашего портфеля. В столбце B перечислите названия каждой инвестиции в вашем портфеле. В столбце C введите общую текущую стоимость каждой из ваших инвестиций. В столбце D введите ожидаемую доходность каждой инвестиции.

В ячейке E2 введите формулу = (C2 / A2), чтобы отобразить вес первых инвестиций. Введите ту же формулу в последующих ячейках, чтобы рассчитать вес портфеля для каждой инвестиции, всегда деля на значение в ячейке A2. В ячейке F2 введите формулу = ([D2 * E2] + [D3 * E3] +…), чтобы отобразить общий ожидаемый доход.

Пример

В приведенном выше примере предположим, что три инвестиции на общую сумму 100 000 долларов США представляют собой государственные облигации с годовой купонной ставкой 3,5%, 4,6% и 7%.

После маркировки всех ваших данных в первой строке введите общую стоимость портфеля в размере 100 000 долларов США в ячейку A2. Затем введите названия трех инвестиций в ячейки с B2 по B4. В ячейках с C2 по C4 введите значения 45 000, 30 000 и 25 000 долларов соответственно. В ячейках с D2 по D4 введите соответствующие купонные ставки, указанные выше.

Затем в ячейках с E2 по E4 введите формулы = (C2 / A2), = (C3 / A2) и = (C4 / A2), чтобы получить веса инвестиций 0,45, 0,3 и 0,25 соответственно.

Наконец, в ячейке F2 введите формулу = ([D2 * E2] + [D3 * E3] + [D4 * E4]), чтобы найти ожидаемую годовую доходность вашего портфеля. В этом примере ожидаемая доходность:

= ([0,45 * 0,035] + [0,3 * 0,046] + [0,25 * 0,07]) = 0,01575 + 0,0138 + 0,0175 = 0,04705, или 4,7%

Зачем рассчитывать ожидаемую доходность?

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

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

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

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

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

Несмотря на простоту, пророю возникают вопросы …

Как часто и в каких случаях делать ребалансировку?
Как рассчитать, сколько продать одного актива и на сколько докупить другого?

Частота ребалансировки

К ребалансировке есть несколько подходов. Иногда рекомендуют дожидаться заданного отклонения от исходного процента, а потом ребалансировать. Например, дождаться, когда один из активов отклонится на 10%. После этого проводится процесс ребалансировки.

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

Другой метод – календарная ребалансировка. Чаще всего рекомендуют проводить ребалансировку один раз в год. Например, в марте. Тогда в марте проверяется соотношение ценных бумаг. И если отклонение достаточно большое (например, больше 10%), то осуществляется продажа подорожавших бумаг и покупка подешевевших.

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

Пополнение, как ребалансировка

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

Расчет ребалансировки

Рассчитать необходимые суммы продажи и покупки довольно просто самостоятельно в EXCEL или в любом другом виде электронных таблиц.

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

Первая покупка ценных бумаг

На скриншоте калькулятора показана ситуация, когда уже задано целевое соотношение активов. Текущая ситуация заключается в том, что у инвестора есть только 500 тыс. руб.

Нетрудно заметить, что в калькуляторе присутствует два раздела:

Ценные бумаги в рублях
Ценные бумаги в долларах США

При желании можно добавить другие разделы в требуемой валюте.

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

Внимание: Распределение никогда не будет на 100% соответствовать заданному, так как покупка ценных бумаг возможна только кратно стоимости одной ценной бумаги (или лоту ценных бумаг). Так что небольшие отклонения вполне допустимы.

Регулярная ребалансировка портфеля

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

Калькулятор ребалансировки в формате EXCEL

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

К статье прилагаются два шаблона:

Для портфеля с расчетами в рублях (сумма всех ценных бумаг рассчитывается в рублях)
Для портфеля с расчетами в долларах США (сумма всех ценных бумаг рассчитывается в USD)

Файлы для скачивания

Калькулятор ребалансировки в EXCEL (валюта портфеля - RUB)
Файл: rebalancing_rub.zip
Размер: 51053 байт

Калькулятор ребалансировки в EXCEL (валюта портфеля - USD)
Файл: rebalancing_usd.zip
Размер: 50391 байт

Для скачивания файлов необходимо зарегистрироваться или авторизоваться

Сохраняем деньги

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

Почему не приложения?

Потому что есть старый-добрый Эксель. Все давно придумано.

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

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

Но это дорого!

Если вас смущает цена за пакет Microsoft Office, то вы спокойно можете использовать абсолютно бесплатный аналог Экселя – Open Office.

А удобнее всего применять Гугл Таблицы. Они бесплатные и очень простые. Их возможностей вполне хватит начинающему инвестору. Таблицы от Гугла позволяют вести учет где угодно и с любого устройства. Даже с телефона!

Это сложно!

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

Что дает учет

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

Несколько таблиц

У меня есть две основные таблицы, которые я веду уже много лет:

Есть еще вспомогательные таблицы. Я тоже периодически их посматриваю и заполняю:

Калькулятор выхода на пенсию.
Список прочитанных книг.
Список семинаров, вебинаров и конференций.
Список стран, городов и мероприятий, которые я посетил.
и другие таблицы.

Семейный бюджет в Excel

Ранее я уже описывал свою методику ведения семейного бюджета. В книге и в статьях: часть 1 и часть 2.

Если коротко, то я подхожу к семье – как к бизнес-предприятию. И веду семейный бюджет в формате стандартного отчета о прибылях и убытках. Там есть статьи дохода. расходные статьи, сколько я смог отложить и т.д. У меня есть цифры аж с 2006 года. Они позволяют провести глубокий анализ и с очень высокой вероятностью реализовывать все намеченные цели.

Не буду повторяться. Берите и копируйте.

Скопируйте себе файл в Гугл Таблицы. Либо сохраните его в формате XLSX и настройте его под себя в Экселе.

Учет инвестиций в Excel

Скопируйте себе файл в Гугл Таблицы. Либо сохраните его в формате XLSX и настройте его под себя в Экселе.

Шаблон не самый идеальный:

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

Портфель

В этой вкладке я веду учет активов. Хочу отметить наиболее интересные колонки. Сортирую в порядке важности:

Дата выплаты. Я живу на доходы от рынка. Мне критически важно знать когда именно я получу дивиденды, купоны и ренту.
Выплата. Сколько денег я получу на счет.
Дд, чист, %. Чистая дивидендная доходность. Уже с учетом налогов. Дает понимание не пора ли сменить “дойную коровку” на другую. Или может лучше перейти на “коз” и “кур”.😜
Дивиденд в рублях. Размер дивиденда на одну акцию. Технический параметр.
Доля акции или облигации в портфеле. Если одна компания занимает в портфеле более 15%, то стоит задуматься о ребалансировке. Если акции в сумме занимают слишком существенную долю (более 85%), то мне некомфортно. Это тоже повод задуматься о балансировке.
Справедливая цена акции. При какой цене стоит задуматься о продаже актива. Очень условная цифра. Я убрал значения по всем бумагам, чтобы не смущать читателей.

История

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

На какие суммы пополнил портфель.
Зачем снимал деньги.
Когда купил или продал актив.
Почему купил или продал.

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

Дивиденды

Вторая по популярности вкладка (после портфеля):

Сколько получил дивидендов и купонов.
Когда мне отправили деньги.
Когда я их получил фактически.
Какие налоги заплатил с дивидендов и купонов.

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

Анализ и план закупок

Данная вкладка – поле для творчества. Здесь я творю что хочу. Отвечаю себе на следующие вопросы:

Не стоит ли добавить в портфель новую дивидендную “коровку”.
Что я буду покупать в моменты коррекций.
Что я буду менять в периоды ребалансировки.
Вердикт по эмитенту.
А что там на западных рынках?
и т.д.

Динамика капитала

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

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

Бонус!

У меня еще есть отдельный калькулятор пенсии. Поставьте плюсик в комментариях. Если пост наберет 30 плюсиков, то напишу статью про него и поделюсь шаблоном.

Ой, совсем забыл. Советую сделать свой шаблон самостоятельно. Ну или изменить мои наработки под себя. Вы начнете понимать как все работает.

Ставьте лайк, если статья понравилась.

И подписывайтесь на самый нескучный телеграм-канал по инвестициям "На пенсию в 35 лет" @pensiya35

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

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

На всякий случай повторим основные показатели риска: стандартное отклонение доходности портфеля и каждого актива в отдельности от доходности рынка; коэффициент Шарпа; коэффициент бета; коэффициент Трейнора; Альфа Дженсена; коэффициент Швагера; коэффициент Сортино; М-квадрат. По очереди теперь разберемся с расчетом каждого из этих индикаторов в Excel. Стоит наверное всех обнадежить – сделать это довольно просто, по крайней мере гораздо легче, чем может показаться на первый взгляд. Более того, в случае расчетов в Excel достаточно просто использовать соотвтетсвующие функции. Сразу оговоримся, что все цифры условные и взяты из предыдущей статьи.

Коэффициент Шарпа – разница в доходности портфеля к безрисковому активу, деленная на стандартное отклонение портфеля. Формула рассчитывается просто: при доходности портфеля в 2% и полученному стандартному отклонению 0.5%, а также и доходности безрискового актива в 0.4%, необходимо от первой ячейки отнять вторую и все это разделить на третью.

Коэффициент бета – скорость изменения доходности портфеля по сравнению с доходностью рынка. Соответственно необходимо математически рассчитать насколько доходность рынка влияет на доходность портфеля. Формула используется лишь два показателя: ковариация портфеля и рынка (произведение сумм корелляции и стандартного отклонения портфеля и рынка) и дисперсия доходности рынка (рассчитывается как стандартное отклонение в квадрате).

Для начала рассчитаем коэффициент ковариации. При корелляции портфеля с рынком в 0.7, стандартного отклонения рынка 0.3% и стандартного отклонения портфеля 0.5%, получается ковариация 0.7*0.3%*0.5%=0.0000105. Для расчета дисперсии рынка достаточно стандартное отклонение возвести в квадрат: 0.3%^2=0.000009. Отсюда находим бета-коэффициент 0.0000105/0.000009=1.16667.

Из полученных данных теперь рассчитаем коэффициент Трейнора – отношение премии за риск рынка и самого риска портфеля. Для его расчета требуется всего три показателя: общая доходность рынка, общая доходность портфеля и бета-коэффициент портфеля. При полученных выше показателях получается: (20%-15%)/1.16667=0.04285.

Альфа Дженсена – коэффициент, показывающий, насколько более эффективно активное управление портфелем по сравнению с пассивным. Для расчета необходимы 4 показателя: средняя доходность портфеля (2%), средняя доходность рынка (1.5%), доходность безрискового актива (0.4%) и коэффициент бета портфеля (1.16667). Чем больше значение данного коэффициента, тем более эффективно управляется портфель.

Коэффициент Швагера – отношение прибыли портфеля к средней максимальной просадке. Мы условно взяли данные за год при объеме портфеля в 500 тыс. долларов. Для расчета необходимо всего два показателя: среднегодовая доходность портфеля (500*0.2=100 тыс. долларов) и максимальная просадка (условно возьмем 5%: 500 *0.2=25 тыс. долларов). Далее делаем расчет по формуле.

Коэффициент Сортино, также как и показатель Шарпа, указывает на эффективность управления портфелем, но не ниже минимально допустимой доходности. Для начала рассчитаем минимально допустимую доходность. Как правило, за такой показатель берется доходность безрискового актива (0.4%). Таким образом, с учетом количества рабочих дней в году (условно 280) минимально допустимая дневная доходность составляет 0.4%/280=0,0014%.

Далее необходимо количество дней ниже минимально допустимой доходности (условно возьмем этот показатель 15). После этого можно рассчитать непосредственно сам коэффициент Сортино. Рассчитывается следующим образом: (2%-0.0014%)/15=0.0013.

Наконец, последний коэффициент – М-квадрат или просто М 2 . Данный индикатор указывает на то, насколько более высокую доходность показывает портфель, по сравнению с безрисковым активом. Для расчета требуются четыре показателя: средняя доходность портфеля (2%) и безрискового актива (0.0014%), стандартное отклонение доходностей портфеля (условно возьмем 1%) и стандартное отклонение доходности рынка (условно возьмем 0.5%).

Вот и все. Расчет всех эти коэффициентов в Excel оказался не таким уж и сложным, как и было обещано в начале статьи. После освоения этих двух материалов надеемся, что ключевые элементы портфельного анализа были успешно освоены.

Читайте также: