Как посчитать комиссионный сбор excel

Обновлено: 02.07.2024

Я продал квартиру и вложил деньги в фондовый рынок. Чтобы отслеживать изменения по портфелю, попробовал несколько публичных сервисов — платных и бесплатных, но все они показались неудобными, либо с ежемесячной оплатой. Вернулся к старому доброму «Экселю». На разработку таблицы потратил две недели.

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

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

На основе данных в таблице построены графики: по типам активов (акции роста, акции дивидендов, защитные активы, бонды), разбивка по секторам экономики (я визуал), по валютам всех активов.

Таблица считает сумму дивидендного дохода в год и средний в месяц, в рублях и долларах отдельно + конвертация долларов по курсу в рублях и общий итог ДД в месяц.

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

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

Действую так: Купил акцию — добавил строчку в соответствующий сектор. Указываю эмитента, сектор, количество купленных бумаг, брокера, валюту акции, сумму покупки и планируемый дивиденд на одну акцию. Формулы просчитывают все остальное.

Если акция уже была — просто изменил количество акций в строчке. Автоматически просчитывается чистая ДД (за вычетом налога) на то количество акций, которое я указал. Чистая ДД прибавляется в итоговую сумму заработка за год. Если это доллары — они конвертируются в рубли по курсу 75 рублей за доллар и добавляются к сумму заработка за год.

В комплекте к таблице идут принципы инвестирования, которым я следую. Например, доля одного эмитента не может быть более 5% от портфеля, а доля одного сектора не может быть более 15% от портфеля. Покупки совершаются в три этапа: 30% + 30% + 40% в зависимости от степени падения бумаги. По некоторым эмитентам использую так называемую «демо покупку»: когда бумага на хаях, и я захожу на одну акцию, чисто чтобы за ней следить и так далее. В совокупности таблица и принципы отлично дисциплинируют.

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

Сделки я совершаю один-два раза в месяц, каждую фиксирую в таблице. Занимает это около 10 минут.

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

Итог: Узнайте, как рассчитать комиссию в Excel за базовый многоуровневый план и прейскурант с использованием функций IF, VLOOKUP или XLOOKUP.

Уровень квалификации: средний

Видеоурок

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

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

Пожалуйста, ознакомьтесь с моей статьей о том, как использовать VLOOKUP для поиска ближайшего совпадения, чтобы получить подробное объяснение по установке последнего аргумент имеет значение ИСТИНА.

Загрузить файл

Загрузите файл, чтобы продолжить.

Расчет комиссионных с ВПР

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

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

В этом примере наш план комиссионных выглядит следующим образом:

Представитель продает от 0 до 50 000 долларов США, они зарабатывают 5%
Представитель продает от 51 000 до 100 000 долларов США, они зарабатывают 7%
Представитель продает от 100 001 до 150 000 долларов США, они зарабатывают 10%
Представитель продает более 150 001 долларов, они зарабатывают 15%.

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

Мы можем использовать формула ВПР для расчета ставки выплат для заданной суммы продаж (значение поиска). Чтобы это сработало, нам нужно установить последний аргумент в vlookup [range_lookup] на TRUE.

Если последний аргумент установлен на TRUE, vlookup найдет ближайшее совпадение со значением поиска, которое равно меньше или равно искомой сумме . По сути, это позволяет нам находить значение между диапазонами двух чисел (ярусов).

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

Как настроить таблицу ставок

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

В этом примере для таблицы ставок комиссионных первая строка в диапазоне поиска должна быть нулевой. Это связано с тем, что торговый представитель потенциально может иметь продажи в размере 0 долларов США, а значение поиска будет равно нулю. Если бы значение поиска (сумма продаж) было отрицательным числом, то vlookup вернет ошибку.

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

Если сумма продаж превышает последнюю строку в диапазоне поиска, то vlookup вернет последнюю строку. Например, если представитель сделал продажи на сумму 175 000 долларов, то vlookup вернет 15%.

Рассчитать комиссию для возврата стоимости в долларах

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

Это означает, что выплата будет одинаковой, независимо от суммы продаж в рамках уровня. В приведенном ниже примере выплата составит 1000 долларов, если сумма продаж составляет 55 000 или 95 000 долларов.

Выплата НЕ по скользящей шкале. Это фиксированная ставка для каждого уровня.

Если вы ищете расчет по скользящей шкале, см. Мою статью о расчете комиссии с многоуровневой структурой ставок с использованием SUMPRODUCT.

Расчет комиссионных с помощью XLOOKUP

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

Преимущества XLOOKUP

У XLOOKUP есть два двух основных отличия и преимущества:

Недостаток XLOOKUP

Таким образом, ВПР отлично работает в этом сценарии, и его намного проще написать, чем вложенную формулу ЕСЛИ.

Избегайте вложенных формул ЕСЛИ

Распространенным подходом к расчету комиссионных является использование операторов ЕСЛИ. С такой таблицей ставок вам придется написать несколько операторов IF. По сути, вам нужно написать один оператор IF для каждого уровня (строки) в таблице.

Затем вам нужно объединить все операторы IF в одну длинную и уродливую формулу . Это , называемые вложенными операторами IF .

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

Формулу ЕСЛИ гораздо сложнее поддерживать . Он требует модификации при добавлении/удалении строк из таблицы. Для VLOOKUP и XLOOKUP этот тип обслуживания НЕ требуется.

Если вы все же используете IF, убедитесь, что делает ссылку каждой ячейки на таблицу тарифов абсолютной ссылкой ( F4 на клавиатуре). В противном случае вы получите неверные результаты при копировании формулы.

Использование ВПР намного проще и чище, чем использование вложенных формул ЕСЛИ. Как вы можете видеть в этом примере, vlookup позволяет использовать одну формулу для расчета ставки комиссионных выплат для любой заданной суммы продаж.

Дополнительные ресурсы

Как использовать ВПР с ближайшим соответствием?

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

Пожалуйста, оставьте комментарий ниже с любыми вопросами или предложениями. Спасибо!

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

Итак, приступим. Открываем на рабочем столе таблицу "Мониторинг_облигаций.xlsx". Запускаем торговую систему Квик. В таблице со списком облигаций нажимаем правой кнопкой мыши и выбираем функцию "Вывод через DDE сервер".

В новом окне нажмите на кнопку "Начать вывод" и подождите пару минут для обновления информации в нашем Экселе. Затем кликните "Остановить вывод" и закройте текущее окно.

Переходим к работе с таблицей. Нажимаем один раз левой кнопкой мыши на ячейку О-1. Вводим название: "Комиссия брокера и биржи, руб.".

Я покупаю ценные бумаги на фондовом рынке через Сбербанк. Размер комиссий брокера и биржи составляет 0,07% от суммы сделки. Объем сделки определяется как номинал умноженный на цену плюс накопленный купонный доход умноженные на 0,07%.

Переведём эту формулу до понятных Экселю значений. В ячейке О-2 ставим равно (=) и открываем скобку. Затем кликаем мышкой на значение номинала (ячейка К-2). Ставим символ умножения (звездочка *). Нажимаем на ячейку L-2. В ней указана цена для выбранной нами облигации. Так как стоимость долговых бумаг на фондовом рынке выражена в процентах мы должны разделить данное число на 100. Нажимаем на символ деления (/) косая черта и прибавляем НКД из ячейки Н-2. Закрываем скобку. Умножаем (*) на размер комиссий 0,07 и делим (/) на 100. Мы прибегли к делению, так как комиссии указаны в процентах от суммы сделки. Для завершения ввода формулы нажимаем ENTER.

Введённая нами формула для Экселя выглядит так:

Однако, для нас он сразу отобразит результат вычислений 0,707 рубля. На следующем этапе мы должны повторить данную операцию в каждой строке. Для более чем 1 600 строк с облигациями это делать очень долго. Поэтому мы воспользуемся встроенной функцией автозаполнения.

Нажмите мышкой на ячейку О-2. Она будет выделена зелёной рамкой. Затем левой кнопкой мыши нажмите на кубик в её нижнем правом углу и удерживая его тяните вниз до конца списка облигаций.

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

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

В новом окне выбираем формат данных "Числовой" и ставим галочку рядом с пунктом "Разделитель групп разрядов". Подтверждаем выбранные настройки кнопкой ОК.

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

Мы с вами уже изучали, как посчитать простую доходность к погашению у облигаций . Первым этапом является расчёт стоимости ценной бумаги в денежном выражении с учётом всех дополнительных платежей. Такой процесс я коротко называю "Расходы на покупку". Вспомним эту замечательную методику: номинал в рублях Х цену % + НКД в рублях + комиссии в рублях.

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

Нажимаем левой кнопкой мыши на ячейку Р-1 и пишем её название "Расходы на покупку, руб.". Не забываем нажать Enter для подтверждения ввода данных.

В ячейке Р-2 ставим знак равенства (=) и нажимаем мышкой на номинал (ячейка К-2) умножаем (ставим символ *) на цену предложения (ячейка L-2) делим (символ /) на 100 прибавляем (+) НКД (ячейка Н-2) и прибавляем (+) комиссии (ячейка О-2). Нажимаем Enter для завершения ввода формулы.

Для экселя формула будет выглядеть так:

Мы будем видеть результат его вычислений: 1011,467 р.

Как говориться, доверяй, но проверяй. Попробуем вручную пересчитать полученный результат: 1000р. х 100,011% + 10,26р. + 0,71р. = 1011,08р. Мы молодцы, всё правильно. Теперь растягиваем нашу формулу вниз до конца списка.

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

Чтобы нам удобнее было воспринимать полученные цифры, нажмите правой кнопкой мыши на букву Р вверху столбца. Здесь выберите пункт "Формат ячеек". В новом окне укажите формат "Числовой" и поставьте галочку рядом с пунктом "Разделитель групп разрядов". Этот способ форматирования цифр до двух знаков после запятой я уже показывал выше. Не забывайте нажать на "Дискетку" вверху экселя для сохранения сделанных изменений.

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

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

Я приведу пример. Перед большими праздниками людям нужны деньги на покупку подарков. Большая часть их сбережений вложена в ценные бумаги. Чтобы быстро вывести деньги с фондового рынка они вынуждены устраивать распродажи. Как найти облигации, которые они дёшево продают среди более 1600 бумаг? Можно ежедневно в ручную перебирать весь список или создать собственного робота для автоматического мониторинга. Именно об этом я рассказываю в новом цикле своих статей.

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

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

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

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

Далее на примере показано, как можно вести отчётность. Подразумевается, что все разделы отчётности — листы одной книги Excel, т.е. физически всё хранится в одном файле.

1. Движение денежных средств по счёту

Простая таблица из трёх столбцов: «Дата», «Зачисление», «Отзыв». Желательно, чтобы была ещё и сводная статистика по зачисленным и отозванным денежным средствам за всё время инвестирования.

Для определения «Зачислил, Всего» вам нужно суммировать все значения в столбце «Зачисление». Для получения значения «Отозвал, Всего» — суммируете значения в столбце «Отзыв». «Сальдо» определяется как разница между «Отозвал, Всего» и «Зачислил, Всего». В том, что сальдо отрицательное, нет ничего страшного, если вы вносите на счёт намного большие суммы, чем отзываете. Важнее другое: сумма сальдо и текущего баланса счёта должна быть положительной. Это означает, что в случае, если вы закроете счёт и выведете все деньги, то окажетесь с прибылью. В нашем примере 31.12.18 года на счёте было 820 тысяч рублей, сальдо равнялось − 606 тысяч. Если бы вы закрыли счёт и вывели все деньги в последний день 2018 года, то ваша прибыль составила бы 214 тысяч рублей. Это накопленный итог за всё время инвестирования.

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

Одна полезная функция, встроенная в Excel — возможность фиксировать строки, чтобы они не прокручивались. Обратите внимание, что на рисунке после третьей строки идёт 121-я. Фиксация строк делает работу с длинными таблицами намного удобнее.

2. Состав портфеля

Следующий лист в книге — «Состав портфеля на отчётную дату». В нашем примере отчётными датами выступают последние дни кварталов.

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

Лист с составом портфеля позволяет отслеживать динамику акций. Если вы собираетесь проводить ребалансировку, то столбец с долями акций в портфеле подскажет, как её осуществить.

3. Динамика портфеля по годам

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

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

«Чистая доходность» — это внутренняя норма доходности ваших вложений, учитывающая ввод и вывод денежных средств со счёта. Смысл внутренней нормы доходности поясним на примере.

На рисунке вы видите, что по итогам 2016 года «Чистая доходность» составила 25,52%. Представьте, что вы нашли депозит в банке со ставкой 25,52% и совершили следующие действия:

внесли в начале года 341 246 рублей;
в течение года внесли суммарно 212 600 рублей, такими же частями и в те же даты, что и на брокерский счёт;
в течение года вывели 8 360 рублей, такими же частями и в те же даты, что и с брокерского счёта.

В этом случае в конце года на вашем депозите было бы 646 751 рубль.

При отслеживании динамики портфеля по годам желательно сравнивать «Чистую доходность» портфеля с индексом Мосбиржи. Об этом читайте в статье « Почему стоит ориентироваться на индекс при покупке акций ».

4. Учёт дивидендов и купонов

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

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