Технологии финансовых расчетов в MS EXCEL

 

Цель: научиться использовать возможности MS Excel для проведения финансовых расчетов.

Задачи:

Расчет будущей стоимости инвестиций.

Вычисление ренты. Расчет сроков займов.

Составление планов погашения займов.

Расчет амортизационных отчислений линейным методом, накопленного износа и остаточной стоимости.

 

Теоретические сведения

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

Так как некоторые финансовые функции Excel могут оказаться, недоступны в процессе работы, поэтому перед началом работы с финансовыми функциями рекомендуется установить надстройку Пакет анализа. Для этого выполните команду меню Сервис®Надстройки ….В диалоговом окне Надстройки установите флажок напротив строки Пакет анализа. В результате вам будут доступны все 54 финансовые функции Excel, которые ориентированы на решение задач, связанных с расчетами различных аннуитетов, амортизации, цены, доходности и других параметров ценных бумаг (облигаций, акций и т.п.), а также задач оценки эффективности инвестиционных проектов.

Расчет будущих стоимостей инвестиций.

Проценты по кредиту, выдаваемые банками, бывают простые и сложные (проценты, начисляемые на проценты).

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

S = P(l + rt).

В этой формуле примем, что t = 1 год, тогда S=Р(1 + r).

Отношение S/P носит название - коэффициент наращения, здесь обозначено Р - предоставляемая сумма, r – банковский процент, t - период времени пользования кредитом. В финансовых расчетах наряду с банковским процентом используется коэффициент дисконта d, связанный с банковским процентом формулой: .

 

Сложные проценты начисляются c использованием формулы:

S=P(1+r)t.

При использовании финансовых функций необходимо учитывать, точку зрения кредитора и дебитора. Дебитор получает сумму Р, а в конце периода возвращает сумму S, знак «-» на рис 4.1. Наоборот кредитор лишается суммы Р, но в конце получает сумму S, знак «+» на рис. 4.2.

Рис.4.1. Рис.4.2.

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

Технология выполнения работы

Задача №1.

Выдан кредит в сумме 1 млн. долл. с 15.01.93 по 15.03.93 под 120% годовых. Рассчитать сумму погасительного платежа.

 

Решение.

Откройте новую книгу MS Excel и создайте таблицу согласно рис.4.3. Лист 1 переименуйте в Задача 1. В столбце D приведены формулы, которые необходимо ввести в ячейки В6:В8.

Рис. 4.3.

 

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

Синтаксис функции БС:

=БС(ставка; кпер; плт; пс; тип)

СТАВКА - процентная ставка за период.

КПЕР - это общее число периодов платежей по аннуитету.

ПЛТ - это выплата, производимая в каждый период; это значение не может меняться в течение всего периода выплат. Обычно ПЛТ состоит из основного платежа и платежа по процентам, но не включает других налогов и сборов. Если аргумент опущен, должно быть указано значение аргумента ПС.

ПС - это приведенная к текущему моменту стоимость или общая сумма, которая на текущий момент равноценна ряду будущих платежей. Если аргумент ПС опущен, то он полагается равным 0. В этом случае должно быть указано значение аргумента ПЛТ.

ТИП - число 0 или 1, обозначающее, когда должна производиться выплата. Если аргумент ТИП опущен, то он полагается равным 0.

 

Введите в ячейку В9 формулу, рассчитывающую сумму возврата с использованием функции БС. Для этого выполните команду Вставка/Функция или щелкните по пиктограмме .

Перейдите в категорию Финансовые и выберите необходимую функцию БС.

В диалоговом окне Аргументы функции установите необходимые значения (см. рис.4.4). Щелкните по кнопке ОК. Значение получилось отрицательное. Кредиты нужно возвращать!!!

Сохраните файл в своей папке под именем Финансовые расчеты.xls.

Рис. 4.4. Аргументы финансовой функции БС

 

Задача №2.

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

 

Решение.

Перейдите на новый лист и переименуйте его в Задача 2. Подготовьте таблицу для расчетов согласно рис. 4.5.

Рис. 4.5.

 

В данной задаче базовый период - квартал, поэтому срок ссуды (количество периодов) – 6. За период начисляется ставка 7% = 28% / 4. Тогда формула, дающая решение задачи, имеет вид: =БС(28%/4;6;;20000). Она возвращает результат: -$30 014,61.

Сохраните изменения в файле Финансовые расчеты.xls.

 

Задача №3.

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

 

Решение.

Перейдите на новый лист и переименуйте его в Задача 3. Подготовьте таблицу для расчетов согласно рис. 4.6.

Рис.4.6.

 

Рассчитайте ставки за период для обоих вкладов в ячейках В4 и С4. Для первого вклада будет формула: =B2*B3/12 (100%*3/12), для второго: =C2*C3/12 (110%*6/12).

Рассчитайте, используя функцию БС накопленную сумму. Для первого вклада получим формулу: =БС(B4;2;;B5), где 2 – число платежей (мы вкладываем деньги дважды за полгода). Для второго: =БС(C4;1;;C5), где 1 – число платежей (мы вкладываем деньги только один раз).

Сравните полученные результаты (рис.4.7) и сделайте вывод.

Рис.4.7.

 

Сохраните изменения в файле Финансовые расчеты.xls.

 

Задача №4 (самостоятельно).

Рассчитать будущее значение вклада 1000 долл. через 1, 2, 3, 4, 5 лет при годовых процентных ставках 10%, 20%,..., 50%. Дополнительные поступления и выплаты отсутствуют.

Примечание. Для расчетов создайте таблицу на новом листе Задача 4 согласно рис. 4.8. Для ячеек В5:В8 используйте процедуру копирования формулы. Сохраните изменения в файле Финансовые расчеты.xls.

Рис.4.8.