7 ошибок в Excel, из-за которых инвестор может неправильно посчитать свой портфель

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

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

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

Разберём семь распространённых ошибок на конкретных примерах.

Ошибка №1. Неправильно считать среднюю цену покупки

Допустим, акции одной компании покупались два раза.

Первая покупка:

10 акций по 200 ₽.

Вторая:

100 акций по 300 ₽.

Кажется логичным сложить цены и разделить пополам:

(200 + 300) / 2 = 250 ₽

Но это неправильная средняя цена.

В первой сделке куплено всего 10 акций, а во второй — 100.

Поэтому нужно учитывать количество бумаг.

Первая покупка:

10 × 200 = 2 000 ₽

Вторая:

100 × 300 = 30 000 ₽

Всего вложено:

32 000 ₽

Количество акций:

Средняя цена:

32 000 / 110 = 290,91 ₽

Разница огромная:

не 250 ₽, а почти 291 ₽.

Представим, что акция сейчас стоит 280 ₽.

При неправильном расчёте таблица покажет прибыль:

280 − 250 = +30 ₽ на акцию.

Кажется, позиция находится в плюсе.

На самом деле средняя цена — 290,91 ₽, поэтому позиция всё ещё убыточна.

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

Ошибка №2. Считать пополнение счёта прибылью

В начале года на брокерском счёте находилось:

500 000 ₽

К концу года:

800 000 ₽

Очень хочется написать:

800 000 − 500 000 = 300 000 ₽ прибыли

или:

300 000 / 500 000 × 100 = 60% доходности

Но допустим, в течение года инвестор самостоятельно внёс ещё 250 000 ₽.

Тогда рост стоимости счёта на 300 тысяч совершенно не означает прибыль в 300 тысяч.

Упрощённый финансовый результат:

800 000 − 500 000 − 250 000 = 50 000 ₽

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

Поэтому я бы обязательно держал отдельный лист:

Вводы и выводы

Минимальные столбцы:

Дата | Операция | Сумма

Тогда собственные деньги не смешиваются с инвестиционным результатом.

Ошибка №3. Не учитывать комиссии

Возьмём простую сделку.

Куплено 100 акций по 200 ₽.

Стоимость:

100 × 200 = 20 000 ₽

Через некоторое время акции проданы по 210 ₽.

Получено:

100 × 210 = 21 000 ₽

На первый взгляд прибыль:

1 000 ₽

Но реальные операции проходят с комиссиями.

Допустим, при покупке брокер удержал 60 ₽, а при продаже ещё 63 ₽.

Тогда фактические затраты на приобретение составили:

20 000 + 60 = 20 060 ₽

После продажи получено:

21 000 − 63 = 20 937 ₽

Результат:

20 937 − 20 060 = 877 ₽

Разница со «школьным» расчётом — 123 ₽.

На одной сделке это может показаться мелочью.

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

Особенно у активного инвестора.

Поэтому в журнале сделок я бы всегда оставлял отдельный столбец «Комиссия».

Ошибка №4. Дважды прибавлять дивиденды

Это довольно коварная ошибка.

Представим портфель стоимостью 500 000 ₽.

За год компании выплатили 30 000 ₽ дивидендов.

Дивиденды поступили на брокерский счёт и остались там в виде денежных средств.

К концу года брокерский счёт показывает:

570 000 ₽

Инвестор записывает конечную стоимость портфеля 570 тысяч и затем дополнительно прибавляет:

+30 000 ₽ дивидендов.

Получается:

600 000 ₽.

Но это двойной учёт.

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

Другой случай — если дивиденды выводились на банковскую карту.

Тогда их действительно необходимо учитывать отдельно, поскольку в текущей стоимости брокерского счёта этих денег уже нет.

Поэтому важно заранее определить:

входит ли денежный остаток в указанную стоимость портфеля?

Иначе одни и те же деньги легко посчитать дважды.

Ошибка №5. Смотреть только на проценты

Представим две инвестиции.

Первая:

вложено 10 000 ₽;

прибыль +50%.

Результат:

+5 000 ₽

Вторая:

вложено 500 000 ₽;

прибыль +8%.

Результат:

+40 000 ₽

Какая инвестиция сильнее повлияла на портфель?

Конечно, вторая.

Но если таблица отсортирована только по процентной доходности, наверху окажется первая позиция с красивыми +50%.

Из-за этого легко переоценить влияние небольших удачных сделок.

Поэтому рядом с процентной доходностью полезно всегда показывать результат в деньгах:

Доходность, %

и

Результат, ₽

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

Ошибка №6. Неправильно считать долю актива

Представим портфель:

акции — 600 000 ₽;

облигации — 300 000 ₽;

свободные деньги — 100 000 ₽.

Общая стоимость:

1 000 000 ₽

Одна конкретная акция стоит в портфеле 180 000 ₽.

Её доля от всего капитала:

180 000 / 1 000 000 × 100 = 18%

Но иногда при расчёте долей инвестор учитывает только акции.

Тогда:

180 000 / 600 000 × 100 = 30%

Обе цифры математически правильные.

Но означают совершенно разные вещи.

18% — доля позиции во всём портфеле.

30% — доля позиции только в акционной части.

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

Поэтому вместо абстрактного столбца «Доля» лучше использовать конкретные названия:

Доля от всего портфеля

или

Доля среди акций

Особенно это важно, когда в портфеле одновременно находятся акции, облигации, валюта и свободные денежные средства.

Ошибка №7. Считать доходность валютного актива только по его цене

Представим, что инвестор купил иностранный актив.

Цена покупки:

$100

Цена продажи:

$105

Рост:

+5%

Кажется, инвестор заработал 5%.

Но если учёт портфеля ведётся в рублях, результат зависит ещё и от курса валюты.

Допустим, при покупке доллар стоил:

100 ₽

Стоимость позиции:

100 × 100 = 10 000 ₽

При продаже актив стоит $105, но курс доллара снизился до 90 ₽.

Получаем:

105 × 90 = 9 450 ₽

В валюте:

+5%

В рублях:

9 450 − 10 000 = −550 ₽

То есть актив вырос, а инвестор в своей базовой валюте получил убыток.

Возможна и обратная ситуация.

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

Поэтому для валютных активов полезно хранить как минимум:

цену актива;

валюту;

курс на момент операции;

стоимость в базовой валюте портфеля.

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

Ещё одна проблема — неправильное копирование формул

Это уже чисто техническая ошибка Excel, но встречается постоянно.

Представим формулу:

=B2*$F$1

В ячейке F1 находится курс валюты.

Знаки $ фиксируют ссылку на конкретную ячейку.

Если написать:

=B2*F1

и протянуть формулу вниз, Excel начнёт менять ссылку:

F1;

F2;

F3;

F4.

В результате первые строки могут считаться правильно, а остальные — использовать совершенно другие данные.

Особенно неприятно, когда таблица содержит тысячи строк.

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

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

Проверяйте итог другим способом

Есть простой приём, который помогает обнаружить множество ошибок.

Не доверять одному расчёту.

Допустим, таблица утверждает:

За год заработано 127 450 ₽

Попробуем приблизительно проверить результат другим способом.

Берём:

стоимость портфеля на конец года;

плюсуем все выводы;

вычитаем стоимость на начало;

вычитаем все пополнения.

Если получается близкая цифра — хорошо.

Если основная таблица показывает +127 тысяч, а контрольный расчёт +73 тысячи, значит где-то есть проблема.

Возможно:

дважды учтены дивиденды;

пропущено пополнение;

неправильно обработана продажа;

сломалась формула;

не учтена комиссия.

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

Нужно ли для такого учёта именно Excel?

Нет.

Большинство описанных расчётов можно сделать и в других табличных редакторах.

Главная проблема вообще не в программе, а в правильной структуре данных и формулах.

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

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

При этом покупать сложный инструмент ради таблицы из десяти строк смысла нет.

Как бы я построил таблицу портфеля

Чтобы снизить вероятность ошибок, я бы не складывал всё на один лист.

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

Операции

Все покупки и продажи.

Портфель

Текущие позиции, средняя цена и стоимость.

Дивиденды и купоны

Полученные выплаты.

Вводы и выводы

Движение собственных денег.

Курсы валют

Если используются валютные активы.

Так гораздо проще проверить, откуда взялась конкретная цифра.

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

Самая опасная ошибка Excel

На мой взгляд, самая опасная ошибка — не #VALUE!, не #DIV/0! и не сломанная формула.

Такие проблемы сразу заметны.

Гораздо хуже, когда Excel показывает красивую и вполне правдоподобную цифру.

Например:

Доходность портфеля: +24,7%

Человек видит результат и считает его правильным.

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

Таблица работает.

Ошибок Excel не показывает.

Только финансовый результат неверный.

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

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

«А эта цифра вообще может быть правильной?»

Если за год внесено 200 тысяч рублей, портфель вырос с 500 до 750 тысяч, а Excel показывает прибыль 250 тысяч — стоит остановиться и проверить расчёт.

Итог

Excel способен посчитать практически всё, что нужно обычному частному инвестору.

Но программа не понимает экономический смысл наших данных.

Она не знает, что пополнение брокерского счёта — это не прибыль.

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

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

И уж точно не знает, в какой валюте мы хотим измерять результат.

Поэтому хорошая инвестиционная таблица — это не таблица с максимальным количеством формул.

Это таблица, в которой понятно происхождение каждой важной цифры.

А перед тем как радоваться очередным +30% доходности, иногда полезно взять калькулятор и проверить Excel вручную.