Найти в Дзене
Piter Melnikov

Модели финансовых вычислений и их реализация в Excel

Оглавление

Вычисления по простым процентам

Расчет наращенной суммы

Под наращенной суммой ссуды (депозита, долга) понимается ее первоначальная сумма плюс начисленные на нее к концу срока проценты. Наращенная сумма вычисляется как последний элемент прогрессии, имеющей общий член P(1+ni), т.е. S = P(1+ni),
где P – первоначальная сумма,
n – количество периодов,
i – ставка за период.

Пример. Требуется вычислить сумму, причитающуюся к возврату, если сумма кредита составляет 200000 ден.ед., срок 6 месяцев при ставке простых процентов 12% годовых (расчет без капитализации процентов).

Решение в электронной таблице. Построим на рабочем листе модель для вычисления, для этого:

  • в столбце A создадим последовательность чисел от 1 до 6 (рис. 1);
  • в ячейку C3 введем сумму кредита (200000), а в ячейку D3 - годоаую ставку;
  • в ячейку B4 введем формулу =$C$3*(1+A4*$D$3/12) (обратите внимание на абсолютные ссылки);
  • с помощью маркера буксировки скопируем введенную формулу на весь диапазон до строки 9 рис. 1.);
  • выделим диапазон B4:B9, включим контекстное меню и установим формат ячеек Денежный.

В конце шестого периода накопленная сумма составит 212000 руб.

Рис. 1.
Рис. 1.

Решение с помощью финансовой функции БС
Решение приведено на рис. 2.

Рис. 2
Рис. 2

В диалоговом окне Аргументы функции в поле Ставка введена формула, вычисляющая ставку за период- 0,2/2, периодов по 6 месяцев в году 2; в поле КПЕР - значение 1, так как по условию задачи период один, именно по истечении шести месяцев начисляются проценты. Результат вычисления 212 000,00 ₽ , такой же, как и при вычислении непосредственно на рабочем листе (рис. 1).

Дисконтирование в электронной таблице

Операция дисконтирования заключается в вычислении исходной суммы Р при заданной сумме S, соответствующей концу финансовой операции. Начисления по процентам в виде разности D = S – P называют дисконтом (скидкой).

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

P = S/(1 + ni).

Пример. Определить сумму вклада, которую нужно положить в банк сроком на два месяца под 10% годовых, чтобы к концу срока получить 101667 рублей.

Решение

Модель решения и полученный результат приведен на рис. 3.

Рис. 3.
Рис. 3.

Тким образом, чтобы накопить 101667 рублей за два месяца при ставке 10% годовых, нужно сделать вклад величиной 100000 рублей 33 копейки.

Расчет реинвестирования по простым процентам

Сумма с начисленными на нее процентами может быть вновь инвестирована под эту или другую процентную ставку. В случае многократного инвестирования в краткосрочные депозиты и применения простой процентной ставки наращенная сумма для всего срока N = Σ ni вычисляется по формуле

-5

где nt – продолжительность t-того периода реинвестирования;
it – ставки, реинвестирования в t-тый период.

В MS Excel приведенную выше формулу реализует функция БС.

Пример. На сумму 100000 ден.ед. начисляется 10% годовых. Проценты простые, точные. Вычислить сумму наращения к концу квартала, если реинвестирование производится ежемесячно в течение 1 квартала (в году 360 дней).

Решение

Используем приведенную выше формулу и реализуем ее на рабочем листе в виде модели, приведенной на рис. 4. Результат решения приведен на рис. 5.

Рис. 4.
Рис. 4.
Рис. 5.
Рис. 5.

Решение этой задачи с использованием финансовой функции БС приведено на рис. 6.

Рис. 6.
Рис. 6.

В диалоговом окне Аргументы функции в поле Ставка введена формула, вычисляющая ставку за период- 0,1/12, так как реинвестирование выполняется ежемесячно; в поле КПЕР - формула, вычисляющая количество периодов, в конце которых начисляются проценты- их 3. Результат вычисления 102520,89 ₽

Финансовые расчеты по сложным процентам

Вычисление наращения

Формула наращения для сложных процентов имеет вид

-9

где P – сумма инвестиций, S – наращенная сумма, i – годовая ставка сложных процентов; n – срок ссуды (количество периодов).
Если количество начислений в году несколько, то для расчетов применяется формула

-10

где N – число периодов начисления, N=mn; j – номинальная годовая ставка сложных процентов; m – число начислений в году, n – количество лет.

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

Пример. Исходная сумма кредита 100000 ден.ед. Ставка 30% годовых. Вычислить наращенную сумму по простым и сложным процентам за 1,5 года.

Решение

Модель и результат решения приведены на рис. 7.

Рис. 7.
Рис. 7.

Используя приведенную выше формулу можно найти величину годовой ставки при известных значениях S, P и n, а также количество периодов n при известных S, P и i. Для этого формулу нужно представить в виде уравнения, например,

-12

и решать его относительно n или i, используя инструмент Подбор параметра.

Для вычисления ставки при заданных значениях S, P и n в табличном процессоре есть функция СТАВКА, которая имеет синтаксис:
СТАВКА (Кпер; Плт; Пс; Бс; Тип),
а для вычисления количества периодов - функция КПЕР:
КПЕР(Ставка; Плт; Пс; Бс; Тип).

Пример. Размер ссуды, предоставленной на 27 месяцев, равен 20 млн. Номинальная ставка равна 20% годовых, начисление процентов ежеквартальное. Требуется вычислить наращенную сумму.

Решение
Для решения применим финансовую функцию БС (рис. 8).

Рис. 8.
Рис. 8.

В диалоговом окне Аргументы функции в поле Ставка введена формула, вычисляющая ставку за период- 0,2/4; в поле КПЕР - формула, вычисляющая количество периодов, в конце которых начисляются проценты. Результат вычисления 31 026 564,32 ₽

Рекомендуем почитать
Документы, вакансии и контакты