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

Для расчета процентной ставки в аннуитетной схеме используется функция СТАВКА() .

Функция СТАВКА(кпер; плт; пс; [бс]; [тип]; [предположение]) возвращает процентную ставку по аннуитету.

Примечание . Английский вариант функции: RATE(nper, pmt, pv, , , ), т.е. Number of Periods – число периодов.

Если постараться решить это уравнение относительно параметра Ставка, то мы получим степенное уравнение (степень уравнения и, соответственно, число его корней будет зависеть от значения Кпер). В отличие от других параметров ПЛТ, БС, ПС и Кпер, найти универсальное решение этого уравнения для всевозможных степеней невозможно, поэтому приходится использовать метод итераций (по сути, ).
Чтобы облегчить поиск Ставки методом итераций, используется аргумент Предположение. Предположение - это приблизительное значение Ставки, т.е. прогноз на основании нашего знания о задаче. Если значение предположения опущено, то оно полагается равным 10 процентам. Значение Предположение также полезно в случае, если имеется несколько решений уравнения – в этом случае находится значение Ставки ближайшее к Предположению .

Задача1 – Выплата кредита

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

В условии задачи содержится следующая информация:

• Заемщик должен сделать 60 равновеликих платежей (12 мес. в году*5 лет), т.е. всего 60 периодов (Кпер);
• Проценты начисляются в конце каждого периода (если не сказано обратное, то подразумевается именно это), т.е. аргумент Тип=0;
• В конце срока задолженность должна быть равна 0 (БС=0).

В результате формула для вычисления годовой ставки будет выглядеть так =12*СТАВКА(12*5;-3000;100000;0;0) или =12*СТАВКА(12*5;-3000;100000)
Знак минус у регулярного платежа показывает, что мы имеем разнонаправленные денежные потоки: +100000 – это деньги, которые банк дал нам, -3000 – это деньги, которые мы возвращаем банку .
Результат вычисления = 26,10%

Формула может вернуть отрицательные значения ставки. Это происходит, когда сумма всех регулярных платежей недостаточна для погашения кредита даже при 0 ставке. Но, в нашем случае все в порядке: 60*(3000)=180000>100000. Отрицательная ставка означает, что банк выплачивает нам проценты за пользование кредитом, что является абсурдом. Это, конечно, ошибка (попробуйте например, в файле примера на Листе Выплата установить платеж =-1000).

Если задать платеж = 0 или того же знака, что и сумма кредита, то функция СТАВКА() вернет ошибку #ЧИСЛО! Это и понятно, при нулевых платежах погасить кредит невозможно.

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

Задача2 – Накопление суммы вклада

Определим, с какой годовой ставкой мы можем накопить 1 000 000 руб., внося ежемесячно по 10 000 руб. в течение 5 лет. (см. файл примера на Лист Накопление )

Формула для вычисления годовой ставки будет выглядеть так =12*СТАВКА(12*5;-10000;0;1000000) =19,38%

Здесь ПС=0, т.е. начальная сумма вклада =0 (). Целевой вклад = 1000000 (БС – ).

Если суммарное количество взносов будет > целевой стоимости (1000000), то ставка станет отрицательной, чтобы соблюсти наше требование БС=1000000.

Если задать величину пополнения = 0 или того же знака, что и целевая сумма, то функция СТАВКА() вернет ошибку #ЧИСЛО! Это и понятно, при нулевых взносах накопить ничего не получится. Взнос того же знака, что и целевая сумма, вероятно, означает, что банк платит нам. Но, это не возможно, т.к. начальная сумма вклада =0, поэтому выдается ошибка.

Функция СТАВКА возвращает процентную ставку по аннуитету за один период.

Описание функции СТАВКА

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

Синтаксис

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

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

Аргументы

кпер плт пс бс тип прогноз

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

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

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

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

Необязательный. Число 0 или 1, обозначающее, когда должна производиться выплата.

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

Если функция СТАВКА не сходится, попробуйте изменить значение аргумента “прогноз”. Функция СТАВКА обычно сходится, если значение этого аргумента находится между 0 и 1.

Замечания

Убедитесь, что единицы измерения, выбранные для аргументов “прогноз” и “колпер” соответствуют друг другу. При ежемесячных выплатах по четырехгодичному займу под 12 процентов годовых используйте значение 12%/12 для аргумента “прогноз” и 4*12 - для аргумента “кол пер”. При ежегодных платежах по тому же займу используйте значение 12% для аргумента “прогноз” и 4 -для аргумента “кол_пер”.​

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

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

Аргументы функции означают:

Номинальная_ставка - это номинальная годовая процентная ставка; Кол_пер -

Пример 2.32. Заем 1 млн. Грн. с номинальной нормой процента 12% будет оплачена в конце 3 лет единой суммой с начисленными процентами. Какая сумма будет уплачена, если проценты начисляются каждые полгода, ежеквартально, ежемесячно, ежедневно.

Решение :

1) по формуле (1.30):

2) используя функцию Excel БС: БС (12% / 2; 2-3;; - 1000) = 1418,52; БС (12% / 4; 4-3;; - 1000) = 1425,76; БС (12% / 12; 12-3;; -1000) = 1430,77; БС (12% / 365; 365-3;; -1000) = 1433,24;

3) используя эффективную процентную ставку - формула (2.17). Будущую стоимость займа можно рассчитать, используя эффективную процентную ставку. Вычислим эффективные ставки в ячейках А1: А4:

А1 = зффект (12% 2) = 0,1236; А2 = зффект (12%, 4) = 0,1255; A3 = зффект (12%, 12) = 0,1268; А4 = зффект (12%; 365) = 0,1275.

В ячейку В1 введем формулу для вычисления будущей стоимости займа

В1 = БС (А1, 3,; -1000) и скопируем ее в В2: В4. Результаты расчетов пе я в ячейках В1: В4 соответственно: 1418,52; 1425,76; 1430,77 и 1433,24.

4) введением математических формул в Excel в строку формул = (1 + 0,12 / 2) л2 - 1;

= (1 + 0,12 / 4) л4 - 1; = (1 + 0,12 / 12) Л12 - 1;

= (1 + 0,12 / 365) Л365 - 1.

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

В Excel номинальную годовую процентную ставку, если известна эффективная процентная ставка и количество периодов в году рассчитывает функция НОМИНАЛ:

Синтаксис НОМИНАЛ (эффективная ставка; кпер). (2.18)

Аргументы функции означают:

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

Значение функции НОМИНАЛ - аргумент i формулы (1.4).

Пример 2.33. Эффективная ставка составляет 28%, начисление процентов ежемесячное. Рассчитать номинальную процентную ставку. Решение: Задачу можно решить несколькими способами: 1) по формуле (1.31)

2) с помощью функции НОМИНАЛ (28%, 12) = 0,2494 или 24,94%.

> Расчеты в Excel по ценным бумагам

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

> Операции с векселями в Excel (учетные процентные ставки)

Функция ПОЛУЧЕНО рассчитывает сумму, полученную в срок вступления в силу ценных бумаг при использовании учетной (дисконтной) ставки (рассчитывает сумму, полученную в срок погашения полностью обеспеченных ценных бумаг):

Синтаксис ПОЛУЧЕНО (дата_согл; датавступлвсилу;

инвестиция; скидка; базис). (2.19)

Аргументы функции означают:

дата_согл

дата_вступл_в_силу -

инвестиция -

скидка - скидка на ценную бумагу;

базис - это используемый способ вычисления срока

Пример 2.34. Учетная ставка 12% годовых. Векселедатель получил 1200 тыс. Грн., Вексель был выдан на 3 календарных месяца. Определите номинал векселя. Способ исчисления срока: фактический / фактический. Считать дату расчета за вексель 1 января 2005

Решение: Задачу можно решить несколькими способами:

1) По формуле (1.33):

2) С помощью функции (2.19):

ПОЛУЧЕНО (05,01,01; 05,04,01; 1200000; 12%; 1) = 1236 589,5 грн.

1200 / (1 - (90/365) * 0,12). Функция СКИДКА вычисляет норму скидки - учетную ставку для ценных бумаг, по которым не предусмотрены периодические выплаты (рассчитывает ставку дисконтирования ценных бумаг):

Синтаксис СКИДКА (дата_согл; Дата_ вступлвсилу; цена; погашение; базис). (2.20)

Аргументы функции означают:

дата_согл - дата расчета за ценные бумаги (позднее, чем дата выпуска, когда ценные бумаги были проданы покупателю)

дата_вступл_в_силу - срок погашения ценных бумаг. Эта дата определяет момент срока действия ценных бумаг;

цена - это цена ценных бумаг;

погашение -

базис -

Пример 2.35. Определите величину учетной ставки, если вексель выдан 1 января 2005 на сумму 870 тыс. Грн. с погашением суммы долга 1 млн. грн. через три месяца (2005 год - не високосный). Способ исчисления срока: фактический / фактический.

Решение: Задачу можно решить несколькими способами:

1) по формуле d = S - - = 1000-870 = 0,5272222 или 52,72%,

Sn 1000 .2 °.

где n = - = - лет.

2) с помощью функции (2.20):

СКИДКА (05,01,01; 05,04,01; 870000; 1000000; 1) = 0,52722222 или

3) введением математической функции в Excel:

= (1000 - 870) / (1000 * 90/365).

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

Функция ЦЕНАСКИДКА определяет цену за 100 грн. номинальной стоимости ценных бумаг, на которые сделана скидка с цены погашения:

Синтаксис ЦЕНАСКИДКА (дата_согл; датавступлвсилу; скидка; погашение; базис). (2.21)

Аргументы функции означают:

дата_согл - дата расчета за ценные бумаги (позднее, чем дата выпуска, когда ценные бумаги были проданы покупателю)

дата_вступл_в_силу - срок погашения ценных бумаг. Эта дата определяет момент срока действия ценных бумаг;

скидка - скидка на ценную бумагу;

погашение - это выкупная стоимость ценных бумаг;

базис - это используемый способ вычисления срока.

Пример 2.36. Вексель номиналом 1 млн. Грн. выдано 1 января 2005 сроком на 3 месяца под учетную ставку 20% годовых. Определите сумму, полученную векселедателем. Способ исчисления срока: фактический / фактический.

Решение: Задачу можно решить несколькими способами:

1) по формуле P = S - (1 - nd ) = 1-Й --90--0,21 = 0,950685 млн. Грн.;

2) С помощью функции (2.21):

ЦЕНАСКИДКА (05,01,01; 05,04,01; 20%; 1000000; 1) = 950 684,9315 грн.;

3) введением математической функции в Excel:

1000000 * (1- (90/365) * 0,2).

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

Функция ИНОРМА рассчитывает годовую ставку дополнительного дохода (наращивание) для ценных бумаг без периодической выплаты процентов по формуле расчета простой процентной ставки (1.2) (рассчитывает процентную ставку для полностью инвестированных ценных бумаг):

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

Аргументы функции означают:

дата_соглашения - дата расчета за ценные бумаги (позднее, чем дата выпуска, когда ценные бумаги были проданы покупателю)

дата_вступл_в_силу - срок погашения ценных бумаг. Эта дата определяет момент срока действия ценных бумаг;

инвестиция - это объем инвестиции в ценные бумаги;

погашение - это выкупная стоимость ценных бумаг;

базис - это используемый способ вычисления срока.

Пример 2.37. Определите годовой уровень дохода по векселю, который был выпущен 1 января 2005 на три месяца, если векселедатель получил 0,85 номинала. Способ исчисления срока: фактический / фактический.

Решение: Задачу можно решить несколькими способами: s - р и - 085

1) по формуле j = - = - , - = 0,7156864 или 71,57%;

P n 0,85 - -

2) с помощью функции (2.22)

ИНОРМА (05,01,01; 05,04,01; 85; 100; 1) = 0,715686275;

3) введением математической функции в Excel:

= (1 - 0,85) / (0,85 * 90/365). Функция ДОХОДСКИДКА рассчитывает годовую ставку дополнительного дохода для ценных бумаг, периодические выплаты процентов по которым не предусмотрены и на которые сделана скидка по формуле расчета простой процентной ставки (1.2):

Синтаксис ДОХОДСКИДКА (дата_согл; датавступлвсилу; цена; погашение; базис). Аргументы функции означают:

дата_согл - дата расчета за ценные бумаги (дата продажи ценных бумаг покупателю, более поздняя, чем дата выпуска)

дата_вступл_в_силу - срок погашения ценных бумаг. Эта дата определяет момент, когда заканчивается срок действия ценных бумаг;

цена - цена ценных бумаг на 100 грн. номинальной стоимости;

погашение - выкупная стоимость ценных бумаг на 100 грн. номинальной стоимости;

базис - это используемый способ вычисления срока.

Пример 2.38. Цена облигации на дату приобретения 1.07.2011 г.. Равна 77,345, дата погашения 31.12.2011 г.. По курсу - 100. Найдите годовую ставку дохода. Способ исчисления срока: фактический / фактический.

Решение

5 _ г. 100 - 77 345

3) по формуле и = - = - "- = 0,5842 или 58,42%;

В этой статье описаны синтаксис формулы и использование функции СТАВКА в Microsoft Excel.

Описание

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

Синтаксис

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

Примечание: Полное описание аргументов "кпер", "плт", "пс", "бс" и "тип" см. в разделе, посвященном функции ПС.

Аргументы функции СТАВКА описаны ниже.

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

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

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

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

Тип Необязательный. Число 0 или 1, обозначающее, когда должна производиться выплата.

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

• Если аргумент "прогноз" опущен, предполагается, что его значение равно 10 %.

  Если функция СТАВКА не сходится, попробуйте изменить значение аргумента "прогноз". Функция СТАВКА обычно сходится, если значение этого аргумента находится между 0 и 1.

Замечания

Убедитесь, что единицы измерения, выбранные для аргументов "прогноз" и "кол_пер" соответствуют друг другу. При ежемесячных выплатах по четырехгодичному займу под 12 процентов годовых используйте значение 12%/12 для аргумента "прогноз" и 4*12 - для аргумента "кол_пер". При ежегодных платежах по тому же займу используйте значение 12% для аргумента "прогноз" и 4 -для аргумента "кол_пер".

Пример

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем - клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.