1. Функция БС (будущая стоимость)

Функция БС (в английской версии FV) рассчитывает будущую стоимость инвестиции при постоянной процентной ставке и периодических равных платежах. Синтаксис:

• ставка – процентная ставка за период;
• кпер – общее число периодов;
• плт – платёж, производимый в каждый период (обычно неизменный);
• пс (необязательный) – текущая стоимость (начальный взнос). По умолчанию 0;
• тип – 0, если платёж в конце периода (обычный аннуитет), 1 – в начале периода.

Пример 1. Вы открываете вклад на 100 000 ₽ под 10% годовых на 5 лет с ежегодной капитализацией (без дополнительных взносов). Формула: =БС(10%;5;0;-100000). Результат: 161 051 ₽.

Пример 2 (с регулярными взносами). Вы ежемесячно откладываете по 5000 ₽ в течение 3 лет под 8% годовых с ежемесячной капитализацией. Ставка за месяц = 8%/12, число периодов = 36, текущей стоимости нет. Формула: =БС(8%/12;36;-5000;0;0) ≈ 202 710 ₽.

2. Функция ПС (приведённая стоимость)

Функция ПС (PV) вычисляет текущую стоимость инвестиции (сколько нужно вложить сейчас, чтобы получить заданную сумму в будущем). Синтаксис:

• бс – желаемая будущая стоимость.

Пример 3. Сколько нужно положить сегодня под 10% годовых, чтобы через 5 лет получить 200 000 ₽? =ПС(10%;5;0;-200000) ≈ 124 184 ₽.

Пример 4 (аннуитет). Оцените текущую стоимость арендных платежей: ежегодно 150 000 ₽ в течение 10 лет при ставке 12%. =ПС(12%;10;-150000;0;0) ≈ 847 525 ₽.

3. Функция ПЛТ (аннуитетный платёж)

Функция ПЛТ (PMT) возвращает сумму периодического платежа для погашения кредита при постоянной ставке и равных взносах. Синтаксис:

Пример 5. Ипотека 3 млн ₽ на 5 лет (60 мес.) под 12% годовых (1% в месяц). Ежемесячный платёж: =ПЛТ(12%/12;60;3000000;0;0) ≈ –66 729 ₽. Отрицательное число – расход.

С помощью ПЛТ можно легко построить график платежей, разложив его на процентную часть и основной долг (используя функции ПРПЛТ и ОСПЛТ).

4. Функция ЧИСТНДОХ (XIRR) – доходность нерегулярных потоков

Функция ЧИСТНДОХ (в английской версии XIRR) вычисляет внутреннюю норму доходности для денежных потоков, которые происходят в произвольные даты. Это незаменимый инструмент для расчёта реальной доходности инвестиций с неравномерными поступлениями.

Синтаксис:

• значения – массив денежных потоков (отрицательные – вложения, положительные – поступления);
• даты – массив дат, соответствующих каждому потоку;
• предположение (необязательно) – начальное приближение (например, 0,1).

Пример 6. Вы инвестировали 100 000 ₽ 01.01.2025. Затем получили 30 000 ₽ 01.07.2025 и 110 000 ₽ 01.01.2026. Заполните столбцы: даты и суммы. Формула =ЧИСТНДОХ(диапазон_сумм; диапазон_дат) вернёт, скажем, 18,5% годовых.

ЧИСТНДОХ приводит все потоки к единому знаменателю и выдаёт эффективную годовую ставку, учитывая точное время.

5. Как построить график платежей в Excel

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

1. Создайте столбцы: «Период», «Остаток долга», «Платёж», «Проценты», «Основной долг».
2. В первый период остаток долга = сумме кредита. Проценты = остаток · ставка_периода.
3. Основной долг = ПЛТ – проценты.
4. Новый остаток = старый остаток – основной долг.
5. Протяните формулы на весь срок.

Можно также использовать функции ОСПЛТ и ПРПЛТ, которые сразу возвращают эти суммы. =ОСПЛТ(ставка; период; кпер; пс) возвращает выплату основного долга за конкретный период; =ПРПЛТ(ставка; период; кпер; пс) – сумму процентов.

Пример: для ипотеки 3 млн на 60 мес. под 12% годовых, платёж найден выше. В Excel легко создать график и проверить, что общая сумма процентов совпадает с расчётами.

Заключение

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

Дополнительные материалы

• Аннуитетные платежи: как банки считают ипотеку
• Номинальная и эффективная процентная ставка
• Реальная доходность: побеждаем инфляцию