Встроенные функции финансового анализа excel

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

Выполнение расчетов с помощью финансовых функций

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

Переход к данному набору инструментов легче всего совершить через Мастер функций.

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



2. Запускается Мастер функций. Выполняем клик по полю «Категории».



3. Открывается список доступных групп операторов. Выбираем из него наименование «Финансовые».



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

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

Имеется в наличии также способ перехода к нужному финансовому оператору без запуска начального окна Мастера. Для этих целей в той же вкладке «Формулы» в группе настроек «Библиотека функций» на ленте кликаем по кнопке «Финансовые». После этого откроется выпадающий список всех доступных инструментов данного блока. Выбираем нужный элемент и кликаем по нему. Сразу после этого откроется окно его аргументов.

Урок: Мастер функций в Excel

ДОХОД

Одним из наиболее востребованных операторов у финансистов является функция ДОХОД. Она позволяет рассчитать доходность ценных бумаг по дате соглашения, дате вступления в силу (погашения), цене за 100 рублей выкупной стоимости, годовой процентной ставке, сумме погашения за 100 рублей выкупной стоимости и количеству выплат (частота). Именно эти параметры являются аргументами данной формулы. Кроме того, имеется необязательный аргумент «Базис». Все эти данные могут быть введены с клавиатуры прямо в соответствующие поля окна или храниться в ячейках листах Excel. В последнем случае вместо чисел и дат нужно вводить ссылки на эти ячейки. Также функцию можно ввести в строку формул или область на листе вручную без вызова окна аргументов. При этом нужно придерживаться следующего синтаксиса:

=ДОХОД(Дата_сог;Дата_вступ_в_силу;Ставка;Цена;Погашение»Частота;[Базис])

БС

Главной задачей функции БС является определение будущей стоимости инвестиций. Её аргументами является процентная ставка за период («Ставка»), общее количество периодов («Кол_пер») и постоянная выплата за каждый период («Плт»). К необязательным аргументам относится приведенная стоимость («Пс») и установка срока выплаты в начале или в конце периода («Тип»). Оператор имеет следующий синтаксис:

=БС(Ставка;Кол_пер;Плт;[Пс];[Тип])

ВСД

Оператор ВСД вычисляет внутреннюю ставку доходности для потоков денежных средств. Единственный обязательный аргумент этой функции – это величины денежных потоков, которые на листе Excel можно представить диапазоном данных в ячейках («Значения»). Причем в первой ячейке диапазона должна быть указана сумма вложения со знаком «-», а в остальных суммы поступлений. Кроме того, есть необязательный аргумент «Предположение». В нем указывается предполагаемая сумма доходности. Если его не указывать, то по умолчанию данная величина принимается за 10%. Синтаксис формулы следующий:

=ВСД(Значения;[Предположения])

МВСД

Оператор МВСД выполняет расчет модифицированной внутренней ставки доходности, учитывая процент от реинвестирования средств. В данной функции кроме диапазона денежных потоков («Значения») аргументами выступают ставка финансирования и ставка реинвестирования. Соответственно, синтаксис имеет такой вид:

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

ПРПЛТ

Оператор ПРПЛТ рассчитывает сумму процентных платежей за указанный период. Аргументами функции выступает процентная ставка за период («Ставка»); номер периода («Период»), величина которого не может превышать общее число периодов; количество периодов («Кол_пер»); приведенная стоимость («Пс»). Кроме того, есть необязательный аргумент – будущая стоимость («Бс»). Данную формулу можно применять только в том случае, если платежи в каждом периоде осуществляются равными частями. Синтаксис её имеет следующую форму:

=ПРПЛТ(Ставка;Период;Кол_пер;Пс;[Бс])

ПЛТ

Оператор ПЛТ рассчитывает сумму периодического платежа с постоянным процентом. В отличие от предыдущей функции, у этой нет аргумента «Период». Зато добавлен необязательный аргумент «Тип», в котором указывается в начале или в конце периода должна производиться выплата. Остальные параметры полностью совпадают с предыдущей формулой. Синтаксис выглядит следующим образом:

=ПЛТ(Ставка;Кол_пер;Пс;[Бс];[Тип])

ПС

Формула ПС применяется для расчета приведенной стоимости инвестиции. Данная функция обратная оператору ПЛТ. У неё точно такие же аргументы, но только вместо аргумента приведенной стоимости («ПС»), которая собственно и рассчитывается, указывается сумма периодического платежа («Плт»). Синтаксис соответственно такой:

=ПС(Ставка;Кол_пер;Плт;[Бс];[Тип])

ЧПС

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

=ЧПС(Ставка;Значение1;Значение2;…)

СТАВКА

Функция СТАВКА рассчитывает ставку процентов по аннуитету. Аргументами этого оператора является количество периодов («Кол_пер»), величина регулярной выплаты («Плт») и сумма платежа («Пс»). Кроме того, есть дополнительные необязательные аргументы: будущая стоимость («Бс») и указание в начале или в конце периода будет производиться платеж («Тип»). Синтаксис принимает такой вид:

=СТАВКА(Кол_пер;Плт;Пс[Бс];[Тип])

ЭФФЕКТ

Оператор ЭФФЕКТ ведет расчет фактической (или эффективной) процентной ставки. У этой функции всего два аргумента: количество периодов в году, для которых применяется начисление процентов, а также номинальная ставка. Синтаксис её выглядит так:

=ЭФФЕКТ(Ном_ставка;Кол_пер)

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

ФУНКЦИИ MS EXCEL
ДЛЯ ФИНАНСОВОГО АНАЛИЗА.

Цель занятия. Изучение
информационной технологии использования встроенных вычислительных функций Excel
для финансового анализа.
Инструментарий.
ПЭВМ IBM PC, программа MS Excel.

Литература.
1. Информационные технологии в
профессиональной деятельности: учебное пособие/ Елена Викторовна Михеева. –
М.: Образовательно-издательский центр «Академия», 2004.
2. Практикум по информационным
технологиям в профессиональной деятельности: учебное пособие-практикум /
Елена Викторовна Михеева. – М.: Образовательно-издательский центр «Академия»,
2004.

ЗАДАНИЯ

Задание 1. Создать таблицу
финансовой сводки за неделю, произвести расчеты, построить диаграмму изменения
финансового результата, произвести фильтрацию данных.
Исходные данные представлены на рис.1, результаты работы на рис.7, 9 и 12.

Рис.1.

Порядок работы.

1. Откройте редактор электронных таблиц Microsoft Excel и создайте новую
электронную книгу (при стандартной установке MS Office выполните Пуск/Все программы/ Microsoft Excel).

2. Введите заголовок таблицы «Финансовая сводка за неделю (тыс. р.)», начиная с
ячейки А1.

3. Для оформления шапки таблицы выделите ячейки на третьей строке А3:D3 и
создайте стиль для оформления. Для этого выполните команду Формат/Стиль, в
открывшемся окне Стиль (рис.2),
наберите имя стиля «Шапка таблиц» и нажмите кнопку Изменить. В открывшемся окне на вкладке Выравнивание задайте Переносить по словам и
выберите горизонтальное и вертикальное выравнивание – по центру (рис.3), на
вкладке Число укажите
формат – Текстовый,
на вкладке Шрифт укажите
вид шрифта Times New Roman, начертание – полужирный, размер 12 . После этого
нажмите кнопку Добавить.

Рис.2

Рис.3

4. На третьей строке введите названия колонок таблицы — «Дни недели», «Доход»,
«Расход», «Финансовый результат», далее заполните таблицу исходными данными
согласно Заданию 1.

Краткая справка.

Для ввода дней недели наберите «Понедельник» и
произведите автокопирование до «Воскресенья» (левой кнопкой мыши за маркер
автозаполнения в правом нижнем углу ячейки).

5. Произведите расчеты в графе «Финансовый результат» по следующей формуле:
Финансовый результат = Доход – Расход,
Для этого в ячейке D4 наберите формулу = В4 – С4.

Краткая справка.

Вводите расчетные формулы только для расчета по строке
«Понедельник», далее произведите автокопирование формул (так как в графе
«Расход» нет незаполненных данными ячеек, можно производить автокопирование
двойным щелчком мыши по маркеру автозаполнения в правом нижнем углу ячейки).

6. Для ячеек с результатом расчетов задайте формат – «Денежный» с выделением
отрицательных чисел красным цветом (рис.4) (Формат/
Ячейки/ вкладка Число/формат — Денежный/ отрицательные числа – красные (с минусом). Число
десятичных знаков задайте равное 2).
Обратите внимание, как изменился цвет отрицательных значений финансового
результата на красный.

Рис.4.

7. Рассчитайте средние значения Дохода и Расхода, пользуясь мастером функций
(кнопка fx ). Функция «Среднее значение» (СРЗНАЧ) находится в разделе
«Статистические». Для расчета функции СРЗНАЧ дохода установите курсор в
соответствующей ячейке (В11), запустите Мастер функций и выберите функцию
СРЗНАЧ (Вставка/Функция/
категория – Статистические/ СРЗНАЧ) (рис.5.) В качестве первого
числа выделите группу ячеек с данными для расчета среднего значения В4:В10.
Аналогично рассчитайте среднее значение расхода.

Рис.5.

8. В ячейке D13 выполните расчет общего финансового результата (сумма по
столбцу «Финансовый результат»). Для выполнения автосуммы удобно пользоваться
кнопкой Автосуммирования (∑) на панели инструментов или функцией СУММ (Вставка/Функция/ категория –
Математические/ СУММ). В качестве первого числа выделите группу
ячеек с данными для расчета суммы – D4:D10 (рис.6).

Рис.6.

9. Проведите форматирование заголовка таблицы. Для этого выделите интервал
ячеек от А1 до D1, объедините их кнопкой панели инструментов Объединить и поместить в центре или
командой меню Формат/Ячейки/
вкладка Выравнивание/ отображение – Объединение ячеек. Задайте
начертание шрифта – полужирное, цвет – по вашему усмотрению.

Проведите форматирование ячейки D13 таблицы. Для выделения результата
финансового анализа выполните заливку цветом ячейки D13 (Формат/ Ячейки/ вкладка Вид).
Конечный вид таблицы приведен на рис.7.

Рис.7.

Выполните обрамление таблицы линиями (Формат/
Ячейка/ Граница)

10. Постройте диаграмму (линейчатого типа) изменения финансовых результатов по
дням недели с использованием мастера диаграмм.
Для этого выделите интервал ячеек с данными финансового результата D4:D10 и
выберите команду Вставка/Диаграмма.
На первом шаге работы с Мастером диаграмм выберите тип диаграммы – линейчатая;
на втором шаге на вкладке Ряд
в окошке Подписи
оси Х укажите интервал ячеек с днями недели А4:А10 (выделите мышью
этот интервал ячеек)(рис.8).

Рис.8.

Далее введите название диаграммы и подписи осей; дальнейшие шаги построения
диаграммы осуществляется по подсказкам мастера. Конечный вид диаграммы приведен
на рис.9.

Рис.9.

11. Произведите фильтрацию значений дохода, превышающих 4200 руб.

Краткая справка.

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

Для установления режима фильтра установите курсор внутри созданной таблицы и
воспользуйтесь командой Данные/Фильтр/Автофильтр.
В заголовках полей появятся стрелки выпадающих списков. Щелкните по стрелке в
заголовке поля, на которое будет наложено условие (в столбце «Доход»), и вы
увидите список всех неповторяющихся значений этого поля. Выберите команду для
фильтрации — условие (рис.10).

В открывшемся окне «Пользовательский автофильтр» задайте условие «Больше 4200»
(рис.11).

Рис.10.

Рис.11

Произойдет отбор данных по заданному условию.
Проследите, как изменился вид таблицы и построенная диаграмма (рис.12).

Рис.12.
12. Сохраните созданную электронную книгу в своей папке.

Задание 2. Заполнить таблицу «Анализ продаж»
(рис.13), произвести расчеты, выделить минимальную и максимальную сумму
покупки; по результатам расчета построить круговую диаграмму суммы продаж.

Порядок работы.

Используйте созданный стиль «Шапка таблиц» Формат/Стиль/«Шапка таблиц».

Рис.13

Формулы для расчета:

Сумма = Цена * Количество

Всего = сумма значений колонки «Сумма»

Краткая справка. Для определения
максимального (минимального) значения данных установите курсор в ячейке
расчета, выберите встроенную функцию МАКС (МИН) из категории «Статистические»,
в качестве первого числа выделите диапазон ячеек значений столбца «Сумма»
(ячейки Е3:Е10).

Дополнительные задания

Задание 3. Заполнить
ведомость учета брака (рис.14.), произвести расчеты, выделить минимальную,
максимальную и среднюю сумму брака, а также средний процент брака; произвести
фильтрацию данных по условию процента брака < 9 %, построить график
отфильтрованных значений изменения суммы брака по месяцам.

Рис.14

Порядок работы.

Формула для расчета:

Сумма брака =
Процент брака * Сумма зарплаты

Краткая справка. В колонке
«Процент брака» установите процентный формат чисел (Формат/Ячейки/ вкладка Число/ формат – Процентный).

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

Вставка функции

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

1. Выбрав нужную ячейку щелкаем по значку “fx (Вставить функцию)” слева от строки формул.
2. Или переключаемся во вкладку “Формулы” и жмем аналогичную кнопку, расположенную в левом углу ленты программы.

Независимо от выбранного варианта, откроется окно вставки функции, в котором требуется выбрать категорию “Финансовые”, определиться с нужным оператором (например, ДОХОД), после чего нажать кнопку OK.

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

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

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

Альтернативный способ

Находясь во вкладке “Формулы” можно нажать кнопку “Финансовые” в группе “Библиотека функций”. Раскроется список доступных вариантов, среди которых просто кликаем по нужному.

После этого сразу же откроется окно с аргументами функции для заполнения.

Популярные финансовые функции

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

БС

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

Обязательными аргументами (параметрами) для заполнения являются:

• Ставка – процентная ставка за период;
• Кпер – общее количество периодов выплат;
• Плт – неизменная выплата за каждый период.

Необязательные аргументы:

• Пс – приведенная (нынешняя) стоимость. Если не заполнять, будет принято значение, равное “0”;
• Тип – здесь указывается:
  • 0 – выплата в конце периода;
  • 1 – выплата в начале периода
  • если поле оставить пустым, по умолчанию будет принято нулевое значение.

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

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

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

Результат в ячейке и выражение в строке формул:

ВСД

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

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

Необязательный аргумент – “Предположение”. Здесь указывается предполагаемая величина, которая близка к результату ВСД. Если не заполнять данное поле, по умолчанию будет принято значение, равное 10% (или 0,1).

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

=ВСД(значения;[предположение])

Результат в ячейке и выражение в строке формул:

ДОХОД

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

Обязательные аргументы:

• Дата_согл – дата соглашения/расчета по ценным бумагам (далее – ц.б.);
• Дата_вступл_в_силу – дата вступления в силу/погашения ц.б.;
• Ставка – годовая купонная ставка ц.б.;
• Цена – цена ц.б. за 100 рублей номинальной стоимости;
• Погашение – суммы погашения или выкупная стоимость ц.б. за 100 руб. номинальной стоимости;
• Частота – количество выплат за год.

Аргумент “Базис” является необязательным, в нем задается способ вычисления дня:

• 0 или не заполнен – армериканский (NASD) 30/360;
• 1 – фактический/фактический;
• 2 – фактический/360;
• 3 – фактический/365;
• 4 – европейский 30/360.

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

=ДОХОД(дата_согл;дата_вступл_в_силу;ставка;цена;погашение;частота;[базис])

Результат в ячейке и выражение в строке формул:

МВСД

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

У функции только обязательные аргументы, к которым относятся:

• Значения – указываются отрицательные (платежи) и положительные числа (поступления), представленные в виде массива или ссылок на ячейки. Соответственно, здесь должно быть указано, как минимум, одно положительное и одно отрицательное числовое значение;
• Ставка_финанс – выплачиваемая процентная ставка за оборачиваемые средства;
• Ставка _реинвест – процентная ставка при реинвестировании за оборачиваемые средства.

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

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

Результат в ячейке и выражение в строке формул:

ИНОРМА

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

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

• Дата_согл – дата расчета по ц.б.;
• Дата_вступл_в_силу – дата погашения ц.б.;
• Инвестиция – сумма, вложенная в ц.б.;
• Погашение – сумма к получению при погашении ц.б.;
• аргумент “Базис” как и для функции ДОХОД является необязательным.

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

=ИНОРМА(дата_согл;дата_вступл_в_силу;инвестиция;погашение;[базис])

Результат в ячейке и выражение в строке формул:

ПЛТ

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

Обязательные аргументы:

• Ставка – процентная ставка за период займа;
• Кпер – общее количество периодов выплат;
• Пс – приведенная (нынешняя) стоимость.

Необязательные аргументы:

• Бс – будущая стоимость (баланс после последней выплаты). Если поле оставить незаполненным, по умолчанию будет принято значение, равное “0”.
• Тип – здесь указывается, как будет производиться выплата:
  • “0” или не указано – в конце периода;
  • “1” – в начале периода.

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

=ПЛТ(ставка;кпер;пс;[бс];[тип])

Результат в ячейке и выражение в строке формул:

ПОЛУЧЕНО

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

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

• Дата_согл – дата расчета по ц.б.;
• Дата_вступл_в_силу – дата погашения ц.б.;
• Инвестиция – сумма, инвестированная в ц.б.;
• Дисконт – ставка дисконтирования ц.б.;
• “Базис” – необязательный аргумент (см. функцию ДОХОД).

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

=ПОЛУЧЕНО(дата_согл;дата_вступл_в_силу;инвестиция;дисконт;[базис])

Результат в ячейке и выражение в строке формул:

ПС

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

Обязательные аргументы:

• Ставка – процентная ставка за период;
• Кпер – общее количество периодов выплат;
• Плт – неизменная выплата за каждый период.

Необязательные аргументы – такие же как и для функции “ПЛТ”:

• Бс – будущая стоимость;
• Тип.

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

=ПС(ставка;кпер;плт;[бс];[тип])

Результат в ячейке и выражение в строке формул:

СТАВКА

Оператор поможет найти процентную ставку по аннуитету (финансовой ренте) за 1 период.

Обязательные аргументы:

• Кпер – общее количество периодов выплат;
• Плт – неизменная выплата за каждый период;
• Пс – приведенная стоимость.

Необязательные аргументы:

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

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

=СТАВКА(кпер;;плт;пс;[бс];[тип];[предположение])

Результат в ячейке и выражение в строке формул:

ЦЕНА

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

Обязательные аргументы:

• Дата_согл – дата расчета по ц.б.;
• Дата_вступл_в_силу – дата погашения ц.б.;
• Ставка – годовая купонная ставка ц.б.;
• Доход – годовой доход по ц.б.;
• Погашение – выкупная стоимость ц.б. за 100 руб. номинальной стоимости;
• Частота – количество выплат за год.

Аргумент “Базис” как и для оператора ДОХОД является необязательным.

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

=ЦЕНА(дата_согл;дата_вступл_в_силу;ставка;доход;погашение;частота;[базис])

Результат в ячейке и выражение в строке формул:

ЧПС

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

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

• Ставка – ставка дисконтирования за 1 период;
• Значение1 – здесь указываются выплаты (отрицательные значения) и поступления (положительные значения) в конце каждого периода. Поле может содержать до 254 значений.
• Если лимит аргумента “Значение 1” исчерпан, можно перейти к заполнению следующих – “Значение2”, “Значение3” и т.д.

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

=ЧПС(ставка;значение1;[значение2];...)

Результат в ячейке и выражение в строке формул:

Заключение

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

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

Вставка функции

Для начала вспомним, как вставить функцию в ячейку таблицы. Сделать это можно несколькими способами:

1. Выбрав нужную ячейку, щелкните значок «fx (Вставить функцию)» слева от строки формул.
2. Или перейдите на вкладку «Формулы» и нажмите аналогичную кнопку, расположенную в левом углу ленты программы.

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

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

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

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

Альтернативный способ

На вкладке «Формулы» вы можете нажать кнопку «Финансы» в группе «Библиотека функций». Откроется список доступных опций, среди которых просто нажмите на тот, который вам нужен.

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

Популярные финансовые функции

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

БС

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

Обязательные аргументы (параметры) для заполнения:

• Ставка — процентная ставка за период;
• Nпер — общее количество периодов выплат;
• Pmt — постоянный платеж за каждый период.

Необязательные аргументы:

• Ps — текущая стоимость (настоящая). Если оставить поле пустым, будет принято значение, равное «0”;
• Тип — здесь указан:
  • 0 — платеж в конце периода;
  • 1 — выплата в начале периода
  • если поле оставить пустым, значение по умолчанию будет равно нулю.

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

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

= BS (ставка; nper; plt; [ps]; [type])

Результат в ячейке и выражение в строке формул:

ВСД

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

Есть только один обязательный аргумент: «Значения», в котором необходимо указать массив или координаты диапазона ячеек с числовыми значениями (хотя бы одно отрицательное и одно положительное число), по которым будет производиться вычисление.

Необязательный аргумент — «Угадай». Здесь указывается расчетное значение, которое близко к результату IRR. Если вы не заполните это поле, значение по умолчанию будет 10% (или 0,1).

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

= IRR (значения; [предположить])

Результат в ячейке и выражение в строке формул:

ДОХОД

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

Обязательные темы:

• Date_sogl — дата заключения договора / расчета по ценным бумагам (далее — БСЭ.);
• Дата вступления в силу — дата вступления в силу / дата погашения ценной бумаги.;
• Ставка — годовая купонная ставка центрального банка.;
• Цена — цена ЦБ за 100 рублей номинальной стоимости;
• Выкуп — сумма выкупа или выкупная стоимость ценных бумаг в расчете на 100 рублей номинальной стоимости;
• Периодичность: количество выплат в год.

Аргумент Base является необязательным, он указывает, как рассчитывается день:

• 0 или без заполнения — американский (NASD) 30/360;
• 1 — действенный / действенный;
• 2 — эффективное / 360;
• 3 — эффективный / 365;
• 4 — 30/360 евро.

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

= ДОХОД (дата_ соглашения; интересная_дата; ставка; цена; срок погашения; частота; [база])

Результат в ячейке и выражение в строке формул:

МВСД

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

У функции есть только обязательные аргументы, в том числе:

• Значения — отображаются отрицательные (оплаченные) и положительные (полученные) числа в виде массива или ссылок на ячейки. Следовательно, здесь должно быть указано как минимум одно положительное и одно отрицательное числовое значение;
• Rate_finance — процентная ставка, выплачиваемая за возврат средств;
• _Реинвестиционная ставка — процентная ставка реинвестирования оборотных средств.

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

= MVRD (значения; финансовый_рейт; реинвестиционный_рейт)

Результат в ячейке и выражение в строке формул:

ИНОРМА

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

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

• Date_sogl — дата расчета центральным банком.;
• Date_input_in_force — дата погашения центрального банка.;
• Инвестиции: сумма, вложенная в центральный банк.;
• Погашение — сумма, получаемая при погашении ценных бумаг.;
• аргумент «Базовый», как и для функции ДОХОД, является необязательным.

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

= INORMA (date_accord; date_involving_for_for_for_for_investment; инвестиции; возврат; [база])

Результат в ячейке и выражение в строке формул:

ПЛТ

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

Обязательные темы:

• Ставка — процентная ставка на период кредита;
• Nпер — общее количество периодов выплат;
• Ps — текущая стоимость (присутствует.

Необязательные аргументы:

• Fs — будущая стоимость (остаток после последнего платежа). Если поле оставить пустым, значением по умолчанию будет «0”.
• Тип — здесь вы можете указать, как будет производиться оплата:
  • «0» или не указано — в конце периода;
  • «1» — в начале периода.

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

= PMT (ставка; кол-во; пс; [bs]; [тип])

Результат в ячейке и выражение в строке формул:

ПОЛУЧЕНО

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

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

• Date_sogl — дата расчета центральным банком.;
• Date_input_in_force — дата погашения центрального банка.;
• Инвестиции: сумма, вложенная в центральный банк.;
• Дисконт — это ставка дисконтирования для центрального банка.;
• «База» — необязательный аргумент (см. Функцию ДОХОД).

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

= ПОЛУЧЕНО (date_accord; date_involvement_in_for_for_forward; инвестиции; скидка; [базовая])

Результат в ячейке и выражение в строке формул:

ПС

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

Обязательные темы:

• Ставка — процентная ставка за период;
• Nпер — общее количество периодов выплат;
• Pmt — постоянный платеж за каждый период.

Необязательные аргументы такие же, как для функции «PMT”:

• Fs — будущая стоимость;
• Вид.

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

= PS (ставка; кол-во; plt; [bs]; [тип])

Результат в ячейке и выражение в строке формул:

СТАВКА

Оператор поможет подобрать процентную ставку по аннуитету (финансовой аренде) на 1 период.

Обязательные темы:

• Nпер — общее количество периодов выплат;
• Пт — постоянный платеж за каждый период;
• Ps — это текущая стоимость.

Необязательные аргументы:

• Fs — будущее значение (см. Функцию PMT);
• Тип (см. Функцию PMT);
• Предположение — оценочная стоимость ставки. Если не указано, значение по умолчанию — 10% (или 0,1).

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

= RATE (nper ;; plt; ps; [bs]; [тип]; [предположение])

Результат в ячейке и выражение в строке формул:

ЦЕНА

Оператор позволяет узнать цену за 100 рублей номинала ЦБ, по которой выплачиваются периодические проценты.

Обязательные темы:

• Date_sogl — дата расчета центральным банком.;
• Date_input_in_force — дата погашения центрального банка.;
• Ставка — годовая купонная ставка центрального банка.;
• Доход — годовой доход центрального банка.;
• Погашение — погашаемая стоимость облигаций в расчете на 100 рублей номинальной стоимости;
• Периодичность: количество выплат в год.

Аргумент Base, как и для оператора INCOME, необязателен.

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

= ЦЕНА (дата_ соглашения, дата_интересности, ставка, доход, срок погашения, частота, [база])

Результат в ячейке и выражение в строке формул:

ЧПС

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

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

• Ставка — ставка со скидкой на 1 период;
• Значение1 — здесь указаны выплаты (отрицательные значения) и поступления (положительные значения) в конце каждого периода. Поле может содержать до 254 значений.
• Если лимит аргумента «Значение 1» исчерпан, можно перейти к заполнению следующих значений — «Значение2», «Значение3» и т.д.

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

= ЧПС (ставка; значение1; [значение2];…)

Результат в ячейке и выражение в строке формул:

Заключение

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