Функция ПЛТ в 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. Нажимаем “ОК”
Останется понаблюдать за результатом.
Как видно из строки формул – появились фигурные скобки (признак массива) и функция ТАБЛИЦА. Не ищите её просто так, она появится только при использовании “Таблицы данных” из “Анализ “что если?”.
Готово, наш небольшой калькулятор готов. Можно будет с помощью пользовательских форматов дописать “месяцев” к нашим периодам, но это как раз можно почитать в предыдущей статье.
Всем удачи!
Способы расчета аннуитетного платежа в Excel, SAS, Oracle
- Информация о материале
- Автор: Иванов Алексей Львович
- Категория: Статьи
- Опубликовано: 28 августа 2017
Формула для расчета аннуитетного платежа выглядит следующим образом:
Обозначение аргументов:
A – сумма кредита;
R – процентная ставка;
N – срок кредита;
Пример
Предположим мы хотим взять кредит на 50000 рублей, под 25% годовых на 30 месяцев, подставим известные значения в формулу, чтобы получить сумму месячного аннуитетного платежа, получается:
A = 50000 (сумма кредита)
R = 0.25/12 (месячная процентная ставка)
N = 30 (срок кредита в месяцах)
В результате расчета этой формулы мы получим значение 2258.158, это означает, что тридцать месяцев нам придется выплачивать эту сумму.
Расчет аннуитетного платежа с помощью Excel
Для расчета месячного платежа в Excel есть функция PMT (ПЛТ на русском).
Функция PMT имеет синтаксис: =PMT(rate, nper, pv, [fv],[type])
Аргументы функции PMT(ПЛТ):
rate – процентная ставка;
nper – срок кредита;
pv – сумма кредита;
аргументы [fv] и [type] являются необязательными, [fv] – значение будущей стоимости, аргумент [type] определяет, когда нужно платить он может быть равен 0 – в конце периода (по умолчанию) и 1 – в начале периода.
Возьмем за основу пример выше, подставим значения в эту функцию и сравним результат (перед сумой кредита нужно поставить знак минус, чтобы получить положительное число).
Как видим, значения совпадают, мы можем изменить формат ячейки на числовой и увидеть неокругленную сумму 2258,158162640140
Расчет аннуитетного платежа с помощью SAS
В SAS есть очень гибкая функция mort, с помощью которой можно вычислять не только сумму месячного аннуитетного платежа, но и сумму кредита, ставку и срок.
Функция mort имеет следующий синтаксис: MORT(a,p,r,n).
Аргументы функции mort
a – сумма кредита;
p – платеж;
r – процентная ставка;
n – срок кредита;
Наша цель определить сумму месячного аннуитетного платежа, подставим в правильном порядке значения из предыдущего примера в функцию mort, для второго аргумента поставим символ точку «.» получим следующий код:
data mort_example;
payment = mort(50000, . , 0.25/12, 30);
run;
proc print data=mort_example;
run;
После выполнения данного кода увидим результат:
Как было написано выше, с помощью функции mort можно вычислять любые другие неизвестные аргументы, предположим мы знаем сумму месячного платежа, сумму кредита и ставку, требуется вычислить срок, тогда функция вызываться должна так:
term = mort( 50000, 2258.16 , 0.25/12, .);
Расчет аннуитетного платежа с помощью Oracle PL SQL
В БД Oracle нет встроенной функции для расчета месячного аннуитетного платежа, но её довольно просто написать самому, например вот так:
create or replace function getMortPayment(pAmount in number, pRate in number, pTerm in number ) return number is /*Формула расчета месячного аннуитетного платежа p := a * r * ((1+r)^n) / ((1+r)^n) -1; */ vPayment number; begin vPayment := pAmount * pRate*power((1+pRate),pTerm)/ (power((1+pRate),pTerm)-1) ; return vPayment; end;
Вызовем функцию
select getMortPayment(pAmount => 50000, pRate => 0.25/12, pTerm => 30) as pfrom dual
Получим результат 2258,15816264014
Как видим, рассчитать месячный аннуитетный платеж совсем не сложно 🙂
Добавить комментарий
КУРС
EXCEL ACADEMY
Научитесь использовать все прикладные инструменты из функционала MS Excel.
Мы подумали, что блок статей о формулах Excel просто не сможет обойтись без обозревания таких удобных и востребованных функций, как финансовые. Поэтому представляем вашему вниманию небольшой экскурс в мир «денежных» функций.
По «старой» традиции начнем с того, как найти финансовые формулы в программе. Сделать это очень просто: на главной панели найти кнопку «Формулы», нажать на нее и выбрать в появившемся списке название раздела «Финансовые».
Дальше выпадет перечень формул, которые вы можете использовать:
КУРС
EXCEL ACADEMY
Научитесь использовать все прикладные инструменты из функционала MS Excel.
5. Формулы ПРПЛТ(), ОБЩПЛАТ()
Функция очень похожа на ОСПЛТ() с небольшой оговоркой: она помогает высчитать размер выплат по процентам за выбранный период, предполагая неизменяемыми размер платежей и ставку.
У функция ПРПЛТ() точно такие же аргументы, как и у ОСПЛТ(), и выглядит в строке ввода формул так:
ПРПЛТ(Ставка; Период; Кпер; Пс; БС; Тип)
Применим формулу к нашему примеру:
КУРС
EXCEL ACADEMY
Научитесь использовать все прикладные инструменты из функционала MS Excel.
В предыдущей заметке была рассмотрена техническая сторона расчета кредита. Но если практически, то запускаем Excel, там полно финансовых функций и рассчитать платеж по кредиту не проблема. Используемые переменные: Кпер — число периодов платежей по кредиту (ежемесячно на 5 лет можем написать 5*12). Ставка — процентная ставка (если 17% годовых, то пишем или 17%/12 или 0,17/12, но никак не 17 просто). Плт — платеж, производимый в каждый период, состоит из основного платежа и платежа по процентам. Пс — сумма на текущий момент. Бс — будущая стоимость. Тип — число 0 – оплата в конце периода, число 1 – оплата в начале периода (по умолчанию – 0). Эти переменные могут быть положительными или отрицательными, в зависимости от того получаем мы деньги или отдаем. Используя функцию ПЛТ(ставка; кпер; пс; [бс]; [тип]), рассчитаем сумму ежемесячного платежа при сумме кредита 50 000 руб. сроком на 2 года по 17% годовых. Аргументы в квадратных скобках необязательные. Пишем в ячейке =ПЛТ(17%/12; 2*12; 50000). Получаем -2 472,11р. Минус говорит, что это наши расходы. Другие функции выглядят так: КПЕР(ставка; плт; пс; [бс]; [тип]) – вычисляет количество периодов платежей ПС(ставка; кпер; плт; [бс]; [тип]) – сумма денег сейчас БС(ставка; кпер; плт; [пс]; [тип]) – сумма денег спустя определенное (кпер) время СТАВКА(кпер; плт; пс; [бс]; [тип]; [предположение]) – вычисляет ставку за период (а не за год) ———————————————————————— Рассмотрим применение этих функций. Пример 1. Пусть ставка кредита 17% годовых, сумма 100 000 руб. и мы можем выплачивать по 5 000 ежемесячно. Определить за сколько периодов мы погасим кредит поможет функция КПЕР(). =КПЕР(17%/12;-5000;100000) Пишем минус 5000, т.к. отдаем деньги. Получаем приблизительно 23,68 периодов, т.е. практически 2 года. ————————————————————– Пример 2. На какую сумму можно взять кредит, если ставка 17% годовых и выплачивать мы можем по 10 000 руб. на протяжении 2-х лет (24 периода). Используем ПС(). =ПС(17%/12;24;-10000) Получаем приблизительно 202 256 руб. ————————————————————– Пример 3. Какую сумму кредита можно взять под 17% годовых с ежемесячным начислением процентов, если выплачивать мы можем на протяжении 2-х лет (24 периода) и в итоге готовы выплатить 300 000 руб. Используем ПС(). =ПС(17%/12;24;;-300000) Здесь у нас нет переменной плт, зато есть переменная бс. Ее пишем с минусом, т.к. отдаем эту сумму. Получаем приблизительно 214 041 руб. ————————————————————– Пример 4. Хотим сделать вклад под 15% годовых с ежемесячным начислением процентов на сумму 100 000 руб. и на 3 года (36 периодов). Используем БС(). =БС(15%/12;36;;-100000) Здесь переменная пс равна -100 000 минус обозначает то, что мы отдаем деньги. Результат 156 394 руб. ————————————————————– Пример 5. Пусть те же условия, что и в примере выше, только ежемесячно мы будем пополнять счет еще на 1 000 руб. =БС(15%/12;36;-1000;-100000) Результат 201 510 руб. ————————————————————– Пример 6. То же, что и в примере 4, только ежемесячно будем не пополнять, а снимать по 1 000 руб. =БС(15%/12;36;1000;-100000) Т.к. снимаем деньги, то переменная плт с плюсом. Результат 111 279 руб. ————————————————————– Пример 7. Пусть хотим взять кредит в 100 000 рублей на 2 года, выплачивать можем по 5 000 руб. ежемесячно. Какая ставка нам подходит. Используем функцию СТАВКА(). =СТАВКА(24;-5000;100000) Результат 1,51308%, но это за месяц. Умножим на 12, получим 18,157%.
Время выполнения — 3 часа.
Цель работы: научиться использовать финансовую функцию ПЛТ табличного процессора Microsoft Excel для решения экономических задач, с использованием представленных примеров.
1.Решить все описанные упражнения самостоятельно, руководствуясь методическими указаниями;
2. Выполнить задание;
3. Проверить свои знания по контрольным вопросам и сдать лабораторную работу.
Основные сведения по теме:
Финансовая функция ПЛТ
Лист1 в книге ФИНАНСОВЫЙ АНАЛИЗ переименуйте в ПЛТ. Все упражнения в данной лабораторной работе выполняйте на листе ПЛТ.
Рассмотрим пример расчета 30-летней ипотечной ссуды со ставкой 8% годовых при начальном взносе 20% и ежемесячной (ежегодной) выплате с помощью функции ПЛТ.
Для приведенного на рис.4.1.1 ипотечного расчета в ячейки введены формулы, показанные на рис. 4.1.2.
Рис. 4.1.1 Расчет ипотечной ссуды
Введите представленные на рис. 4.1.2. данные на лист ПЛТ и сравните полученный результат с данными на рис. 4.1.1.
Рис. 4.1.2 Формулы для расчета ипотечной ссуды
Функция ПЛТ вычисляет величину постоянной периодической выплаты ренты (например, регулярных платежей по займу) при постоянном процентной ставке.
Синтаксис: ПЛТ(ставка; кпер; пс; бс; тип).
ставка—процентная ставка по ссуде, кпер — общее число выплат по ссуде, пс — приведенная к текущему моменту стоимость, или общая сумма, которая на текущий момент равноценна ряду будущих платежей, называемая также основной суммой, бс — требуемое значение будущей стоимости, или остатка средств после последней выплаты. Если аргумент бс опущен, то он полагается равным 0 (нулю), т. е. для займа, например, значение бс равно 0, Тип — число 0 (нуль) или 1, обозначающее, когда должна производиться выплата.
Если бс = 0 и тип = 0, то функция ПЛТ вычисляет по формуле (1):
(1)
Отметим, что очень важно быть последовательным в выборе единиц измерения для задания аргументов ставка и КПЕР. Например, если вы делаете ежемесячные выплаты по четырехгодичному займу из расчета 12% годовых, то для задания аргумента ставка используйте 12%/12, а для задания аргумента КПЕР — 4*12. Если вы делаете ежегодные платежи по тому же займу, то для задания аргумента ставка используйте 12%, а для задания аргумента КПЕР — 4.
Для нахождения общей суммы, выплачиваемой на протяжении интервала выплат, умножьте возвращаемое функцией ПЛТ значение на величину КПЕР. Интервал выплат — это последовательность постоянных денежных платежей, осуществляемых за непрерывный период. Например, заем под автомобиль или заклад являются интервалами выплат. В функциях, связанных с интервалами выплат, выплачиваемые вами деньги, такие как депозит на накопление, представляются отрицательным числом, а деньги, которые вы получаете, такие как чеки на дивиденды, представляются положительным числом. Например, депозит в банк на сумму 1000 руб. представляется аргументом -1000, если вы вкладчик, и аргументом 1000, если вы — пpeдставитель банка.
Читайте также: Как очистить сопла картриджа canon
Задание
Рассчитайте 5-летнюю ипотечную ссуду в размере 500 тыс. руб. со ставкой 18% годовых при начальном взносе 20% и ежемесячной (ежегодной) выплате.
Задание выполняйте в новой книге Задания.xls , Лист1 переименуйте в ПЛТ.
Порядок отчета лабораторной работы:
При отчете лабораторной работы необходимо:
1) Продемонстрировать выполненные упражнения, описанные в методических указаниях;
2) Продемонстрировать выполненное задание, прокомментировать порядок его выполнения и объяснить полученные результаты;
3) Ответить на контрольные вопросы.
Контрольные вопросы:
1) Какие задачи позволяет решать Microsoft Excel?
2) Как вызывается нужная функция?
3) Какие категории функций Вам известны?
4) Что позволяет вычислить функция ПЛТ?
5) Какие параметры у функции ПЛТ?
Список литературы:
1. Гарнаев А.Ю. Использование MS Excel и VBA в экономике и финансах.-СПб.: БХВ- Санкт-Петербург, 1999.- 336 с., ил.
2. Информатика. Серия «Учебники, учебные пособия». И57 // Под ред. П.П. Беленького. – Ростов н/Д: Феникс, 2002. 448с.
3. А.В. Могилев, Пак, Хеннер. Информатика. М: Изд. центр «Академия», 2000г.- 816 с.
Лабораторная работа №2
Организация стока поверхностных вод: Наибольшее количество влаги на земном шаре испаряется с поверхности морей и океанов (88‰).
Папиллярные узоры пальцев рук — маркер спортивных способностей: дерматоглифические признаки формируются на 3-5 месяце беременности, не изменяются в течение жизни.
Общие условия выбора системы дренажа: Система дренажа выбирается в зависимости от характера защищаемого.
Функция ПЛТ в 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. Нажимаем «ОК»
Останется понаблюдать за результатом.
Как видно из строки формул — появились фигурные скобки (признак массива) и функция ТАБЛИЦА. Не ищите её просто так, она появится только при использовании «Таблицы данных» из «Анализ «что если?».
Готово, наш небольшой калькулятор готов. Можно будет с помощью пользовательских форматов дописать «месяцев» к нашим периодам, но это как раз можно почитать в предыдущей статье.
Простая математика, Excel. Как минимум. Но не только.
Страницы
пятница, 5 ноября 2010 г.
Финансовые функции Excel
В предыдущей заметке была рассмотрена техническая сторона расчета кредита.
Но если практически, то запускаем Excel, там полно финансовых функций и рассчитать платеж по кредиту не проблема.
Используемые переменные:
Читайте также: Как убрать из друзей в одноклассниках человека
Эти переменные могут быть положительными или отрицательными, в зависимости от того получаем мы деньги или отдаем.
Используя функцию ПЛТ(ставка; кпер; пс; [бс]; [тип]) , рассчитаем сумму ежемесячного платежа при сумме кредита 50 000 руб. сроком на 2 года по 17% годовых. Аргументы в квадратных скобках необязательные.
Пишем в ячейке =ПЛТ(17%/12; 2*12; 50000) . Получаем -2 472,11р. Минус говорит, что это наши расходы.
Другие функции выглядят так:
КПЕР(ставка; плт; пс; [бс]; [тип]) — вычисляет количество периодов платежей
ПС(ставка; кпер; плт; [бс]; [тип]) — сумма денег сейчас
БС(ставка; кпер; плт; [пс]; [тип]) — сумма денег спустя определенное (кпер) время
Рассмотрим применение этих функций.
Пример 1. Пусть ставка кредита 17% годовых, сумма 100 000 руб. и мы можем выплачивать по 5 000 ежемесячно. Определить за сколько периодов мы погасим кредит поможет функция КПЕР() .
Пишем минус 5000, т.к. отдаем деньги. Получаем приблизительно 23,68 периодов, т.е. практически 2 года. ————————————————————— Пример 2. На какую сумму можно взять кредит, если ставка 17% годовых и выплачивать мы можем по 10 000 руб. на протяжении 2-х лет (24 периода). Используем ПС() .
Получаем приблизительно 202 256 руб. ————————————————————— Пример 3. Какую сумму кредита можно взять под 17% годовых с ежемесячным начислением процентов, если выплачивать мы можем на протяжении 2-х лет (24 периода) и в итоге готовы выплатить 300 000 руб. Используем ПС() .
Здесь у нас нет переменной плт , зато есть переменная бс . Ее пишем с минусом, т.к. отдаем эту сумму. Получаем приблизительно 214 041 руб. ————————————————————— Пример 4. Хотим сделать вклад под 15% годовых с ежемесячным начислением процентов на сумму 100 000 руб. и на 3 года (36 периодов). Используем БС( ).
Здесь переменная пс равна -100 000 минус обозначает то, что мы отдаем деньги. Результат 156 394 руб. ————————————————————— Пример 5. Пусть те же условия, что и в примере выше, только ежемесячно мы будем пополнять счет еще на 1 000 руб.
Результат 201 510 руб. ————————————————————— Пример 6. То же, что и в примере 4, только ежемесячно будем не пополнять, а снимать по 1 000 руб.
Т.к. снимаем деньги, то переменная плт с плюсом. Результат 111 279 руб. ————————————————————— Пример 7. Пусть хотим взять кредит в 100 000 рублей на 2 года, выплачивать можем по 5 000 руб. ежемесячно. Какая ставка нам подходит. Используем функцию СТАВКА() .
Результат 1,51308%, но это за месяц. Умножим на 12, получим 18,157%.
ли со статьей или есть что добавить?