Взаимосвязь между параметрами Кпер, Ставка, Плт

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

 

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

Пример 8.1. Найти современное значение долга, полная сумма которого через три года составит 700 тыс.руб. Проценты начисляются по следующим ставкам: 1) 14% в конце каждого года; 2) 2% в конце каждого квартала.

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

1) В первом случае процент начисляется один раз в год, процентная ставка задана также годовая, поэтому параметры Ставка и Кпер финансовой функции ПС, предназначенной для расчета приведенной суммы (современного значения долга) применяем без преобразования. Результаты применения функции ПС приведены на рисунке 52.

Рис. 52. Применение финансовой функции ПС для 1)

Как видно из рисунка, современное значение долга или размер ссуды равен 472 480,06 руб. или 472 480 руб. 06 коп.

2) Во втором случае процентная ставка задана в кварталах и начисления также производятся поквартально, остается параметр Кпер также представить в кварталах. В функции ПС параметр Кпер умножим на 4 (количество кварталов в году), результат вычислений размещен на рисунке 53.

Рис. 53. Применение финансовой функции ПС для 2)

Пример 8.2. Найти срок ренты постнумерандо, если ее наращенная стоимость 2000 у.д.е. i = 0,15, R = 100.

Решение. Для нахождения срока финансовой операции используем финансовую функцию КПЕР при заданных значениях ренты, наращенной стоимости и процентной ставки. Результат применения функции приведен на рисунке 54 и составляет 9,919 или приблизительно 10 лет.

Рис. 54. Применение финансовой функции КПЕР

Пример 8.3. Пусть за кредит в размере 100 тыс. руб., выданный на полгода, взимается плата 8 тыс. руб. Каковы в этом случае полугодовая процентная ставка, наращенная сумма, коэффициент наращения?

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

Рассчитаем для начала наращенную сумму, общий вид формулы представлен на рисунке 55.

Для расчета годовой процентной ставки в качестве аргументов используем: Кпер – 1 полугодие, Пс – размер кредита 100000, Бс – только что вычисленная наращенная сумма 108000. Не забудем поставить знак "минус" перед одной из сумм, в зависимости от точки зрения на финансовую операцию (со стороны банка или со стороны клиента). Формула в ячейке В6 примет вид: =СТАВКА(1;;-B2;B5).

Рис. 55. Применение финансовых функций БС и СТАВКА

Коэффициент наращения вычислим простым делением наращенной суммы на размет кредита, т.е. в ячейку В7 введем формулу: =B5/B2.

Пример 8.4. Вклад в размере 30 тыс. руб. внесен на два года по ставке 8% годовых. Найти обеспеченные этим вкладом выплаты, равные по величине, которые производятся в конце каждого полугодия.

Решение. Полугодовые выплаты по вкладу в течение двух лет представляют собой ренту постнумерандо, которая рассчитывается с помощью встроенной функции ПЛТ. Параметры функции Ставка и Кпер необходимо привести к единым единицам измерений, т.е. представить срок в кварталах и процентную ставку за квартал. Современная стоимость этой ренты S (0) = 30000 руб. Внешний вид формулы имеет вид: =ПЛТ(B4/2;B3*2;-B2), а результат применения функции ПЛТ представлен на рисунке 56.

Рис. 56. Применение финансовой функции ПЛТ

 

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

· НОМИНАЛ(факт_ставка; кол_пер) – возвращает номинальную годовую процентную ставку;

· ЭФФЕКТ(номинальная_ставка; кол_пер) – возвращает фактическую (эффективную) годовую процентную ставку;

· ПРОЦПЛАТ(ставка; период; кпер; пс) – вычисляет проценты, выплачиваемые за определенный инвестиционный период;

· ПРПЛТ(ставка; период; кпер; пс; бс; тип) – возвращает сумму платежей процентов по инвестиции за данный период на основе постоянства сумм периодических платежей и постоянства процентной ставки;

· ОСПЛТ(ставка; период; кпер; пс; бс; тип) – возвращает величину платежа в погашение основной суммы по инвестиции за данный период на основе постоянства периодических платежей и постоянства процентной ставки;

· БЗРАРПИС(первичное; план) – возвращает будущее значение первоначальной основной суммы после применения ряда (плана) ставок сложных процентов;

· ДОХОД(дата_согл; дата_вступл_в_силу; ставка; цена; …) – возвращает доходность ценных бумаг, по которым выплачивается периодический процент.

Рассмотрим использование функций ЭФФЕКТ, ПРПЛТ, БЗРАРПИС.

Пример 8.5. Банк начисляет сложные проценты по номинальной ставке 12%. Найти эффективную годовую ставку при капитализации процентов 1) ежедневно, K =365; 2) ежемесячно.

Решение. Применение встроенной финансовой функции ЭФФЕКТ весьма тривиально, функция имеет всего два параметра: номинальная ставка и количество периодов в году, за которые начисляются сложные проценты. Формула для ежедневного начисления и результаты применения функции представлены на рисунке 57. Формула для ежемесячного начисления имеет вид: =ЭФФЕКТ(C2;12).

 

Рис. 57. Применение финансовой функции ЭФФЕКТ

Пример 8.6. Составить план погашения кредита на сумму 30 тыс. руб. выданного на 6 месяцев на условиях одинаковых месячных выплат основного долга под 18% годовых.

Решение. Здесь мы заменим формулу расчета, которую, так или иначе, нужно помнить, на встроенную функцию ПРПЛТ для расчета процентов за текущий период.

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

Пример 8.7. Банк выдал кредит на 5 лет в сумме 6 млн.руб. Процентная ставка по кредиту для первого года – 10,5% годовых, для второго года – на 1,5% больше, для третьего и последующих годов надбавка к процентной ставке соответствует 0,75%. Определить сумму долга, подлежащую погашению и коэффициент наращения за 5 лет.

Решение. Функция БЗРАРПИС в качестве параметра требует массив применяемых процентных ставок, поэтому в нашей компьютерной модели необходимо ввести этот массив, где для трех последних месяцев процентная ставка будет одинаковой. Решение представлено на рисунке 59.

Рис. 58. Применение финансовой функции ПРПЛТ

Рис. 59. Применение финансовой функции БЗРАРПИС

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

Решение. Компьютерная модель задачи представлена на рисунке 35, где в ячейки В3 и В4 введены формулы для расчета номинальной ставки с ежемесячным начислением процентов и номинальной ставки с ежеквартальным начислением процентов (рис. 60), =НОМИНАЛ(B2;12) и =НОМИНАЛ(B2;4) соответственно. В качестве параметра Факт_ставка в обоих случаях используется одна и та же ячейка В2, в которую изначально помещаем значение процента, близкое к искомому, например, 9%.

Для расчета номинальной ставки с ежемесячным начислением процента воспользуемся функцией Подбор параметра, которая была рассмотрена в предыдущем параграфе. При вызове функции Подбор параметра заполним окно функции следующим образом: Установить в ячейке В3 Значение 0,09, Изменяя значение ячейки В2. В ячейке В3 установится значение 9%, а значение в ячейке В4 пересчитается автоматически и установится равным 9,07%, что соответствует ответу примера. Ячейка В2 играет в решении задачи вспомогательную роль.

Рис. 60. Применение финансовой функции НОМИНАЛ и Подбора параметра

Операции с ценными бумагами

Для расчета операций с ценными бумагами (векселями) в электронной таблице используются функции ЦЕНАСКИДКА, ИНОРМА, ДОХОДПОГАШ, СКИДКА. Функция ЦЕНАСКИДКА рассчитывает цену за 100 руб. нарицательной стоимости векселя, ее синтаксис имеет вид:

ЦЕНАСКИДКА (Дата_соглашения; Дата_вст.в_силу; Скидка; Погашение; Базис), где Дата_соглашения – дата продажи покупателю, дата учета (в числовом формате); Дата_вст.в_силу – дата погашения (в числовом формате); Скидка – норма скидки для ценных бумаг (годовая номинальная учетная ставка); Погашение – цена при погашении (за 100 руб. нарицательной стоимости ценных бумаг); Базис – тип используемого способа вычисления дня (1 – английская практика (365/365), 2 – французская практика (365/360), 3 – германская практика (360/360)). Для базиса в числителе указывается точное (365) или приближенное (360) фактическое число дней сделки, в знаменателе – точное или приближенное число дней в году.

Функция СКИДКА (Дата_соглашения; Дата_вст.в_силу; Цена; Погашение; Базис) возвращает ставку дисконтирования для ценных бумаг.

Построим компьютерные модели с применением встроенных функций ЦЕНАСКИДКА и СКИДКА для задач учета векселей, а именно для нахождения стоимости и ставки дисконтирования для ценных бумаг.

Пример 8.9. Банк учел 10.02 три одинаковых векселя суммарной стоимостью 240000 руб. со сроками погашения: 15.04, 10.05 и 05.06. Какова номинальная стоимость каждого векселя, если процентная ставка составляет 5% годовых? (К=360).

Решение. Математическая модель задачи имеет вид: S=(S₁(n₀)+S₂(n₀)+S₃(n₀))/(1-d*(n₁-n₀)/360+1-d*(n₂-n₀)/360+1-d*(n₃-n₀)/360), где S = S(n₁) = S(n₂) = S(n₃) – номинальная стоимость каждого векселя, S₁,₂,₃(n₀) – учетная стоимость 1-го, 2-го и 3-го векселя соответственно, при этом d – учетная ставка, n₀ – дата учета векселей, n₁, n₂, n₃ – даты погашения векселей; S₁(n₀)+S₂(n₀)+S₃(n₀) = 240000. При подстановке других исходных данных в формулу получим результат: 240000/(1 – 0,05*(15.04.2017 – 10.02.2017)/360 + 1 – 0,05*(10.05.2017 – 10.02.2017)/360 + 1 – 0,05*(05.06.2017 – 10.02.2017)/360) = 240000/(1 – 0,05*0,177778 + 1 – 0,05*0,247222 +1 – 0,05*0,319444) = 240000/2,96 = 81005,06.

Построим модель задачи в Excel. Введем исходные данные (процентную ставку, четыре даты, примерную стоимость 1-го векселя (определяем отношением 240000/3=80000)) в ячейки С2, С3, С5:С8 (рис. 61).

 

Рис. 61. Компьютерная модель задачи 8.9

 

Для нахождения стоимости каждого векселя используем встроенную функцию ЦЕНАСКИДКА, которая, как было сказано выше, возвращает цену за 100 руб. нарицательной стоимости ценных бумаг, поэтому в ячейке С4 найдем эту цену делением 80000 на 100 (=С3/100). В ячейки D6:D8 введем формулы для расчета цен векселей при досрочной продаже (в ячейке D6 формула примет вид: =ЦЕНАСКИДКА($C$5;C6;$C$2;$C$4;2)*100, копируем ее в ячейки D7, D8). В ячейке D9 находим сумму трех векселей: =СУММ(D6:D8), и пока она равна 237022,2222. Далее нам необходимо вычислить, сколько будет стоить каждый из трех одинаковых векселей при учете, чтобы сумма векселей составила 240000 руб. Для выполнения этой задачи воспользуемся средством анализа «Что-Если / Подбор параметра». В диалоговом окне в строку: Установить в ячейке внесем адрес D9, в строку Значение – 240000, в строку Изменяя значение ячейки – адрес C3 (рис. 62). MS Excel сообщит, что решение найдено, и после нажатия клавиши Enter, оно отобразится на листе. Таким образом, каждый вексель должен стоить по 81005,06, чтобы при досрочном погашении этих векселей их суммарная стоимость составила 240000 руб.

Рис. 62. Диалоговое окно средства анализа «Подбор параметра»

 

Пример 8.10. Владелец векселя приобрел вексель 01.01.2017 г. со сроком погашения 5 месяцев. Спустя два месяца с момента получения векселя продает его банку. Банк выкупает (учитывает) вексель, но не за полную стоимость 100 тыс. руб., а за 94 тыс. руб. Найти годовую учетную ставку и выкупную стоимость векселя за месяц до погашения по схеме простых процентов.

Решение. Математическая модель задачи выглядит следующим образом.

Учетную стоимость векселя по схеме простых процентов при его досрочной продаже вычисляют по формуле S(t) = S(T)∙(1 – (T – t)∙d), где S(t) – учетная стоимость векселя в момент t (годы) от начала действия векселя или за срок (T – t) до момента его погашения, здесь 0 ≤ t ≤ T; S(T) – полная стоимость векселя в момент T (годы) его погашения; d – простая годовая учетная ставка. Таким образом, d = (S(T) – S(T – t))/S(T)*(T – t).

Если подставить данные задачи в формулу, то годовая учетная ставка будет равна: d = (100 – 94)/100*(1/4) = 0,24 = 24%. Здесь срок (T – t) до момента погашения векселя равно 3 месяца, т.е. 1/4 года.

Найдем стоимость векселя за месяц до его погашения: S(t) = 100 * (1 – 0,24*1/12) = 98 тыс. руб. Здесь срок (T – t) до момента погашения векселя равно 1 месяцу, т.е. 1/12 года.

Построим компьютерную модель задачи. На лист Excel введем известные исходные данные (даты погашения и учета векселя, полную и выкупную (учетную) стоимость векселя) (рис. 63).

Для нахождения годовой учетной ставки используем встроенную функцию СКИДКА. Формула будет иметь вид: = СКИДКА(B5;B7;B2;B1). Для нахождения стоимости векселя за месяц до погашения используем уже рассмотренную в первом примере функцию ЦЕНАСКИДКА, где в качестве учетной ставки используем значение, полученное нами в ячейке В8.

 

Рис. 63. Компьютерная модель задачи 8.10

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

Задания для самостоятельного выполнения

Задание 8.1. Ссуда в размере 10000 руб. выдана сроком на полгода под 12% годовых. Найти полную сумму долга, если проценты начисляются ежемесячно. (БС)

Задание 8.2. Какую сумму надо положить в банк, выплачивающий ежеквартально 16% годовых, чтобы через полгода получить 15000 руб.? (ПС)

Задание 8.3. Отчисления в пенсионный фонд составляют 200 руб. ежемесячно. За какой период на пенсионном счете накопится 10000 руб., если номинальная процентная ставка составляет 9,8% при ежемесячном начислении? (КПЕР)

Задание 8.4. Определить ежемесячные выплаты по займу в 10 млн. руб., взятому на 7 месяцев под 9% годовых. (ПЛТ)

Задание 8.5. Ссуда в размере 100000 руб. выдана сроком на два года под 16% годовых. Найти полную сумму долга, если проценты начисляются один раз в квартал. (БС)

 

Задание 8.6. Какую сумму надо положить в банк, выплачивающий ежеквартально 15% годовых, чтобы через три года получить 120000 руб.? (ПС)

 

Задание 8.7. Отчисления в пенсионный фонд составляют 400 руб. ежемесячно. За какой период на пенсионном счете накопится 20000 руб., если номинальная процентная ставка составляет 8,8% при ежеквартальном начислении? (КПЕР)

 

Задание 8.8. Определить ежемесячные выплаты по займу в 1 млн. руб., взятому на 2 года под 9% годовых (ПЛТ)

 

Задание 8.9. Для получения через 2 года суммы в 1 млн. руб. предприятие готово вложить 250 тыс. руб. сразу и затем каждый месяц по 25 тыс. руб. Определить годовую процентную ставку.

 

Задание 8.10. Клиент заключает с банком договор о выплате ему в течение 5 лет ежегодной ренты в размере 5 тыс. руб. в конце каждого года. Какую сумму необходимо внести клиенту в начале первого года, чтобы обеспечить эту ренту, исходя из годовой процентной ставки 20%? (ПС)

 

СПИСОК ЛИТЕРАТУРЫ И ССЫЛКИ НА РЕСУРСЫ ИНТЕРНЕТ

Литература

1. Алексенко Н.В., Бурмистрова Н.А., Мещерякова Н.А. Финансовые вычисления. Математическое и компьютерное моделирование: учебное пособие. – Омск: ООО «Образование информ», 2016. – 132 с.

2. Бурмистрова, Н.А. Сборник прикладных математических задач для студентов экономических вузов: учебное пособие / Н.А. Бурмистрова. – Омск: Издат. дом «Наука», 2011. – 140 с.

3. Каракулев Ю.А., Иванов А.Н. Руководство к решению задач с применением электронных таблиц EXCEL: Учебное пособие.- СПб. СПб ГУ ИТМО, 2010. – 48 с.

4. Лебедев В.М., Мещерякова Н.А., Распутин А.П., Федоров С.В. Финансовый анализ и поиск решения в MS EXCEL: Методические указания. – Омск: Полиграфический центр КАН, 2008. – 76 с.

5. Лоран, П. Ж. Аппроксимация и оптимизация. – М.: Мир, 1975. – с. 496.

6. Магомедов Р.М., Ниматулаев М. Сборник упражнений и заданий по Excel. Методические указания и задания к изучению дисциплины “Информатика”. Учебное издание для студентов всех специальностей всех форм обучения. М.: Финансовая академия при Правительстве Российской Федерации, кафедра «Информационные технологии», 2009. – 70 с.

7. Мамонтова Е.А. Практикум по экономической информатике. Учеб. Пособие. Часть 1. / Под ред. Шуремова Е.Л., Тимаковой Н.А., Мамонтовой Е.А. М.: Перспектива. 2000, 300 с.

8. Мельников П.П. Компьютерные технологии в экономической науке и практике./ Практикум: Учебное пособие для студентов магистратуры, обучающихся по направлению 080100.68 Экономика. – М.: Финансовая академия при Правительстве РФ, 2007. – 177с.

9. Уокенбах, Джон. Подробное руководство по созданию формул в Excel 2003 / Пер. с англ. – М.: Издательский дом «Вильямс», 2005. – 640 с.

 

Интернет ресурсы

10. Блау, С.Л. Инвестиционный анализ. Учебник [Электронный ресурс] / С.Л. Блау. – М.:Дашков и Ко, 2014. – 256 с. – ISBN 978-5-394-02333-0. – URL: http://biblioclub.ru/index/php?page=book&id=230035

11. Капитоненко, В.В. Задачи и тесты по финансовой математике. Учебное пособие [Электронный ресурс] / В.В. Капитоненко. – 2-е изд., перераб. и доп. – М.:Финансы и статистика, 2011. – 386 с. – ISBN 978-5-279-03476-5. – URL: http://biblioclub.ru/index/php?page=book&id=85915

12. Кузнецов, Б.Т. Математические методы финансового анализа. Учебное пособие [Электронный ресурс] / Б.Т. Кузнецов. – М.:Юнити-Дана, 2012. – 160 с. – ISBN 5-238-00977-1.

 – URL: http://biblioclub.ru/index/php?page=book&id=114720

13. Аппроксимация и интерполяция данных. Основные определения – https://studopedia.ru/15_9577_approksimatsiya-i-interpolyatsiya-dannih-osnovnie-opredeleniya.html

14. Глобальный экстремум – http://lomonosovclub.com/ru/study_lists/123#!/articles/584