ПЛТ для расчета аннуитетного платежа
ПЛТ для расчета аннуитетного платежа





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

Конспекты

Курсы

Практикум


Скачать файл
Теги: Бесплатный курс Excel, самоучитель Excel, конспекты тренинга Excel, без регистрации.

ПЛТ для расчета аннуитетного платежа

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

Финансовые функции для составления графика платежей по кредитам

Для занятия Вам потребуется книга Excel с заготовками таблиц, по которым составлен конспект - этот файл прикреплен в самом низу страницы (без проблем откроется в Excel версии 2007 и новее)

 

Как посчитать ежемесячный платеж

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

В учебных целях предлагаю купить квартиру за три с половиной миллиона рублей (что соответствует однушке в подмосковье и скорее всего в новостройке), первый взнос сделаем 10%, у банка, соответственно, позаимствуем 3.150.000 рублей под 13,5% годовых. Этой информации вполне достаточно, чтобы посчитать сумму ежемесячного платежа по ипотеке и функция для такого расчета в Excel предусмотрена – это ПЛТ [PMT]. Аккуратно заполняем аргументы этой функции и получаем результат мягко говоря большой:

Дело в том, что сейчас в окне аргументов функции ПЛТ [PMT] указан годовой платеж, так как срок указан в годах и применяется годовая процентная ставка. Чтобы вычислить месячный платеж, все показатели необходимо привести к этому же периоду: срок в годах умножить на 12, а значение ставки на 12 поделить. Делать это будем не в ячейках листа, а непосредственно в окошке аргументов:

Итоговая формула для расчета аннуитетного платежа выглядит так: =ПЛТ(C7/12;C6*12;C5. Функция показывает результат со знаком минус и в этом нет никакой ошибки, потому как посчитан отток денежных средств, а значит и минус в бюджете плательщика – и еще какой минус… Подтверждаю это как потребитель сего продукта :-) Ну а для того, чтобы в ячейке избавиться от знака минус, поставьте его же перед именем функции, например

Следующим встает вопрос какова же итоговая сумма выплат за такую квартиру? Для подобного расчета функция как таковая не нужна – просто умножаем сумму платежа на количество выплат (срок в годах умножить на 12) и не забываем прибавить первый взнос (стоимость квартиры умножить на величину первого взноса в %) =C8*C6*12+C3*C4:

Теперь подставляем вместо данных этого примера свои конкретные цифры и получаем результат. Больше у банка получится может (пусть это будет на их совести), а вот меньше… - не встречала

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

 

Как узнать - сколько в каждом платеже процентов и суммы основного долга?

И думается мне, что интересно посмотреть на график платежей – ведь каждый платеж состоит из двух частей: суммы основного долга и процентов. Наверняка Вы слышали, что в первых платежах как раз платим одни почти только проценты – оценить масштаб бедствия можно при помощи функций ОСПЛТ [PPMT] и ПРПЛТ [IPMT], которые помогут вычислить для каждого платежа сумму основного долга и процентов соответственно. Для первого платежа вот такие получаются формулы, чтобы посчитать сумму основного долга: =ОСПЛТ($C$7/12;A17;$C$6*12;-C5)

 

И вот такая, чтобы высчитать проценты (хотя, конечно, это может быть просто разница между суммой платежа и уже вычисленной величиной основного долга): =ПРПЛТ($C$7/12;A17;$C$6*12;-C5)

Во вложенном файле полностью готовый к работе график платежей (для срока не более 30 лет)

 

Пара слов о регрессивной шкале

Первые платежи получаются несоизмериом огромными, потому что при такой схеме сумма долга делится поровну на весь период займа (и это, конечно, кажется нам справделивым), а вот проценты накурчиваются на величину остатка долга… И конечно, первые платежи незначительно меняют сумму остатка по кредиту, поэтому и сумма процентов получается огромной.  В конечном итоге гораздо выгоднее взять кредит и гасить его платежами, рассчитанными по регрессивной шкале – готовый график также есть во вложенном файле. Он, кстати, так и составлен (формулы можно посмотреть) - для начала сумма долга равными долями раскидывается на весь срок кредита, выводится остаток долга на конец периода до совершения платежа и именно на него начисляются проценты, соответственно в каждом последующем месяце величина процентов меньше, чем в предыдущем, так как уменьшается сумма основного долга. Но подъемны ли первые выплаты?

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

ВАЖНО (!) При работе с конспектами помним, что все названия программных продуктов являются зарегистрированными торговыми марками. Никакая часть настоящего издания ни в каких целях не может быть воспроизведена в какой бы то ни было форме и какими бы то ни было средствами, будь то электронные и механические, включая фотокопирование и запись на магнитный носитель, если на это нет письменного разрешения автора

Похожие материалы

Относительные и абсолютные ссылки в формулах
Типы ссылок
Основные правила составления формул в таблицах Excel. Логика копирования и переноса формул. Зачем ячейке нужны знаки доллара $ и как их поставить.

Создание и настройка выпадающих списков.
Выпадающие списки.
Как ограничить ввод данных в ячейке списком допустимых значений (справочником)? Где расположить этот справочник. Команда Проверка данных содержит такую настройку.

Интерфейс и основные понятия
Основные понятия
Вводный урок Excel, чтобы разобраться, как правильно называются элементы интерфейса программы. Это займёт немного времени, но дальнейшие материалы будут намного понятнее.

Консолидация данных, операции с группой листов
Консолидация
Обычная СУММ и команда Консолидация позволяют получить итоговый отчёт на основании нескольких исходных таблиц данных. Они могут быть идентичными ли отличаться друг от друга.

Заказчики

Я сотрудничаю с многими известными компаниями. Вот некоторые из них:

ВТБ МегаФон Глобус Абсолют

Связаться с автором

Оставьте ваше имя и адрес электронной почты, в ближайшее время я свяжусь
с вами для консультации и отвечу на вопросы!



Оставляя заявку, вы соглашаетесь на обработку ваших персональных данных.