Инвестиции для начинающих: как считать доходность портфеля в Excel
Инвестиции для начинающих: как считать доходность портфеля в Excel
Есть миф о том, что инвестирование в России — это сложно и только для богачей. На самом деле:
- Вы можете открыть счёт за 10 минут в приложении брокера.
- Вы можете начать со 100 рублей (хотя смысла в этом мало).
- Вы можете считать доходность портфеля в Excel за пять минут.
Проблема не в сложности инвестирования. Проблема в том, что большинство начинающих инвесторов не могут ответить на простой вопрос: "Сколько я заработал?"
Они видят на экране "Портфель +15%", но не понимают, что это значит. 15% от какой суммы? 15% за какой период? А если я добавил деньги в середине года, это ещё 15%?
В этой статье я покажу, как все это считается в Excel, и почему цифры в приложении брокера часто не совпадают с вашей интуицией.
Вступление: с чего начать инвестировать в России
Если вы в России и хотите начать инвестировать, у вас есть несколько путей:
1. Брокерский счёт
- Открывается за 10 минут в приложении Sberbank Invest, Тинькофф Инвестиции, ВТБ, БКС и других.
- Вы можете торговать акциями, облигациями, ETF.
- Комиссия брокера: 0-0.1% на каждую сделку.
- Налог: НДФЛ 13% на прибыль физического лица.
2. Индивидуальный инвестиционный счёт (ИИС)
- Это специальный счёт с налоговыми льготами.
- Вы можете вносить до 1 000 000 рублей в год.
- Вычет типа А: вы получаете возврат 13% от внесённой суммы (максимум 52 000 рублей в год).
- Вычет типа Б: вы не платите налоги на прибыль, если закроете счёт через три года.
- Лучший вариант для начинающих.
3. Фонды (ПИФы, ETF)
- Это готовые портфели, которые управляет профессионал.
- Вы просто вносите деньги и получаете прибыль/убыток.
- Комиссия: 0.5-2% в год.
Я рекомендую начать с ИИС + готовых ETF (exchange-traded funds) на индексы акций и облигаций. Это просто, дешево и понятно.
Сценарий 1: "Я открыл ИИС, теперь что считать"
Представьте, вы открыли ИИС и внесли деньги:
| Дата | Операция | Сумма | Остаток на счёте |
|---|---|---|---|
| ------------ | -------------- | ---------- | ------------------ |
| 1.01.2024 | Пополнение | 100000 | 100000 |
| 15.03.2024 | Пополнение | 50000 | 150000 |
| 30.06.2024 | Пополнение | 100000 | 250000 |
| 30.12.2024 | - | - | 287500 |
В конце года на счёте 287 500 рублей. Вы внесли 250 000 рублей. Остаток: 37 500 рублей.
Вопрос: это прибыль 37 500 рублей? Или это 37 500 / 250 000 = 15%?
Ответ: не совсем. Потому что деньги попали на счёт в разные моменты времени.
Сценарий 2: "Как считать простую доходность (неправильно)"
Первый способ считать доходность — это неправильный способ, но интуитивный:
Доходность = (Конечная стоимость - Начальная стоимость) / Начальная стоимость
Доходность = (287500 - 100000) / 100000 = 187500 / 100000 = 187.5%
Это неправильно, потому что вы внесли ещё деньги в течение года. Вы считаете доходность только от начального взноса, а остальные взносы игнорируете.
Сценарий 3: "Как считать простую доходность (правильно)"
Второй способ — считать доходность на среднее остаток:
Средний остаток = (100000 + 150000 + 250000) / 3 = 166667
Доходность = (287500 - 250000) / 166667 = 37500 / 166667 = 22.5%
Это лучше, чем первый способ, но всё ещё неправильно, потому что вы не учитываете, как долго каждый взнос был на счёте.
100 000 рублей были на счёте 12 месяцев, а последние 100 000 рублей только полгода. Они должны иметь разный "вес" при расчёте.
Сценарий 4: "Как считать доходность правильно — TIME-WEIGHTED RETURN"
Это сложный способ, но правильный. Идея: вы игнорируете взносы и считаете доходность только в промежутки между взносами.
| Период | Стоимость начало | Стоимость конец | Доходность |
|---|---|---|---|
| --------------------------- | ------------------ | ----------------- | ----------- |
| 1.01 - 15.03 (73 дня) | 100000 | 150000 | 50% |
| 15.03 - 30.06 (107 дней) | 150000 | 250000 | 66.7% |
| 30.06 - 30.12 (184 дня) | 250000 | 287500 | 15% |
Потом вы считаете доходность за весь период, учитывая весовое время:
Доходность = (1 + 50%) × (1 + 66.7%) × (1 + 15%) - 1 = 1.5 × 1.667 × 1.15 - 1 = 188.5% - 1 = 188.5% ... [неправильно]
На самом деле это сложно считать в Excel, потому что нужно отслеживать даты между взносами.
Сценарий 5: "Как считать доходность правильно — XIRR (внутренняя норма доходности)"
Есть функция в Excel, которая делает это за вас: XIRR (или ВСД в русском Excel). Это "внутренняя норма доходности" — это ставка, при которой все ваши взносы и выводы, если расставить их на временной оси, дают конечную стоимость портфеля.
Создайте таблицу:
| Дата | Операция | Сумма | (со знаком) |
|---|---|---|---|
| ------------ | -------------- | ------------ | ------------ |
| 1.01.2024 | Пополнение | 100000 | -100000 |
| 15.03.2024 | Пополнение | 50000 | -50000 |
| 30.06.2024 | Пополнение | 100000 | -100000 |
| 30.12.2024 | Стоимость | 287500 | 287500 |
Важно: взносы пишутся со знаком минус (это деньги, которые вы дали), а конечная стоимость со знаком плюс (это деньги, которые вы получили бы, если бы закрыли счёт).
Формула XIRR в Excel:
=XIRR(Диапазон_сумм, Диапазон_дат)
Или на примере:
=XIRR(A:A, B:B)
Если в Excel ошибка "Имя?", используйте ВСД вместо XIRR (русская версия):
=ВСД(A:A, B:B)
Результат: примерно 17.3% в год.
Это означает, что если вы инвестировали так, как показано в таблице, ваша годовая доходность составит 17.3%.
Сценарий 6: "Как считать взвешенную доходность по времени"
Есть ещё один способ, который часто используют профессионалы: Money-Weighted Return или "доходность, взвешенная по деньгам". Это то, что считает XIRR.
Но есть и "доходность, взвешенная по времени" (Time-Weighted Return) — это когда вы игнорируете взносы и смотрите только на изменение стоимости активов.
Для простого примера:
| Дата | Стоимость портфеля | Взнос | Доходность в период |
|---|---|---|---|
| ------------ | ------------------- | ------- | ------------------- |
| 1.01.2024 | 0 | 100000 | - |
| 15.03.2024 | 150000 | 50000 | (150000-100000)/100000 = 50% |
| 30.06.2024 | 250000 | 100000 | (250000-150000-100000)/150000 = 0% |
| 30.12.2024 | 287500 | 0 | (287500-250000)/250000 = 15% |
Доходность за весь период:
= (1 + 50%) × (1 + 0%) × (1 + 15%) - 1 = 1.5 × 1.0 × 1.15 - 1 = 72.5%
Это 72.5% за весь период, но какова годовая доходность?
Годовая доходность = (1 + 72.5%)^(365/365) - 1 = 72.5%
(Потому что весь период это один год)
Но это отличается от XIRR (17.3%), потому что Time-Weighted Return игнорирует факт, что последний взнос был сделан только 30 июня, а не в начале года.
Для простоты рекомендую использовать XIRR — это одна формула, и она учитывает все нюансы.
Сценарий 7: "Мой портфель из разных активов — как считать общую доходность"
Представьте, вы инвестировали в несколько активов:
| Актив | Сумма | Текущая стоимость | Прибыль/убыток |
|---|---|---|---|
| -------------------- | ---------- | ------------------- | ---------------- |
| Яндекс (акция) | 50000 | 62500 | +12500 (25%) |
| ОФЗ (облигация) | 30000 | 31500 | +1500 (5%) |
| ВТБ (акция) | 20000 | 18000 | -2000 (-10%) |
| RSTI (ETF акций) | 40000 | 46000 | +6000 (15%) |
| GLDRUB (ETF золота) | 10000 | 10800 | +800 (8%) |
| ИТОГО | 150000 | 168800 | +18800 (12.5%) |
Как считать общую доходность?
Вариант 1: просто разделите прибыль на вложение
=18800 / 150000 = 12.5%
Это работает, если вы внесли все деньги сразу. Но если вы вносили деньги в разное время, используйте XIRR (как в сценарии 5).
Вариант 2: взвешенная доходность (если вы хотите видеть, какие активы вас "тянут" вверх)
Каждый актив имеет вес в портфеле. Его вес — это процент от общей суммы.
| Актив | Сумма | Вес | Доходность | Вклад в портфель |
|---|---|---|---|---|
| -------------------- | ------- | ------- | ------------ | ------------------ |
| Яндекс | 50000 | 33.3% | 25% | 33.3% × 25% = 8.3% |
| ОФЗ | 30000 | 20% | 5% | 20% × 5% = 1% |
| ВТБ | 20000 | 13.3% | -10% | 13.3% × -10% = -1.3% |
| RSTI | 40000 | 26.7% | 15% | 26.7% × 15% = 4% |
| GLDRUB | 10000 | 6.7% | 8% | 6.7% × 8% = 0.5% |
| ИТОГО | 150000 | 100% | - | 8.3% + 1% - 1.3% + 4% + 0.5% = 12.5% |
Формулы:
- Вес:
=Сумма / СУММА(все_суммы) - Вклад в портфель:
=Вес × Доходность
Это помогает вам видеть, какие активы "тянут" портфель вверх, а какие вниз.
Сценарий 8: "Как считать дивиденды и купоны"
Если вы инвестируете в акции, вы можете получать дивиденды. Если вы инвестируете в облигации, вы получаете купоны.
Пример: вы купили 100 акций Сбербанка по 300 рублей = 30 000 рублей.
Через полгода:
- Цена акции выросла до 320 рублей. Прибыль от роста: (320 - 300) × 100 = 2000 рублей.
- Вам выплатили дивидендов: 8 рублей на акцию × 100 = 800 рублей.
- Общая прибыль: 2000 + 800 = 2800 рублей
- Доходность: 2800 / 30 000 = 9.3% за полгода
В XIRR дивиденды обрабатываются как промежуточные платежи:
| Дата | Операция | Сумма | (со знаком) |
|---|---|---|---|
| ------------ | ------------------- | -------- | ------------ |
| 1.06.2024 | Покупка акций | 30000 | -30000 |
| 15.08.2024 | Дивиденд получен | 800 | +800 |
| 30.12.2024 | Продажа + остаток | 32800 | +32800 |
Формула XIRR:
=XIRR(A:A, B:B)
Результат: примерно 19.2% в год (потому что 9.3% за полгода это примерно 19.2% в год).
Если вы получаете дивиденды регулярно (ежеквартально или ежегодно), вносите их в таблицу как отдельные строки. XIRR автоматически учтёт, что вы получали деньги в течение периода.
Сценарий 9: "Как считать реальную доходность (с учётом инфляции)"
Номинальная доходность — это то, что показывает ваш брокер (12.5% в примере выше).
Реальная доходность — это доходность минус инфляция.
Если инфляция в России за год 5%, то ваша реальная доходность:
Реальная доходность = (1 + 12.5%) / (1 + 5%) - 1 = 1.125 / 1.05 - 1 = 7.1%
Формула:
=(1 + Номинальная_доходность) / (1 + Инфляция) - 1
Это важно, потому что 12.5% звучит хорошо, но если инфляция 10%, то реальная доходность всего 2.3%.
Сценарий 10: "Как отслеживать портфель месяц за месяцем"
Создайте таблицу, где каждая строка — месяц:
| Дата | Добавлено | Стоимость портфеля | XIRR за период | Комментарий |
|---|---|---|---|---|
| ---------- | ----------- | ------------------- | ---------------- | ------------ |
| 1.01.2024 | 100000 | 100000 | - | Начало |
| 1.02.2024 | 0 | 95000 | -5% | Коррекция |
| 1.03.2024 | 50000 | 155000 | 3.1% | Добавил денег |
| 1.04.2024 | 0 | 180000 | 15.2% | Рост |
| 1.05.2024 | 0 | 170000 | 9.8% | Коррекция |
| 1.06.2024 | 100000 | 285000 | 12.3% | Добавил денег |
На каждый месяц считайте XIRR от начала инвестирования до этого месяца. Это покажет вам, как меняется ваша доходность со временем.
Важно понимать, что XIRR будет прыгать в зависимости от цены активов и размера взносов. Это нормально.
Сценарий 11: "Сравнение с индексом"
Профессиональные инвесторы сравнивают свою доходность с индексом — например, индексом Мосбиржи (MOEX).
Если ваша доходность 12.5%, а доходность индекса 8%, значит, вы "переиграли" индекс на 4.5%. Это хорошо.
Если ваша доходность 12.5%, а доходность индекса 15%, значит, вы "недоиграли" индекс на 2.5%. Это плохо.
Для сравнения:
| Показатель | Значение |
|---|---|
| ---------------------- | ---------- |
| Доходность портфеля | 12.5% |
| Доходность индекса | 8% |
| Переиграла индекс | 4.5% |
Где взять доходность индекса? На сайте Мосбиржи есть исторические данные. Или просто инвестируйте в ETF на индекс (например, RSTI или POSI) и сравнивайте с его доходностью.
Сценарий 12: "Как считать годовую доходность из месячных"
Если вы знаете доходность за месяц, вы можете примерно посчитать годовую:
Годовая доходность ≈ (1 + Месячная доходность)^12 - 1
Пример: если в январе вы заработали 2%, то примерно за год:
= (1 + 2%)^12 - 1 = 1.02^12 - 1 = 26.8%
Важно: это работает, если каждый месяц вы будете зарабатывать ровно 2%. На практике доходность прыгает, поэтому это только грубое приближение.
Лучше считать годовую доходность по XIRR, чем экстраполировать из одного месяца.
Сценарий 13: "Налоги на инвестиции"
На обычном брокерском счёте:
- Вы платите НДФЛ 13% на прибыль от продажи акций.
- Вы платите НДФЛ 13% на дивиденды.
- Налог рассчитывается и платится автоматически.
На ИИС с вычетом типа Б:
- Вы не платите НДФЛ на прибыль и дивиденды.
- Условие: счёт должен быть открыт не менее 3 лет.
Это делает ИИС очень привлекательным.
Пример:
- Вы внесли 1 000 000 рублей на ИИС под вычет типа А.
- Вы получаете возврат: 1 000 000 × 13% = 130 000 рублей (это вычет налогов, которые вы уплатили в течение года).
- Через 3 года счёт вырос до 1 200 000 рублей.
- Вы закрываете счёт.
- Прибыль: 200 000 рублей.
- Налоги на прибыль: 0 (потому что это ИИС с вычетом типа Б).
- Итого вы получили: 130 000 (вычет) + 200 000 (прибыль) = 330 000 рублей.
На обычном счёте вы бы получили только: 200 000 - 200 000 × 13% = 174 000 рублей. Разница огромная.
Ключевые формулы для инвестирования
1. Простая доходность:
=(Конечная_стоимость - Начальная_сумма) / Начальная_сумма
2. Доходность с учётом взносов (XIRR):
=XIRR(Диапазон_сумм, Диапазон_дат)
3. Взвешенная доходность портфеля:
=SUM(Вес × Доходность_актива)
4. Годовая доходность из месячной:
=(1 + Месячная)^12 - 1
5. Реальная доходность:
=(1 + Номинальная) / (1 + Инфляция) - 1
6. Прибыль от акции с дивидендом:
=(Цена_конец - Цена_начало) × Количество + Дивидендов_получено
Теория: почему большинство начинающих инвесторов совершают ошибки
1. Они сравнивают абсолютные цифры
"Я купил акцию за 100 рублей, она выросла на 10 рублей, я заработал 10%".
Это правильно только если вы внесли ровно 100 рублей и больше ничего. Если вы вносили деньги в процессе, это не работает.
2. Они игнорируют время
"Я заработал 20 000 рублей за полгода, значит, за год я заработаю 40 000".
Это не работает, потому что доходность сложная, а не линейная. Если вы заработали 20% за полгода, то за год это не 40%, а (1.2)^2 - 1 = 44%.
3. Они не считают общую доходность портфеля
"Акция выросла на 25%, облигация на 5%, я очень доволен".
Но если 80% портфеля в облигациях и 20% в акциях, то общая доходность только 8.5%, а не 15%.
4. Они забывают про дивиденды и купоны
"Акция упала на 5%, но я получил дивидендов на 3%, итого я в минусе на 2%".
На самом деле, если в начале года вы получили дивиденд, а потом цена упала, ваша реальная доходность может быть близка к нулю или даже положительной, потому что дивиденд был получен раньше падения цены.
Как начать
- Откройте ИИС в любом крупном брокере (Тинькофф, Sberbank Invest, ВТБ).
- Пополните счёт на сумму, которую вы можете позволить себе не трогать 3 года.
- Инвестируйте в 2-3 ETF:
- Один на акции России (например, RSTI)
- Один на облигации (например, GLDRUB или ОФЗ прямо)
- Можно третий на иностранные акции (например, FXUS или FXCN)
- Создайте в Excel таблицу с датами пополнений и текущей стоимостью.
- Один раз в год считайте XIRR и смотрите, как растёт ваша доходность.
Если вы хотите упростить отслеживание, используйте наш Трекер инвестиций. Там уже встроены XIRR, взвешенная доходность, сравнение с индексом и налоговое планирование.
Но даже в обычном Excel вы можете все это считать. Главное — начать инвестировать и отслеживать доходность, а не полагаться на "ощущения".
А отслеживать доходность — это половина успеха инвестирования.
Начните с малого, не спешите и помните: инвестирование это не азартная игра. Это скучный, долгий процесс добавления денег каждый месяц и того, что цена растёт медленнее, чем вам хочется. И именно скучность — это ваш главный друг.