Краткий практикум кредитной арифметики с применением Excel

Кредитную арифметику легко освоить с помощью электронных таблиц Excel. Особенно если вы знакомы с этой программой хотя бы на начальном уровне.

Фактически вам потребуется научиться оперировать только двумя формулами из богатого арсенала Excel.

1) величину ежемесячного аннуитетного платежа можно без проблем вычислить с помощью формулы ПЛТ (в некоторых вариантах Excel эта формула называется ППЛАТ);

2) годовую процентную ставку, соответствующую определенному значению ежемесячного аннуитетного платежа, можно вычислить с помощью формулы СТАВКА (в некоторых вариантах Excel эта формула называется НОРМА).

Приступим. В качестве основы возьмем пример 1 из главы 2 «Кредитная арифметика».

Банк А предлагает кредит в 5 тыс. у. е. на 18 месяцев под 13% годовых. Выплаты кредита — ежемесячно равными частями (аннуитет). Банк Б предлагает аналогичные условия, но с дифференцированными выплатами (размер ежемесячного платежа по мере погашения долга уменьшается). Дополнительных комиссий в обоих случаях не предусмотрено. Сколько заемщик заплатит за кредит банку А и банку Б?

Для решения задачи нам необходимо построить графики выплат по кредитам, выданным банком А и банком Б.

Строим график аннуитетных платежей

В самом начале обозначим н специально выделенных ячейках условия задачи:

(именно итог вариант используется в версии Excel, установленной на домашнем компьютере автора). Для этого нужно вызвать пункт меню «Вставка/ Функция» или попросту нажать на иконку fx:

В необходимые поля формулы последовательно вводим условия задачи, а именно обозначения ячеек со значениями переменных. Обратите внимание, что годовая процентная ставка в ноле «Норма» делится на 12, так как срок кредита у нас задастся в месяцах:

Программа выдает значение ежемесячного платежа.

Это не очень удобно. Поэтому в строке формул лучше обозначить знак «-»:

При определенном умении проще всего в строке формул самим набрать комбинацию:

=-ППЛАТ(ПС/12; КМ; СК), где в качестве аргументов функции присутствуют процентная ставка (ПС), количество месяцев кредита (КМ) и сумма кредита (СК).

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

Теперь нужно позаботиться о том, чтобы в формуле исходные условия задачи предстали в виде констант. Для этого необходимо аргументы формулы предварить знаком «$» (кстати, это можно было сделать и с самого начала):

Следующим шагом задаем начальное условие в столбце «Остаток долга на начало месяца». По сути, попросту копируем значение ячейки с исходными данными:

іи.иштіяміддш—ррр—————

Далее задаем значения столбца «Выплата процентов». Полагаем при этом, что «в году 12 месяцев» (пример с точным числом дней в месяце,

т. е. подход «в году 365 дней», рассмотрим ниже). Заносим в строку формул следующее (не забывая, что годовая процентная ставка выступает у нас в качестве константы):

Шс>мм*«р*дита&*) 5000 геештша* до

Jp Спот «ведите СнаСІ 18 І-ОГІВІрі’Г?

СТ»#*« (ГОДОІМр 13%

fi

ft

7

t

m

M

Ш

яь

и.

U:

Ж

ш

Ы

ЛЮ»»і Ич.= а п Ж\'А джаатт -» Ж

т

Значение в ячеііке «Погашение основного долга» есть разница между значением ячейки «Ежемесячный аннуитетный платеж» и значением ячейки «Выплата процентов»:

Наконец, задаем значение в столбце «Остаток долга на конец месяца». Это есть разница между значением ячейки «Остаток долга на начало месяца» и ячейки «Погашение основного долга»:

Последний штрих перед финальным действием Копируем значение ячейки «Остаток долга на конец месяца» в ячейку «Остаток долга на начало месяца» в следующей строке:

Теперь последовательно копируем значения ячеек следующим образом:

Наконец-то у нас образовалась «рабочая строка», которую можно скопировать так, чтобы количество строк в таблице совпадало с количеством месяцев, на которые выдается кредит:

О том, что все сделано правильно, свидетельствует нулевое значение последней ячейки в столбце «Остаток долга на конец месяца». Таким образом, мы получи чи график аннуитетных платежей по кредиту в 5 тыс. у. е., выданному на 18 месяцев под 13% годовых. Под занавес можем подсчитать суммарную выплату процентов:

Строим график дифференцированных платежей

Исходные условия, как помним, те же. Поэтому для начала копируем на другой лист Excel полученный график аннуитетных платежей:

Вносим поправки Прежде всего речь идет о столбце «Погашение основного долга». Долг должен быть равномерно распределен в течение всего срока кредитования (помним о том, что сумма кредита и срок кредита — константы).

Раз-

Далее правим название третьего столбца, вычеркнув слово «аннуитетный». Задаем значения в этом столбце. Ежемесячный дифференцированный платеж складывается из погашения долга и выплаты процентов.

Раз:

Теперь попробуем решить с помощью Excel пример 5 ил главы 2 («Кредитная арифметика»), т. е. обратную задачу: зная размер ежемесячного платежа, найдем значение годовой процентной ставки.

Большинству наверняка знакомо преоложение «10/10/10» — экспресс- кредит на 10 месяцев с первоначальным взносом в 10% от стоимости товара и удорожанием товара на 10%. Как это предложение соотносится с предложениями других банков, выраженными в процентах годовых?

Предположим, стоимость покупки — 3 тыс. у. е. (рубли, доллары, евро — не важно). Мы вносим в кассу магазина 10%, т. е. 300 у. е., и получаем кредит 10/10/10 на 3000 - 300 = 2700 у. е. Предложение утверждает, что удорожание составит 10% от стоимости товара, т. е. те же 300 у. е. Таким образом, в течение 10 месяцев нужно выплатить 2700 + 300 = 3000 у. е., проще говоря, начальную стоимость товара. В общем, в течение 10 меся цев нужно будет выплачивать по 3000/10 = 300 у. е. ежемесячно. Под какой же годовой процент выдается кредит9

Для того чтобы найти искомую процентную ставку, отталкиваясь от уже известной суммы платежа, нужно в строке формул набрать следующее: =НОРМА(КМ; -ЕП; СК) х 12 (в некоторых версиях Excel нужно набрать =СТАВКА(КМ; -ЕП; СК) х 12), где в качестве аргументов функции выступают следующие переменные: количество месяцев кредита (КМ), размер ежемесячного платежа (ЕП) и сумма кредита (СК).

Итак, решение задачи по шагам.

Шаг 1. Задаем в строке формул нужную комбинацию:

То есть в нашем случае, подставив нужные цифры, получаем: “НОР- МА(10; -300; 2700) х 12. Результат — 23,56% годовых (важно, чтобы ячейка, в которой высветится результат, была переведена в процентный формат, иначе результат будет выглядеть как 0,2356).

Для закрепления материала решим с помощью Excel еще одну задачу.

Банк А предлагает кредит «на неотложные нужды» о 3000 у. е. на 12 месяцев под 12% годовых. Взимаются два вида дополнительной комиссии: за выдачу кредита (1,5% от его сулемы) и за выдачу наличных со счета (0,1%). Банк Б предлагает кредит на аналогичные сумму и срок под 10,5% годовых. Дополнительная комиссия — 0,3% от су ммы кредита в месяц. В обоих случаях выплаты — равными частями (аннуитет). Какое предложение выгоднее?

Все необходимые значения попросту заносим в строку формул (см. рисунок на следующей странице).

В случае банка А чистые процентные выплаты за год составят:

=-ППЛАТ(12%/12; 12; 3000) х 12 - 3000 = 198,56 у. е. Плюс 48 у. е. комиссионных (45 + 3 у. е.). Итого: 246,56 у. е.

В случае банка Б чистые процентные выплаты за год составят:

=-ППЛАТ(10,5%/12; 12; 3000) х 12 - 3000 = 173,35 у. е. Плюс 108 у. е. комиссионных (9 х 12 у. е.). Итого: 281,35 у. е.

Пояснение: значение функции ППЛАТ умножаем на 12 потому, что кредит выдан на 12 месяцев. Был бы кредит выдан на полгода, умножали бы на 6.

В завершение разберем также пример 4.1 (см. «Уловка № 1. Забота за ваш счет» из главы 2 «Кредитная арифметика»).

Банк выдает автомобильный кредит в 5 тыс. у. е. на 1 год под 10% годовых с уплатой в первом месяце только процентов. Выплаты — аннуитетные. Используется расчет точных процентов с точным числом дней ссуды.

Нам необходимо построить график выплат и подсчитать сумму выплачиваемых процентов. Заносим исходные данные, включающие в себя и даты выплат по кредиту (иначе расчета с точным числом дней не получится):

Формула для первой ячейки столбца «Выплата процентов» выглядит следующим образом (так как используется подход «в году 365 дней») — см. строку формул:

Поясним: точное количество дней в Excel можно найти простым вычитанием одной даты из другой. Формула для второй ячейки столбца «Выплата процентов» (п всех последующих ячеек этого столбца) выглядит следующим образом:

Так как в первом месяце выплачиваются только проценты, значение в соответствующей ячейке нулевое:

В остальном все действия аналогичны описанным выше (при составлении графика аннуитетных платежей). Напомню, кстати, что последняя выплата в данном случае будет складываться из ежемесячного аннуитетного платежа и остатка невыплаченного долга: 477,59 + 2,35 = 479,94. Такой казус происходит потому, что в формуле аннуитетного платежа используется подход «в году 12 месяцев», а при расчете процентов с точным числом дней подход «в году 365 дней».