Финансовые функции в редакторе «МойОфис Таблица»

Иногда случается так, что на мобильный телефон поступает смс-сообщение из банка с заманчивым предложением оформить кредит (рис. 1).

Рисунок 1. Предложение от банка

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

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

Исходные данные и вычисление процентной ставки

Сперва внесем данные, которые известны: сумму кредита, срок кредитования и размер ежемесячного платежа. Финансовые функции работают с денежными потоками – учитывают движения денежных средств. В нашей ситуации сумма кредита будет денежным потоком со знаком плюс, так как мы получаем деньги. Ежемесячный платёж, наоборот, будет отрицательным потоком. Это деньги, которые мы должны возвратить банку. Отрицательные денежные потоки записывают со знаком минус в начале числового значения (рис. 2).

Рисунок 2. Отрицательные денежные потоки в ячейке С6

Теперь вычислим процентную ставку по предложению банка. Для этого используем функцию СТАВКА. 
У функции несколько атрибутов:
количество_периодов – общее количество периодов платежа;
выплата_за_период – выплата, производимая в каждый период;
текущая_стоимость – текущая стоимость аннуитета (Аннуитет — график погашения кредита, предполагающий выплату основного долга и процентов по кредиту равными суммами через равные промежутки времени);
[будущая_стоимость] – необязательный атрибут. Значение будущей стоимости, которая остаётся после осуществления последнего платежа;
[конец_или_начало] – необязательный атрибут (0 по умолчанию). Срок выплат в конце (0) или в начале (1) каждого периода;
[приблизительная_ставка] – необязательный атрибут. Предполагаемая величина ставки.

Для работы функции достаточно указать первые три атрибута (аргумента). В ячейке C8 введём формулу:
=СТАВКА(С5;С6;С4;0)
где:
С5 – срок кредитования, т.е. общее количество периодов платежа;
С6 – ежемесячный платеж или выплата, производимая в каждый период;
С4 – сумма кредитования или текущая стоимость аннуитета;
0 – значение будущей стоимости, т.е. долг банку.

Получаем результат:
 

Рисунок 3. Результат работы функции СТАВКА

Срок кредитования указан в месяцах, как и сумма платежа. Соответственно, в процессе расчета значение процентной ставки так же получили за месяц. Для перевода в привычный годовой формат умножим полученное значение на 12. Итоговая годовая процентная ставка составит 22,25%.

Расчет общей суммы платежа и переплаты

Для подсчета общей суммы платежей умножим значение в ячейке С6 (ежемесячный платёж) на значение в ячейке С5 (срок кредита в месяцах) и получаем значение -1 832 100₽. 

Так как общая сумма платежей со знаком минус, то математически верно будет сложить это значение с суммой кредита, чтобы узнать переплату -732 100₽ (рис. 4).

Рисунок 4. Вычисление значения переплаты по кредиту

Проверка корректности вычислений

Для проверки корректности вычислений можем использовать финансовые функции ПЛТ – платеж, и КПЕР – количество периодов.

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

У функции несколько атрибутов:
ставка – годовая процентная ставка;
количество_платежей – количество платежей по аннуитету;
текущая_стоимость – текущая стоимость займа;
[будущая_стоимость] – необязательный атрибут. Требуемый объём сбережений после осуществления последнего платежа;
[конец_или_начало] – необязательный атрибут (0 по умолчанию). Число, определяющее, когда должен производиться платёж: в конце (0) или в начале (1) каждого периода.

В ячейку D6 введём формулу:
=ПЛТ(C8/12; C5; C4)
где:
C8/12 –  годовая процентная ставка, переведенная в месячный формат путем деления на 12, для  расчёта ежемесячного платежа;
C5 – количество платежей;
С4 – текущая стоимость займа, т.е. сумма кредита.

В результате вычисления получаем значение -30 535₽ (рис. 5). Сумма платежа совпадает с суммой, которую предложил банк.

Рисунок 5. Результат вычисления функции ПЛТ

Функция КПЕР определяет количество платежей по вкладу (в нашем случае по кредиту).

Атрибуты функции:
ставка – процентная ставка;
выплата_за_период – выплата, производимая в каждый период;
текущая_стоимость – текущая стоимость аннуитета;
[будущая_стоимость] – необязательный атрибут. Значение будущей стоимости, которая останется после осуществления последнего платежа;
[конец_или_начало] – необязательный атрибут (0 по умолчанию). Срок выплат в конце (0) или в начале (1) каждого периода.

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

=КПЕР(C8/12; C6; C4)
где:
C8/12 – годовая процентная ставка, переведенная в месячный формат путем деления на 12;
C6 – размер ежемесячного платежа;
С4 – текущая стоимость займа, т.е. сумма кредита.

В результате вычисления получаем значение 60 (рис. 6), что так же совпадает с исходными данными.

Рисунок 6. Результат вычисления функции КПЕР

Таким образом в результате простых вычислений с использованием финансовых функций СТАВКА, ПЛТ и КПЕР в редакторе «МойОфис Таблица» вы научились вычислять процентную ставку по кредиту, проверили корректность вычислений и смогли оценить выгоду предложения банка.

Можно расширить возможности применения стандартных финансовых функций. Например, подобрать оптимальный платёж, одновременно учитывая различные процентные ставки и сроки кредитования. Для того, чтобы узнать, как это сделать смотрите запись вебинара «Финансовые расчеты в «МойОфис Таблица».