Аннуитет. Расчет в EXCEL погашения основной суммы долга. Примеры и описание

Расчет остатка основной суммы (при БС=0, вид=0)

Предположим, был взят кредит в размере 100 000 рублей. на 10 лет по ставке 9%. Кредит должен быть погашен ежемесячно равными долями (в конце периода). Требуется рассчитать сумму основного долга, которая будет выплачена в первый месяц третьего года платежей. Решение простое - используйте функцию TPMT(): = TPMT(9%/12;25;10*12;100000) Ставка за период (ставка): 9%/12 Номер периода (первый месяц третьего платежного года) : 25=2* 12+1 Всего периодов (кпер): 10*12 Кредит: 100 000 Ответ: Основная сумма, подлежащая уплате в первый месяц третьего года выплат: -618,26 руб.

Теперь проведем те же расчеты, только осмысленно, т.е. понимая суть расчета.

Выведем формулу:

1. Доля платежа, идущая на погашение основной суммы долга в 1-м периоде = ПЛТ-ПС * ставка, а с учетом знаков = - ПЛТ-ПС * ставка (так, чтобы сумма долей была того же знака, что и ПС). Обозначим эту долю как PS1. PS * Ставка – это сумма процентов, выплаченных за пользование кредитом в первый период.
2. Доля платежа, идущая на погашение основной суммы долга во 2-м периоде *(1+ставка)=ПС1*(1+ставка). Обозначим эту долю как PS2. PV-PV1 – остаток долга на конец второго периода.
3. Доля платежа, идущая на погашение основной суммы долга в 3-м периоде PS2*(1+ставка) = PS1*(1+ставка)^2
4. Очевидно, что доля платежа, идущая на погашение основной суммы долга в последнем периоде (кпер) = ПС1 * (1 + ставка) ^ nпер = - (ПЛТ + ПС * ставка) * (1 + ставка) ^ кпер
5. Для полного погашения кредита необходимо, чтобы количество долей, используемых для погашения кредита, было равно сумме кредита, т.е. = - (ПЛТ + ПС * ставка) * (1- (1 + ставка) ^ кпер) / ставка = ПС. Эта формула получается как сумма членов геометрической прогрессии: первый член = - (ПМТ + ПС * скорость), знаменатель = (1 + скорость).
6. Решая простое уравнение, полученное на предыдущем шаге, получаем, что PS=PMT*(1-(1+bet)^-nper)/bet. Это формула приведенной стоимости (с PV=0 и платежами, произведенными в конце периода (тип=0)).
1. Рассчитаем сумму основного долга, которую необходимо будет выплатить, начиная с 25-го месяца (т.е. с 25-го по 120-й период). Сделаем это, используя формулу текущей стоимости PV=PMT*(1-(1+ставка)^-nper)/ставка. Теперь мы знаем ПЛТ, ПС – необходимая сумма основного долга, которую нужно будет выплатить, начиная с 25-го месяца, т.е. за 96 периодов (120-24=кпер). PS=86466.91 Правильность расчета можно проверить с помощью TOTALINCOME() .
2. Рассчитаем сумму процентов, которые будут выплачены в 25-м месяце: 86466,91 * ставка = 648,50 Правильность расчета можно проверить с помощью PRMT().
3. Наконец, поскольку каждый платеж содержит сумму, идущую на выплату основной суммы долга, и проценты, начисленные за период, то Сумма основного долга, которая будет выплачена в первый месяц третьего года платежей, будет получена как : РМТ-648,50 = 618,26

Как видите, сумма совпадает с вычисленным ранее результатом OSPLT() (с точностью до знака).

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

Эти расчеты можно выполнить несколькими способами (см. пример файла).

Метод 1. Функция ОБЩИЙ ДОХОД() Функция ОБЩИЙ ДОХОД(ставка; кол_период; n; начальный_период; конечный_период; тип) возвращает кумулятивную (общую) сумму, уплаченную для погашения основной суммы кредита между двумя периодами ( начальный_период и конечный_период ) .

Примечание. Английская версия функции: CUMPRINC(rate, nper, pv, start_period, end_period, type) возвращает CUMulative PRincipal, уплаченный за инвестиционный период с постоянной процентной ставкой.

Аргументы функции: Rate — обязательный аргумент. Процентная ставка за период. Number_per — обязательный аргумент (number_per — это аргумент nper в других функциях аннуитета, таких как PMT()). Общее количество периодов оплаты. Nz — обязательный аргумент (nz — аргумент ps в других функциях аннуитета, таких как PMT()). Начальная стоимость (чаще всего - сумма кредита). start_period — обязательный аргумент. Номер первого периода, включенный в расчеты. Нумерация периодов оплаты начинается с 1. Конечный_период — обязательный аргумент. Номер последнего периода, включенный в расчеты. Тип - обязательный аргумент, указывающий время платежа. Для постнумерандо аннуитета Тип=0, для пренумерандо Тип=1.

Примечание. Убедитесь, что аргумент «Курс» соответствует курсу за период (период не обязательно должен быть равен году). Например, если у вас есть 4-летний кредит с выплатой 12 процентов в месяц, используйте 12%/12 в качестве аргумента Ставка. Аргумент "Col_per" будет равен 4*12. Для ежегодных платежей по одному и тому же кредиту используйте 12% в качестве аргумента ставки и 4 в качестве аргумента на число. Для ежеквартальных платежей по одному и тому же кредиту используйте 12%/4 в качестве аргумента ставки и 4*4 в качестве аргумента per_number. Примечание. Функция ИТОГОДОХОД() возвращает #ЧИСЛО! если «Stake» ≤ 0, «number_per» ≤ 0 или «nc» ≤ 0, «start_period» «end_period» «type» — любое число, отличное от 0 и 1. Примечание. Функция ИТОГОДОХОД() возвращает #ЗНАЧ! если для «типа» установлено значение FALSE или TRUE.

Альтернативная формула

Из анализа альтернативной формулы видно, что функцию ОБЩИЙ ДОХОД () можно использовать только при BS=0, т.е. когда предполагается, что через число периодов «Col_per» кредит полностью погашен. Это ограничение можно обойти, написав выражение =ОБЩИЙ ДОХОД(ставка; количество_за; nc+BS; начальный_период; конечный_период; тип)+(начальный_период=1)*тип*BS* ставка /(1+ ставка)

Способ 2. Функция OSPLT()

Функция SPMT(ставка; период; nper; ps; [bs]; [type]) используется для расчета регулярных погашений основного долга почти с теми же аргументами, что и PLMT() .

Примечание. Английская версия функции: PPMT(rate, per, nper, pv, [fv], [type]), т.е. Principal Payment - оплата основной части долга.

Для расчета суммы основного долга, выплаченного между двумя периодами, нужно использовать не одну, а несколько функций OSPT(). Например, рассчитаем сумму долга, выплаченного в 3-м и 4-м периодах: =СПМТ(ставка; 3; кпер; пс; [бс]; [тип]) + ОСПЛТ(ставка; 4; кпер; пс; [бс] ; [тип])

Чтобы вычислить сумму основного долга, которая была выплачена между двумя произвольными периодами начальный_период и конечный_период, используйте формулу: =СУММПРОИЗВ(SPMT(ставка,СТРОКА(ДВССЫЛ(нач_период&":"&конечный_период)); nper; ps; [bs]; [type ]))

Выражение STRING(ДВССЫЛ(нач_период&":"&конец_периода)) создает массив последовательных чисел, например 2:3:4:5 (если начальный_период=2 и конечный_период=5). Функция СУММПРОИЗВ() суммирует результаты SPLT() , т. е. приведенная выше формула эквивалентна формуле: =SPMT(ставка; 2; кпер; пс; [бс]; [тип]) + PSFT(ставка; 3; кпер; пс; [бс]; [тип]) + PSFT(коэффициент; 4; кпер; пс; [бс]; [тип])+OSPLT(коэффициент; 5; кпер; пс; [бс]; [тип])

Способ 3. Расчет суммы основного долга через Будущую стоимость

Функция PV(rate; nper; pmt; [ps]; [type]) возвращает будущую стоимость инвестиций на основе периодических, постоянных (равных сумм) платежей и постоянной процентной ставки. Подробнее читайте в статье Аннуитет. Определить будущую стоимость в MS EXCEL. Чтобы рассчитать основную сумму, которая была выплачена между двумя произвольными периодами start_period и end_period, используйте формулу:

=- BS(ставка; конечный_период; pt; [пс]; [тип]) /(1+тип *ставка) + BS(ставка; начальный_период-1; pt; [пс]; [тип]) /IF(start_period = 1;1;1+тип *скорость)

Файл примера также предоставляет эквивалентную формулу без использования функции BS().

Способ 4. Расчет основной суммы через текущую стоимость

Функция PV(rate; nper; pmt; [bs]; [type]) возвращает текущую стоимость инвестиции. Подробнее читайте в статье Аннуитет. Определяем в MS EXCEL Настоящую (Текущая) стоимость. Чтобы рассчитать основную сумму, которая была выплачена между двумя произвольными периодами start_period и end_period, используйте формулу:

=PV(ставка; nper-end_period; pmt; [bs]; [type]) /(1+type *rate) - PV(rate; nper-beginning_period+1; pmt; [ps]; [type]) /IF (нач_период =1;1; 1+тип *ставка)

Файл примера также предоставляет эквивалентную формулу без использования функции ps(). Чтобы убедиться в идентичности вышеперечисленных методов, файл примера содержит график платежей, рассчитанный на основе функции OSLT().