Функция плт в 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. Нажимаем “ОК”

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

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

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

Всем удачи!

Способы расчета аннуитетного платежа в 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%.