usto-excel

Клиент берёт в банке кредит на сумму $10000 на 12 месяцев под 24% годовых. На основе этих данных необходимо создать график платежей по кредиту, т.е. рассчитать какую сумму ежемесячно нужно будет платить клиенту, чтобы к концу срока полностью погасить основную сумму кредита плюс проценты.

Прежде всего, занесём в MS Excel вводные данные по кредиту, а именно:

• ПС - приведённая (текущая) стоимость, в нашем случае сумма кредита;
• Кпер - количество периодов, в течении которых нужно производить выплаты, т.е. если по условиям кредита клиент обязан ежемесячно погашать кредит, то количество периодов платежа равняется 12-ти;
• Ставка - процентная ставка под которую выдаётся кредит.

Рассчитываем ежемесячную сумму платежей

=ПЛТ(ставка; кпер; пс; [бс]; [тип])

Три первых аргумента (ставка, кпер, пс) у нас уже имеются.
Четвёртый аргумент "бс" означает будущую стоимость, которой мы хотим достичь после внесения последнего платежа. Данный аргумент является необязательным и принимается равным нулю, если не указывать его значение.
Пятый аргумент "тип" означает тип платежа, а именно производится ли платёж в начале периода (1) или же в конце периода (0). Данный аргумент также является необязательным и принимается равным нулю, если не указывать его значение.

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

=ПЛТ(C4/C3;C3;C2)

Так как сумма платежа для каждого периода будет одинаковой, используем постоянные ссылки на ячейки, что позволит нам протянуть её вниз и получить одинаковую сумму платежа для всех периодов.



Как видим у нас тут две проблемы:

1. В связи с тем, что понятие платежа подразумевает денежную сумму, при первом вводе в ячейку функции ПЛТ() MS Excel автоматически меняет её формат на денежный. 
2. И так как платёж означает уменьшение оставшихся у клиента денежных средств результат функции ПЛТ() отображается со знаком минус.

Для решения первой проблемы просто выделим ячейки с формулами и изменим их формат на числовой. А чтобы преобразовать отрицательный результат формулы в положительный подкорректируем формулу с помощью функции ABS(), которая возвращает абсолютную величину числа.



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

Рассчитываем сумму ежемесячных платежей по процентам

=ПРПЛТ(ставка; период; кпер; пс; [бс]; [тип])

где период - это порядковый номер периода платежа, который мы будем брать из столбца В, используя относительную ссылку:

=ПРПЛТ($C$3/$C$2;B6;$C$2;$C$1)

Меняем формат ячейки на числовой и корректируем формулу, для отображения абсолютной величины суммы платежа, используя функцию ABS().

Рассчитываем сумму ежемесячных платежей по основной сумме долга

=ОСПЛТ(ставка; период; кпер; пс; [бс]; [тип])

Опять же, изменим формат ячейки на числовой и добавим функцию ABS():



Если мы всё сделали правильно, то сумма платежей по процентам и основной сумме по каждому периоду будет равняться сумме общих платежей по данному периоду.

Рассчитываем остаточную сумму кредита

Таким образом, за 12 месяцев клиент должен будет заплатить $11 347,15 из которых $1 347,15 будут составлять проценты по кредиту.