Финансовые функции Excel считают платёж по кредиту, ставку, срок, будущую сумму вклада и доходность проекта. Для начинающих хватает десяти: ПЛТ, ПРПЛТ и ОСПЛТ разбирают кредит, СТАВКА, КПЕР, ПС и БС решают обратные задачи, ЧПС, ВСД и ЧИСТВНДОХ оценивают вложения.
Ежемесячный платёж по кредиту даёт формула =ПЛТ(ставка/12;срок_в_месяцах;-сумма). Всё остальное в статье разобрано на одном кредите и одном проекте, чтобы цифры можно было повторить у себя. Как вообще устроены формулы и где искать остальные функции, рассказали в обзоре основных функций Excel.
CheckroiПодборка курсов по Microsoft Excel684 курса • 64 школыСравните цены, школы, программу и найдите выгодные предложения по обучениюСравнить→
10 финансовых функций Excel: таблица
В Excel больше полусотни финансовых функций, полный список есть в справке Microsoft. Большая часть нужна для облигаций и амортизации, а в повседневной работе хватает этих десяти.
| Функция | Имя в английском Excel | Что считает |
|---|---|---|
| ПЛТ | PMT | платёж по кредиту равными суммами |
| ПРПЛТ | IPMT | сколько из платежа уходит на проценты |
| ОСПЛТ | PPMT | сколько из платежа гасит сам долг |
| СТАВКА | RATE | ставку, если известны платёж и сумма |
| КПЕР | NPER | число платежей до полного погашения |
| ПС | PV | сумму, которую можно взять при заданном платеже |
| БС | FV | сколько накопится на вкладе с пополнением |
| ЧПС | NPV | чистую приведённую стоимость проекта |
| ВСД | IRR | внутреннюю доходность проекта |
| ЧИСТВНДОХ | XIRR | доходность, когда деньги приходят в разные даты |
Английские имена пригодятся, если Excel у вас на английском или пример взят из зарубежной справки.
Два правила, без которых формулы врут
Почти все ошибки в финансовых формулах происходят из-за двух вещей.
- Ставка и число периодов в одних единицах. Если платите раз в месяц, делите годовую ставку на 12, а срок пишите в месяцах. Справка к функции ПЛТ приводит пример: при 12 % годовых и ежемесячных выплатах на четыре года ставка равна 12%/12, а число периодов 4*12. Как устроены сами проценты в формулах, показали в разборе процента от числа в Excel.
- Знак показывает направление денег. Деньги, которые вы отдаёте, идут с минусом, деньги, которые получаете, с плюсом. В справке к ПС пример такой: банковский депозит на 1000 ₽ для вкладчика задаётся аргументом −1000.
Третий аргумент, о котором часто забывают, называется «тип». Ноль или пустое место означают платёж в конце периода, единица означает платёж в начале.
Канал основателя Checkroi Вани Буявца3 700 человек читают мой Телеграм-канал про нейросетиСобрал промпты для Claude Code и ChatGPT, разборы ИИ-инструментов и лайфхаки по продвижению бизнеса в одном местеПрисоединитьсяПЛТ: ежемесячный платёж по кредиту
Возьмём кредит на 300 000 ₽ под 18 % годовых на 24 месяца. В ячейке C2 сумма, в C3 ставка, в C4 срок в месяцах.
Синтаксис функции такой: ПЛТ(ставка;кпер;пс;[бс];[тип]). Последние два аргумента необязательные, для обычного кредита их пропускают.
=ПЛТ(C3/12;C4;-C2)Результат: 14 977,23 ₽ в месяц. Минус перед C2 нужен, чтобы платёж вышел положительным: банк дал вам деньги, а вы их возвращаете. Без минуса Excel покажет тот же платёж со знаком «минус» или в скобках.
Скриншоты в статье сделаны в Google Таблицах с английскими региональными настройками. Функции там называются так же, но аргументы разделяет запятая, а дробную часть точка. В русском Excel пишите точку с запятой и дробь через запятую.
Переплату ПЛТ не показывает, но её легко посчитать. Справка к ПЛТ советует умножить платёж на число периодов: 14 977,23 × 24 = 359 453,53 ₽, из них 59 453,53 ₽ проценты. Такой расчёт стоит делать до подписания договора, это одна из базовых привычек финансовой грамотности.
Если платить в начале месяца
Пятый аргумент «тип» равен 1, четвёртый пропускаем:
=ПЛТ(C3/12;C4;-C2;;1)Платёж станет 14 755,89 ₽. Первый взнос гасит долг сразу, поэтому процентов набегает меньше. Налоги и комиссии ПЛТ не учитывает, это прямо сказано в описании функции, так что с графиком банка сумма может разойтись.
ПРПЛТ и ОСПЛТ: куда уходит платёж
Платёж по аннуитету одинаковый, а его состав меняется каждый месяц. В первый месяц около трети уходит на проценты, к концу почти всё гасит долг. Две функции раскладывают платёж по любому месяцу:
=ПРПЛТ($C$3/12;E2;$C$4;-$C$2)считает проценты за месяц из ячейки E2;=ОСПЛТ($C$3/12;E2;$C$4;-$C$2)считает погашение основного долга за тот же месяц.
Знаки доллара закрепляют условия кредита, поэтому формулу можно протянуть вниз на все 24 строки. В первом месяце проценты составляют 4 500 ₽, а долг уменьшается на 10 477,23 ₽. В 24-м месяце проценты уже 221,34 ₽, а на долг идёт 14 755,89 ₽.
Итоги за весь срок удобнее считать функциями ОБЩПЛАТ и ОБЩДОХОД. Суммы по строкам округлены до копеек функцией ОКРУГЛ и на итоге могут разойтись на копейку.
=-ОБЩПЛАТ(C3/12;C4;C2;1;24;0)Формула вернёт 59 453,53 ₽ процентов. Обратите внимание на знак: здесь сумма кредита идёт без минуса, потому что по справке к ОБЩПЛАТ при пс ≤ 0 функция возвращает ошибку. Минус ставим перед всей функцией, чтобы итог вышел положительным.
СТАВКА, КПЕР, ПС и БС: обратные задачи
Эти четыре функции отвечают на вопросы, которые задают перед тем, как подписать договор или открыть вклад. В каждой те же аргументы, что у ПЛТ, только неизвестное другое.
| Вопрос | Формула | Ответ |
|---|---|---|
| Какая ставка, если за 300 000 ₽ просят 24 платежа по 15 500 ₽? | =СТАВКА(24;-15500;300000)*12 |
21,57 % годовых |
| Сколько месяцев платить, если вносить по 20 000 ₽? | =КПЕР(C3/12;-20000;C2) |
17,12, то есть 18 платежей |
| Сколько можно взять, если платить 12 000 ₽ три года? | =ПС(C3/12;36;-12000) |
331 928,21 ₽ |
| Сколько накопится, если класть 10 000 ₽ в месяц под 12 % на три года? | =БС(12%/12;36;-10000) |
430 768,78 ₽ |
В Google Таблицах результаты СТАВКА мы умножили на 100, чтобы показать проценты числом. В Excel проще поставить ячейке процентный формат.
Пара пояснений к ответам:
- СТАВКА возвращает ставку за один период, поэтому месячную ставку умножаем на 12. Функция ищет ответ перебором, и по справке Microsoft возвращает ошибку, если за 20 итераций результат не сошёлся. Тогда помогает шестой аргумент «прогноз», например 0,02.
- КПЕР выдаёт дробное число. 17,12 значит, что после 17 полных платежей останется небольшой хвост, и восемнадцатый взнос будет меньше 20 000 ₽.
- БС считает вклад с ежемесячной капитализацией и пополнением в конце месяца. Внесено 360 000 ₽, остальное начислено процентами. У реального вклада условия капитализации бывают другими, их стоит сверить с договором.
ЧПС и ВСД: стоит ли вкладываться в проект
Проект требует 500 000 ₽ вложений сейчас и за четыре года приносит 150 000, 180 000, 200 000 и 170 000 ₽. Ставка дисконтирования, то есть доходность, которую можно получить в другом вложении, равна 15 %. Что это за ставка и откуда её брать, подробно разобрали в статье про NPV.
=ЧПС(C16;C18:C21)+C17Результат: −4 758,06 ₽. Проект почти отбивает 15 %, но не дотягивает.
Главная ловушка ЧПС в том, куда поставить вложения. По справке Microsoft функция считает, что первый поток приходит в конце первого периода. Поэтому сегодняшние 500 000 ₽ прибавляют снаружи формулы. Если затолкать их внутрь, =ЧПС(C16;C17:C21), Excel сдвинет вложения на год вперёд и покажет −4 137,44 ₽. В учебном примере разница чуть больше 620 ₽, в проекте на десятки миллионов она вырастет пропорционально.
=ВСД(C17:C21)ВСД вернёт 14,54 %. Это ставка, при которой ЧПС равна нулю. Вложения здесь идут внутри диапазона, первым значением. Раз 14,54 % меньше 15 %, ЧПС и вышла отрицательной: обе функции говорят одно и то же разными словами.
Для ВСД в диапазоне нужно хотя бы одно отрицательное и одно положительное значение, так требует справка к функции. Иначе будет #ЧИСЛО!.
Канал основателя Checkroi Вани БуявцаЗабирайте промпты и обучение по нейросетям в моём Телеграм-каналеБольше 3 700 человек уже применяют Claude Code, ChatGPT и другие нейросети в работе, учёбе, бизнесе и жизниПерейти в каналЧИСТВНДОХ и ЧИСТНЗ: когда деньги приходят в разные даты
ЧПС и ВСД считают, что потоки идут через равные промежутки. В жизни деньги приходят когда придётся, и тогда нужны функции с датами. Возьмём те же суммы, но с реальными датами: вложения 15 января 2026 года, поступления 20 июня 2026-го, 30 декабря 2026-го, 15 сентября 2027-го и 31 марта 2028-го.
=ЧИСТВНДОХ(C28:C32;A28:A32)
=ЧИСТНЗ(15%;C28:C32;A28:A32)- ЧИСТВНДОХ вернёт 29,7 % годовых;
- ЧИСТНЗ при ставке 15 % вернёт 82 104,43 ₽.
Суммы те же, а доходность вдвое выше, потому что все деньги вернулись за два года и два с половиной месяца вместо четырёх лет. В этом и смысл функций с датами: они замечают, что деньги пришли раньше.
Даты в ячейках должны быть датами, текст функция не поймёт. По справке к ЧИСТВНДОХ даты вводят функцией ДАТА или получают другими формулами. Для первой даты нашего примера это ДАТА(2026;1;15).
Частые ошибки и что с ними делать
- Платёж получился огромным. В формулу попала годовая ставка при сроке в месяцах. Решение: делите ставку на 12, как в
C3/12. - Результат с минусом или в скобках. Excel показывает направление денег. Решение: поставьте минус перед суммой кредита или перед всей функцией.
- СТАВКА или ВСД вернули #ЧИСЛО!. В потоках нет смены знака или перебор не сошёлся. Решение: проверьте, что вложения с минусом, и задайте последний аргумент-прогноз.
- ЧПС не совпадает с калькулятором. Сегодняшние вложения стоят внутри ЧПС. Решение: прибавьте их после функции, как в разделе выше.
- ЧИСТВНДОХ вернула #ЗНАЧ!. Одна из дат записана текстом. Решение: перепишите её через ДАТА или выставьте ячейке формат даты.
- Сумма процентов по строкам расходится с ОБЩПЛАТ на копейку. Каждая строка округлена отдельно. Решение: итог считайте функцией ОБЩПЛАТ.
Где освоить Excel для финансовых расчётов
Десять функций закрывают кредит, вклад и простую оценку проекта. Финансовая модель компании требует большего: сценарии, сводные таблицы, связку десятков листов. Этим каждый день занимается финансовый аналитик, и этому учат на курсах.
Ниже подборка курсов по Excel, полный список с ценами и отзывами есть в разделе курсов Microsoft Excel.
| Курс | Школа | Стоимость со скидкой | В рассрочку | Длительность | Обзор курса от Checkroi |
|---|---|---|---|---|---|
| Excel Перейти на сайт курса |  Нетология | 31 900 ₽ | 1691 ₽/мес. | 8 недель | Обзор курса |
| Power BI & Excel PRO Перейти на сайт курса | Нетология | 38 500 ₽ | 3208 ₽/мес. | 2 месяца | Обзор курса |
| Excel для работы Перейти на сайт курса |  Эдюсон | 16 500 ₽ | 1375 ₽/мес. | 2 недели | Обзор курса |
| Excel + Google Таблицы с нуля до PRO Перейти на сайт курса |  Skillbox | 29 899 ₽ | 2965 ₽/мес. | 4 месяца | Обзор курса |
| Excel для аналитика Перейти на сайт курса |  Moscow Business Academy | 17 200 ₽ | 2866 ₽/мес. | 2 месяца | Обзор курса |
Больше программ — в полном каталоге курсов по Microsoft Excel
