Использование встроенных функций MS Excel

 

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

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

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

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

Технология работы с финансовыми функциями Excel

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

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

F Осуществляется вызов «Мастера функций [18] »

На основной панели инструментов имеются кнопки " Мастер функций ", с помощью которой открывается диалоговое окно Диспетчера функций [19].

Диалоговое окно «Диспетчер функций» организовано по тематическому принципу. После выбора в левом списке «Категории» тематической группы «Финансовые», на экран будет выведено диалоговое окно с полным перечнем списка имен функций, содержащихся в данной группе[20].
Поиск необходимой финансовой функции осуществляется путем последовательного просмотра списка.

ðДля выбора функции курсор устанавливается на имя функции.

 

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

Рис. 2‑1 Последовательность действий при выборе необходимой финансовой функции

Рис. 2‑2 Фрагмент листа Excel с диалоговым окном финансовой функции БС (расчет будущей стоимости инвестиции) и справочной информацией

 

 

ðПеренос формулы необходимой функции в ячейку осуществляется двойным щелчком на ее имени,[21] либо щелчком на кнопке «ОК»

 

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

 

FВ поля диалогового окна функции:

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

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

процентная ставка вводится в виде десятичной дроби, либо с использованием знака %;

все даты как аргументы функций имеют числовой формат представления, например дата 1 сентября 2006 года представляется числом 38961 [22]

 

Примечание
          Если значение аргумента типа «Дата» берется из ячейки, то дата может быть записана в обычном виде, например 1.09.2006.
      При вводе аргумента типа «Дата» непосредственно в поле ввода диалогового окна финансовой функции, можно воспользоваться встроенной функцией «Дата», которая осуществляет преобразование строки символов в дату[23].

 

 

Для исчисления характеристик финансовых операций с наращением и дисконтированием вложенных сумм удобно использовать функции БС(), ПС(), КПЕР(), НОРМА(), БЗРАСПИС(,)НОМИНАЛ(), ЭФФЕКТ(). ПЛПРОЦ(), ОББШПЛАТ(), ОСНПЛАТ(), ОБЩДОХОД().

 

Таблица 2‑1

Функции рабочего листа Excel для оценки разовых и периодических (потоков) платежей

Наименование функции				Формат функции				Назначение функции
БС В младших версиях Excel эта функция обозначена как БЗ			БС (ставка; кпер; платеж; нc; [тип])				рассчитывает будущую стоимость периодических постоянных платежей и будущее значение вклада (или займа) на основе постоянной процентной ставки
ПС В младших версиях Excel эта функция обозначена как ПЗ			ПС(ставка; кпер; платеж; бс; [тип])				предназначена для расчета текущей стоимости, как единой суммы вклада (займа), так и будущих фиксированных периодических платежей. Текущий объем — это общая сумма, которую составят будущие платежи. Например, когда деньги берутся взаймы, заимствованная сумма и есть текущий объем для заимодавца. Этот расчет является обратным к определению будущей стоимости при помощи функции ПС
КПЕР			КПЕР(ставка; платеж; нз; бс; [тип])				вычисляет количество периодов начисления процентов, исходя из известных величин r, FV и PV.
СТАВКА В младших версиях Excel эта функция обозначена как НОРМА			СТАВКА (кпер; платеж; нз; бс; [тип])				вычисляет процентную ставку, которая в зависимости от условий операции может выступать либо в качестве цены, либо в качестве нормы ее рентабельности.
ПЛТ			ПЛТ(ставка; кпер; нз; [бс]; [тип])				позволяет рассчитать сумму постоянных периодических платежей (CF). необходимых для равномерного погашения займа при известных сумме займа, ставки процентов и срока на который выдан заем.
БЗРАСПИС		БЗРАСПИС (сумма; массив ставок)				удобно использовать для расчета будущей величины разовой инвестиции в случае, если начисление процентов осуществляется по плавающей ставке. (Например, доходы по облигациям государственного сберегательного займа, начисляются раз в квартал по плавающей купонной ставке).
НОМИНАЛ	НОМИНАЛ (эф_ставка;кол_пер)				Возвращает номинальную годовую процентную ставку, если известны фактическая ставка и число периодов, составляющих год.
ЭФФЕКТ			ЭФФЕКТ(ном_ставка; кол_пер)				Возвращает фактическую годовую процентную ставку, если заданы номинальная годовая процентная ставка и количество периодов, составляющих год.
ПЛПРОЦ			ПЛПРОЦ(ставка; период;кпер;пс))				Вычисляет проценты, выплачиваемые за определенный инвестиционный период
			ПРПЛТ(ставка;период;кпер;пс; бс;тип)				Возвращает сумму платежей процентов по инвестиции за данный период на основе постоянства сумм периодических платежей и постоянства процентной ставки.
			ОСПЛТ(ставка; период; кпер;пс; бс; тип)				Возвращает величину платежа в погашение основной суммы по инвестиции за данный период на основе постоянства периодических платежей и постоянства процентной ставки
			ОБДОХОД(ставка; кпер; нз; нач_период; кон_период, тип)				Вычисляет сумму основных платежей по займу, который погашается равными платежами в конце или начале каждого расчетного периода, между двумя расчетными периодами

 

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

ставка - процент­ная ставка за период (норма доходности или цена заемных средств – r). Например, если получена ссуда на автомобиль под 10 процентов годовых и делаются ежемесячные выплаты, то процентная ставка за месяц составит 10%/12 или 0,83%. В качестве значения аргумента ставка нужно ввести в формулу 10%/12 или 0,83% или 0,0083

кпер - срок (число периодов n) проведения операции. Например, если получена ссуда на 4 года под приобретение автомобиля и делаются ежемесячные платежи, то ссуда имеет 4*12 (или 48) периодов. В качестве значения аргумента кпер в формулу нужно ввести число 48.

Плт - выплата, производимая в каждый период и не меняющаяся за все время выплаты ренты. Обычно выплаты включают основные платежи и платежи по процентам, но не включают других сборов или налогов. Например, ежемесячная выплата по четырехгодичному займу в 10 000 руб. под 12 процентов годовых составит 263,33 руб. В качестве значения аргумента выплата нужно ввести в формулу число -263,33.

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

Бс - требуемое значение будущей стоимости (FV) или остатка средств после последней выплаты. Если аргумент опущен, он полагается равным 0 (будущая стоимость займа, например, равна 0). Например, если предполагается накопить 50000 руб. для оплаты специального проекта в течение 18 лет, то 50 000 руб. это и есть будущая стоимость

[тип] - число 0 или 1, обозначающее, когда должна производиться выплата [1 - начало пери­ода (обычная рента или пренумерандо), 0 - конец периода (постнумерандо)], необязательный аргумент.

 

F Завершение ввода аргументов и запуск расчета значения функции выполняется нажатием кнопки «ОК».

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

ðВыполнить редактирование аргументов функции в строке формул

либо

ðповторно вызвать, используя «Мастер функций», диалоговое окно функции и в нем выполнить необходимую коррекцию[24].

 

Операции наращения. Функция БС()

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

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

 

F Простые проценты. Для решения задач наращения по схеме простых процентов функция БС() в качестве аргументов использует только аргументы: норма; число периодов; ПС.

Остальные аргументы не используются.

 

 

Пример 2‑1

Определить наращенную сумму для вклада в размере 10000 руб., размещенного под 15% годовых на один год

 

 

Рис. 2‑3 Решение примера 3-1

 

Таким образом, через год наращенная сумма составит 11500руб.

В приведенном примере, в качестве аргумента функции Кпер было указано целое число (1 год).

Если продолжительность финансовой операции представлена в днях, то необходимо ввести корректировку в процентную ставку, т.е. аргумент норма должен быть представлен как t / T * r %.

 

Пример 2‑2

Вклад размером в 2000 руб. положен с 06.06 по 17.09 невисокосного года под 30% годовых. Найти величину капитала на 17.09 по различной практике начисления процентов.

Решение

FГерманская практика расчета[25]

 В соответствии с германской практикой расчета период накопления составляет 101 день.

БС(((B8-B7)-2)/360*B2;B3;;B5) = 2168.3 руб.

 

Рис. 2‑4 Решение примера 3-2 (германская практика расчета)

 

Обратите внимание, что в строке «Норма» диалогового окна функции записано выражение, характеризующее величину процентной ставки за период накопления t / T * r % =(B8-B7)-2)/360*B2. При этом из точного значения периода накопления в соответствии с германской практикой расчета вычтено 2 дня (продолжительность июня и августа составляли 31 день).

 

FФранцузская система расчета[26]

В соответствии с французской практикой расчета период накопления составляет 103 дня.

БС(((B8-B7))/360*B2;B3;;B5) = 2171.7 руб.

Таким образом, начисление процентов по германской практике приведет к получению суммы в размере 2168,33 руб., по английской практике – 2169,3 руб., по французской практике – 2171,7 руб.

 

Примечание

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

 

 

Рис. 2‑5 Диалоговое окно функции «Дата»

 

В этом случае, запись в строке «Ставка» диалогового окна функции БС будет иметь вид:

 БС(((ДАТА(2006;9;17) -ДАТА(2006;6;6)))/360*B2;B3;;B5)

 

 

Рис. 2‑6 Диалоговое окно функции БС с использованием вложенной функции «Дата» для расчета продолжительности интервала накопления.

FСложные проценты

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

Пример 2‑3

Определить будущую величину вклада в 10000,00, помещенного в банк на 5 лет под 5% годовых, если начисление процентов осуществляется:

а) раз в году;

б) раз в месяц.

Решение

 

 

Рис. 2‑7 Решение примера 2-3 при начислении процентов один раз в год

 

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

ставка – берется ставка процентов за период начисления, т.е. используется номинальная годовая ставка процентов, скорректированная на число раз (m) начисления процентов в течение года r % / m;

число периодов – указывается общее число раз начисления процентов за весь срок финансовой операции n • m.

Операции дисконтирования

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

FАргументы функции:

Ø норма;

Ø кпер;

Ø выплата;

Ø БС;

Ø Тип

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

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

 

Пример 2‑4

Фирме потребуется 5000 тыс. руб. через 10 лет. В настоящее время располагает деньгами и готова положить их на депозит единым вкладом с тем, чтобы через 10 лет получить необходимую сумму.

Определить необходимую сумму текущего вклада еслм ставка процента по нему составляет 12% в год.

Решение.

ПС(B2;B3;;B5) =  -1609866.19 руб.

 

 

 

 

Рис. 2‑8 Решение примера 2-4

 

 

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

 

Определение срока финансовой операции

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

Аргументы функции:

Ø норма;

Ø выплата;

Ø НЗ;

Ø БС;

Ø тип.

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

 

Пример 2‑5

По вкладу в 10000,00, помещенному в банк под 5% годовых, начисляемых ежегодно, была выплачена сумма 12762,82. Определить срок проведения операции (количество периодов начисления).

Решение.

КПЕР(B2;;B5;B6) =5 лет

 

Рис. 2‑9 Решение примера 2-5

 

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

Если платежи производятся несколько раз в год, то значение функции означает общее число периодов начисления процентов.

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

 

Пример 2‑6

Через сколько лет вклад размером 500 руб. достигнет величины 1000 руб. при ставке процентов 10% с ежемесячным начислением процентов?

Решение.

КПЕР(10%/12;;-500;1000) =83.5 мес. =83.5/12» 7 лет.

 

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

Для определения величины процентной ставки при известных величинах вложенных и наращенных сумм и количестве периодов начисления процентов Excel предлагает использование финансовой функции «Ставка».

FАргументы функции:

Пс – вложенная сумм

Бс – наращенная сумма;

Кпер– количество периодов начисления процентов.

 

Пример 2‑7

Фирме через 2 года потребуется 100000 руб. Для достижения этой цели фирма готова положить на депозит 25000 руб. Каким должен быть процент на инвестированные средства с тем, чтобы к концу второго года была получена необходимая сумма?

 

Решение

 

 

Рис. 2‑10 Решение примера 2-7

 

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

Для расчета эффективной и номинальной ставки процентов Excel  предлагает использование функций ЭФФЕКТ() и НОМИНАЛ() [27].

 

FФункция ЭФФЕКТ()

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

Аргументы функции:

Номинальная_ставка;

Кол_пер – количество периодов, составляющих годэ

 

 

Пример 2‑8

Номинальная ставка составляет 11%. Рассчитайте эффективную процентную ставку при следующих вариантах начисления процентов:

полугодовом;

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

ежемесячном.

 

Решение

11.3%; b)11.46; c)11.57

 

Рис. 2‑11 Решение примера 2-8 для варианта а)

 

FФункция НОМИНАЛ()

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

Аргументы функции:

Эффект_ставка;

Кол_пер – число периодов, составлящих год

Пример 2‑9

Эффективная ставка составляет 28%, а начисление процентов производится ежемесячно. Необходимо рассчитать номинальную ставку.

 

 

 

Рис. 2‑12 Решение примера 2-9

 

 

Начисление процентов по плавающей ставке

 

Для расчета будущей величины разовой инвестиции в случае, если начисление процентов осуществляется по плавающей ставке используется функция БЗРАСПИС(). Подобные операции широко распространены в отечественной финансовой и банковской практике. В частности, доходы по облигациям государственного сберегательного займа (ОГСЗ), начисляются раз в квартал по плавающей купонной ставке

 

Пример 2‑10

Ставка банка по срочным валютным депозитам на начало года составляет 20% годовых, начисляемых раз в квартал. Первоначальная сумма вклада - $1000. В течении года ожидается снижение ставок раз в квартал на 2, 3 и 5 процентов соответственно. Определить величину депозита к концу года

Решение

Введем ожидаемые значения процентных ставок в блок ячеек электронной таблицы, например: 20%/4 в ячейку B2, 18%/4в ячейку B3, 17%/4в ячейку B4и 15%/4 в ячейку B5. Тогда функция будет иметь следующий вид:

=БЗРАСПИС(1000; B1.B4)

 (Результат: 1186,78).

 

Заметьте, что величина годовой ставки скорректирована на количество периодов начисления.