По-настоящему мощным инструментом Excel является благодаря своей уникальной многофункциональности и умению решать задачи людей из разных профессиональных областей. Excel незаменим для менеджеров и экономистов, предпринимателей и финансистов, бухгалтеров и аналитиков, математиков и инженеров. Универсальность ему придают специфические встроенные функции, которые те или иные специалисты используют в своих расчётах.
Одна из самых больших и популярных категорий функций - финансовые. В последней версии Excel есть 55 функций, относящихся к этой группе. Многие из них специфические и узконаправленные, но некоторые могут пригодиться практически каждому. Одна из таких базовых функций - ПЛТ (PMT).
Как гласит официальная справка, функция ПЛТ возвращает сумму периодического платежа для аннуитета на основе постоянства сумм платежей и постоянной процентной ставки. Если Вас смущает специфический термин "аннуитет" - не пугайтесь. Иными словами, с помощью функции ПЛТ можно рассчитать сумму, которую нужно будет выплачивать каждый месяц при условии, что процент по кредиту не изменится и платежи вносятся регулярно равными суммами.
Функция имеет следующий синтаксис:
ПЛТ(ставка; кпер; пс; [бс]; [тип])
Разберем по очереди все аргументы:
Особенностью синтаксиса функции является указание направления денежного потока. Если денежный поток входящий (например, сумма полученного кредита, указанная в аргументе Пс), то необходимо указывать его как положительное число. Исходящие потоки наоборот, указываются как отрицательные числа (например, после вычисления функция ПЛТ вернет отрицательный результат, так как размер платежа по кредиту - это исходящий денежный поток).
Задача 1. Расчет суммы выплат по кредиту
Предположим, что в банке получен кредит на сумму 1 000 000 руб. под 17,5% годовых на срок 6 лет. Кредит будет погашаться равными платежами ежемесячно на протяжении всего срока займа. К концу срока будет выплачена вся сумма долга. Первый платеж будет внесен в конце первого периода. Необходимо найти величину ежемесячного платежа.
Итак, нам известна годовая ставка, а кредит будет погашаться ежемесячно. Значит для расчета нам потребуется перевести годовую ставку в месячную, разделив 17,5% на 12 месяцев. В первый аргумент записываем 17,5%/12 .
Кредит получен на 6 лет. Выплачивается ежемесячно. Значит, количество периодов выплат = 6*12. Во второй аргумент записываем 72 .
В третий аргумент пишем сумму кредита. Она равна 1 000 000 руб. (для займополучателя это входящий денежный поток, указываем его как положительное число).
Четвертый аргумент опустим, так как сумма будет полностью погашена к концу срока. Пятый аргумент также опустим, так как выплаты производятся в конце периода.
Формула примет вид:
ПЛТ(17,5%/12;72;1000000)
Результат вычисления равен -22526,05 руб . Число отрицательное, так как платеж по кредиту для займополучателя является исходящим денежным потоком. Именно такую сумму нужно будет вносить каждый месяц для погашения кредита, описанного в условии.
Чтобы посчитать сумму итоговой переплаты, нужно умножить ежемесячный платеж на число периодов (Кпер) и вычесть из полученного результата сумму займа (Пс).
Задача 2. Расчет суммы пополнения депозита для накопления определенного объема средств
В банке открыт пополняемый депозит со ставкой 9% годовых. Вы планируете каждый квартал вносить на депозит одинаковую сумму денег (например, часть полученной квартальной премии) с целью накопить на счете через 4 года ровно 1 000 000 руб. Вопрос: на какую сумму нужно пополнять счёт каждый квартал?
Первый аргумент указываем как 9%/4 (так как годовую ставку нужно перевести в квартальную), второй аргумент = 4*4 (4 года по 4 квартала - итого 16 взносов). Третий аргумент - сумма кредита. Его мы принимаем за 0, так как ничего не брали. Четвертый аргумент - будущая стоимость. Указываем сумму, которую хотим накопить (1 000 000 руб.). Пятый аргумент снова опускаем (выплаты в конце периода, это самая распространенная ситуация).
Получим формулу:
ПЛТ(9%/4;4*4;0;1000000).
Результат вычисления: -52 616,63 руб. Такую сумму нужно вносить на указанный депозит каждый квартал, чтобы через четыре года иметь на счету миллион рублей.
Общая сумма внесенных средств = 52616,63 * 16 = 841 866,08 руб. Остальное накоплено за счет процентов.
При использовании функции обращайте внимание на следующие моменты:
Поддержать наш проект и его дальнейшее развитие можно .
Ваши вопросы по статье можете задавать через нашего бота обратной связи в Telegram:
ЛАБОРАТОРНЫЕ РАБОТЫ
Лабораторная работа №1
Тема: Финансовая функция ПЛТ
Время выполнения - 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):
где Р - пс;
i - ставка;
n - кпер.
Отметим, что очень важно быть последовательным в выборе единиц измерения для задания аргументов ставка и КПЕР. Например, если вы делаете ежемесячные выплаты по четырехгодичному займу из расчета 12% годовых, то для задания аргумента ставка используйте 12%/12, а для задания аргумента КПЕР - 4*12. Если вы делаете ежегодные платежи по тому же займу, то для задания аргумента ставка используйте 12%, а для задания аргумента КПЕР - 4.
Для нахождения общей суммы, выплачиваемой на протяжении интервала выплат, умножьте возвращаемое функцией ПЛТ значение на величину КПЕР. Интервал выплат - это последовательность постоянных денежных платежей, осуществляемых за непрерывный период. Например, заем под автомобиль или заклад являются интервалами выплат. В функциях, связанных с интервалами выплат, выплачиваемые вами деньги, такие как депозит на накопление, представляются отрицательным числом, а деньги, которые вы получаете, такие как чеки на дивиденды, представляются положительным числом. Например, депозит в банк на сумму 1000 руб. представляется аргументом -1000, если вы вкладчик, и аргументом 1000, если вы - пpeдставитель банка.
Существуют сотни онлайновых финансовых планировщиков. Все они просты в использовании, но ограничены по функциональности. MS Excel на их фоне - настоящий комбайн. В нём есть 53 финансовые формулы на все случаи жизни, а для контроля и планирования бюджета полезно знать три из них.
Одна из актуальнейших функций, с помощью которой можно рассчитать сумму платежа по кредиту с аннуитетными платежами, то есть когда кредит выплачивается равными частями. Полное описание функции .
ПЛТ(ставка;кпер;пс;бс;тип)
Вычисляет процентную ставку по займу или инвестиции, базируясь на величине будущей стоимости. Полное описание функции .
СТАВКА(кпер;плт;пс;бс;тип;прогноз)
Возвращает эффективную (фактическую) годовую процентную ставку, если заданы номинальная годовая процентная ставка и количество периодов в году, за которые начисляются сложные проценты. Полное описание функции
Функция ПЛТ в Excel входит в категорию «Финансовых». Она возвращает размер периодического платежа для аннуитета с учетом постоянства сумм платежей и процентной ставки. Рассмотрим подробнее.
Синтаксис функции: ставка; кпер; пс; [бс]; [тип].
Расшифровка аргументов:
Особенности функционирования ПЛТ:
Для корректной работы функции необходимо правильно внести исходные данные:
Размер займа указывается со знаком «минус», т.к. эти деньги кредитная организация «дает», «теряет». Для записи значения процентной ставки необходимо использовать процентный формат. Если записывать в числовом, то применяется десятичное число (0,08).
Нажимаем кнопку fx («Вставить функцию»). Откроется окно «Мастер функций». В категории «Финансовые» выбираем функцию ПЛТ. Заполняем аргументы:
Когда курсор стоит в поле того или иного аргумента, внизу показывается «подсказка»: что необходимо вводить. Так как исходные данные введены в таблицу Excel, в качестве аргументов мы использовали ссылки на ячейки с соответствующими значениями. Но можно вводить и числовые значения.
Обратите внимание! В поле «Ставка» значение годовых процентов поделено на 12: платежи по кредиту выполняются ежемесячно.
Ежемесячные выплаты по займу в соответствии с указанными в качестве аргументов условиями составляют 1 037,03 руб.
Чтобы найти общую сумму, которую нужно выплатить за весь период (основной долг плюс проценты), умножим ежемесячный платеж по займу на значение «Кпер»:
ПЛТ(Ставка;Кол_пер;Пс;[Бс];[Тип])
Формула ПС применяется для расчета приведенной стоимости инвестиции. Данная функция обратная оператору ПЛТ . У неё точно такие же аргументы, но только вместо аргумента приведенной стоимости («ПС» ), которая собственно и рассчитывается, указывается сумма периодического платежа («Плт» ). Синтаксис соответственно такой:
ПС(Ставка;Кол_пер;Плт;[Бс];[Тип])
Следующий оператор применяется для вычисления чистой приведенной или дисконтированной стоимости . У данной функции два аргумента: ставка дисконтирования и значение выплат или поступлений. Правда, второй из них может иметь до 254 вариантов, представляющих денежные потоки. Синтаксис этой формулы такой:
ЧПС(Ставка;Значение1;Значение2;…)
Функция СТАВКА рассчитывает ставку процентов по аннуитету. Аргументами этого оператора является количество периодов («Кол_пер» ), величина регулярной выплаты («Плт» ) и сумма платежа («Пс» ). Кроме того, есть дополнительные необязательные аргументы: будущая стоимость («Бс» ) и указание в начале или в конце периода будет производиться платеж («Тип» ). Синтаксис принимает такой вид:
СТАВКА(Кол_пер;Плт;Пс[Бс];[Тип])
Оператор ЭФФЕКТ ведет расчет фактической (или эффективной) процентной ставки. У этой функции всего два аргумента: количество периодов в году, для которых применяется начисление процентов, а также номинальная ставка . Синтаксис её выглядит так:
ЭФФЕКТ(Ном_ставка;Кол_пер)
Нами были рассмотрены только самые востребованные финансовые функции . В общем, количество операторов из данной группы в несколько раз больше. Но и на данных примерах хорошо видна эффективность и простота применения этих инструментов, значительно облегчающих расчеты для пользователей.
В статье рассмотрены финансовые функции ПЛТ() , ОСПЛТ() , ПРПЛТ() , КПЕР() , СТАВКА() , ПС() , БС() , а также ОБЩДОХОД() и ОБЩПЛАТ() , которые используются для расчетов параметров аннуитетной схемы.
Данная статья входит в цикл статей о расчете параметров аннуитета. Перечень всех статей на нашем сайте об аннуитете.
В этой статье содержится небольшой раздел о теории аннуитета, краткое описание функций аннуитета и их аргументов, а также ссылки на статьи с примерами использования этих функций.
Аннуитет (иногда используются термины «рента», «финансовая рента») представляет собой однонаправленный денежный поток, элементы которого одинаковы по величине и производятся через равные периоды времени (например, когда платежи производятся ежегодно равными суммами).
Синтаксис ПРПЛТ (ставка; период; кпер; пс; бс, тип). (2.13)
Аргументы функции означают: ставка
период - задает период, для которого надо найти платежи по процентам, значение должно быть в интервале от 1 до "кпер";
кпер - общее число периодов платежей по аннуитету;
пс - приведенная к текущему моменту стоимость или общая сумма, которая на текущий момент равноценна ряду будущих платежей, называемая также основной суммой;
бс - требуемое значение будущей стоимости или остатка средств после последней выплаты;
тип - число 0 или 1, обозначающее, когда должна производиться выплата. Если этот аргумент опущен, то он полагается равным 0.
Если данная функция недоступна или возвращает ошибку # ИМЯ?, то установите и загрузите надстройку «Пакет анализа". Для этого в меню Сервис Выбрать команду Надстройки Excel. В списке надстроек выберите Пакет анализа и нажмите кнопку ОК. Следуйте инструкциям программы установки, если это необходимо.
Решение: ПРПЛТ (10% / 12, 1, 12 * 3; 800) = - 6,667 тыс. Грн.
Пример 2.28. За счет ежегодных отчислений в течение 6 лет был сформирован фонд в 500 тыс. Грн. Надо рассчитать, какой доход приносили вложения владельцу за последний год , если годовая ставка составляла 17,5%.
Решение: Доход за последний год (6 периодов) составил:
ПРПЛТ (17,5%; 6; 6;; 500) = 66,48110268 тыс. Грн.
Ежегодно полагалось ПЛТ (17,5%; 6;; 500) = - 53,627 тыс. Грн.
Сумма основного платежа по займу (выплата задолженности), который погашается равными платежами в конце или в начале каждого расчетного периода, на указанный период рассчитывается с помощью функции Excel ОСПЛТ:
Синтаксис ОСПЛТ (ставка; период; кпер; пс; бс; тип) (2.14)
или находится как разница между фиксированной периодической выплатой и процентам по непогашенной части займа. Аргументы функции означают: ставка - процентная ставка за период;
период - задает период, значение должно быть в интервале от 1 до "кпер";
кпер - общее число периодов выплат годовой ренты;
пс - приведенная стоимость, то есть общая сумма, которая равноценна ряду будущих платежей;
тип - число 0 или 1, обозначающее, когда должна производиться выплата.
Пример 2.29. Определите сумму основного платежа с двухлетней займа 2000 грн. за первый месяц из расчета 10% годовых. Начисление процентов ежемесячно.
Решение: Основной платеж по займу за первый месяц:
ОСПЛТ (10% / 12, 1, 2 * 12; 2000) = - 75,62 грн.
Накопленный доход по займу (сумму платежей по процентам), которая погашается равными платежами в конце или в начале каждого расчетного периода, между двумя периодами выплат рассчитывает в Excel функция ОБЩПЛАТ.
Синтаксис ОБЩПЛАТ (ставка; кпер; пс;
начпериод; конпериод; тип). (2.15)
Аргументы функции означают: ставка - процентная ставка; кпер пс нач_период -
кон_период -
тип - это выбор времени платежа.
Пример 2.30. Изданная заем под покупку недвижимости размером 125 тыс. Грн. сроком на 30 лет под 9% годовых, проценты начисляются ежемесячно. Определить величину процентных платежей а) за второй год, б) за первый месяц.
Решение: Кумулятивная выплата по процентам за второй год (с 13-го периода по 24-й) составит:
ОБЩПЛАТ (9% / 12; 30 * 12; 125 000; 13; 24; 0) = - 11135,23 грн. Одна выплата за первый месяц составит:
ОБЩПЛАТ (9% / 12; 30 * 12; 125 000, 1, 1, 0) = - 937,50 грн. Это же значение будет получено при расчете по формуле:
ПРПЛТ (9% / 12, 1, 30 * 12; 125 000) = - 937,50 грн. В Excel функция ОБЩДОХОД рассчитывает кумулятивную (нарастающим итогом) сумму, выплачиваемую в погашение основной суммы займа в промежутке между двумя периодами:
Синтаксис ОБЩДОХОД (ставка; кпер;
пс; начпериод; конпериод; тип). (2.16)
Аргументы функции означают:
ставка - процентная ставка;
кпер - это общее количество периодов выплат;
пс - это стоимость инвестиции на текущий момент;
нач_период - это номер первого периода, включенного в вычисления. Периоды выплат нумеруются, начиная с 1;
кон_период - это номер последнего периода, включенного в вычисления;
тип - это выбор времени платежа.
Пример 2.31. Изданная заем размером 125 тыс. Грн. сроком на 30 лет под 9% годовых, проценты начисляются ежемесячно. Определить величину основных выплат: а) за первый месяц; б) второй год (платежи с 13-го периода по 24-й).
решение:
а) ОБЩДОХОД (9% / 12; 30 * 12; 125000, 1, 1, 0) = - 68,27827118 грн.;
б) если заем погашается равными платежами в конце каждого расчетного периода, то размер выплаты задолженности за второй год составит:
ОБЩДОХОД (9% / 12; 30 * 12; 125000; 13; 24; 0) = - +934,1071234 грн. Периоды с 13-го по 24-й составляют второй год.
http://ru.excelfunctions.eu/%D0%91%D0%A1/A%D0%BD%D0%B3%D0%BB%D0%B8%D0%B9%D1%81%D0%BA%D0%BE%D0%BC
http://office.microsoft.com/ru-ru/excel-help/HP010069823.aspx
Применимо к: Microsoft Office Excel 2007
Печать
Показать все
Возвращает будущую стоимость инвестиции на основе периодических постоянных (равных по величине сумм) платежей и постоянной процентной ставки.
Синтаксис
БС (ставка ;кпер ;плт ;пс;тип)
Дополнительные сведения об аргументах функции БС, также о других финансовых функциях см. в описании функции ПС.
Ставка - процентная ставка за период.
Кпер - общее число периодов платежей по аннуитету.
Плт - выплата, производимая в каждый период; это значение не может меняться в течение всего периода выплат. Обычно аргумент «плт» состоит из основного платежа и платежа по процентам, но не включает других налогов и сборов. Если он опущен, аргумент «пс» является обязательным.
Пс - приведенная к текущему моменту стоимость или общая сумма, которая на текущий момент равноценна ряду будущих платежей. Если аргумент «пс» опущен, предполагается значение 0. В этом случае аргумент «плт» является обязательным.
Тип - число 0 или 1, обозначающее срок выплаты. Если аргумент «тип» опущен, предполагается значение 0.
Замечания
Убедитесь, что единицы измерения, выбранные для аргументов «ставка» и «кпер», соответствуют друг другу. При ежемесячных выплатах по четырехгодичному займу из расчета 12 процентов годовых используйте значение 12%/12 для аргумента «ставка» и 4*12 - для аргумента «кпер». При ежегодных платежах по тому же займу используйте значение 12% для аргумента «ставка» и 4 - для аргумента «кпер».
Все аргументы, которым соответствуют выплачиваемые денежные средства (например, сберегательные вклады), представляются в виде отрицательных чисел, а получаемые (например, дивиденды) - в виде положительных.
Пример 1
Инструкции
|
|
ПРИМЕЧАНИЕ. Годовая процентная ставка делится на 12, поскольку начисление сложных процентов производится ежемесячно.
Применимо к: Microsoft Office Excel 2007
Печать
Показать все
Возвращает сумму периодического платежа для аннуитета на основе постоянства сумм платежей и постоянства процентной ставки.
Синтаксис
ПЛТ (ставка ;кпер ;пс ;бс;тип)
Более подробное описание аргументов функции ПЛТ см. в описании функции ПС.
Ставка - процентная ставка по ссуде.
Кпер - общее число выплат по ссуде.
Пс - приведенная к текущему моменту стоимость или общая сумма, которая на текущий момент равноценна ряду будущих платежей, называемая также основной суммой.
Бс - требуемое значение будущей стоимости или остатка средств после последней выплаты. Если аргумент бс опущен, то он полагается равным 0 (нолю), т. е. для займа, например, значение бс равно 0.
Тип - число 0 (ноль) или 1, обозначающее, когда должна производиться выплата.
Замечания
Выплаты, возвращаемые функцией ПЛТ, включают основные платежи и платежи по процентам, но не включают налогов, резервных платежей или комиссий, иногда связываемых со ссудой.
Убедитесь, что вы последовательны в выборе единиц измерения для задания аргументов «ставка» и «кпер». Если вы делаете ежемесячные выплаты по четырехгодичному займу из расчета 12 процентов годовых, то используйте 12%/12 для задания аргумента «ставка» и 4*12 для задания аргумента «кпер». Если вы делаете ежегодные платежи по тому же займу, то используйте 12 процентов для задания аргумента «ставка» и 4 для задания аргумента «кпер».
Совет. Для нахождения общей суммы, выплачиваемой на протяжении интервала выплат, умножьте возвращаемое функцией ПЛТ значение на «кпер».
Пример 1
Чтобы этот пример проще было понять, скопируйте его на пустой лист.
Копирование примера
|
|