Плт в excel формула

Функция ПЛТ в Excel

Функция ПЛТ в Excel

Добрый день, уважаемые подписчики и читатели блога. Очень много поступает вопросов по поводу «кредитных калькуляторов» как их создать в Excel и применять на практике.

Действительно в Excel есть минимально необходимый набор функций. Например, ПЛТ (платёж). То есть мы должны узнать сумму кредита и минусовать с неё платёж первого периода, считать процент, минусовать процент следующего платежа и т.д. Условие одно — платежи должны быть равными.

Давайте попробуем воспользоваться данной функцией. Построим небольшую таблицу:

Позовём нашу функцию и посмотрим на её аргументы.

Аргументов много (в принципе каждый аргумент ПЛТ это отдельная функция):

Ставка — это ставка для периода (если ставка квартальная то 13% я делю на 4 квартала, если ставка месячная то 13% делим на 12 и т.д), в нашем случае берём именно второй вариант.

Кпер — количество периодов для выплат по займу.

Пс — текущая стоимость займа (в нашем случае 700000 рублей).

Бс — будущая стоимость займа.

Тип — принимает значения 0 или 1 в зависимости от платежа вначале или в в конце периода (в конце 0, в начале 1).

Заполним аргументы функции нашими данными.

В итоге получим. Оставим «Бс» и «Тип» пустыми, они примут значение 0, он то нам и нужен!

Результат со знаком минус — мы теряем эти деньги. Если хочется видеть положительную сумму — сумму кредита нужно ввести со знаком минус (-700000).

Результат налицо! Это будет наш ежемесячный платёж. Нетрудно посчитать, что за весь период мы выплатим банку 750365,12 рублей.

Идём дальше, давайте проведём небольшой анализ по процентной ставке и сроку кредита. Возьмём ставки — 13%, 15%, 19% и 25%. Периоды кредитования — 12, 24, 36, 48 и 60 месяцев.

Из формул массивов мы знаем, что можно умножать диапазон на диапазон, но нам также нужно учесть и первоначальную сумму кредита. Поэтому воспользуемся возможностью программы «Анализ что если?». Предварительно выделим всю таблицу данных (от А8 до F12):

  • переходим на вкладку «Данные»;
  • в блоке кнопок «Работа с данными» нажимаем кнопку «Анализ что если?»;
  • выбираем «Таблица данных»

Теперь нужно указать куда (в какие ячейки подставлять) наши показания по количеству месяцев (столбцы) и процентную ставку (строки). Укажем соответствующие ячейки — B4 и B5. Нажимаем «ОК»

Останется понаблюдать за результатом.

Как видно из строки формул — появились фигурные скобки (признак массива) и функция ТАБЛИЦА. Не ищите её просто так, она появится только при использовании «Таблицы данных» из «Анализ «что если?».

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

Источник: pcandlife.ru

Функция ПЛТ() в EXCEL

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

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

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

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

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

Пример 1

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

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

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

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

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

Пример 2

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

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

К концу 5 летнего периода сумма начисленных процентов составит более 225 тыс. руб., т.е. если бы человек просто складывал бы деньги себе в сейф, то он скопил бы только порядка 775 тыс. руб.

Читайте также:  Как в эксель работать с формулами

Источник: excel2.ru

Расчет кредита в Excel

Кто как, а я считаю кредиты злом. Особенно потребительские. Кредиты для бизнеса – другое дело, а для обычных людей мышеловка”деньги за 15 минут, нужен только паспорт” срабатывает безотказно, предлагая удовольствие здесь и сейчас, а расплату за него когда-нибудь потом. И главная проблема, по-моему, даже не в грабительских процентах или в том, что это “потом” все равно когда-нибудь наступит. Кредит убивает мотивацию к росту. Зачем напрягаться, учиться, развиваться, искать дополнительные источники дохода, если можно тупо зайти в ближайший банк и там тебе за полчаса оформят кредит на кабальных условиях, попутно грамотно разведя на страхование и прочие допы?

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

Но если уж случится так, что вам или вашим близким придется влезть в это дело, то неплохо бы перед походом в банк хотя бы ориентировочно прикинуть суммы выплат по кредиту, переплату, сроки и т.д. “Помассажировать числа” заранее, как я это называю 🙂 Microsoft Excel может сильно помочь в этом вопросе.

Вариант 1. Простой кредитный калькулятор в Excel

Для быстрой прикидки кредитный калькулятор в Excel можно сделать за пару минут с помощью всего одной функции и пары простых формул. Для расчета ежемесячной выплаты по аннуитетному кредиту (т.е. кредиту, где выплаты производятся равными суммами – таких сейчас большинство) в Excel есть специальная функция ПЛТ (PMT) из категории Финансовые (Financial) . Выделяем ячейку, где хотим получить результат, жмем на кнопку fx в строке формул, находим функцию ПЛТ в списке и жмем ОК. В следующем окне нужно будет ввести аргументы для расчета:

  • Ставка – процентная ставка по кредиту в пересчете на период выплаты, т.е. на месяцы. Если годовая ставка 12%, то на один месяц должно приходиться по 1% соответственно.
  • Кпер – количество периодов, т.е. срок кредита в месяцах.
  • Пс – начальный баланс, т.е. сумма кредита.
  • Бс – конечный баланс, т.е. баланс с которым мы должны по идее прийти к концу срока. Очевидно =0, т.е. никто никому ничего не должен.
  • Тип – способ учета ежемесячных выплат. Если равен 1, то выплаты учитываются на начало месяца, если равен 0, то на конец. У нас в России абсолютное большинство банков работает по второму варианту, поэтому вводим 0.

Также полезно будет прикинуть общий объем выплат и переплату, т.е. ту сумму, которую мы отдаем банку за временно использование его денег. Это можно сделать с помощью простых формул:

Вариант 2. Добавляем детализацию

Если хочется более детализированного расчета, то можно воспользоваться еще двумя полезными финансовыми функциями Excel – ОСПЛТ (PPMT) и ПРПЛТ (IPMT) . Первая из них вычисляет ту часть очередного платежа, которая приходится на выплату самого кредита (тела кредита), а вторая может посчитать ту часть, которая придется на проценты банку. Добавим к нашему предыдущему примеру небольшую шапку таблицы с подробным расчетом и номера периодов (месяцев):

Функция ОСПЛТ (PPMT) в ячейке B17 вводится по аналогии с ПЛТ в предыдущем примере:

Добавился только параметр Период с номером текущего месяца (выплаты) и закрепление знаком $ некоторых ссылок, т.к. впоследствии мы эту формулу будем копировать вниз. Функция ПРПЛТ (IPMT) для вычисления процентной части вводится аналогично. Осталось скопировать введенные формулы вниз до последнего периода кредита и добавить столбцы с простыми формулами для вычисления общей суммы ежемесячных выплат (она постоянна и равна вычисленной выше в ячейке C7) и, ради интереса, оставшейся сумме долга:

Чтобы сделать наш калькулятор более универсальным и способным автоматически подстраиваться под любой срок кредита, имеет смысл немного подправить формулы. В ячейке А18 лучше использовать формулу вида:

Эта формула проверяет с помощью функции ЕСЛИ (IF) достигли мы последнего периода или нет, и выводит пустую текстовую строку (“”) в том случае, если достигли, либо номер следующего периода. При копировании такой формулы вниз на большое количество строк мы получим номера периодов как раз до нужного предела (срока кредита). В остальных ячейках этой строки можно использовать похожую конструкцию с проверкой на присутствие номера периода:

=ЕСЛИ(A18<>“”; текущая формула; “”)

Т.е. если номер периода не пустой, то мы вычисляем сумму выплат с помощью наших формул с ПРПЛТ и ОСПЛТ. Если же номера нет, то выводим пустую текстовую строку:

Вариант 3. Досрочное погашение с уменьшением срока или выплаты

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

В случае уменьшения срока придется дополнительно с помощью функции ЕСЛИ (IF) проверять – не достигли мы нулевого баланса раньше срока:

А в случае уменьшения выплаты – заново пересчитывать ежемесячный взнос начиная со следующего после досрочной выплаты периода:

Читайте также:  В excel формула еслиошибка

Вариант 4. Кредитный калькулятор с нерегулярными выплатами

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

  • в зеленые ячейки пользователь вводит произвольные даты платежей и их суммы
  • отрицательные суммы – наши выплаты банку, положительные – берем дополнительный кредит к уже имеющемуся
  • подсчитать точное количество дней между двумя датами (и процентов, которые на них приходятся) лучше с помощью функции ДОЛЯГОДА (YEARFRAC)

Источник: www.planetaexcel.ru

Функция ПЛТ

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

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

Воспользуйтесь средством Excel Formula Coach для расчета ежемесячных выплат по ссуде. При этом вы узнаете, как использовать функцию ПЛТ в формуле.

Синтаксис

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

Примечание: Более подробное описание аргументов функции ПЛТ см. в описании функции ПС.

Аргументы функции ПЛТ описаны ниже.

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

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

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

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

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

Когда нужно платить

В конце периода

В начале периода

Замечания

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

Убедитесь, что вы последовательны в выборе единиц измерения для задания аргументов “ставка” и “кпер”. Если вы делаете ежемесячные выплаты по четырехгодичному займу из расчета 12 процентов годовых, то используйте значения 12%/12 для задания аргумента “ставка” и 4*12 для задания аргумента “кпер”. Если вы делаете ежегодные платежи по тому же займу, то используйте 12 процентов для задания аргумента “ставка” и 4 для задания аргумента “кпер”.

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

Пример

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

Источник: support.office.com

Примеры функции ПЛТ в Excel: расчет выплат по аннуитетному кредиту

Функция ПЛТ в Excel входит в категорию «Финансовых». Она возвращает размер периодического платежа для аннуитета с учетом постоянства сумм платежей и процентной ставки. Рассмотрим подробнее.

Синтаксис и особенности функции ПЛТ

Синтаксис функции: ставка; кпер; пс; [бс]; [тип].

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

Особенности функционирования ПЛТ:

  1. В расчете периодического платежа участвуют только выплаты по основному долгу и платежи по процентам. Не учитываются налоги, комиссии, дополнительные взносы, резервные платежи, иногда связываемые с займом.
  2. При задании аргумента «Ставка» необходимо учесть периодичность начисления процентов. При ссуде под 6% для квартальной ставки используется значение 6%/4; для ежемесячной ставки – 6%/12.
  3. Аргумент «Кпер» указывает общее количество выплат по кредиту. Если человек совершает ежемесячные платежи по трехгодичному займу, то для задания аргумента используется значение 3*12.



Примеры функции ПЛТ в Excel

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

Размер займа указывается со знаком «минус», т.к. эти деньги кредитная организация «дает», «теряет». Для записи значения процентной ставки необходимо использовать процентный формат. Если записывать в числовом, то применяется десятичное число (0,08).

Нажимаем кнопку fx («Вставить функцию»). Откроется окно «Мастер функций». В категории «Финансовые» выбираем функцию ПЛТ. Заполняем аргументы:

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

Обратите внимание! В поле «Ставка» значение годовых процентов поделено на 12: платежи по кредиту выполняются ежемесячно.

Читайте также:  Номер строки в excel формула

Ежемесячные выплаты по займу в соответствии с указанными в качестве аргументов условиями составляют 1 037,03 руб.

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

Исключим из расчета ежемесячных выплат по займу платеж, произведенный в начале периода:

Для этого в качестве аргумента «Тип» нужно указать значение 1.

Детализируем расчет, используя функции ОСПЛТ и ПРПЛТ. С помощью первой покажем тело кредита, посредством второй – проценты.

Для подробного расчета составим таблицу:

Рассчитаем тело кредита с помощью функции ОСПЛТ. Аргументы заполняются по аналогии с функцией ПЛТ:

В поле «Период» указываем номер периода, для которого рассчитывается основной долг.

Заполняем аргументы функции ПРПЛТ аналогично:

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

Рассчитываем остаток по основному долгу. Получаем таблицу следующего вида:

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

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

Источник: exceltable.com

Персональный сайт учителя информатики

Сайт учителя о компьютерах и программном обеспечении (пакет MS Office 2010), методические разработки

Применение функций ПЛТ (бывшая ППЛАТ) и ПРОЦПЛАТ (бывшая ПЛПРОЦ) в табличном процессоре MS Excel.

Здравствуйте, уважаемые читатели блога!

Вам приходилось брать кредит в банке? Тогда эта статья для Вас. При оценке и анализе вариантов займов необходимо получить конечные значения (а сколько же придется заплатить?) для разных наборов исходных данных (в данном случае процентных ставок). Одним из преимуществ табличного процессора MS Excel является возможность быстрого решения подобных задач и автоматического перерасчета результатов при изменении исходных данных. Допустим вы планируете какой-либо проект и для этого берете кредит в банке. В какой срок лучше отдать кредит, какие процентные ставки выбрать? Для решения подобных задач в MS Excel применяется Таблица подстановки. Использование этого средства MS Excel происходит таким образом.

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

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

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

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

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

В ячейку D7 вводим формулу периодических постоянных выплат по займу при условии, что сумму необходимо погасить в течении срока займа: = ПЛТ (C4/12;C3*12;C2)

Процентную ставку делим на 12 в случае ежемесячных платежей и формат ячейки выбираем процентный – процентная ставка в этом случае записывается т.о.: 12% – 0,0125 – формат ячейки – процентный.

Кпер – число периодов выплат. Если период в годах, то для вычисления ежемесячных выплат умножаем на 12.

Пс – указываем сумму, которую берем взаймы (в нашем случае – это 100000).

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

Выделяем диапазон ячеек, содержащий значения процентных ставок и формулы для расчета – C7:D18.

Выполните команду Данные – Анализ “что если” – Таблица данных. На экране появится диалоговое окно Таблица данных. (см.рис). Это окно используется для задания рабочей ячейки, на которую ссылается формула расчета. В нашем примере это ячейка С4, которую необходимо указать в поле Подставлять значения по строкам в:.

Если исходные данные расположены в столбце, то ссылку на рабочую ячейку необходимо ввести в поле Подставлять значения по столбцам в:. После нажатия кнопкиОК программа заполнит колонку результатами. Полученные числа имеют знак “-”.

Допустим, что вам захотелось определить, какая часть платежа идет на погашение процента по кредиту, а какая – проценты по кредиту. Для этого в следующий столбец, в ячейку Е7 необходимо ввести формулу: = ПРОЦПЛАТ (C4/12;1;C3*12;C2) (см.рис).

Затем опять выполните команду Данные – Анализ “что если” – Таблица данных, предварительно выделив необходимый диапазон ячеек. После нажатия кнопки ОКпоявляется таблица Плата по процентам за 1 мес. (см.рис). Если вас не испугают эти цифры, то можете смело отправляться в банк за ссудой.

Удачи в расчетах платежей по процентам

Источник: informatikrus.ru