Время выполнения — 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дставитель банка.
Задание
Рассчитайте 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, там полно финансовых функций и рассчитать платеж по кредиту не проблема.
Используемые переменные:
Кпер — число периодов платежей по кредиту (ежемесячно на 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 ежемесячно. Определить за сколько периодов мы погасим кредит поможет функция КПЕР() .
Пишем минус 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%.
