Финансовые функции в Excel: подробное описание

Назначение и возможности табличного процессора Excel

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

1. Решение математических задач (расчеты с большими объемами данных, нахождение значений функций, решение уравнений).
2. Построение графиков, диаграмм, работа с матрицами.
3. Сортировка, фильтрация данных по определенному критерию.
4. Осуществление статистического анализа, основные операции с базами данных.
5. Реализация табличных связей, обмен данными с другими приложениями.
6. Создание макросов, экономических алгоритмов, собственных функций.

Возможности Excel для анализа финансовой информации не так уж ограничены. Именно поэтому программа популярна среди экономистов.

Анализ и обработка экономической информации средствами Excel

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

И мы рассмотрим несколько примеров практического использования Excel в финансовых целях.

Кредиты и ренты

1. Компания создала фонд для покрытия будущих расходов. Взносы передаются в виде годового аннуитета postnumerando. Единовременный платеж составляет 20 000 рублей. На вклады начисляются проценты по ставке 12% годовых. Экономисту было предложено рассчитать, когда сумма составит 100 000 руб. Для решения воспользуемся функцией КПЕР. Цель состоит в том, чтобы определить общее количество периодов для инвестиционных платежей на основе постоянных взносов и постоянной процентной ставки.
   Вызвать функцию можно из меню «Формулы» — «Экономика» — «КПЕР»
   Аргументы функции и порядок их заполнения показаны на картинке.

   Фирме понадобится 4 года, чтобы увеличить размер фонда до 100 000 рублей. При квартальных процентах первое значение функции будет выглядеть так: 12%/4. Результат:

2. Компания взяла кредит в размере 100 000 рублей под 20% годовых. Срок — три года. Вам нужно найти процентные платежи за первый месяц. В этом поможет встроенная функция Excel CPMT. Его также можно вызвать из меню «Формулы» — «Финансовые» — «ПРПЛТ». Аргументы функции:

   Мы используем функцию IPMT, если периодические платежи и процентная ставка постоянны. Результат расчета:

3. Компания взяла в банке кредит в размере 120 млн рублей. Срок 10 лет. Процентные ставки меняются. Воспользуемся функцией БЗРАСПИС для расчета суммы долга «Формулы» — «Финансовая» — «БЗРАСПИС».

   Результат:

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

Если минимальный срок составляет один месяц (а не год), годовая ставка в формуле делится на 12 (х/12).

Платежеспособность фирмы

В экономике есть такое понятие, как коэффициент покрытия.

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

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

Соотношение между коэффициентами позволяет сделать вывод о платежеспособности предприятия.

Все это можно сделать с помощью простых инструментов Excel:

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

Расширенные возможности Excel

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

Для построения соответствующей модели решения есть дополнение «Поиск решения».

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

1. Расчет максимального производства при ограниченных ресурсах.
2. Составление/оптимизация штатного расписания при минимально возможных затратах.
3. Минимизация транспортных расходов.
4. Оптимизация средств под различные инвестиционные проекты.

Ссылка на модуль «Поиск решения»:

1. В меню «Офис» выберите «Параметры Excel» и перейдите на вкладку «Надстройки». Здесь вы увидите активные и неактивные, но доступные надстройки.
2. Если нужное дополнение неактивно, перейдите по ссылке «Управление» (внизу таблички) и установите дополнение. Появится диалоговое окно, в котором необходимо поставить галочку «Поиск решения» и нажать ОК

Теперь на простой задаче рассмотрим, как использовать расширенные функции Excel.

Для нормальной работы малого бизнеса достаточно 4-6 рабочих, 7-9 продавцов, 2 менеджера, заведующий складом, бухгалтер и директор. Их заработная плата должна быть определена. Ограничения: месячный фонд оплаты труда минимален; заработная плата рабочего не ниже прожиточного минимума в 100 долларов. Коэффициент А показывает, во сколько раз заработная плата специалиста больше, чем заработная плата рабочего.

Таблица известных параметров:

• менеджер получает на 30 долларов больше, чем продавец (объясним, откуда взялся фактор Б);
• заведующий складом — на 20 долларов больше, чем рабочий;
• директор — на 40 долларов больше, чем менеджер;
• бухгалтер на 10 долларов больше, чем менеджер.

1. Найдем зарплату каждого специалиста (на рисунке все понятно).
2. Перейдите на вкладку «Данные» — «Анализ» — «Поиск решения» (поскольку мы добавили настройку, она теперь доступна).
3. Завершает меню. Для установки ограничений используйте кнопку «Добавить». Строка «Изменить ячейки» должна содержать ссылки на ячейки, для которых программа будет искать решения. Готовый вариант будет выглядеть так:
4. Нажимаем кнопку «Выполнить» и получаем результат:

Теперь найдем зарплату для всех категорий работников и рассчитаем общую зарплату (Lønnsfondet).

Возможности Excel если и не безграничны, то их можно без ограничений расширять с помощью настроек. Настройки можно найти в Интернете или написать самостоятельно на языке макросов VBA.

Читайте также: 5 вариантов использования функции ИНДЕКС (INDEX)

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

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

Самый простой способ получить доступ к этому набору инструментов — через мастер функций.

1. Выберите ячейку, в которой должны отображаться результаты расчета, и нажмите кнопку «Вставить функцию» возле строки формул.
2. Запустится мастер функций. Нажмите на поле «Категории».
3. Откроется список доступных групп агентов. Выберите из него название «Финансовый».
4. Составили список необходимых инструментов. Выберите конкретную функцию для выполнения задачи и нажмите кнопку «ОК». Это открывает окно аргументов для выбранного оператора.

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

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

ДОХОД

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

=ДОХОД(Date_cog, Effective_Date, Price, Price, Redemption» Frequency, Basis)

БС

Основной задачей функции ФБ является определение будущей стоимости инвестиций. Аргументами являются процентная ставка за период («Rate»), общее количество периодов («Col_per») и постоянный платеж за каждый период («Pmt»). Необязательные аргументы включают текущую стоимость («Ps») и то, должна ли дата выполнения быть установлена ​​в начале или в конце периода («Тип»). Оператор имеет следующий синтаксис:

=BS(Цена;Количество_за;Тунт;Ps</a>;Тип)

ВСД

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

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

МВСД

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

=MIRR(Значения;Rate_finance;Rate_reinvestor)

ПРПЛТ

Оператор ИПМТ рассчитывает сумму процентных платежей за указанный период. Аргументами функции являются процентная ставка за период («Ставка»); номер периода («Период»), значение которого не может превышать общее количество периодов; количество периодов («Col_per»); текущая стоимость («Ps»). Кроме того, есть необязательный аргумент — будущая стоимость («Fs»). Эту формулу можно использовать только в том случае, если платежи в каждом периоде производятся равными долями. Синтаксис имеет следующий вид:

=PRPLT(Ставка,Период,Per_number,Ps,[Bs])

ПЛТ

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

=PMT(Ставка;Number_trans;Ps;[Bs];Тип)

ПС

Формула PV используется для расчета приведенной стоимости инвестиций. Эта функция обратна оператору PMT. У нее точно такие же аргументы, но вместо аргумента текущей стоимости («PV»), который фактически рассчитывается, дается сумма периодического платежа («Pmt»). Таким образом, синтаксис:

=PS(Цена;Количество_за;Пт;[Бс];Тип)

ЧПС

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

=NPV(Цена;Значение1;Значение2;…)

СТАВКА

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

=СТАВКА(Число_на, Тч, Пс[Бс], Тип)

ЭФФЕКТ

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

= ЭФФЕКТ(Номинальная_ставка,Количество_на)

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

Примеры функции БС в Excel с фиксированной процентной ставкой

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

Примеры использования финансовой функции БС в Excel

Пример 1. Инвестор внес вклад с ежемесячной капитализацией 100 000 рублей под 13% годовых сроком на 4 года. Сколько средств он может снять с депозитного счета по окончании договора с банком?

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

• B3/12 — ставка на период (капитализация осуществляется ежемесячно);
• B4 – количество периодов капитализации депозита;
• 0 — сумма платежа за период капитализации (неизвестное значение в рамках данной задачи, поэтому значение равно 0);
• B2*(-1) – сумма первого депозита (инвестиция, которая должна быть отрицательным числом).

Через 4 года вкладчик получит 167 733 рубля.

Расчет суммы долга по кредиту по состоянию на 30-й период погашения

Пример 2. Заемщик взял кредит в банке под 26% годовых в сумме 220 000 рублей сроком на 3 года с ежемесячным фиксированным платежом. Сколько заемщик должен в конце 30 платежного периода?

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

• B3/12 – ежемесячные проценты;
• 30 — номер периода расчета остатка долга;
• PMT(B3/12;B4;B2) — функция, возвращающая сумму ежемесячного платежа;
• B2 — это сам кредит.

Фактическая задолженность по кредиту на конец 30-го месяца составит примерно 49 372 рубля.

Сравнительный инвестиционный анализ условий депозита в банке

Пример 3. Вкладчик получил депозитные предложения от двух банков с разными условиями:

1. Ставка 12% годовых, капитализация ежемесячная.
2. Ставка 33% годовых, капитализация ежеквартальная.

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

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

BS(B3/12;B5;0;B6*(-1));»Первый берег»;»Второй берег»)’ class=’formula’>

С помощью функции ЕСЛИ мы определяем, в каком случае будущее значение будет больше, и возвращаем соответствующее значение. Результат:

Выводим результаты расчетов функций БС и разности сумм:

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

Особенности использования финансовой функции БС в Excel

Функция FV используется вместе с другими финансовыми функциями (PS, PMT, NPER и другими) и имеет следующий синтаксис:

=BS(коэффициент;nper;plt ;[ps];[тип])

• ставка — аргумент, который принимает числовое или процентное значение ставки за указанный период. Обязательный. Если в условии используется годовая ставка, то необходимо произвести перерасчет по следующей формуле: R=Rg/n, где Rg — годовая ставка, n — количество периодов.
• кпер — числовое значение, характеризующее количество периодов оплаты. Аргумент обязателен. Если кредит был взят сроком на 3 года, который нужно выплачивать каждый месяц, то аргумент nper должен иметь значение 3*12=36 (12 месяцев в году).
• pmt — числовое значение, характеризующее фиксированную сумму платежа за каждый период. Аргумент обязателен. Если оплата за период является неизвестной величиной, аргумент pmt может быть установлен равным 0, но следующий аргумент указывается явно.
• [ps] – текущее значение на данный момент. Например, когда заемщик берет кредит в финансовом учреждении, кредитное учреждение представляет собой текущую стоимость. По умолчанию аргумент [ps] равен 0, а plt должен быть ненулевым.
• [тип] — числовое значение, характеризующее тип платежей: в конце или начале периода. Принимает только два значения: 0 (если не указано явно) и 1.

1. При указании аргумента ставки можно использовать процентный формат данных (например, 17%) и числовой эквивалент (0,17).
2. Дебетовые проводки (текущая стоимость, оплата за период) необходимо вводить со знаком «-», то есть они должны быть отрицательными числами.
3. Функция BS использует следующую формулу в своих вычислениях:
4. Эту функцию можно использовать для расчета остатка на конец периода и оставшейся суммы кредита, непогашенного в настоящее время.
5. Если процентная ставка колеблется с течением времени, для расчета следует использовать формулу BEDAIS.
6. Аргументами функции могут быть числовые значения или текстовые данные, которые можно преобразовать в числа. Если один или несколько аргументов функции FN принимают текстовые строки, не содержащие числовых значений, будет возвращен код ошибки #ЗНАЧ!.

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