Цель работы.
Научиться использовать функции Excel для решения различных типов экономических задач.
Методические рекомендации.
Руководителям предприятий часто приходится решать задачи финансового характера, например, обеспечение кредитами, вложение капитала, амортизационные отчисления и т.д. Для решения таких задач можно использовать ряд финансовых функций предусмотренных в Excel.
Для использования финансовых функций в полном объеме на компьютере следует установить дополнение Excel Пакет Анализ. Если надстройка Пакет Анализ не отображается, то используя кнопку Офис откройте Параметры Excel и в Надстройках из списка Неактивные надстройки Excel выберите Пакет Анализ и нажмите кнопку Перейти.
При использовании финансовых функций в отдельных ячейках рабочего листа подготавливаются значения основных аргументов функции, выполняется выбор требуемой функции по Мастеру функций, через диалоговые окна которого заносятся аргументы.
Существует специфика задания значений аргументов финансовых функций:
1. Все аргументы, означающие расходы денежных средств (напр., ежегодные платежи), представляются отрицательными числами, а аргументы, означающие поступления (напр., дивиденты), - положительными числами.
2. Все даты, как аргументы, имеют числовой формат (например, 1 января 1995г. представляется числом 34700). При ссылках на ячейки с датами в последние даты записываются в обычном виде (например, 1.01.95).
3. Если не используется последний аргумент (или несколько подряд идущих), то соответствующие разделительные знаки (“;”) можно опустить.
Финансовые функции включают:
Функция Цена
Рассчитывает курс (цену) покупки ценной бумаги с периодическими выплатами купонных процентов по формуле
=ЦЕНА(дата_соглашения;дата_вступления_в_силу;ставка;доход;погашение; частота;базис)
Функция БС (БЗ)
Производит расчет будущей или наращенной стоимости периодических постоянных платежей и будущую стоимость текущего значения вклада или займа при постоянной процентной ставке.
§ Если производится расчет будущей стоимости единой суммы вклада, то используют формулу
= БС(норма;число_периодов;;нз)
§ если платежи производятся систематически в начале периода, так называемые “обязательные платежи” (тип=1), используется формула
=БС(норма;число_периодов;выплата;;1)
для фиксированных периодических платежей, производимых в конце периода, так называемые “обычные платежи” (тип=0), используется формула
=БС(норма;число_периодов;выплата;;0) или =БС(норма;число_периодов;выплата) т.е. аргумент тип равный нулю можно опустить.
Аргумент выплата означает фиксированную периодическую выплату.
Аргумент нз – начальное значение вклада - берется с отрицательным знаком, что означает вложение денег. В случае выдачи денег знак аргумента нз положительный.
Для задания аргументов число_периодов и норма применяется таблица 15, где используются параметры:
n=число лет — для расчета общего числа периодов выплат (аргумент число_периодов),
k=годовая процентная ставка — для расчета процента за период начисления (аргумент норма).
Таблица 15
Расчет аргументов функции БС
| Метод начисления процентов | Общее число периодов начисления процентов | Ставка процента за период начисления, % |
| Ежегодный | n | k |
| Полугодовой | n×2 | k /2 |
| Квартальный | n×4 | k /4 |
| Месячный | n×12 | k /12 |
| Ежедневный | n×365 | k /365 |
Подробную информацию о функциях и их параметрах смотрите в справочной системе Excel.
Пример 8.1.
С помощью функции Цена определить курс покупки ценных бумаг (облигаций), если:
a) облигации приобретены (дата_соглашения) 6.09.93;
b) облигации будут погашены (дата_вступления_в_силу) 12.09.97;
c) размер купонной ставки (ставка) – 9% с выплатой раз в полугодие (частота = 2);
d) ожидаемая годовая ставка помещения (доход) 12,57%;
e) номинал облигации (погашение) 100;
f) базис расчета 1 (фактический способ исчисления временного периода – год = 366 дней).
Исходные данные и результаты
| A | B | |
| Пример по использованию функции ЦЕНА | ||
| Переменные для функции | Исходные данные | |
| Дата соглашения | 06.09.93 | |
 4
| Дата вступления в силу | 12.09.97 |
| Ставка | 9,000% | |
| Доход | 12,57% | |
| Погашение | ||
| Частота | ||
| Базис | ||
| Цена | 89,00047468 |
Функция ПЛТ (ППЛАТ).
Для вычисления величины выплаты по ссуде на основе постоянных выплат и постоянной процентной ставки используется функция ПЛТ.
Синтаксис: ПЛТ(ставка;кпер;нз;бз;тип).
Ставка - это процентная ставка по ссуде за расчетный период.
Кпер (количество периодов) - это общее число выплат (расчетных периодов) по ссуде.
Нз (Нс, Пз или Пс) - это текущее значение или общая сумма, которую составят будущие платежи, называемая также основной или начальной суммой (начальное значение).
Бз (Бс) - это будущая сумма или баланс наличности, которой нужно достичь после последней выплаты. Если Бз опущено, оно полагается равным 0, т.е. будущая сумма ссуды, например, равна 0 (базовое значение).
Тип - это число 0 или 1, обозначающее, когда должна производиться выплата (0 - в конце периода, 1 - в начале периода).
Замечания
· Выплаты, возвращаемые функцией ПЛТ включают основные платежи и платежи по процентам, но не включают налогов, резервных платежей или гонораров, иногда связываемых со ссудой.
· Если подразумевается, что сумма, полученная пользователем имеет знак "+", тогда сумма, выплачиваемая пользователем будет со знаком "-".
· Убедитесь, что вы последовательны в выборе единиц измерения для задания аргументов Ставка и Кпер. Если вы делаете ежемесячные выплаты по четырехгодичному займу из расчета 12 процентов годовых, то используйте 12% /12 для задания аргумента Ставка и 4*12 для задания аргумента Кпер. Если вы делаете ежегодные платежи по тому же займу, то используйте 12% для задания аргумента Ставка и 4 для задания аргумента Кпер.
· Для нахождения общей суммы, выплачиваемой на протяжении интервала выплат, умножьте возвращаемое функцией ПЛТ значение на Кпер.
Пример
Следующая формула возвращает ежемесячные выплаты по займу в 10 000 руб. и годовой процентной ставке 8%, которые можно выплачивать в течении 10 месяцев:
ПЛТ(8%/12; 10; 10000) равняется -1037,03 руб.
Для того же займа, если выплаты должны делаться в начале периода, то выплата составит:
ПЛТ(8%/12; 10; 10000; 0; 1) равняется -1030,16 руб.
Функция ЧПС (НПЗ).
Для определения рентабельности инвестиций используется функция ЧПС, которая вычисляет чистый текущий объем вклада, используя учетную ставку. Если чистая текущая стоимость больше нуля, то инвестиция рентабельна и чем больше ее значение, тем лучше.
Синтаксис: ЧПС (Ставка;Значение1;Значение2;...)
Ставка - это учетная ставка за один период.
Значение1, Значение2,... - это от 1 до 29 аргументов, представляющих расходы и доходы. Предполагается, что все значения равномерно распределены во времени, выплаты осуществляются в конце каждого периода.
ЧПС использует порядок аргументов Значение1, Значение2,... для определения порядка поступлений и платежей. Убедитесь в том, что ваши платежи и поступления введены в правильном порядке.
Аргументы, которые являются числами, пустыми ячейками, логическими значениями или текстовыми представлениями чисел учитываются; аргументы, которые являются значениями ошибки или текстами, которые не могут быть преобразованы в числа, игнорируются.
Если аргумент является массивом или ссылкой, то учитываются только числа. Пустые ячейки, логические значения, тексты или значения ошибок в массиве или ссылке игнорируются.
Замечания
Считается, что инвестиция, значение которой вычисляет функция ЧПС, начинается за один период до даты денежного взноса Значение1 и заканчивается с последним денежным взносом в списке. Вычисления функции ЧПС базируются на будущих денежных взносах. Если первый денежный взнос приходится на начало первого периода, то первое значение следует добавить к результату функции ЧПС, но не включать в список аргументов.
Пример
Рассмотрим инвестицию, при которой вы выплачиваете 10 000 руб. через год после сегодняшнего дня и получаете годовые доходы 3 000 руб., 4 200 руб. и 6 800 руб. в последующие 3 года. Предположим, что учетная ставка составляет 10%, в таком случае чистый текущий объем инвестиции составит:
ЧПС(10%; -10000; 3000; 4200; 6800) равняется 1188,44 руб.
Подбор Параметра
Некоторые задачи финансового анализа требуют вычислений величин, для которых в Excel не предусмотрены отдельные функции. В этом случае успешно применяется аппарат Excel Подбор Параметра. С его помощью можно выполнять исследование области допустимых значений аргументов или подбирать значения аргументов под заданное значение функции. При подборе параметра значение влияющей ячейки (параметра) изменяется до тех пор, пока формула, зависящая от этой ячейки, не возвратит заданное значение.
Установив курсор в ячейку (C11), содержащую формулу с финансовой функцией (Цена), выполнить команду Данные ® Работа с данными®Анализ что-если®Подбор параметра. В появляющееся диалоговое окно (рис. 8.1) задается требуемое значение функции (желаемая или заданная ЦЕНА), а в поле “Изменяя значение ячейки” указывается адрес подбираемого аргумента (один из параметров исходных данных, например, по адресу $C$5 - СТАВКА).
Рис. 8.1. Диалоговое окно Подбор параметра
В Excel можно проводить исследование влияния некоторых исходных данных, принимающих различные значения, на результирующую функцию, т.е. проводить вариантныефинансовые расчеты. Это делается с помощью Диспетчера сценариев. Сценарий – именованная совокупность значений изменяемых ячеек, в которые вводятся различные значения аргументов. При работе со сценариями следует присвоить имена ячейкам с исходными данными, которые будут “варьироваться”. Например, для аргументов финансовой функции ЦЕНА ячейкам с исходными данными присвойте имена: $B$5 – Ставка, $B$6 – Доход, $B$8 – Частота, а ячейке-результату $B$10 – Цена.
Установите курсор на любую ячейку рабочего листа.
Диспетчер сценариев
Команда Данные ® Работа с данными®Анализ что-если® Диспедчер сценариев вызывает диалоговое окно Диспетчера сценариев для создания, редактирования, объединения, удаления и просмотра созданных сценариев расчета.
Новый сценарий создается кнопкой Добавить, появляется окно Добавление сценария (рис. 8.2), в котором название сценария задается в соответствующем поле, например 1. Поле Изменяемые ячейки заполняется с использованием клавиши CTRL, выделяя на рабочем листе ячейки, значения которых будут изменяться.
Рис. 8.2. Создание нового сценария
Так готовятся аргументы, и после нажатия кнопки ОК, выполняются вариантные расчеты для различных вводимых значений (рис. 8.3). Кнопка Добавить переводит на предыдущее окно для создания нового сценария, а кнопка ОК – на окно, в котором можно увидеть список всех сценариев и перейти на создание отчета.
Рис. 8.3. Задание значений изменяемым ячейкам сценария
Эти действия повторяются для каждого нового сценария.
Все итоги сценариев можно собрать в табличный отчет в виде структуры, содержащий для каждого сценария состав изменяемых ячеек и значение выбранных результатных ячеек.
Задание.