Методические указания к лабораторным работам по финансовой математике

Департамент образования, науки и молодёжной политики Воронежской области

ГОБУ спо ВО «воронежский государственный промышленно-гуманитарный колледж» (вгпгк)

М Е Т О Д И Ч Е С К И Е У К А З А Н И Я

к лабораторным работам

по дисциплине

ЕН. 02. ФИНАНСОВАЯ МАТЕМАТИКА

для специальности

38.02.07 Банковское дело

2014

ББК 65.431

М54

Печатается по решению методического совета

Воронежского государственного

промышленно-гуманитарного колледжа

Составитель Н.Л. ЛАТЫШЕВА

ВВЕДЕНИЕ

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

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

Выполнение лабораторных работ требует предварительной подготовки студентов. Она включает знание теории изучаемой проблемы, вычислительной техники и программного обеспечения, методики выполнения работы.

ФИНАНСОВО-ЭКОНОМИЧЕСКИЕ РАСЧЕТЫ В MS EXCEL.

Количественный финансовый анализ предполагает применение унифицированных моделей и методов расчета финансовых показателей.

Условно методы финансовой математики делятся на 2 категории: базовые и прикладные.

К базовым методам и моделям относятся:

простые и сложные проценты как основа операций, связанных с наращением или дисконтированием платежей;

расчет потоков платежей применительно к различным видам финансовых рент.

К прикладным методам финансовых расчетов относятся:

планирование и оценка эффективности финансово – кредитных операций;

планирование погашения задолженности;

финансовые расчеты по ценным бумагам;

планирование и анализ инвестиционных проектов и др.

К основным средствам Excel, используемым для проведения финансового анализа, относятся:

финансовые функции Excel;

подбор параметра;

таблица подстановки.

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

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

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

Осуществляется вызов Мастера функций с помощью команды ВСТАВКА  Функции или нажатием одноименной кнопки на панели инструментов Стандартная.

Выполняется выбор категории Финансовые.

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

Завершение ввода аргументов и запуск расчета значения встроенной функции выполняется нажатием кнопки Готово.

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

При установке курсора в ячейку, содержащую формулу, построенную с использованием финансовых функций, и выполнении команды СЕРВИС  Подбор параметра, появляется диалоговое окно, в котором задается требуемое значение функции. В поле Установить в ячейке: указывается ссылка на ячейку с нужным Вам значением. В поле Значение: указывается само значение. В поле Изменяя значение ячейки: указывается адрес ячейки, содержащей значение одного из аргументов функции. Excel решает обратную задачу: подбор значения аргумента для заданного значения функции.

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

Одним из достоинств Excel является возможность проводить оценку и анализ вариантов при различных наборах исходных данных, а также возможность автоматического пересчета результатов при их изменении. Для решения подобных задач используется Таблица подстановки (ДАННЫЕ  Таблица подстановки).

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

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

Ссылка на ячейку ввода значений указывается в диалоговом окне Таблица подстановки. При нажатии кнопки ОК Excel заполнит выделенную область полученными значениями.

ЛАБОРАТОРНАЯ РАБОТА 1

Тема: Определение будущей стоимости.

Цель работы: Освоить применение функций Excel по расчету будущих значений стоимости и инвестиций.

ТЕОРЕТИЧЕСКАЯ ЧАСТЬ

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

Синтаксис: БС (ставка;кпер;плт;пс;тип),

где ставка – процентная ставка за период;

кпер – общее число периодов;

плт – величина периодического платежа

(хотя этот аргумент считается обязательным, его можно опускать, что эквивалентно нулевому значению данного аргумента);

пс – необязательный аргумент, задающий приведенную (текущую) стоимость;

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

ПРАКТИЧЕСКАЯ ЧАСТЬ

Задание 1. Рассчитать, какая сумма окажется на счете, если 27 тыс.р. положены на 33 года под 18,5 % годовых. Проценты начисляются каждые полгода.

Рекомендации по выполнению.

Данные задачи необходимо оформить на рабочем листе Excel, как показано в таблице 1.

Таблица 1

Расчет будущей стоимости

Для расчета применяется формула:

FV = PV (1 + i)n

где FV – будущая стоимость вклада или займа;

PV – текущая стоимость вклада (займа);

n – общее число периодов начисления процентов;

i – процентная ставка по вкладу (займу).

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

Общее число процентных периодов кпер = 33  2.

Ставка процента за период ставка = 18,5 % / 2.

По условиям аргумент пс = -27, т.к. это единовременное вложение. Отрицательное число означает вложение денег. В итоге расчет должен дать результат:

=БС(18,5%/2;33*2;;-27;) = 9272,12 р.

Задание 2. Предположим, есть два варианта инвестирования средств в течение 4 лет: в начале каждого года под 26% и в конце года под 38% годовых. Пусть ежегодно вносится 300 тыс.р. Определить, сколько денег окажется на счете в конце четвертого года для каждого варианта.

Рекомендации по выполнению.

Данные задачи необходимо оформить на рабочем листе Excel, как показано в таблице 2.

Таблица 2

Инвестирование средств

В данном случае производятся периодические платежи, поэтому расчет ведется по формулам определения будущей (наращенной стоимости):

в первом варианте – для срочной ренты

во втором варианте - для обычной ренты

где FV – будущая стоимость серии финансовых платежей;

R – размер ежегодного взноса;

n – общее число периодов выплат;

i – процентная ставка.

Используя функцию БС аналогично заданию 1, получим:

=БС(26%;4;300;;1) = 2210,53 р. – для первого варианта;

=БС(38%;4;300;;0) = 2073,74 р. – для второго варианта.

Задание 3. Сумма 20 000 р. размещена под 9% годовых на три года. Проценты начисляются раз в квартал. Какая сумма будет на счете?

Задание 4. Выдан кредит в сумме 1 млн.р. с 15.01.2005 по 15.03. 2005 под 120% годовых (табл. 3). Рассчитать сумму погасительного платежа.

Таблица 3

Расчет суммы погасительного платежа

* Если в результате получилось значение даты, то необходимо перевести ячейку В7 в числовой формат.

Задание 5. Ссуда в 20 000 р. дана на полтора года под ставку 28% годовых с ежеквартальным начислением. Определить сумму конечного платежа.

Результат: 30 014,61.

 

Задание 6. Банк принимает вклад на срок 3 месяца с объявленной годовой ставкой 100% или на 6 месяцев под 110%. Как выгоднее вкладывать деньги: дважды на 3 месяца или один раз на полгода?

Результат: 1,56 р., 1,50 р.

Задание 7. На счет в банке вносится сумма 10 000 р. в течение 10 лет равными долями в конце каждого года; в начале каждого года. Годовая ставка 4%. Какая сумма будет на счете после 10 лет?

Результат: 120 061,07 р.; 124 863,51 р.

КОНТРОЛЬНЫЕ ВОПРОСЫ

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

Назовите виды процентных ставок и дайте им определение.

Приведите основные формулы расчета при начислении простых процентов.

Приведите основные формулы расчета при начислении сложных процентов.

Как производится расчет основных величин при внутригодовой капитализации процентов?

Каковы особенности платежей пре- и постнумерандо?

Назовите основные средства Excel, используемые для проведения финансового анализа.

Опишите технологию использования финансовых функций Excel.

Для чего используется функция БС? Приведите ее синтаксис и классическую формулу, которую заменяет эта функция.

Какие варианты использования функции БС вы знаете?

ЛАБОРАТОРНАЯ РАБОТА 2

Тема: Определение текущей стоимости.

Цель работы: Освоить применение функций Excel для расчета текущей стоимости.

ТЕОРЕТИЧЕСКАЯ ЧАСТЬ

Во многих задачах используется понятие текущей (современной) стоимости денег. Согласно концепции временнóй стоимости денег, расходы и доходы, не относящиеся к одному моменту времени, можно сопоставить путем дисконтирования, т.е. путем приведения к одному сроку. Текущая стоимость получается как результат приведения будущих расходов и доходов к начальному периоду времени.

Excel содержит ряд функций, которые позволяют рассчитать:

текущую стоимость единой суммы вклада (займа) и фиксированных периодических платежей – функция ПС;

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

Функция ПС возвращает приведенную (текущую) стоимость инвестиций при условии равных по величине платежей через равные интервалы и постоянной процентной ставки. Этот расчет является обратным к определению будущей стоимости при помощи функции БС.

Синтаксис: ПС (ставка;кпер;плт;бс;тип),

где ставка – процентная ставка за период;

кпер – общее число периодов;

плт – величина периодического платежа (хотя этот аргумент считается обязательным, его можно опускать, что эквивалентно нулевому значению данного аргумента);

бс – необязательный аргумент, задающий будущую (наращенную) стоимость или остаток средств после последней выплаты;

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

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

Синтаксис: ЧПС (ставка;значение1;значение2;…),

где ставка – процентная ставка за период;

значение1;значение2;… - от 1 до 29 аргументов, представляющих расходы (отрицательные значения) и доходы (положительные значения).

При вычислении функции ЧПС предполагается, что начисление процентов происходит только в конце периода.

Считается, что инвестиция, чистую текущую стоимость которой вычисляет функция ЧПС, начинается за один период до даты аргумента значение1 и заканчивается с последним значением в списке. Если первый денежный взнос приходится на начало первого периода, то первое значение следует добавить (вычесть, если это затраты) к результату функции ЧПС, но не включать в список аргументов (см. задание 4).

ПРАКТИЧЕСКАЯ ЧАСТЬ

Задание 1. Фирме потребуется 500 000 р. через 12 лет. В настоящее время фирма располагает деньгами и готова положить их на депозит отдельным вкладом, чтобы через 12 лет он достиг 500 000 р. Определить необходимую сумму вклада, если ставка процента по нему составляет 12% в год.

Рекомендации по выполнению.

Решение задачи оформите в виде таблицы 4.

Таблица 4

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

Задание 2. Подсчитать, какую сумму необходимо положить в банк под 20% годовых, чтобы иметь возможность в конце первого, второго, третьего и четвертого года заплатить за обучение по 135 000 р.

Рекомендации по выполнению.

Расчет ведется по формуле для определения текущей стоимости обычной ренты:

где PV– текущая стоимость серии финансовых платежей;

R – размер ежегодного взноса;

n – общее число периодов выплат;

i – процентная ставка.

Поэтому к задаче применима финансовая функция ПС, дающая результат 349 479 р.

Решим эту же задачу с помощью средства Excel Подбор параметра.

Создадим рабочий лист, позволяющий вычислить современную стоимость аннуитета, как на рис. 1.

 

Рис. 1. Рабочий лист, предназначенный для вычисления

современной стоимости аннуитета.

Сделаем предварительные установки:

введем в ячейку D3 число 20% (ставка за период);

введем в ячейку В3 число 4 (количество периодов);

введем в ячейку В4 число: – 135 000 (взнос за период).

Далее с помощью команды СЕРВИС  Подбор параметра открываем одноименное диалоговое окно, где делаем следующие установки:

в поле Установить в ячейке указываем адрес ячейки В5;

в поле Значение вводим число 0 – после оплаты четвертого года обучения на счету ничего не останется;

в поле Изменяя значение ячейки указываем адрес В2

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

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

Задание 3. Предположим, рассматривается два варианта покупки дома: заплатить сразу 2 300 000 р. или в рассрочку – по 20 500 р. ежемесячно в течение 15 лет. Определить, какой вариант предпочтительнее, если ставка процента – 8% годовых.

Результат: - 2 145 132,14 р.

Задание 4. Инвестиции в проект к концу первого года его реализации составят 500 000 р. В последующие три года ожидаются годовые доходы по проекту 200 000 р., 400 000 р., 520 000 р., издержки привлечения капитала составляют 10%. Рассчитать чистую стоимость проекта.

Результат: 366 436,72 р.

Задание 5. Допустим, затраты по проекту в начальный момент его реализации составляют 370 000 р., а ожидаемые доходы за первые пять лет: 80 000 р., 92 000 р., 100 000 р., 139 000 р. и 145 000 р. На шестой год ожидается убыток в 50 000 р., цена капитала 8% годовых. Рассчитать чистую текущую стоимость проекта.

Результат: 31 677,70 р.

КОНТРОЛЬНЫЕ ВОПРОСЫ

Дайте определение понятия дисконтирование.

Для чего используется функция ПС? Проведите ее синтаксис.

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

Дайте определение понятиям: инвестиция, инвестиционный процесс.

Назовите показатели оценки эффективности инвестиций.

Сформулируйте понятие и основное правило чистой приведенной стоимости.

В каких случаях применяется функция ЧПС? Приведите синтаксис функции ЧПС и классическую формулу, которая заменяет эта функция.

Опишите, как используется инструмент анализа Подбор параметра.

Какой тип задач решается с помощью Подбора параметра?

ЛАБОРАТОРНАЯ РАБОТА 3

Тема: Определение процентной ставки.

Цель работы: Освоение навыков применения функций Excel для расчета процентной ставки.

ТЕОРЕТИЧЕСКАЯ ЧАСТЬ

Функции этой группы позволяют находить величины, расчет которых весьма затруднен, если ведется вручную. К ним относятся:

значение постоянной процентной ставки за один период для серии фиксированных периодических платежей; значение ставки процента по вкладу или займу – функция СТАВКА;

номинальная и эффективная процентные ставки при заданном количестве периодов между промежуточными вычислениями – взаимообратные функции НОМИНАЛ и ЭФФЕКТ.

Функция СТАВКА определяет значение процентной ставки за один расчетный период. Для нахождения годовой процентной ставки полученное значение следует умножить на число расчетных периодов, составляющих год.

Синтаксис: СТАВКА (кпер;плт;пс;бс;тип;предположение),

где кпер – общее число периодов;

плт– величина периодического платежа (хотя этот аргумент считается обязательным, его можно опускать, что эквивалентно нулевому значению данного аргумента);

пс – аргумент, задающий приведенную (текущую) стоимость (хотя этот аргумент считается обязательным, его можно опускать, что эквивалентно нулевому значению данного аргумента);

бс – необязательный аргумент, задающий будущую (наращенную) стоимость или остаток средств после последней выплаты;

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

предположение – предполагаемая величина ставки, по умолчанию равная 10%.

Функция вычисляется методом последовательного приближения и может не иметь решения или иметь несколько решений. Если после 20 итераций погрешность определения ставки превышает 0,0000001, то функция СТАВКА возвращает значение ошибки. В этом случае можно попытаться задать другой аргумент предположение. В большинстве случаев этого не требуется.

Функции НОМИНАЛ и ЭФФЕКТ взаимообратные: одна вычисляет эффективную процентную ставку по номинальной, а другая – номинальную по эффективной и имеют одинаковый синтаксис:

Синтаксис: НОМИНАЛ (ставка;кол_периодов),

где ставка – эффективная процентная ставка;

кол_периодов – число процентных периодов.

Синтаксис: ЭФФЕКТ (ставка;кол_периодов)

где ставка – номинальная процентная ставка;

кол_периодов – число процентных периодов.

Если значение аргумента кол_периодов не является целым числом, то в качестве аргумента берется целая часть этого числа.

Если значение аргумента кол_периодов равно 1, то функции НОМИНАЛ и ЭФФЕКТ возвращают значения аргумента без изменения.

ПРАКТИЧЕСКАЯ ЧАСТЬ

Задание 1. Три коммерческих банка предложили возможным клиентам следующие условия: первый банк предлагает простые проценты из расчета 35% годовых, второй – по номинальной ставке 30% при ежемесячном начислении процентов, третий – по номинальной ставке 32% и поквартальном начислении процентов. В какой банк клиенту выгоднее вкладывать деньги?

Результат: 0,35; 0,34489; 0,3605.

Задание 2. Какой должна быть минимальная процентная ставка, чтобы произошло удвоение вклада за год при начислении процентов:

поквартально;

ежемесячно?

Рекомендации по выполнению.

Решите задачу с помощью средства Подбор параметра.

Задание 3. Определите сложную процентную ставку при ежемесячном начислении процентов, эквивалентную сложной учетной ставке 60% при дисконтировании 1 раз в год.

Результат: 95,22%

Задание 4. Предположим, что компании Х потребуется 1 млн.р. через 2 года. Компания готова вложить 50 тыс.р. сразу и по 25 тыс.р. каждый последующий месяц. Каким должен быть процент на инвестированные средства, чтобы получить необходимую сумму в конце второго года?

Результат: 39,36%

Задание 5. Предположим, что компания Х отказалась от ежемесячных выплат (см.предыдущую задачу) и готова сегодня положить на депозит 40 тыс.р. Определить, как в этом случае изменится минимальная годовая процентная ставка.

Результат: 46,7%

Задание 6. Рассчитать процентную ставку для четырехлетнего займа в 70 тыс.р. с ежемесячным погашением по 2,5 тыс.р. при условии, что заем полностью погашается.

Результат: 29,5%

КОНТРОЛЬНЫЕ ВОПРОСЫ

Раскройте сущность процентной ставки. От чего зависит ее фактический размер?

Что определяет функция СТАВКА, и каким методом она вычисляется?

Перечислите варианты практического применения функции СТАВКА и приведите ее синтаксис в каждом конкретном случае.

В каком случае функция СТАВКА возвращает значение ошибки?

Какая ставка процента называется номинальной? Эффективной?

Что характеризует эффективная ставка процента? Приведите классическую формулу, по которой она определяется.

Для чего используются функции НОМИНАЛ и ЭФФЕКТ? Почему они являются взаимообратными?

Приведите синтаксис функций НОМИНАЛ и ЭФФЕКТ.

Какие платежи называются эквивалентными? В чем состоит принцип эквивалентности?

Какой уровень процентной ставки называется критическим, или барьерным?

Приведите формулы, определяющие эквивалентность процентных ставок.

ЛАБОРАТОРНАЯ РАБОТА 4

Тема: Определение срока платежа и срока окупаемости.

Цель работы: Изучение возможностей применения функций Excel для расчета срока платежа и срока окупаемости.

ТЕОРЕТИЧЕСКАЯ ЧАСТЬ

Функция КПЕР возвращает общее количество периодов выплат, как для единой суммы вклада (займа), так и для инвестиции на основе периодических постоянных выплат и постоянной процентной ставки. Полученное значение можно также использовать как показатель срока окупаемости при анализе инвестиционного проекта.

Синтаксис: КПЕР (ставка;плт;пс;бс;тип),

где ставка – процентная ставка за период;

плт – величина периодического платежа (хотя этот аргумент считается обязательным, его можно опускать, что эквивалентно нулевому значению данного аргумента);

пс – аргумент, задающий приведенную (текущую) стоимость (хотя этот аргумент считается обязательным, его можно опускать, что эквивалентно нулевому значению данного аргумента);

бс – необязательный аргумент, задающий будущую (наращенную) стоимость или остаток средств после последней выплаты;

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

ПРАКТИЧЕСКАЯ ЧАСТЬ

Задание 1. Рассчитать, через сколько лет вклад размером 1 млн.р. достигнет величины 1 млрд.р., если годовая ставка процента по вкладу 16,79% и начисление процентов производится ежеквартально.

Результат: 42 года.

Задание 2. Какой срок погашения нужно поставить в векселе номиналом 10 тыс.р., если владелец векселя получил 8 тыс.р.? Величина номинальной учетной ставки – 16%. Дисконтирование поквартальное. Банковский год – 360 дней.

Результат: 1,36656 лет, или 492 дня.

Задание 3. У вас в банке на счету лежит 1 млн. р. под 15% годовых. Каждый месяц с этого счета вы снимаете 20 тыс.р. Задача состоит в определении срока, за который вы сможете потратить свой миллион.

Рекомендации по выполнению.

Решите задачу с помощью средства Подбор параметра.

Задание 4. Для обеспечения будущих расходов создается фонд. Средства в фонд поступают в виде постоянной годовой ренты постнумерандо. Размер разового платежа 16 млн.р. На поступившие взносы начисляется 11,18% годовых. Определить, когда величина фонда будет равна 100 млн.р.

Результат: 5 лет.

Задание 5. Ожидается, что ежегодные доходы от реализации проекта составят 33 млн.р. Необходимо рассчитать срок окупаемости проекта, если инвестиции к началу поступления доходов составят 100 млн.р., а норма дисконтирования 12,11%.

Результат: 4 года.

Задание 6. Ссуда размером 66 тыс.р., выданная под 36% годовых, погашается обычными ежемесячными платежами 6630 р. Рассчитать срок погашения ссуды.

Результат: 1 год.

Задание 7. Платеж 40 000 р. с уплатой через 3 месяца заменяется на платеж 50 000 р. Определить срок второго платежа, если сложная ставка 40% годовых.

 

КОНТРОЛЬНЫЕ ВОПРОСЫ

Дайте определение понятия «период начисления».

Почему необходимо учитывать временной фактор при оценке финансовых показателей?

Чем отличаются математическое дисконтирование и банковский учет?

Что показывают коэффициенты дисконтирования?

Для чего используется функция КПЕР? Приведите ее синтаксис.

Перечислите, при каких расчетах может применяться функция КПЕР, и приведите классические формулы, которые заменяет эта функция в каждом конкретном случае.

Что такое конверсия платежей? Консолидация платежей?

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

Как определить срок погашения объединенного векселя?

ЛАБОРАТОРНАЯ РАБОТА 5

Тема: Расчет периодических платежей.

Цель работы: изучение функций Excel, предназначенных для расчета потоков платежей.

ТЕОРЕТИЧЕСКАЯ ЧАСТЬ

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

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

Синтаксис: ОСПЛТ (ставка;период;кпер;пс;бс;тип),

где ставка – процентная ставка за период;

период – указывает период, за который производится расчет;

кпер – общее число периодов;

пс – аргумент, задающий приведенную (текущую) стоимость (хотя этот аргумент считается обязательным, его можно опускать, что эквивалентно нулевому значению данного аргумента);

бс – необязательный аргумент, задающий будущую (наращенную) стоимость или остаток средств после последней выплаты;

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

Синтаксис: ПРПЛТ (ставка;период;кпер;пс;бс;тип),

где ставка – процентная ставка за период;

период – указывает период, за который производится расчет;

кпер – общее число периодов;

пс – аргумент, задающий приведенную (текущую) стоимость (хотя этот аргумент считается обязательным, его можно опускать, что эквивалентно нулевому значению данного аргумента);

бс – необязательный аргумент, задающий будущую (наращенную) стоимость или остаток средств после последней выплаты;

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

Синтаксис: ПЛТ (ставка;кпер;пс;бс;тип),

где ставка – процентная ставка за период;

кпер – общее число периодов;

пс – аргумент, задающий приведенную (текущую) стоимость (хотя этот аргумент считается обязательным, его можно опускать, что эквивалентно нулевому значению данного аргумента);

бс – необязательный аргумент, задающий будущую (наращенную) стоимость или остаток средств после последней выплаты;

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

ПРАКТИЧЕСКАЯ ЧАСТЬ

Задание 1. Предположим, что необходимо накопить 35 тыс.р. за 3 года, откладывая постоянную сумму в конце каждого месяца. Какой должна быть эта сумма, если норма процента по вкладу составляет 12% годовых?

Результат: - 812,50 р.

Задание 2. Допустим, банк выдал ссуду 200 тыс.р. на 4 года по 18% годовых. Ссуда выдана в начале года, а погашение начинается в конце года одинаковыми платежами. Определить размер ежегодного погашения ссуды.

Результат: 74 347,7 р.

Задание 3. Вычислить платежи по процентам за первый месяц от трехгодичного займа в 800 000 р. из расчета 10% годовых.

Результат: - 6666,67 р.

Задание 4. Предположим, что за счет ежегодных отчислений в течение 6 лет был сформирован фонд в 50 000 р. Определить, какой доход приносили вложения владельцу за последний год, если годовая ставка составила 17,5%, и сколько ежегодно отчислялось в фонд.

Результат: 6648,11 р.; - 5362,69 р.

Задание 5. Составить схему погашения займа в 70 000 р., выданного сроком на 3 года под 17% годовых. Погашение займа происходит равными годовыми выплатами. Решение оформить в виде таблицы 5.

Таблица 5

Схема погашения займа

Задание 6. Фирма получила кредит размером 2 млн. р. сроком на 4 года под 30% годовых. Кредит должен погашаться равными суммами в начале каждого полугодия. Составить план погашения кредита. Оформить решение аналогично заданию 5.

КОНТРОЛЬНЫЕ ВОПРОСЫ

Дайте определение регулярным потокам платежей.

Дайте классификацию финансовых рент по моменту выплат.

Напишите формулы для определения наращенной суммы обычной и срочной ренты.

Напишите формулы для определения текущей стоимости обычной и срочной ренты.

Перечислите функции Excel, которые позволяют вычислять величины, связанные с периодическими выплатами.

Для чего используется функция ПЛТ? Приведите ее синтаксис.

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

Что определяет функция ПРПЛТ? Приведите ее синтаксис.

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

Что вычисляет функция ОСПЛТ? Приведите ее синтаксис.

Какие способы погашения кредита вы знаете? Чем они различаются?

ЛАБОРАТОРНАЯ РАБОТА 6

Тема: Определение внутренней нормы доходности инвестиций.

Цель работы: изучение функций Excel, вычисляющих внутреннюю норму доходности инвестиций.

ТЕОРЕТИЧЕСКАЯ ЧАСТЬ

Функция ВСД возвращает внутреннюю ставку доходности для инвестиции, состоящей из платежей (отрицательные величины) и доходов (положительные величины), которые осуществляются в последовательные и одинаковые по продолжительности периоды.

Синтаксис: ВСД (значения; предположение),

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

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

Функция ВСД вычисляет итеративным методом норму дисконтирования R, при которой чистая текущая стоимость равна нулю. Начиная со значения предположение, функция ВСД выполняет циклические вычисления, пока не получит результат с точностью 0,00001%. Если функция ВСД не может получить результат после 20 попыток, то возвращается значение ошибки #ЧИСЛО! В большинстве случаев нет необходимости задавать аргумент предположение.

Если известна рыночная норма дохода k, то вычисленное значение можно использовать в качестве оценки целесообразности принятия того или иного проекта вложения средств. Проект принимается, если R>k, и отвергается, если R<k.

Функция ВСД тесно связана с функцией ЧПС, поскольку ставка доходности, вычисляемая функцией ВСД, гарантирует нулевую чистую приведенную стоимость. Функции ЧПС и ВСД связаны формулой

ЧПС(ВСД(значения);значения)=0.

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

Синтаксис: МВСД (значения; ставка_финанс;ставка_реинвест),

где значения – массив значений или ссылка на диапазон ячеек, содержащих последовательность платежей (отрицательные величины) и доходов (положительные величины); обязательно должны содержаться хотя бы одно положительное и одно отрицательное значения;

ставка_финанс – ставка процентов, начисляемых на выплаты;

ставка_реинвест – ставка процентов, начисляемых на доходы.

ПРАКТИЧЕСКАЯ ЧАСТЬ

Задание 1. Предположим, затраты по проекту составят 500 млн.р. Ожидаемые доходы составят 50, 200, 200, 300 млн.р. в течение последующих 4 лет. Оценить экономическую целесообразность проекта по внутренней ставке доходности инвестиции, если рыночная норма дохода составляет 12%.

Рекомендации по выполнению.

Оформите решение задачи в виде таблицы (рис.2).

Полученный результат меньше, чем рыночная норма, поэтому проект должен быть отвергнут.

Рис. 2. Оценка экономической целесообразности проекта

Задание 2. Построить схему (диаграмму) денежных потоков по затратам и доходам проекта, используя данные предыдущей задачи (рис. 3).

Рис. 3. Схема денежных потоков.

Задание 3. Ожидается, что доходы по проекту в течение последующих 4 лет составят: 50 000, 100 000, 20 000, 300 000 р. Определить, какими должны быть первоначальные затраты, чтобы обеспечить норму доходности 10%.

Рекомендации по выполнению.

Оформите решение задачи как показано в таблице 6.

Таблица 6

Исходные данные

* Значение первоначальных затрат указывается пока приблизительно.

Для решения такой задачи используйте аппарат подбора параметра пакета Excel, вызываемый командой меню СЕРВИС  Подбор параметра, так как отсутствует соответствующая финансовая функция Excel.

Установите в диалоговом окне Подбор параметра:

Установить в ячейке: B6

Значение: 10%

Изменяя значение ячейки: B1

В результате в ячейке В1 получим значение первоначальных затрат, равное -489 974 р. (табл. 7).

Таблица 7

Первоначальные затраты

Задание 4. Проект рассчитан на три года и требует начальных инвестиций в размере 10 млн. р., имеет предполагаемые денежные поступления в размере 3 млн., 4 млн., 7 млн. р. Рассчитать чистую текущую стоимость будущих периодических расходов и поступлений в предположении ставки 10%. Определить внутреннюю доходность для этого проекта.

Рекомендации по выполнению.

Введите данные в таблицу и рассчитайте значения Чистой текущей стоимости и Внутренней доходности (табл. 8).

Таблица 8

Расчет чистой текущей стоимости

и внутренней доходности проекта.

Для того, чтобы лучше понять взаимоотношения Чистой текущей стоимости и Внутренней доходности, постройте таблицу значений чистой текущей стоимости для различных ставок (табл. 9):

в D2:D10 введите произвольные значения процентных ставок;

в Е2 введите формулу =ЧПС(D2;В4:В6)+В3 и размножьте в диапазонеЕ3:Е10.

Таблица 9

На основе диапазона D2:E10 постройте график зависимости чистой текущей стоимости от возрастающей процентной ставки. Значения процентных ставок отметьте по оси Х, значения чистой текущей стоимости – по оси У.

На основании произведенных расчетов и графика функции сделайте вывод.

Задание 5. Предположим, пять лет назад была взята ссуда в размере 1 млрд. р. под 10% годовых для финансирования проекта, прибыль по которому за эти годы составила: 100 млн., 270 млн., 450 млн., 340 млн. и 300 млн. р. Эти деньги были реинвестированы под 12% годовых. Найти модифицированную внутреннюю ставку доходности инвестиции. Результат оформить на рабочем листе в виде таблицы.

Результат: 12,25%.

КОНТРОЛЬНЫЕ ВОПРОСЫ

Что такое внутренняя норма доходности?

Сформулируйте правило внутренней нормы доходности.

Какие функции Excel позволяют определить скорость оборота инвестиций?

Для чего используется функция ВСД?

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

Для чего используется функция МВСД?

Приведите синтаксис функции МВСД и классическую формулу, с помощью которой ее значение можно вычислить.

ЛАБОРАТОРНАЯ РАБОТА 7

Тема: Оценка инвестиций на основе Таблицы подстановки.

Цель работы: овладение навыками использования функций Excel с применением Таблицы подстановки.

ТЕОРЕТИЧЕСКАЯ ЧАСТЬ

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

Принцип использования этого средства Excel состоит в следующем:

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

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

ПРАКТИЧЕСКАЯ ЧАСТЬ

Задание 1. Требуется определить, какие ежемесячные выплаты необходимо вносить по ссуде размером 200 000 р., выданной на 3 года, при разных процентных ставках.

Рекомендации по выполнению.

Оформите данные задачи в виде таблицы (табл. 10).

Введите в ячейку С5 формулу для расчета периодических постоянных выплат по займу при условии, что он полностью погашается в течение срока займа =ПЛТ(В3/12;В2*12;В1).

Для заполнения Таблицы подстановки выделите диапазон ячеек, содержащий исходные значения процентных ставок и формулу для расчета – В5:С11.

Выполните команду ДАННЫЕ  Таблица подстановки. На экране появится диалоговое окно. В поле Подставлять значение по строкам в: указать В3.

В результате получим столбец решений С5:С11 (табл. 11).

Таблица 10

Таблица 11

Задание 2. Самостоятельно в следующем столбце D рассчитать ежемесячные выплаты по процентам за первый месяц для разных процентных ставок.

Рекомендации по выполнению.

Используйте функцию ПРПЛТ и повторите все вышеперечисленные шаги, для создания Таблицы подстановки.

Задание 3. Необходимо найти ежемесячные выплаты по займу размером 300 000 р. для различных сроков погашения и процентных ставок.

Рекомендации по выполнению.

Введите данные задачи в таблицу (табл. 12).

По горизонтали введите значение произвольных сроков погашения займа, а по вертикали значения произвольных процентных ставок.

В ячейку, находящуюся на пересечении столбца и строки произвольных данных, введите формулу ежемесячных выплат по займу в 300 000 р. для срока погашения 3 года и процентной ставки 8,5%.

При создании Таблицы подстановки по строкам укажите сроки погашения займа, а по столбцам – процентные ставки.

Таблица 12

Задание 4. Предположим, что в конце года капиталовложения по проекту составят 1280 млн.р. Ожидается, что за последующие 3 года проект принесет следующие доходы: 420 млн., 490 млн., 550 млн., 590 млн.р. Рассчитать чистую текущую стоимость проекта для различных норм дисконтирования и объемов капиталовложений (табл. 13).

Таблица 13

Чистая текущая стоимость проекта для различных норм

дисконтирования и объемов капиталовложений

Задание 5. Представить значения, полученные в предыдущей задаче, в графическом виде. Проанализировать полученный результат. Сделать выводы.

КОНТРОЛЬНЫЕ ВОПРОСЫ

Для решения какого типа финансовых задач используется Таблица подстановки?

Опишите принцип использования Таблицы подстановки.

Какие типы таблиц данных позволяет создавать Таблица подстановки?

Опишите построение Таблицы подстановки для одной переменной.

Опишите построение Таблицы подстановки для двух переменных.

Каким образом производится оценка эффективности инвестиций на основе Таблицы подстановки и функции ЧПС?

ИСПОЛЬЗОВАННАЯ ЛИТЕРАТУРА

Александрова Т.Н. Финансовая арифметика. Просто как дважды два / Т.Н. Александрова, А.А. Минько. – М. : ЭКСМО, 2012. – 240 с.

Данилин В.И. Финансовый менеджмент: задачи, тесты, ситуации: учеб. пособие / В.И. Данилин. – М. : ТК Велби, Изд-во Проспект, 2012. – 360 с.

Информационные системы в экономике : практикум / Е.Л. Торопцев, А.С. Мараховский, Е.В. Богушевич и др. ; под ред. П.В. Акинина. – М. : КНОРУС, 2013. – 256 с.

Калугина О.Б. Работа с электронными таблицами. Microsoft Office Excel 2003 / О.Б. Калугина, В.С. Люцарев; Интернет ун-т информ. технологий. – М. : Интернет ун-т информ. технологий, 2011. – 240 с. : ил.

Капитоненко В.В. Задачи и тесты по финансовой математике : учеб. пособие / В.В. Капитоненко. – М. : Финансы и статистика, 2012. – 256 с. : ил.

Капельян С.Н. Основы коммерческих и финансовых расчетов / С.Н. Капельян, О.А. Левкович. – Мн. : НТЦ «АПИ», 2012. – 224 с.

Просветов Г.И. Бизнес-планирование : задачи и решения : учеб.-практ. пособие / Г.И. Просветов.  2-е изд., доп. – М. : Издательство «Альфа-Пресс», 2011. – 256 с.

Самаров К.Л. Финансовая математика : практический курс : учеб. пособие. / К.Л. Самаров. – М. : Альфа-М ; ИНФРА-М, 2013. – 80 с.