Финансовые функции в Google Таблицах: ПЛТ, ЧПС и ВСД

Google Таблицы содержат полноценный набор финансовых функций, которые позволяют считать то, что обычно делают в специализированных программах или на финансовых калькуляторах. Три из них закрывают большинство практических задач: ПЛТ рассчитывает платеж по кредиту или вкладу, ЧПС оценивает инвестиционный проект с учетом стоимости денег во времени, ВСД находит ту ставку доходности, при которой проект выходит в ноль. В этой статье разберем каждую из них на конкретных примерах.

ПЛТ: ежемесячный платеж по кредиту

ПЛТ (в английском интерфейсе PMT) рассчитывает размер одного периодического платежа при аннуитетной схеме погашения. Аннуитет означает, что платеж каждый месяц одинаковый: часть идет на погашение основного долга, часть на проценты. Именно так работает большинство потребительских кредитов и ипотек.

Синтаксис функции:

=ПЛТ(ставка; число_платежей; текущий_размер_выплат; [остаток]; [конец_или_начало])

Аргументы:

• ставка – процентная ставка за один период. Если годовая ставка 18%, а платежи ежемесячные, то ставка = 18%/12.
• число_платежей – общее количество платежей. Кредит на 3 года с ежемесячными платежами: число_платежей = 3*12 = 36.
• текущий_размер_выплат – сумма кредита. Указывается как положительное число.
• остаток – необязательный аргумент. Остаток долга после последнего платежа. По умолчанию 0, то есть кредит погашается полностью.
• конец_или_начало – необязательный аргумент. 0 означает платеж в конце периода (по умолчанию), 1 – в начале.

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

Пример. Кредит 500 000 руб. на 5 лет под 16% годовых. Какой будет ежемесячный платеж?

=ПЛТ(16%/12; 5*12; 500000)

Результат: около -12 150 руб. в месяц.

Чтобы узнать общую переплату по кредиту, умножьте результат ПЛТ на количество периодов и прибавьте сумму кредита:

=ПЛТ(16%/12; 5*12; 500000)*60 + 500000

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

Расчет для вклада

ПЛТ работает в обе стороны. Если нужно накопить определенную сумму, регулярно откладывая деньги, укажите сумму цели в аргументе остаток, а в текущий_размер_выплат поставьте 0 или начальную сумму вклада со знаком минус. Знак минус здесь означает, что эти деньги вы уже отдали банку, то есть они сразу работают.

Пример: хотите накопить 1 000 000 руб. за 4 года, вклад под 10% годовых, начальный взнос 100 000 руб. Сколько нужно вносить ежемесячно?

=ПЛТ(10%/12; 4*12; -100000; 1000000)

Результат покажет необходимый ежемесячный взнос.

ЧПС: стоит ли вкладывать деньги в проект

ЧПС, или чистая приведенная стоимость (в английском интерфейсе NPV), отвечает на вопрос: сколько сегодня стоят все будущие доходы от проекта, если учесть, что деньги со временем дешевеют? Деньги обесцениваются, и 100 рублей через год реально стоят меньше, чем 100 рублей сегодня. ЧПС учитывает это и приводит все будущие поступления к сегодняшнему дню. Если результат положительный, проект приносит больше, чем стоит привлечение денег. Если отрицательный, то проект убыточен при данной ставке.

Синтаксис:

=ЧПС(ставка_дисконтирования; платеж_1; [платеж_2; …])

Аргументы:

• ставка_дисконтирования – требуемая доходность за один период. Это может быть ставка по кредиту, средняя доходность альтернативных вложений или просто минимальный порог доходности, ниже которого проект неинтересен.
• платеж_1, 2, ... – ожидаемые доходы и расходы по периодам. Расходы передаются отрицательными числами, доходы – положительными.

Важный нюанс: ЧПС дисконтирует только те потоки, которые переданы в аргументы. Начальные инвестиции, сделанные в нулевой момент времени, то есть до старта проекта, в аргументы не включаются – их нужно прибавить к результату отдельно. Поскольку это расход, в ячейке они хранятся со знаком минус, поэтому прибавление их фактически уменьшает итог. Если включить начальные вложения в диапазон аргументов, функция ошибочно дисконтирует их как будущий поток, а не как уже понесенные затраты.

Пример. Вы рассматриваете открытие небольшого производства. Начальные вложения составляют 800 000 руб. Ожидаемые доходы по годам: 200 000, 300 000, 350 000, 400 000. Ставка дисконтирования 12% – столько стоит кредит, которым финансируется проект.

Данные в ячейках: A1 = -800000, A2 = 200000, A3 = 300000, A4 = 350000, A5 = 400000.

=ЧПС(12%; A2:A5) + A1

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

Ставку дисконтирования выбрать непросто. Чаще всего используют ставку кредита (если проект финансируется заемными деньгами), среднюю доходность альтернативных вложений или ставку рефинансирования плюс надбавка за риск. Заниженная ставка делает плохой проект хорошим, завышенная – хороший проект плохим.

ВСД: какова реальная доходность проекта

ВСД, или внутренняя ставка доходности (в английском интерфейсе IRR), находит такую ставку дисконтирования, при которой ЧПС проекта равна нулю. Проще говоря, это максимальная стоимость привлечения денег, при которой проект еще остается безубыточным. Если привлекать деньги дороже этой ставки, проект уйдет в минус.

Синтаксис:

=ВСД(денежные_потоки; [примерная_ставка])

Аргументы:

• денежные_потоки – диапазон с денежными потоками. Первое значение – начальные инвестиции со знаком минус, дальше доходы со знаком плюс. В отличие от ЧПС, начальное вложение включается в диапазон.
• примерная_ставка – необязательный аргумент, начальное предположение для алгоритма. По умолчанию 10%. Если функция возвращает ошибку #ЧИСЛО!, попробуйте задать примерную ставку явно.

Тот же пример. Начальные вложения -800 000, доходы по годам: 200 000, 300 000, 350 000, 400 000. Данные в ячейках A1:A5.

=ВСД(A1:A5)

Допустим, результат 18%. Это означает, что проект выдержит финансирование по ставке до 18% годовых. Деньги привлекаются под 12%, и проект выгоден: разница в 6 процентных пунктов – это запас прочности. Если кредит стоит 20%, проект убыточен.

Как читать результат ВСД

ВСД удобно сравнивать со ставкой депозита или кредита. Когда ВСД выше ставки депозита, вкладывать в проект выгоднее, чем держать деньги в банке. Когда ВСД ниже ставки кредита, брать деньги в долг ради этого проекта невыгодно.

Кроме того, ВСД позволяет сравнивать проекты разного масштаба. Проект с вложениями 10 млн и ЧПС 2 млн и проект с вложениями 1 млн и ЧПС 500 тыс. сложно сравнивать по абсолютной прибыли, но ВСД покажет, какой из них эффективнее на каждый вложенный рубль.

Связь между ЧПС и ВСД

Эти две функции описывают одно и то же явление с разных сторон. ЧПС при ставке, равной ВСД, всегда будет равна нулю – это математическое тождество. Поэтому их удобно использовать вместе: ВСД показывает максимально допустимую стоимость финансирования, а ЧПС при заданной ставке – конкретную величину выгоды в рублях.

Если потоки денег нерегулярные, то есть промежутки между ними неодинаковые, вместо ЧПС и ВСД используйте их аналоги с привязкой к датам: ЧИСТНЗ и ЧИСТВНДОХ. Принцип тот же, но в аргументах дополнительно передается диапазон дат для каждого потока.

Частые ошибки

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

• ПЛТ: ставка не пересчитана на период. Самая распространенная ошибка – передать годовую ставку при ежемесячных платежах. Например, при годовой ставке 18% и ежемесячных платежах нужно писать 18%/12, а не 18%. Иначе функция будет считать, что 18% начисляется каждый месяц, и результат окажется примерно в 12 раз больше реального платежа. Всегда делите годовую ставку на количество периодов в году: на 12 для ежемесячных платежей, на 4 для квартальных.

• ЧПС: начальные инвестиции включены в диапазон. Функция ЧПС предполагает, что первый переданный поток происходит через один период, а не прямо сейчас. Если вложить начальные инвестиции в аргументы, функция ошибочно их продисконтирует – то есть посчитает, будто эти деньги тоже были потрачены не сегодня, а в будущем. Правильная схема: в аргументы передаются доходы за периоды 1, 2, 3 и далее, а начальные инвестиции прибавляются к результату отдельно.

• ВСД: ошибка #ЧИСЛО!. Возникает, если алгоритм не может найти решение за 20 итераций. Чаще всего помогает явно указать аргумент примерная_ставка близким к ожидаемому результату. Также убедитесь, что в диапазоне есть хотя бы одно отрицательное и одно положительное значение – без этого функция в принципе не может найти решение.

• ВСД: ежемесячные потоки на несколько лет. При большом количестве периодов алгоритм тоже может не справиться. Задайте примерная_ставка явно или проверьте данные: пропуски и нули внутри диапазона иногда мешают сходимости.

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

Заключение

ПЛТ, ЧПС и ВСД отвечают на три разных финансовых вопроса: сколько платить по кредиту каждый месяц, выгоден ли проект при заданной стоимости денег и какова максимальная ставка, которую проект выдержит. Разобравшись с логикой аргументов, можно прямо в таблице строить кредитные калькуляторы, прикидывать окупаемость бизнеса и сравнивать инвестиции без стороннего ПО. Главное – следить за согласованностью периодов: ставка и количество периодов всегда должны быть в одних и тех же единицах времени.

Изображение на обложке: Flaticon