Функция эффект в excel

Функция эффект в excel

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

НОРМА(кпер, выплата, нз, , , предположение).

НОРМА(кпер, выплата, , бс, тип, предположение).

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

Предположим, что компании X потребуется 100000 тыс. руб. через 2 года. Компания готова вложить 5000 тыс. руб. сразу и по 2500 тыс. руб. каждый последующий месяц. Каким должен быть процент на инвестированные средства, чтобы no-1 лучить необходимую сумму в конце второго года?

В этой задаче сумма 100000 тыс. руб. (аргумент бс функции НОРМА) формируется за счет приведения к будущему моменту, начального вклада размером 5000 тыс. руб. и фиксированных ежемесячных выплат. Поэтому среди аргументов функции НОР­МА следует указать оба аргумента: выплата = -2500, нз = -5000. Общее число периодов начисления процентов определяем: кпер = 2 * 12. Подставив эти числа, получаем

НОРМА(24, -2500, -5000, 100000) = 3.28%.

Годовая процентная ставка составит 3.28%* 12 = 39.36%. Процент на вложенные средства должен быть не меньше этой величины.

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

Будущее значение ежемесячных выплат по 250 тыс. руб. должно составить через 4 года сумму займа с процентами, т.е. заем должен быть полностью погашен. Текущая стоимость за­йма по условию равна 7000 тыс. руб. (аргумент нз = 7000). По займу начисляется процент в течение 4* 12 периодов (аргумент кпер). При этих условиях ежемесячная ставка процента долж­на составлять

НОРМА(48, -250, 7000) = 2.46%.

Годовая процентная ставка составит 2.46% * 12 = 29.5%.

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

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

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

Синтаксис ЭФФЕКТ(номинальная_ставка, кол^пер).

Рассмотрим заем в 1000 руб. с номинальной нор­мой процента 12% и сроком уплаты 3 года. Пусть весь заем и начисленные на него проценты будут выплачены единой сум­мой в конце этого срока. Какая сумма будет выплачена, если начисление процентов: а) полугодовое; б) квартальное; в) ме­сячное; г) ежедневное.

Задачу можно решить несколькими способами. В EXCEL существует функция БЗ, позволяющая провести следующий рас­чет в соответствии с данными габлицы3.3 и формулой (3.3):

а) БЗ(12%/2,2-3„-1000)= 1418,52,

б) БЗ(12%/4,4-3„-1000) = 1425,376,

в) БЗ(12%/12,12-3„-1000) – 1430,77,

г) БЗ(12%/365,365-3„-1000) = 1433,24.

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

а) А1 — ЭФФЕКТ(12%,2) — 0.1236;

б) А2 = ЭФФЕКТ(12%,4) = 0.1255;

в) A3 = ЭФФЕКТ(12%,12) — 0.1268; ,-) А4 — ЭФФЕКТ(12%,365) = 0.1275.

В ячейку В1 введем формулу для вычисления будущей стоимости займа BI = БЗ(А1Д,-1000) и скопируем ее в В2:В4. Результаты расчетов для вариантов а) — г) находятся в ячейках В1:В4 соответственно: 1418.52, 1425.76, 1430.77, 1433.24.

Не нашли то, что искали? Воспользуйтесь поиском:

Лучшие изречения: Учись учиться, не учась! 10994 — | 8204 — или читать все.

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

Читайте также:  Сабвей серф обновление 2018

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

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

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

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

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

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

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

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

ДОХОД

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

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

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

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

ПРПЛТ

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

Читайте также:  Как выкрутить обломанную свечу зажигания из гбц

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

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

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

СТАВКА

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

ЭФФЕКТ

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

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

Отблагодарите автора, поделитесь статьей в социальных сетях.

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

Примеры использования функции ЭФФЕКТ в Excel

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

Исходная таблица данных:

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

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

  • B4 – полученное выше числовое значение номинальной ставки;
  • B2 – число периодов погашения.

Полученное значение 0,235 соответствует 23,5% (значению эффективной ставки по условию). Расчет номинальной ставки также можно производить с помощью функции НОМИНАЛ.

Формула расчета процентов по вкладу в Excel

Пример 2. Вкладчику предложили сделать депозит в банк под 16% годовых (номинальная ставка), при этом расчете производится с использованием сложных процентов (эффективная ставка). По условиям договора вкладчик сможет снять только полученные проценты. Определить сумму к получению, если размер депозита – 1 млн. рублей, капитализация – ежемесячная.

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

  • B2 – число периодов капитализации;
  • B3 – номинальная ставка;
  • B4 – сумма вклада.

Для сравнения, доход от вклада при использовании простых процентов составил бы 1000000*0,16=160000 рублей, поэтому для вкладчика выгодно использовать предложенный вариант со сложными процентами.

Читайте также:  Андроид не работает будильник

Как посчитать проценты на депозит в Excel для выбора вклада

Пример 3. Два банка предлагают сделать депозитный вклад на одинаковую сумму (250000 рублей) на 1 год при следующих условиях:

  1. Номинальная ставка – 24%, простые проценты, 12 периодов капитализации.
  2. Номинальная ставка 22%, сложные проценты, начисляемые по итогам каждого периода, 4 периода капитализации.

Определить выгодный вариант, отобразить схему выплат.

В первом случае таблица выплат выглядит так:

Проценты – постоянная величина, рассчитываемая по формуле:

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

  • $B$2 – начальная сумма вклада;
  • $B$3 – годовая ставка;
  • $B$4 – число периодов капитализации вклада.

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

Таблица начисления процентов по условиям второго банка:

В данном случае проценты не являются фиксированной величиной и зависят от итоговой суммы накоплений за предыдущий период (поэтому ссылка на ячейку L2 – абсолютная):

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

Для быстрого расчета итоговой суммы используем формулы:

  1. Первый банк:
  2. Второй банк:

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

Особенности использования функции ЭФФЕКТ в Excel

Функция имеет следующий синтаксис:

  • номинальная_ставка – обязательный аргумент, характеризующий числовое (десятичная дробь) или процентное значение номинальной годовой ставки;
  • кол_пер – обязательный аргумент, характеризующий числовое значения числа периодов за год, на протяжении которых начисляются сложные проценты.
  1. Аргумент кол_пер может принимать дробные числа, значения которых будут усечены до целого числа (в отличие от операции округления, при усечении отбрасывается дробная часть).
  2. Каждый из двух аргументов функции ЭФФЕКТ должен быть представлен числовым (или процентным для аргумента номинальная_ставка) значением либо текстовой строкой, которая может быть преобразована в число. При вводе не преобразуемых к числовым значениям текстовых строк и имен, а также данных логического типа функция ЭФФЕКТ будет возвращать код ошибки #ЗНАЧ!.
  3. Аргумент номинальная_ставка принимает значения из диапазона положительных чисел, а кол_пер – из диапазона от 1 до +∞. Если данные условия не выполняются, например, функции =ЭФФЕКТ(0;12) или =ЭФФЕКТ(12%;0) вернут код ошибки #ЧИСЛО!.
  4. Функция ЭФФЕКТ использует для расчетов формулу, которая может быть записана в Excel в виде: =СТЕПЕНЬ(1+(A1/A2);A2)-1, где:
  • A1 – номинальная годовая ставка;
  • A2 – число периодов, в которые происходит начисление сложных процентов.
  • Для понимания термина «сложные проценты» рассмотрим пример. Владелец капитала предоставляет денежные средства в долг и планирует получить прибыль, величина которой зависит от следующих факторов: сумма средств, которая предоставляется в долг; длительность периода кредитования (использования предоставленных средств); начисляемые проценты за использование.
  • Проценты могут начисляться различными способами: базовая сумма остается неизменной (простые проценты) и база изменяется при наступлении каждого последующего периода выплат (сложные). При использовании сложных процентов сумма задолженности (прибыли) увеличивается быстрее при одинаковых сумме и периоде кредитования, в сравнении с применением простых процентов (особенно, если периодов начисления процентов (капитализации) достаточно много.
  • Для получения результата в формате процентов необходимо установить соответствующий формат данных в ячейке, в которой будет введена функция ЭФФЕКТ.
Ссылка на основную публикацию
Функция плотности распределения пуассона
На этой странице мы собрали примеры решения учебных задач, где используется распределение Пуассона. Краткая теория Рассмотрим некоторый поток событий, в...
Фоллаут 76 официальный сайт на русском
Игра Fallout 76 Модификация силовой брони и оружия в честь 300-летия США Голова Волт-Боя Патриотический костюм американца Праздничное приветствие Волт-Боя...
Фольксваген тигуан 2 литра механика
Все минусы Фольксваген Тигуан 2018-2019 ➖ Качество отделочных материалов ➖ Расход топлива Плюсы ➕ Динамика ➕ Управляемость ➕ Удобный салон...
Функция распределения случайного времени безотказной работы радиоаппаратуры
На странице Непрерывная случайная величина мы разобрали примеры решений для произвольно заданных законов распределения (многочлены, логарифмы и т.п.). Здесь же...
Adblock detector