Учёт портфеля в Excel и Google Sheets: что считать инвестору
Учёт портфеля в Excel или Google Sheets нужен не для красивой диаграммы. Он нужен, чтобы инвестор видел стоимость позиции, среднюю цену, комиссии, налоги, денежные выплаты, отклонение от целевых долей и реальную доходность по периодам.
Если таблица показывает только текущую сумму, это не учёт, а витрина. В плохой месяц такая витрина не отвечает на главный вопрос: вы потеряли деньги из-за рынка, из-за лишних сделок, из-за налога, из-за комиссии или из-за того, что портфель давно ушёл от плана.
- Минимальная таблица портфеля должна разделять сделки, денежные потоки, текущие позиции и целевые доли. Один общий лист быстро превращается в бухгалтерский туман.
- Средняя цена считается только по покупкам с учётом комиссий. Продажи не должны механически переписывать историю входа.
- Купоны, дивиденды и выплаты фондов лучше вести отдельным денежным потоком — иначе доходность портфеля смешивается с пополнениями и выводами.
- Для годовой доходности по нерегулярным датам удобна функция xirr — она считает результат с учётом дат покупок, продаж и поступлений.
- НДФЛ нельзя прятать в общей строке расходов. Налог с прибыли и налог с выплат меняют чистый результат по-разному.
Сначала разделите таблицу на четыре листа
Хороший учёт начинается не с формулы, а с архитектуры. Одно полотно на 50 столбцов кажется удобным первую неделю. Потом в нём появляются ручные правки, скрытые строки, сломанные ссылки и неизбежное русское слово потом разберусь. Рынок такие долги помнит.
Минимальная рабочая модель состоит из четырёх листов.
| Лист | Что хранит | Зачем нужен |
|---|---|---|
| Сделки | Покупки, продажи, комиссии, НКД, валюта расчёта | Рассчитать количество, среднюю цену и реализованный результат |
| Денежные потоки | Пополнения, выводы, купоны, дивиденды, выплаты фондов, налоги | Посчитать доходность и отделить рынок от личных переводов |
| Позиции | Текущее количество, цена, дата цены, рыночная стоимость | Видеть текущую структуру портфеля |
| Целевые доли | Плановая доля, фактическая доля, отклонение, действие | Управлять ребалансировкой без эмоций |
Сделки — исторический журнал, а не место для мнений
В листе сделок каждая строка должна отвечать на простой вопрос: что, когда, сколько и за какие деньги произошло. Не нужно писать комментарии вроде удачная покупка или паническая продажа. Через полгода эти слова бесполезны.
Полезные поля — дата, счёт, тикер или название инструмента, операция, количество, цена, сумма сделки, комиссия брокера, НКД для облигаций, валюта, биржа или площадка, номер отчёта брокера.
Денежные потоки показывают, что сделал инвестор
Пополнение счёта не является доходностью. Вывод денег не является убытком. Купон не равен прибыли портфеля, если в тот же день цена облигации снизилась.
Поэтому денежные потоки лучше вести отдельно. Для расчёта доходности покупка инструмента обычно записывается как отрицательный поток, продажа и выплаты — как положительный. Текущая рыночная стоимость портфеля в конце периода добавляется как положительный поток на дату оценки.
Позиции должны иметь дату цены
Цена без даты — ловушка. Если доступная цена фонда относится к 19 июня 2026 года, нельзя подписывать строку как оценку на 26 июня 2026 года. Разница в несколько торговых дней иногда ничего не меняет, а иногда меняет весь вывод.
На листе позиций нужны инструмент, количество, текущая цена, дата цены, рыночная стоимость, доля в портфеле, накопленная прибыль или убыток, тип актива. Для фондов и облигаций полезно добавить признак — накопительный инструмент или инструмент с денежными выплатами.
Сделки: средняя цена и комиссии
Средняя цена нужна не для успокоения. Она нужна, чтобы понять, где начинается прибыль после комиссий и как продажа повлияет на налоговую базу. У многих инвесторов таблица ломается именно здесь — комиссия живёт отдельно, средняя цена отдельно, налог потом удивляет.
Формула для покупок
Для простой позиции без частичных продаж базовая формула выглядит так:
средняя цена = (сумма покупок + комиссии покупок) / количество купленных бумаг
Посмотрите на простой пример.
| Покупка | Количество | Цена | Комиссия | Стоимость с комиссией |
|---|---|---|---|---|
| 1 | 100 | 100 ₽ | 30 ₽ | 10 030 ₽ |
| 2 | 50 | 110 ₽ | 20 ₽ | 5 520 ₽ |
| Итого | 150 | — | 50 ₽ | 15 550 ₽ |
Средняя цена — 15 550 ₽ / 150 = 103,67 ₽.
В Excel и Google Sheets это удобно считать через произведение количества и цены. В англоязычной справке такая логика часто строится через sumproduct; в русской локали Excel название функции может отличаться, но смысл тот же — умножить массивы и сложить результат.
Условная формула выглядит так.
(сумма(количество × цена) + сумма(комиссии)) / сумма(количества)
Частичная продажа не должна портить историю
Если инвестор купил 150 бумаг по средней 103,67 ₽ и продал 40, средняя цена оставшихся 110 бумаг не становится рыночной ценой продажи. Она остаётся базой учёта для оставшегося количества, если вы ведёте простой средневзвешенный управленческий учёт.
Для налоговой логики брокер может считать результат по правилам, которые отличаются от вашей управленческой таблицы. Поэтому таблица не заменяет отчёт брокера. Она помогает заранее видеть порядок цифр и не путать бумажную прибыль, реализованную прибыль и чистый денежный поток.
Комиссии, спред и скрытая цена оборота
В портфельном учёте комиссия — это не мелочь после запятой. Она показывает цену поведения. Чем чаще инвестор переставляет позиции, тем больше таблица должна напоминать ему: каждая сделка имеет билет в один конец.
Минимально разделяйте три вида издержек:
| Издержка | Где появляется | Как учитывать |
|---|---|---|
| Комиссия брокера | В отчёте по сделке | Добавлять к цене покупки или вычитать из результата продажи |
| Биржевой и расчётный сбор | В детализации сделки, если выделен | Включать в полную стоимость операции |
| Спред | В разнице между лучшей покупкой и продажей | Не всегда виден в отчёте, но влияет на фактическую цену входа и выхода |
Комиссия в 0,05% выглядит несерьёзно. Но если портфель на 1 000 000 ₽ полностью обернулся четыре раза за год, оборот составил 4 000 000 ₽. Только видимая комиссия при такой ставке даст 2 000 ₽ расходов, без учёта спреда и налоговых последствий.
Это не призыв ничего не делать. Это просьба к таблице говорить правду — активность стоит денег.
НДФЛ, купоны, дивиденды и выплаты фондов
Налоговый лист нужен даже тем, у кого брокер всё удерживает автоматически. Брокер считает и удерживает налог по своим данным. Инвестор должен понимать, почему чистый результат отличается от доходности на графике.
НДФЛ отделяет прибыль от покупательной способности
НДФЛ платится с номинального финансового результата. Инфляция сама по себе не уменьшает налоговую базу. Если бумага куплена за 100 000 ₽ и продана за 130 000 ₽, налоговая прибыль до учёта комиссий и льгот считается от прироста, а не от реальной покупательной способности этих денег.
В таблице полезно держать поля для реализованной прибыли, удержанного НДФЛ, зачтённого убытка, применённой льготы и года налогового периода. Для индивидуального случая всё равно нужен отчёт брокера и, при споре, налоговый документ. Таблица — инструмент контроля, не юридическое заключение.
Купоны и дивиденды — денежный поток, а не бесплатная доходность
Купон по облигации и дивиденд по акции приходят на счёт деньгами. В учёте они должны попадать в лист денежных потоков с датой выплаты и налогом, если он удержан.
Для БПИФ логика зависит от типа фонда. Многие фонды накапливают доход внутри стоимости пая — купоны и дивиденды отражаются через СЧА и цену пая, а не через отдельную выплату инвестору. Фонды с выплатой дохода, наоборот, создают отдельный денежный поток.
Подробнее про механику накопления дохода можно сверить со статьёй о реинвестировании внутри БПИФ, а налоговую рамку — с разбором НДФЛ по БПИФ.
Целевые доли и ребалансировка
Портфель без целевых долей — это коллекция бумаг. В спокойный год разница почти не видна. В год резкого движения рынка выясняется, что акции выросли до половины портфеля, облигации скукожились, золото стало случайной страховкой, а денежный резерв куда-то исчез.
Целевые доли лучше задавать не по тикерам, а по задачам — акции для роста, облигации для предсказуемости, денежный рынок для резерва, золото или валюта для защиты от отдельных шоков. Конкретные инструменты меняются. Задачи остаются.
| Актив | Целевая доля | Фактическая доля | Отклонение | Что проверить |
|---|---|---|---|---|
| Акции | 40% | 47% | +7 п.п. | Не стала ли позиция слишком крупной после роста |
| Облигации | 35% | 31% | −4 п.п. | Не нужно ли направить новые взносы сюда |
| Денежный рынок | 15% | 12% | −3 п.п. | Хватает ли резерва на ближайшие расходы |
| Золото | 10% | 10% | 0 п.п. | Ничего не делать без причины |
Ребалансировка через взносы дешевле продаж
Самый чистый способ ребалансировки — направлять новые деньги в отстающий класс активов. Продажа создаёт комиссии, возможный налог и риск купить обратно дороже. В таблице для этого нужна строка действие — купить, держать, пополнить без продажи, сократить.
Если портфель ведётся на ИИС, отдельные ограничения и налоговые последствия лучше смотреть заранее. Для этой темы есть отдельный материал про ребалансировку на ИИС-3.
Доходность по периодам: что считать
Одна доходность на весь портфель почти ничего не объясняет. Нужны минимум три слоя — результат за месяц, результат с начала года и годовая доходность по денежным потокам. Иначе инвестор путает успех рынка с размером пополнений.
Простая доходность подходит только для короткой проверки
Простая формула выглядит так.
доходность = (стоимость на конец периода + выводы - пополнения - стоимость на начало периода) / стоимость на начало периода
Она полезна для быстрой сверки месяца. Но если в середине периода были крупные пополнения или выводы, результат искажается. Внесли 300 000 ₽ перед ростом рынка — портфель вырос не потому, что стратегия стала гениальной.
xirr считает годовую доходность по датам потоков
Для нерегулярных покупок, продаж и выплат лучше использовать xirr. Эта функция считает внутреннюю норму доходности по денежным потокам с конкретными датами. В Google Sheets синтаксис выглядит как xirr(суммы; даты), в Excel смысл тот же; разделитель аргументов зависит от локали.
Упрощённый набор потоков может выглядеть так.
| Дата | Поток | Почему знак такой |
|---|---|---|
| 2026-01-10 | −100 000 ₽ | Покупка или пополнение для инвестиций |
| 2026-03-15 | −50 000 ₽ | Дополнительная покупка |
| 2026-06-20 | 4 000 ₽ | Купон, дивиденд или выплата фонда |
| 2026-07-03 | 166 000 ₽ | Текущая стоимость портфеля на дату оценки |
Формула возвращает годовую ставку результата по этим датам. Это не прогноз. Это способ честно сравнить периоды, где деньги входили и выходили неравномерно.
Сравнение с целью важнее сравнения с соседом
Для частного инвестора полезнее вопрос не обогнал ли он всех, а выдерживает ли портфель свою задачу. Если цель — накопить 1 000 000 ₽ к определённой дате, таблица должна считать требуемый ежемесячный взнос и факт выполнения плана. Если цель — сохранить резерв, доходность вторична.
Практический подход к расчёту цели разобран отдельно в статье о том, сколько откладывать через БПИФ. А если нужна общая структура портфеля, можно начать с категории модельных портфелей.
Что должно быть в итоговой строке
Итоговая строка портфеля должна быть скучной. Скука в учёте — признак здоровья.
Проверьте, что в ней есть всё необходимое.
- рыночная стоимость на конкретную дату;
- внесённый капитал за весь период;
- выведенный капитал за весь период;
- полученные выплаты до и после налога;
- реализованная прибыль или убыток;
- нереализованная прибыль или убыток;
- уплаченные комиссии;
- удержанный НДФЛ;
- доходность за месяц, с начала года и годовая доходность по xirr;
- отклонение от целевых долей.
Если какой-то показатель нельзя объяснить словами, его лучше не выводить на главный экран. Таблица должна помогать решать, а не производить впечатление.