Решение финансово-экономических задач средствами excel

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

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

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

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

Для расчетов указанных величин используются функции – НОМИНАЛ (эффективная_ставка; кол_пер) и ЭФФЕКТ (номинальная_ставка; кол_пер).

Задача 1.

Постановка задачи.

Определить эффективную процентную ставку, если номинальная ставка составляет 9%, а проценты начисляются:

а) раз в полгода;

б) поквартально;

в) ежемесячно.

Алгоритм решения задачи.

Для определения эффективной процентной ставки воспользуемся функцией ЭФФЕКТ. Непосредственный ввод аргументов дает следующие значения:

а) = ЭФФЕКТ (9%; 2) = 9,2%,	в) = ЭФФЕКТ (9%; 12) = 9,38%.
б) = ЭФФЕКТ (9%; 4) = 9,31%

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

      (4.13),

где Кол_пер – количество периодов в году, за которые начисляются сложные проценты.

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

Иллюстрация решения с помощью панели функции приведена на рис. 4.15.

Рис. 4.15. Фрагмент окна при использовании функции ЭФФЕКТ

Примечания.

1. Если Номинальная_ставка ≤ 0 или если Кол_пер < 1, то функция ЭФФЕКТ возвращает значение ошибки #ЧИСЛО!

2. Если функция недоступна  или возвращает ошибку #ИМЯ?, следует загрузить надстройку «Пакет анализа».

Задача 2.

Постановка задачи.

Известно, что эффективная ставка составляет 16%, начисления производятся ежемесячно.

Определить номинальную ставку.

 

Алгоритм решения задачи.

Для определения номинальной годовой процентной ставки воспользуемся функцией НОМИНАЛ:

= НОМИНАЛ (16%; 12) = 14,93%.

Значение функции НОМИНАЛ – это аргумент Номинальная_ставка  в формуле (4.13).

Задания для самостоятельной работы

1. Определить эффективную  ставку, если номинальная ставка 10% и начисления процентов осуществляются:

а) 5000 раз в год;

б) ежедневно.

2. Эффективная ставка составляет 12%. Проценты начисляются ежеквартально. Определить номинальную ставку.

3.  Ставка банка по  срочным валютным депозитам составляет 20% годовых. Какова реальная доходность  вклада, если проценты выплачиваются:

а) ежемесячно;

б) раз в год.

Расчет периодических платежей, связанных с погашением займов

Среди финансовых функций Excel выделяются функции, связанные с периодическими выплатами:

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

ПРПЛТ (ставка; период; кпер; пс; бс; тип)

ОБЩПЛАТ (ставка; кол_пер; нз; нач_период; кон_период; тип)

ОСПЛТ (ставка; период; кпер; пс; бс; тип)

ОБЩДОХОД (ставка; кол_пер; нз; нач_период; кон_период; тип)

Задача 1.

Постановка задачи.

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

Какой должна быть эта сумма?

Алгоритм решения задачи.

Для определения ежемесячных выплат применяется функция ПЛТ с аргументами: Ставка = 9%/12 (ставка процента за месяц); Кпер = 2*12 = 24 (общее число месяцев начисления процентов); Бс = 200 (будущая стоимость вклада); Тип = 1, так как вклады пренумерандо.

Тогда величина ежемесячных выплат равна:

= ПЛТ (9%/12; 24; ; 200; 1) = - 7,58 тыс. руб.

Результат со знаком «минус», так как 7,58 тыс. руб. клиент ежемесячно вносит в банк.

Иллюстрация решения задачи приведена на рис. 4.16.

Рис. 4.16. Иллюстрация применения функции ПЛТ

Выплаты, определяемые функцией ПЛТ, включают основные платежи и платежи по процентам. Расчет выполняется по формуле, определяемой из (4.2):

   (4.14)

Расчет задачи по формуле (4.12) дает тот же результат:

Задача 2.

Постановка задачи.

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

Алгоритм решения задачи.

Для определения ежемесячных платежей клиента воспользуемся функцией ПЛТ, а также выполним расчет по формуле (4.14):

= ПЛТ (6%/12; 6; -5000) = 847,98 руб.

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

Задача 3.

Постановка задачи.

Определить платежи по процентам за первый месяц от трехгодичного займа в 100 000 руб. из расчета 10% годовых.

Алгоритм решения задачи.

Для определения платежа по процентам за первый месяц заданного периода применим функцию ПРПЛТ со следующими аргументами: Ставка = 10%/12 (процентная ставка за месяц); Период = 1 (месяц); Кпер = 3*12 = 36 (месяцев), Пс  = 100 000 (величина займа). Тогда платежи по процентам за первый месяц составят:

= ПРПЛТ (10%/12; 1; 36; 100000) = - 833,33 руб.

Знак «минус» означает, что платеж по процентам необходимо внести.

Иллюстрация решения задачи приведена на рис. 4.17.

Рис. 4.17. Фрагмент окна с использованием функции ПРПЛТ

Задача 4.

Постановка задачи.

Клиент ежегодно в течение 5 лет вносил деньги на свой счет в банке и накопил 40 000 руб.

Определить, какой доход получил клиент банка за последний год, если годовая ставка составила 13,5%.

Алгоритм решения задачи.

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

Для расчета воспользуемся функцией ПРПЛТ:

= ПРПЛТ(13,5%; 5; 5; ; 40000) = 4030,77 руб.

Заметим, что при решении данной задачи значения аргументов функции ПРПЛТ Бс и Тип не указываются (считаются равными 0).

Задача 5.

Постановка задачи.

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

Алгоритм решения задачи.

Сумма основного платежа по займу вычисляется с помощью функции ОСПЛТ:

= ОСПЛТ (12%/12; 1; 24; 60000) = - -2 224,41руб.

Иллюстрация решения показана на рис. 4.18.

Рис. 4.18. Фрагмент окна с использованием функции ОСПЛТ

 Знак «минус» в результате  означает, что сумму основного  долга по займу необходимо  внести.

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

Например, для ранее приведенной задачи 2 ежемесячная выплата клиента составляет:

= ПЛТ (6%/12; 6; -5000) = 847,98 руб.

Размер основного платежа:

= ОСПЛТ (6%/12; 1; 6; -5000) = 822,98 руб.

Размер платежа по процентам:

= ПРПЛТ (6%/12; 1; 6; -5000) = 25,00 руб.

Задача 6.

Постановка задачи.

Организация взяла ссуду в банке в размере 500 тыс. руб. на 10 лет под 10,5% годовых; проценты начисляются ежемесячно.

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

Алгоритм решения задачи.

Для вычисления суммы платежей по процентам за требуемые периоды воспользуемся функцией ОБЩПЛАТ (рис. 4.19).

Аргументы функции: Кол_пер = 10*12 = 120 месяцев (общее число выплат); Ставка = 10,5%/12 (процентная ставка за месяц); Нз = 500000 (заем); Тип = 0; для выплаты процентов за 1-й месяц Нач_период = 1 и Кон_период = 1, для выплаты процентов за 3-й год Нач_период = 25 и Кон_период = 36.

Выплата за первый месяц составит:

= ОБЩПЛАТ(10,5%/12; 120; 500; 1; 1; 0) = - 4,375 тыс. руб.

Сумма выплат по процентам за третий год периода составит:

= ОБЩПЛАТ (10,5%/12; 120; 500; 25; 36; 0) = - 44,143 тыс. руб.

Рис. 4.19. Фрагмент окна с использованием функции ОБЩПЛАТ

Задача 7.

Постановка задачи.

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

Алгоритм решения задачи.

Предположим, что ссуда погашается равными платежами в конце каждого расчетного периода. Тогда для расчета суммы выплаты задолженности за второй год применим функцию ОБЩДОХОД. Аргументы функции: Кол_пер = 3*4 = 12 кварталов (общее число расчетных периодов); Ставка = 13%/4 (процентная ставка за расчетный период – квартал); Нз = 1000000; Нач_период = 5 и Кон_период = 8 (второй год платежа по ссуде – это период с 5 по 8 квартал); Тип = 0.

= ОБЩДОХОД (13%/4; 12; 1000000; 5; 8; 0) = - 331522,23 руб.

Иллюстрация решения задачи представлена на рис. 4.20.

Рис. 4.20. Фрагмент окна с использованием функции ОБЩДОХОД

Задача 8.

Постановка задачи.

Банком выдан кредит в 500 тыс. руб. под 10% годовых сроком на 3 года. Кредит должен быть погашен равными долями, выплачиваемыми в конце каждого года. Разработать план погашения кредита, представив его в виде следующей таблицы:

Номер периода

Баланс на конец периода

Основной долг

Проценты

Накопленный долг

Накопленный процент

 

Алгоритм решения задачи.

Введем исходные данные задачи в ячейки электронной таблицы и определим структуру таблицы плана погашения кредита. Расчет числовых значений выполним с помощью  функций Excel. Для получения возможности автозаполнения (копирования) формул, введенных для первого периода плана, на другие периоды, укажем абсолютные ссылки на исходные данные. Иллюстрация решения задачи с указанием примечаний со значениями формул вычислений для 3-го периода приведена на рис. 4.21.

Рис. 4.21. Фрагмент окна с таблицей плана погашения кредита

Приведем также формулы с непосредственным заданием значений аргументов при вычислении плановых данных для 1-го периода:

• размер ежегодного платежа:  = ПЛТ (0,1; 3; -500000) = 201 057,40 руб.;
• основной долг: =ОСПЛТ (0,1;1;3;-500000) = 151 057,40 руб.;
• проценты: =ПРПЛТ (0,1; 1; 3; -500000) = ;50 000 руб.;
• накопленный долг:   =-ОБЩДОХОД (0,1; 3; 500000; 1; 1; 0) = 151 057,40 руб.;
• накопленный процент:  =-ОБЩПЛАТ (0,1; 3; 500000; 1; 1; 0) = 50 000 руб.;
• баланс на конец периода: = Кредит – Накопленный долг = 348 942,60 руб.

 

 

Задача 9.

Постановка задачи.

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

Алгоритм решения задачи.

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

1. В ячейку электронного  листа С3 введем числовое значение суммы кредита (12 000 000).

2. В ячейки С4, С5 введем произвольные (условные) значения процентной ставки (например, 5%) и  срока погашения кредита в годах (например, 1), которые нам понадобятся при построении Таблицы подстановки.

3. В ячейки  В9:В22 введем различные значения процентных ставок. В ячейки  С9:К9 - возможные сроки погашения.

4. В  ячейку В9 введем формулу для  расчета ежемесячных выплат по  займу: = ПЛТ (5%/12; 1*12; 12 000 000).

Заметим, что в качестве аргументов функции можно вводить как адреса ячеек, так и конкретные исходные значения.

5. Выделим интервал для  таблицы данных, включающий формулу  и все исходные данные, - В9:К22.

6. Выберем команды  Данные → Таблица подстановки. В  появившемся диалоговом окне (рис. 4.22) заполним соответствующие поля. Поскольку наша таблица зависит от  двух параметров, то в поле «Подставлять значения по столбцам в:» введем ссылку на ячейку С5 (срок погашения), а в поле «Подставлять значения по строкам в:» - ссылку на ячейку С4 (ставка).

7. Подтвердим ввод нажатием  клавиши [Enter] или кнопкой ОК.

Таблица ежемесячных выплат по кредиту с помощью таблицы подстановки будет сформирована (рис. 4.23).

Задания для самостоятельной работы

1. Разработайте план погашения кредита, полученного на следующих условиях:

а) 700 тыс. руб. сроком на 6 лет под  9% годовых при выплате один раз в конце года.

б) 900 тыс. руб. сроком на 9 лет под  7% годовых при выплате один раз в квартал.

в) 500 тыс. руб. сроком на 4 лет под  11% годовых при выплате один раз в месяц.

2. Ипотечный кредит размером 2 200 000 руб. предоставлен по ставке 12% годовых сроком на 30 лет и требует ежемесячных платежей. Каков будет остаток основной суммы через 8 лет?

3. Кредит в сумме 5 000 000 руб. предоставлен под 20% годовых сроком на 10 лет. Рассчитать величину остатка основной суммы без учета выплаченных процентов на начало третьего года.

4. Рассчитать сумму процентов, начисленных на вклад в 750 тыс. руб. за 2 года, если банк начисляет  проценты ежеквартально из расчета 28% годовых. Какова должна быть  годовая депозитная ставка при прочих равных условиях, если за 2 года необходимо удвоить первоначальный вклад?