Финансово-коммерческие расчеты на основе Microsoft Excel

на абсолютный, с абсолютного на частично абсолютный и т. д.

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

Для того чтобы ввести ссылку в строку формул, нет нужды каждый раз набирать ее там вручную. Ссылку можно ввести в формулу, если при вводе формулы просто щелкнуть мышью в той ячейке, на которую вы хотите сослаться. Адрес ячейки окажется в строке формул. Для того чтобы вставить ссылку на ячейку, которая находится в текущей книге, но на другом листе, нужно в строке формул ввести ссылку в виде: Имя_листа! Адрес_ячейки.

Обратите внимание, что этот механизм работает только при вводе формулы и не работает при вводе текста. То есть для того, чтобы происходило автоматическое помещение адреса ячейки в строку формул, она должна начинаться со знака равенства (=).

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

3.2.Присвоение имен

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

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

2. Щелкните на выделенном фрагменте правой кнопкой мыши и выберите

впоявившемся меню команду «Присвоить имя», чтобы открыть диалоговое окно, показанное на рис.3.1.

В Excel 2007 или Excel 2010 для открытия этого окна нужно на вкладке «Формулы» выбрать функцию «Присвоить имя».

Рис. 3.1. Окно присвоения имени

33

В поле Имя введите имя для выбранной вами ячейки или диапазона и щелкните на кнопке OK. После присвоения имени вы можете вместо адреса ячейки или ссылки на диапазон ячеек использовать присвоенное имя.

Правила присваивания имен ячейкам и диапазонам

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

2.Имя должно начинаться с буквы, обратной косой черты (\) или символа подчеркивания (_).

3.Нельзя использовать имена, которые могут трактоваться как ссылки на ячейки (А1, С4 и т.д.).

4.В качестве имен могут использоваться одиночные буквы за исключением букв R, C.

5.Пробелы необходимо заменять символом подчеркивания.

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

ВExcel 10 имеются также следующие удобные средства работы с именами:

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

функция «использовать в формуле» предназначена для выбора и вставки имени в текущую формулу.

3.3.Специальные финансовые функции

ВExcel 2010 реализована специальная группа из 52 финансовых функций. Вставка функций в лист Excel производится выбором в раскрывающемся списке, который появляется после ввода знака равенства (=) в строке имен. Чтобы открыть окно мастера функций следует выбрать нужную функцию или пункт Другие функции. Это же окно выводится на экран выбором команды f (x) на панели инструментов.

Всписке «Категория» перечислены категории, в которые объединены функции. В списке «Выберите функцию» перечислены функции, относящиеся

квыбранной категории. Под списком расположено описание синтаксиса выбранной функции и самой функции (рис. 3.2).

34

Рис.3.2. Окно мастера функций

По типу решаемых задач все финансовые функции Excel можно разделить на следующие условные группы:

функции для анализа простейших финансовых операций и аннуитетных потоков платежей;

функции для анализа инвестиционных проектов;

функции для анализа ценных бумаг;

функции для расчета амортизационных платежей;

вспомогательные функции.

Каждая встроенная функция Excel – это заранее определенная формула (или несколько формул), которая имеет название или уникальное имя.

В формулах функция выглядит следующим образом:

Название функции (аргументы),

где «Название функции» – это уникальное имя функции, а аргументы – ее

входные данные.

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

Например, в формуле: =Эффект (номинальная ставка; кол._пер)

аргументами или входными данными функции являются номинальная процентная ставка и количество начисления сложных процентов в год,

которые вводятся в ячейки таблицы как исходные данные.

В формуле могут быть указаны ссылки на эти данные или присвоенные данным имена. Здесь, как и во многих других случаях, важен порядок следования аргументов. Перечень финансовых функций, применяемых в Excel 10 приведен в Приложении 1.

35

Рассмотрим основные финансовые функции, применяемые для анализа простейших финансовых операций (табл. 3.1).

Таблица 3.1

Перечень основных финансовых функций для выполнения простейших финансовых операций

Наименование функции		Формат функции
Английское	Русское
FVSCHEDULE	БЗРАСПИС	БЗРАСПИС (стоимость инвестиции на текущий
		момент; массив ставок)
FV	БС	БС (ставка; кпер; плт; [ пс]; [тип])
NPER	КПЕР	КПЕР (ставка; плт; пс;[ сс]; [тип]
NOMINAL	НОМИНАЛ	НОМИНАЛ (эф_ставка; кол_пер )
PMT	ПЛТ	ПЛТ (ставка; кпер; нз; [бс]; [тип])])
PV	ПС	ПС (ставка; кпер; платеж; бс; [тип])
EFFECT	ЭФФЕКТ	ЭФФЕКТ(ном_ставка; кол_пер)
RATE	СТАВКА	СТАВКА (кпер; плт; пс; [бс]; [тип], [прогноз])
TBILLYIELD	ДОХОДКЧЕК	ДОХОДКЧЕК (дата_согл;дата_вступл_в_силу;цена)
TBILLPRICE	ЦЕНАКЧЕК	ЦЕНАКЧЕК (дата_согл;дата_вступл_в_силу;скидка)

Как видно из таблицы 3.1, большинство функций имеют одинаковый набор базовых аргументов:

ставка – процентная ставка (норма доходности или цена заемных средств – r . При этом имеется в виду реальная, а не номинальная ставка процентов. Например, при начислении процентов m раз в году значение

реальной ставки равняется mr (периодическая ставка).

Кпер – общее количество периодов платежей по аннуитету (равняется длительности операции):

 при начислении процентов один раз в году – n (число лет);  при начислении процентов m раз в году – m n .

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

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

Пс – современная величина некоторой будущей суммы или

36

современная стоимость потока платежей (PV) Если аргумент "Пс" опущен, предполагается значение 0. В этом случае аргумент "Плт" является обязательным.

Бс – значение будущей стоимости, т. е. желаемого баланса средств после последнего платежа, будущее значение (FV). Если аргумент «Бс» опущен, предполагается, что он равен 0. В этом случае необходимо указать аргумент «платеж– Плт».

Прогноз – необязательный параметр, предполагаемая величина ставки. Если этот аргумент опущен, предполагается, что его значение равно 10 %.

Тип – необязательный аргумент, равный 0, если выплата производится

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

При анализе простой финансовой операции аргумент “Плт” вводить не требуется.

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

ППП EXCEL разделяются символом «;», а признаком ввода функции служит символ «=».

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

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

Описание функций.

1. БЗРАСПИС (стоимость инвестиции на текущий момент (Пс); массив ставок).

Функцию БЗРАСПИС удобно использовать для расчета будущей величины разовой инвестиции в случае, если начисление процентов осуществляется по плавающей ставке. Ожидаемые значения процентных ставок, скорректированные на число периодов начисления вводятся в смежный блок ячеек электронной таблицы (ячейки B3:B6). Функция БЗРАСПИС заменяет формулу, представленную в ячейке C5 рис.3.3.

В столбце D формулы записаны с использованием имен, присвоенных исходным данным

37

Рис.3.3. Шаблон для вычисления функции БЗРАСПИС

2. Функция БC (ставка; кпер; Плт; [Пс]; [тип])

Эта функция используется для нахождения наращенной суммы по схеме сложных процентов для операции «сделка» а также для определения наращенной суммы аннуитетных платежей FV. При определении наращенной суммы в простейшей финансовой операции Плт не участвует. В этом случае сумму FV также можно получить по формуле, указанной в ячейке B17 (рис.3.4). В столбце С приведены эти же формулы, но со ссылками не на ячейки, а на имена., а в столбце D приведен результат.

Рис.3.4. Шаблон для вычисления наращенной суммы FV для простейшей финансовой операции

3. Функция ПС (ставка; кпер; плт; [бс]; [тип])

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

(рис.3.5).

Рис.3.5. Шаблон для вычисления приведенной суммы PV

38

4.Функция КПЕР (ставка; плт; пс; бс; [тип])

Вычисляет количество периодов начисления процентов, исходя из известных величин r (ставка), FV и PV (на основе периодических постоянных выплат и постоянной процентной ставки). Следует обратить особое внимание на то, что результатом применения функции является число периодов, а не срок операции. При рассмотрении простейших операций аргумент Плт не используется (рис.3.6).

В приведенном примере срок операции составляет 44,49 периода. Этот же результат можно получить по формуле, указанной в ячейке B22 на рис.3.6.

Разделив эту величину на m 4 , получим срок в годах, равный 11,12 года.

Рис.3.6. Вычисление числа периодов простейшей финансовой операции

4. Функция СТАВКА (кпер; плт; пс; бс; [тип])

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

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

Рис. 3.7. Вычисление процентной ставки

39

В данном примере квартальная ставка r m 9,58% , а годовая r 38,32% .

Этот же результат можно получить по формуле, представленной в ячейке C8.

Необходимо помнить, что для получения корректного результата при работе с функциями КПЕР() и СТАВКА(), аргументы «Пс»(PV) и «Бс»(FV)

должны иметь противоположные знаки. Данное требование вытекает из экономического смысла подобных операций.

5. Функции НОМИНАЛ (эф_ставка; кол_пер) и ЭФФЕКТ

(ном_ставка; кол_пер)

Рис.3.8. Вычисление эффективной и номинальной процентных ставок

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

6. Функция ДОХОДКЧЕК (дата_согл;дата_вступл_в_силу;цена)

Функция вычисляет скидку при учете векселя или другого долгового обязательства. Здесь:

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

дата_вступл_в_силу – срок погашения векселя. Соответствует дате, когда истекает срок действия векселя (обязательный параметр.

Цена – цена векселя в расчете на 100 рублей номинальной стоимости, предложенная покупателем.

Рис. 3.9. Вычисление скидки при учете векселя (доходности для покупателя)

В данном примере вексель был учтен по простой учетной ставке 2,98% годовых.

40

7. Функция ЦЕНАКЧЕК (дата_согл;дата_вступл_в_силу;скидка)

Функция вычисляет цену за 100 номинальных рублей векселя. Функция обратная ДОХОДКЧЕК: по заданной скидке определяется, сколько получит владелец векселя за каждые 100 руб. стоимости векселя.

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

В данном примере продавец векселя получит за каждые 100 руб. номинальной стоимости – 98,5 руб. Скидка d , установленная покупателем составляла 2,98% годовых простого процента.

3.4. Шаблоны для расчета параметров финансовых операций

Для расчета параметров финансовых операций целесообразно построить специальные шаблоны. Форма шаблона произвольна. Пример общего шаблона для расчета простейших финансовых операций приведен на рис.3.11.

Рис. 3.11 Пример шаблона для анализа простейших финансовых операций

Шаблон состоит из двух частей. Первая часть занимает блок ячеек предназначенных для ввода исходных данных (известных параметров финансовой операции). Текстовая информация в ячейках А2:В7 содержит наименование исходных параметров финансовой операции и их обозначение (имена), ввод данных осуществляется в ячейки С2:С7.

41

Вторая часть таблицы занимает блок ячеек А9: С13 и предназначена для вывода результатов вычислений. Блок ячеек В9:В14 содержит формулы, необходимые для исчисления соответствующих параметров финансовой операции, а блок С9:С13 – результаты вычисления.

Величины r (процентная ставка) и n (срок операции) в формулах скорректированы на число начислений процентов в году, путем деления и умножения на значение ячейки В3 соответственно. При этом здесь и в дальнейшем подразумевается задание параметра r в виде годовой процентной ставки, а срока проведения операции n – в количестве лет.

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

3.5. Подбор параметра

Подбор параметра – средство Excel, позволяющее решать следующую задачу: изменяя значение одного из исходных данных (параметров), получить значение результата. При этом результат решения задачи должен быть задан в

целевой ячейке формулой, содержащей ссылку на изменяемую ячейку с

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

Рис. 3.12. Окно подбора параметра

Для подбора параметра выполняется команда «Данные/Анализ «чтоесли» /Подбор параметра» и в открывшемся диалоговом окне (рис.3.10) задаются:

•в поле ввода «Установить в ячейке» - ссылка на целевую ячейку;

•в поле ввода «Значение» — требуемое значение,

•в поле ввода «Изменяя значение ячейки» - ссылка на изменяемую

ячейку.

После нажатия кнопки ОК или клавиши Еnter результат подбора параметра будет показан в окне «Результат подбора параметра».

Значение параметра сохранится в изменяемой ячейке (для данного

42