Плт в excel формула как она считает. Есть ли в РНР функция аналогичная ПЛТ() в EXCEL. Описание функции ПЛТ

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

Блок статей, посвященных теории и расчетам параметров аннуитета . В этой статье рассмотрены только синтаксис и примеры использования функции ПЛТ() .

Синтаксис функции ПЛТ()

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

• Ставка. Процентная ставка по кредиту (ссуде).
• Кпер. Общее число выплат по кредиту.
• пс. Сумма кредита.
• Бс. Необязательный аргумент. Требуемое значение остатка по кредиту после последнего платежа. Если этот аргумент опущен, предполагается, что он равен 0 (кредит будет полностью возвращен).
• Тип. Необязательный аргумент. Принимает значение 0 (нуль) или 1. Если =0 (или опущен), то принимается, что регулярный платеж осуществляется в конце периода, если 1, то в начале периода (сумма регулярного платежа будет несколько меньше).

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

Пример 1

Предположим, человек планирует взять кредит в размере 50 000 руб. (ячейка В8 ) в банке под 14% годовых (B6 ) на 24 месяца (В7 ) (см. файле примера ).

Расчет Месячной суммы платежа по такому кредиту с помощью функции ПЛТ()

ПЛТ(B6/12;B7;B8)

СОВЕТ :
Убедитесь, что Вы последовательны в выборе временных единиц измерения для задания аргументов "ставка" и "кпер". В нашем случае рассчитываются ежемесячные выплаты по двухгодичному займу (24 месяца ) из расчета 14 процентов годовых (14% / 12 месяцев ).

Расчет Месячной суммы платежа по такому кредиту с помощью БЕЗ функции ПЛТ()

B8*(B6/12*(1+B6/12)^B7)/((1+B6/12)^B7-1)

Для нахождения суммы переплаты, умножьте возвращаемое функцией ПЛТ() значение на "кпер" (получите число со знаком минус) и прибавьте сумму кредита. В нашем случае переплата составит 7 615,46 руб. (за 2 года).

Пример 2

Предположим, человек планирует ежемесячно откладывать деньги, чтобы скопить через 5 лет (ячейка E7 ) 1 млн. рублей (E8 ). Деньги ежемесячно он планирует относить в банк и пополнять свой вклад. В банке действует процентная ставка 10% (E6 ) и человек полагает, что она будет действовать без изменений в течение 5 лет. Какую сумму человек должен ежемесячно относить в банк, чтобы таким образом через 5 лет скопить 1 млн. руб.? (см. файле примера ).

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

Количественный анализ финансовых данных в Excel при проведении таких операций сводится к исчислению следующих основных характеристик:

· текущей величины (present value – PV) потока платежей;

· будущей величины (future value – FV) потока платежей;

· величины отдельного платежа (payment – P) ;

· нормы доходности (цены) в виде процентной ставки (interest rate – r) ;

· числа периодовпроведения финансовой операции (например, лет, месяцев, и т.д.).

Используемые при этом методы базируются на технике исчисления сложных процентов.

Табличный процессор Excel предоставляет широкие возможности по моделированию подобных расчетов при помощи соответствующих встроенных в Excel финансовых функций: БC(), КПЕР(), СТАВКА(), ПЛТ(), ПС().

Все функции данной группы имеют одинаковый набор базовых аргументов:

ü процентная ставка (норма доходности или цена заемных средств);

ü срок (число периодов) проведения операции;

ü величина периодического платежа;

ü начальная сумма;

ü будущая стоимость (величина) денежных средств;

ü тип начисления процентов (1- начало периода, 0 – конец периода).

Рассмотрим применение вышеперечисленных функций в проведении финансовых расчетов и анализе обыкновенных аннуитетов на конкретном примере.

Предположим, Ваша фирма решила создать специальный фонд для погашения своих долгосрочных обязательств (кредитов, займов), срок погашения которых наступит, например, через 5 лет, путем периодического (ежегодного) пополнения депозита в банке. Начальная сумма депозита составляет 10000 тыс. руб. Размер ежегодных платежей – 1000 тыс. руб. Процентная ставка по банковскому депозиту – 15%.

Необходимо определить величину фонда к концу 5-го года.

Для решения поставленной задачи выполните следующие действия:

1) Введите исходные данные на рабочий лист. Диапазон таблицы С3:С6 содержит входные данные для расчета величины денежного фонда, т.е. будущей стоимости вложений (инвестиций).

2) Установите курсор в ячейку рабочего листа, где будет рассчитана будущая стоимость вложений (депозита), в данном случае в ячейку C7.

3) Выполните команду Формулы / Библиотека функций / Вставить функцию или нажмите кнопку Строки формул . В категорииФинансовые из списка выберите функцию БC() . Нажмите ОК.

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

Функция БС() – позволяет определить будущую величину вклада (Future Value – FV) на основе периодических постоянных платежей при заданных величинах процентной ставки, числа периодов выплат и начальной суммы вклада.

Функция имеет следующий синтаксис:

=БС (норма; число периодов; выплата; нз; тип),

где: норма – процентная ставка (норма доходности по депозиту);

число периодов – срок (число периодов) проведения операции;

выплата – величина периодического платежа;

нз – начальная стоимость вложений (депозита);

тип – тип начисления процентов, является необязательным аргументом.

(0 – в конце периода; 1 – в начале периода). По умолчанию начисление процентов осуществляется в конце периода.

Следует обратить внимание на особенности задания аргументов:

Если процентная ставка задается как абсолютная величина, она должна иметь вид десятичной дроби, например как в примере: 15% – 0,15. Периодический платеж и начальная сумма задаются со знаком минус, т.к. в данной операции для фирмы они означают выплаты (расходование) денежных средств. Такие правила применимы для всех финансовых функций. В зависимости от условия поставленной задачи, значения периодического платежа и начальной стоимости могут быть введены в функцию как в виде положительных, так и отрицательных величин. Это зависит от того, какой экономический субъект проводит подобные расчеты. Кроме того, можно на этапе ввода аргументов определить конечный результат, возвращаемый функцией, который отображается внизу диалогового окна ввода аргументов.

MS Excel при расчетах характеристик денежных аннуитетов выражает каждый показатель исходя из следующего соотношения:

где: НC – начальная (текущая) стоимость вклада;

БС i – будущая стоимость вклада через число периодов i ;

норма – процентная ставка (норма доходности);

выплата – периодический платеж;

i – порядковый номер периода поведения финансовой операции;

тип – тип начисления процентов.

Таким образом, будущая стоимость вложений определяется по формуле:

Для нашего предприятия будущее значение банковского депозита в конце 5-го года будет следующим:

=БС (0,15;5;-1000;-10000) (Возвращаемый результат: 26855,95 тыс. руб.) .

Для банка, определяющего будущую сумму возврата средств по данному депозиту, функция имела бы следующий вид:

=БС (0,15;5;1000;10000) (Возвращаемый результат: -26855,95 тыс. руб.).

Функция КПЕР() – позволяет определить число выплат (поступлений) денежных средств, если известны процентная ставка, периодический платеж, начальная и будущая величины потоков платежей.

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

=КПЕР (0,15;1000;10000;26855,95)(Возвращаемый результат: 5),

где: 0,15 – процентная ставка по депозиту; 1000 – периодический платеж; 10000 – первоначальная сумма депозита; 26855,95 – будущая величина депозита.

Функция СТАВКА() – вычисляет процентную ставку, которая в зависимости от условий операции может выступать либо в качестве цены, либо в качестве нормы рентабельности данной операции. Имеет аргументы:

СТАВКА(кпер, плт, пс, [бс], [тип], [прогноз])

где: кпер – обязательный. Общее число периодов платежей для ежегодного платежа;

плт – обязательный. Выплата, производимая в каждый период; это значение не может меняться в течение всего периода выплат. Обычно аргумент "плт" состоит из основного платежа и платежа по процентам, но не включает других налогов и сборов. Если он опущен, аргумент "пс" является обязательным.

пс – обязательный. Приведенная (текущая) стоимость, т. е. общая сумма, которая на данный момент равноценна ряду будущих платежей;

бс – необязательный. Значение будущей стоимости, т. е. желаемого остатка средств после последней выплаты. Если аргумент "бс" опущен, предполагается, что он равен 0 (например, будущая стоимость для займа равна 0).

тип – необязательный. Число 0 или 1, обозначающее, когда должна производиться выплата (0 или опущен – в конце периода, 1 – в начале периода;

прогноз – необязательный. Предполагаемая величина ставки. Если аргумент "прогноз" опущен, предполагается, что его значение равно 10 %.

Пусть в нашем примере будет неизвестна процентная ставка по банковскому депозиту. Тогда для ее расчета воспользуемся функцией СТАВКА() :

=СТАВКА (5;-1000;-10000;26855,95)(Возвращаемый результат: 15%).

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

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

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

Где: ставка - обязательный аргумент. Процентная ставка по ссуде;

кпер – о бязательный аргумент. Общее число выплат по ссуде;

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

бс – н еобязательный аргумент. Требуемое значение будущей стоимости, т. е. желаемого остатка средств после последнего платежа. Если этот аргумент опущен, предполагается, что он равен 0 (будущая стоимость для займа равна 0);

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

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

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

=ПЛТ (0,15;5;-10000;26855,95)(Возвращаемый результат: -1000).

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

Функция ПС() – позволяет определить текущую (т.е. на момент начала операции – present value ) стоимость аннуитета , если известны 4 обязательных параметра (процентная ставка; число периодов; начальная стоимость; будущая стоимость денежных средств). Имеет следующие аргументы:

ПС(ставка, клер, плт, [бс], [тип])

где: ставка - обязательный. Процентная ставка за период. Например, если получен кредит на автомобиль под 10 процентов годовых и выплаты производятся ежемесячно, процентная ставка за месяц составит 10%/12 (0,83%). В качестве значения аргумента "ставка" нужно ввести в формулу 10%/12, 0,83% или 0,0083.

кпер – обязательный. Общее число периодов платежей для ежегодного платежа. Например, если получен кредит на 4 года на покупку автомобиля и платежи производятся ежемесячно, то кредит имеет 4*12 (или 48) периодов. В качестве значения аргумента "кпер" в формулу нужно ввести число 48.

плт – обязательный. Выплата, производимая в каждый период и не меняющаяся на протяжении всего периода ежегодного платежа. Обычно аргумент "плт" состоит из выплат в счет основной суммы и платежей по процентам, но не включает в себя другие сборы или налоги. Например, ежемесячная выплата по кредиту в размере 10 000 р. под 12 процентов годовых на 4 года составит 263,33р. В качестве значения аргумента "плт" нужно ввести в формулу число -263,33.

бс – необязательный. Значение будущей стоимости, т.е. желаемого остатка средств после последнего платежа. Если аргумент "бс" опущен, предполагается, что он равен 0 (например, будущая стоимость для займа равна 0). Предположим, что для определенной цели требуется накопить 50 000 р. за 18 лет: в этом случае будущая стоимость равна 50 000 р. Предположив, что заданная процентная ставка останется без изменений, можно определить, какую сумму необходимо откладывать каждый месяц. Если аргумент "бс" опущен, необходимо использовать аргумент "плт".

тип – необязательный. Число 0 или 1, обозначающее, когда должна производиться выплата.

Для условия нашей задачи применение данной функции позволяет получить ответ на вопрос: «Какую сумму необходимо вложить в банк на депозит, чтобы получить через 5 лет величину вклада 26855,95 тыс. руб. при ежегодном пополнении вклада на 1000 тыс. руб., если годовая банковская ставка составляет 15%?».

Формула для определения текущей (настоящей) стоимости вклада (ПС) :

Для нашего примера синтаксис функции будет следующим:

=ПС (0,15;5;-1000;26855,95)(Возвращаемый результат: -10000).

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

Допустим, при ежеквартальном пополнении вклада и начисления процентов, функция ПС() примет следующий вид:

=ПС (0,15/4;5*4;-1000;26855,95)(Возвращаемый результат: 1035,09).

Лабораторная работа №2

Работа с финансовыми функциями.

Анализ «Что-если»

Цель работы : научиться работать с финансовыми функциями Excel

и выполнять анализ "Что-если"

1 Финансовые функции при экономических расчётах

2 Прогнозирование с помощью анализа "Что-если"

Финансовые функции при экономических расчётах

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

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

Пример 1 Определить ежемесячный платёж, если банк предоставляет кредит в 140000р. с рассрочкой в 5 лет под 8,5% годовых с ежемесячной выплатой. Последний платёж должен составить 10000р.

Введём данные в таблицу Excel согласно рис. 1)

1 Выделить ячейку В6 и щелкнуть по кнопке Вставка функции (знак f x слева от строки формул). Появится окно Мастера функций, выбрать категорию Финансо­вые.

2 Щелкнуть мышью по функции ПЛТ, перетащить окно ПЛТ на свободное место экрана, чтобы освободить таблицу и

Ри­сунок 1 Расчёт аннуитета заполнить его поля:

▪ Поле Ставка – это процент в месяц,

вводим 0,085,

▪ Кпер – количество периодов выплат, т.е. 5лет*12мес, вводим 5*12

▪ Нз – общая сумма всех платежей с текущего момента, вводим 140000,

▪ Бс – будущая стоимость, вводится 130000 со знаком "-", т.к. платим мы, а не банк,

§ Тип – выплата в конце месяца, поэтому вводим 0 или ничего.

3 Нажать ОК .

Результат : около 2738 р. ежемесячно нужно выплачивать, чтобы погасить 130000 р. за 5 лет (в конце срока последним платежом ещё 10000р.)

2 Прогнозирование с помощью анализа "Что-если"

Анализ «Что-если» позволяет прогнозировать значение какой-либо функции (математической, финансовой, статистической и др.) при изменении её аргументов. Существует три способа прогнозирования значений: с помощью таблиц подстановки данных, с помощью сценариев и с помощью подбора параметров и поиска решения.

1 способ. Таблица подстановки данных – это диапазон ячеек, показывающий, как изменение значений подстановки влияет на возвращаемый формулой результат. Если в какой-либо ячейке записана формула, содержащая элементы из других ячеек, то при изменении значения в какой-нибудь или нескольких ячейках изменится результат в ячейке, содержащей формулу.

Пример 2 Компания сделала заём на 80 000 руб. сроком на 3 года. Определить:

Ежемесячные выплаты при процентных ставках 7%, 8% и 9% годовых,

Ежемесячные выплаты при процентной ставке 5%, сроке заема 5 лет и сумме заема 100 000р.

1 Введем таблицу подстановок в виде (рис. 2):

Рисунок 2 Таблица подстановок

2 Введём в ячейку D2 формулу платежа ПЛТ (В3/12;В4*12;В5) вручную или через окно ПЛТ из Мастера функций (см. пример 1), в D2 появится рассчитанное значение функции -2470,17р.

3 Изменим значение ячейки В3 на 8%, получим в D2 cумму платежа –2506,91р.

4 Изменим значение ячейки В3 на 9%, получим в D2 cумму платежа –2543,98р.

5 Изменим одновременно значения ячеек: В3на 5%, В4на 5 и В5 на 100000, получим в D2 cумму платежа –1887,12р.

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

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

Пример 3 Оформим в виде сценариев варианты подстановки данных из пунктов 2 и 3 примера 2.

Для создания сценария необходимо выполнить следующие действия:

1 Из меню Сервис выберете команду Сценарии.

2 В открывшемся окне Диспетчер сценариев нажмите кнопку Добавить.

3 Введите имя сценария., например "Ставка 7%"".

4 В поле Изменяемые ячейки задайте те ячейки (через двоеточие), которые Вы собираетесь изменить, в данном случае – ячейку В3.

5 Нажмите кнопку ОК.

6 В открывшемся диалоговом окне Значения сценария для каждой изменяемой ячейки введите новое значение или формулу, в данном случае вводим в В3число 0,07. Нажмите кнопку ОК . Исходную модель " что-если " желательно сохранить в виде сценария, присвоив ему, например, имя «Стартовые значения». В противном случае при задании новых изменяемых ячеек исходные данные будут потеряны.

Для просмотра сценария необходимо воспользоваться кнопкой Вывести в окне Диспетчер сценариев. Щелкнув кнопку Итоги в диалоговом окне Диспетчер сценариев, можно получить итоговый отчет на отдельном рабочем листе с названием "Структура сценариев", показывающий влияние разных сценариев на одну или несколько результирующих ячеек. Знаки "+"("-") слева и сверху позволяют разворачивать (сворачивать) отдельные разделы отчёта. Серым выделены изменяемые поля.

3 способ. Подбор параметра. При подборе параметра значение влияющей ячейки (параметра) изменяется до тех пор, пока формула, зависящая от этой ячейки не возвратит заданное значение.

Пример 4 Условие примера 1. Компания может ежемесячно выплачивать не более 2500р. Определить, каким должен для этого быть последний платёж.

1.Выделим ячейку.В6:

2.В меню Сервис выбрать команду Подбор параметра.

В окне Подбор параметра:

В поле Установить в ячейке – введено В6,

В поле Значение - ввести -2500

В поле Изменяя значение ячейки – ввести В3 (ячейка последнего платежа),

Нажать ОК.

Результат: последний платёж = -27716 р.

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

Команда Поиск решения из меню Сервис используется для подбора одновременно нескольких параметров с целью максимизации или минимизации содержимого целевой ячейки и подробно рассматривается в лабораторной работе №7 (excel-7).

Контрольные вопросы

1 Как вывести на экран приложение Мастер функций?

2 Какую операцию выполняет функция ПЛТ, что вводится в её поля Норма, Кпер, Нз, Бс, Тип?

3 Назначение и способы анализа «Что если»?

4 Что такое «Таблица подстановок», каков состав её ячеек?

5 Что такое сценарий, как его создать, просмотреть, получить итоговый отчет на отдельном листе?

6 Сущность операции Подбор параметра, как она выполняется?

Задания

1 Выполнить задание примера 1, изменив сумму кредита на 140000·n , где n - номер студента в журнале преподавателя. Выполнить то же для новой суммы кредита, изменив годовой процент с 8,5% на 5%, а срок кредита с 5 на 10 лет.

2 Выполнить анализ "Что-если" по заданию таблицы подстановки примера 2, изменив сумму заёма на 80000·n, где n- номер студента в журнале преподавателя.

3 Оформить в виде сценариев все операции из п.1 (два сценария) и п.2 (четыре сценария) данного задания к лабораторной работе.

4 Выполнить задание примера 4, изменив сумму ежемесячной выплаты на n·100 .

1Название, цель, содержание работы

2 Письменные ответы на контрольные вопросы

Существуют сотни онлайновых финансовых планировщиков. Все они просты в использовании, но ограничены по функциональности. MS Excel на их фоне - настоящий комбайн. В нём есть 53 финансовые формулы на все случаи жизни, а для контроля и планирования бюджета полезно знать три из них.

Функция ПЛТ

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

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

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

Функция СТАВКА

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

СТАВКА(кпер;плт;пс;бс;тип;прогноз)

• Кпер - общее число периодов платежей для ежегодного платежа.
• Плт - выплата, производимая в каждый период; это значение не может меняться в течение всего периода выплат. Обычно аргумент «плт» состоит из основного платежа и платежа по процентам, но не включает других налогов и сборов. Если он опущен, аргумент «пс» является обязательным.
• Пс - приведённая (текущая) стоимость, т. е. общая сумма, которая на данный момент равноценна ряду будущих платежей.
• Бс (необязательный аргумент) - значение будущей стоимости, т. е. желаемого остатка средств после последней выплаты. Если аргумент «бс» опущен, предполагается, что он равен 0 (например, будущая стоимость для займа равна 0).
• Тип (необязательный аргумент) - число 0 (нуль), если платить нужно в конце периода, или 1, если платить нужно в начале периода.
• Прогноз (необязательный аргумент) - предполагаемая величина ставки. Если аргумент «прогноз» опущен, предполагается, что его значение равно 10%. Если функция СТАВКА не сходится, попробуйте изменить значение аргумента «прогноз». Функция СТАВКА обычно сходится, если значение этого аргумента находится между 0 и 1.

Функция ЭФФЕКТ

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

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

Прежде всего, нужно сказать, что существует два вида кредитных платежей:

• Дифференцированные;
• Аннуитетные.

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

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

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

Этап 1: расчет ежемесячного взноса

Для расчета ежемесячного взноса при использовании аннуитетной схемы в Экселе существует специальная функция – ПЛТ . Она относится к категории финансовых операторов. Формула этой функции выглядит следующим образом:

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

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

Аргумент «Ставка» указывает на процентную ставку за конкретный период. Если, например, используется годовая ставка, но платеж по займу производится ежемесячно, то годовую ставку нужно разделить на 12 и полученный результат использовать в качестве аргумента. Если применяется ежеквартальный вид оплаты, то в этом случае годовую ставку нужно разделить на 4 и т.д.

«Кпер» обозначает общее количество периодов выплат по кредиту. То есть, если заём берется на один год с ежемесячной оплатой, то число периодов считается 12 , если на два года, то число периодов – 24 . Если кредит берется на два года с ежеквартальной оплатой, то число периодов равно 8 .

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

«Бс» — это будущая стоимость. Эта величина, которую будет составлять тело займа на момент завершения кредитного договора. В большинстве случаев данный аргумент равен «0» , так как заемщик на конец срока кредитования должен полностью рассчитаться с кредитором. Указанный аргумент не является обязательным. Поэтому, если он опускается, то считается равным нулю.

Аргумент «Тип» определяет время расчета: в конце или в начале периода. В первом случае он принимает значение «0» , а во втором – «1» . Большинство банковских учреждений используют именно вариант с оплатой в конце периода. Этот аргумент тоже является необязательным, и если его опустить считается, что он равен нулю.

Теперь настало время перейти к конкретному примеру расчета ежемесячного взноса при помощи функции ПЛТ. Для расчета используем таблицу с исходными данными, где указана процентная ставка по кредиту (12% ), величина займа (500000 рублей ) и срок кредита (24 месяца ). При этом оплата производится ежемесячно в конце каждого периода.

1. Выделяем элемент на листе, в который будет выводиться результат расчета, и щелкаем по пиктограмме «Вставить функцию» , размещенную около строки формул.



2. Производится запуск окошка Мастера функций . В категории «Финансовые» выделяем наименование «ПЛТ» и жмем на кнопку «OK» .



3. После этого открывается окно аргументов оператора ПЛТ .

   В поле «Ставка» следует вписать величину процентов за период. Это можно сделать вручную, просто поставив процент, но у нас он указан в отдельной ячейке на листе, поэтому дадим на неё ссылку. Устанавливаем курсор в поле, а затем кликаем по соответствующей ячейке. Но, как мы помним, у нас в таблице задана годовая процентная ставка, а период оплаты равен месяцу. Поэтому делим годовую ставку, а вернее ссылку на ячейку, в которой она содержится, на число 12 , соответствующее количеству месяцев в году. Деление выполняем прямо в поле окна аргументов.

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

   В поле «Пс» указывается первоначальная величина займа. Она равна 500000 рублей . Как и в предыдущих случаях, указываем ссылку на элемент листа, в котором содержится данный показатель.

   В поле «Бс» указывается величина займа, после полной его оплаты. Как помним, это значение практически всегда равно нулю. Устанавливаем в данном поле число «0» . Хотя этот аргумент можно вообще опустить.

   В поле «Тип» указываем в начале или в конце месяца производится оплата. У нас, как и в большинстве случаев, она производится в конце месяца. Поэтому устанавливаем число «0» . Как и в случае с предыдущим аргументом, в данное поле можно ничего не вводить, тогда программа по умолчанию будет считать, что в нем расположено значение равное нулю.

   После того, как все данные введены, жмем на кнопку «OK» .



4. После этого в ячейку, которую мы выделили в первом пункте данного руководства, выводится результат вычисления. Как видим, величина ежемесячного общего платежа по займу составляет 23536,74 рубля . Пусть вас не смущает знак «-» перед данной суммой. Так Эксель указывает на то, что это расход денежных средств, то есть, убыток.



5. Для того, чтобы рассчитать общую сумму оплаты за весь срок кредитования с учетом погашения тела займа и ежемесячных процентов, достаточно перемножить величину ежемесячного платежа (23536,74 рубля ) на количество месяцев (24 месяца ). Как видим, общая сумма платежей за весь срок кредитования в нашем случае составила 564881,67 рубля .



6. Теперь можно подсчитать сумму переплаты по кредиту. Для этого нужно отнять от общей величины выплат по кредиту, включая проценты и тело займа, начальную сумму, взятую в долг. Но мы помним, что первое из этих значений уже со знаком «-» . Поэтому в конкретно нашем случае получается, что их нужно сложить. Как видим, общая сумма переплаты по кредиту за весь срок составила 64881,67 рубля .

Этап 2: детализация платежей

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

1. Для определения величины оплаты по телу займа используем функцию ОСПЛТ , которая как раз предназначена для этих целей. Устанавливаем курсор в ячейку, которая находится в строке «1» и в столбце «Выплата по телу кредита» . Жмем на кнопку «Вставить функцию» .



2. Переходим в Мастер функций . В категории «Финансовые» отмечаем наименование «ОСПЛТ» и жмем кнопку «OK» .



3. Запускается окно аргументов оператора ОСПЛТ. Он имеет следующий синтаксис:

   ОСПЛТ(Ставка;Период;Кпер;Пс;Бс)

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

   Заполняем уже знакомые нам поля окна аргументов функции ОСПЛТ теми самыми данными, что были использованы для функции ПЛТ . Только учитывая тот факт, что в будущем будет применяться копирование формулы посредством маркера заполнения, нужно сделать все ссылки в полях абсолютными, чтобы они не менялись. Для этого требуется поставить знак доллара перед каждым значением координат по вертикали и горизонтали. Но легче это сделать, просто выделив координаты и нажав на функциональную клавишу F4 . Знак доллара будет расставлен в нужных местах автоматически. Также не забываем, что годовую ставку нужно разделить на 12 .



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

   После того, как все данные, о которых мы говорили выше, введены, жмем на кнопку «OK» .



5. После этого в ячейке, которую мы ранее выделили, отобразится величина выплаты по телу займа за первый месяц. Она составит 18536,74 рубля .



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



7. В итоге все ячейки столбца заполнены. Теперь мы имеем график выплаты тела займа помесячно. Как и говорилось уже выше, величина оплаты по данной статье с каждым новым периодом увеличивается.



8. Теперь нам нужно сделать месячный расчет оплаты по процентам. Для этих целей будем использовать оператор ПРПЛТ . Выделяем первую пустую ячейку в столбце «Выплата по процентам» . Жмем на кнопку «Вставить функцию» .



9. В запустившемся окне Мастера функций в категории «Финансовые» производим выделение наименования ПРПЛТ . Выполняем щелчок по кнопке «OK» .



10. Происходит запуск окна аргументов функции ПРПЛТ . Её синтаксис выглядит следующим образом:

    ПРПЛТ(Ставка;Период;Кпер;Пс;Бс)

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



11. Затем результат расчета суммы оплаты по процентам за кредит за первый месяц выводится в соответствующую ячейку.



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



13. Теперь нам предстоит рассчитать общий ежемесячный платеж. Для этого вычисления не следует прибегать к какому-либо оператору, так как можно воспользоваться простой арифметической формулой. Складываем содержимое ячеек первого месяца столбцов «Выплата по телу кредита» и «Выплата по процентам» . Для этого устанавливаем знак «=» в первую пустую ячейку столбца «Общая ежемесячная выплата» . Затем кликаем по двум вышеуказанным элементам, установив между ними знак «+» . Жмем на клавишу Enter .



14. Далее с помощью маркера заполнения, как и в предыдущих случаях, заполняем колонку данными. Как видим, на протяжении всего действия договора сумма общего ежемесячного платежа, включающего платеж по телу займа и оплату процентов, составит 23536,74 рубля . Собственно этот показатель мы уже рассчитывали ранее при помощи ПЛТ . Но в данном случае это представлено более наглядно, именно как сумма оплаты по телу займа и процентам.



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



16. А вот вычисление остатка к выплате после второго и последующих месяцев будет несколько сложнее. Для этого нам нужно отнять от тела кредита на начало кредитования общую сумму платежей по телу займа за предыдущий период. Устанавливаем знак «=» во второй ячейке столбца «Остаток к выплате» . Далее указываем ссылку на ячейку, в которой содержится первоначальная сумма кредита. Делаем её абсолютной, выделив и нажав на клавишу F4 . Затем ставим знак «+» , так как второе значение у нас и так будет отрицательным. После этого кликаем по кнопке «Вставить функцию» .



17. Запускается Мастер функций , в котором нужно переместиться в категорию «Математические» . Там выделяем надпись «СУММ» и жмем на кнопку «OK» .



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

    СУММ(число1;число2;…)

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



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



20. Помесячный расчет остатков к оплате по кредиту сделан за весь кредитный период. Как и положено, на конец срока эта сумма равна нулю.

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

Как видим, при помощи программы Excel в домашних условиях можно без проблем рассчитать общий ежемесячный кредитный платеж по аннуитетной схеме, используя для этих целей оператор ПЛТ . Кроме того, при помощи функций ОСПЛТ и ПРПЛТ можно произвести расчет величины платежей по телу кредита и по процентам за указанный период. Применяя весь этот багаж функций вместе, существует возможность создать мощный кредитный калькулятор, который можно будет использовать не один раз для вычисления аннуитетного платежа.