Простые и сложные проценты в EXCEL.

Финансовые функции НАКОПДОХОДПОГАШ и БС, использование инструмента "Таблица данных"

Расчёт простых процентов
Расчёт сложных процентов
Видео

Для начало немного теории. Отличие простых процентов от сложных заключается в том, что начисление при простых процентах производится только на основную сумму, в то время как по методу сложных процентов начисление производится ещё и на сумму накопленных процентов. Чем чаще присоединение процентной суммы к общей, тем больше будет величина сложных процентов.
Формула простых процентов имеет вид:
FV = PV*(1 + t/D*i)
PV = FV*(1 + t/D*i)^-1,
где PV - первоначальная сумма или современная величина, FV - накопленная сумма или будущая величина, t - величина периода в днях (может быть точным и приближённым, в зависимости от метода начисления). D - базис (может быть 360 дней, 365, 366 в зависимости от метода начисления).
(FV - PV) - величина процентов за рассматриваемый период.
Множитель (1 + t/D*i) называется коэффициентом наращения, а (1 + t/D*i)^-1- коэффициентом приведения.
В Excel применяется четыре метода начисления процентов:
0 и 4 – это 30/360 (обыкновенные проценты с приближенным числом дней ссуды) Американский (NASD) или европейский, он же германский метод начисления процентов;
1 – это фактический/фактический (точные проценты с точным числом дней ссуды) английский метод начисления процентов;
2 – это фактический/360 (обыкновенные проценты с точным числом дней ссуды) французский метод начисления процентов;
3 – фактический/365.

Способы начисления простых процентов в Excel

Большая часть финансовых функций Excel используют формулу сложных процентов. Есть финансовые функции, которые для вычисления используют формулу простых процентов. Одна из них НАКОПДОХОДПОГАШ (дата_выпуска; дата_согл; ставка; номинал; базис). Данная функция определяет величину простых процентов за период:
дата_выпуска - начало периода;
дата_согл - дата конца периода;
ставка - ставка простых процентов;
номинал - сумма вклада;
базис - метод начисления.
Рассмотрим вклад размером 1000 ден. ед., который положен под простые проценты 01.01.2012 (год високосный, т. е. точное число дней будет 366). Ставка простых процентов составляет 10%. Необходимо определить накопленные проценты через полгода и через год после открытия вклада применяя все 4 методики начисления процентов.
Создаём первоначальную таблицу с условиями задачи, конечная дата в этой таблице будет через месяц после открытия. Эта дата условная, так как далее мы будем использовать интервалы полгода и год.

Начальные условия задачи (простые проценты)

Далее мы создаём таблицу для пользования инструментом "Таблица данных", в которой по столбцам ставим даты 01.07.2012 (через полгода) и 01.01.2013 (через год), а по строкам методы начисления простых процентов от 0 до 4.

Вспомогательная таблица для инструмента "Таблица данных"(простые проценты)

В ячейку B10 с помощью финансовой функции вводим формулу расчёта простых процентов: =НАКОПДОХОДПОГАШ(C3;C4;C7;C6;C5).

Диалоговое окно функции НАКОПДОХОДПОГАШ

Формула НАКОПДОХОДПОГАШ

В результате в ячейке B10 получаем значение процентов за месяц по методике 0 (Американская) равное 8,33 ден. ед.
Далее выделяем ячейки с B10 по D15 и переходим на вкладку Данные - Анализ "что если", в которой выбираем инструмент "Таблица данных"

Инструмент "Таблица данных"

Заполняем диалоговое окно инструмента "Таблица данных". По столбцам в таблице будем подставлять дату конца периода, что соответствует ячейке $C$4, а по строкам метод начисления простых процентов, что соответствует ячейке $C$5.

Диалоговое окно инструмента "Таблица данных" (простые проценты)

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

Результат расчёта простых процентов

При полугодовом начислении процентов одинаковый результат дают методики 0 и 4. Так и должно быть, они одинаковы по формуле вычисления. Методы 1, 2, 3 при полугодовом интервале дают различные значения процентов. На всех интервалах наибольшее значение даёт метод 2 (французский (фактический/360)), так как фактическое число дней больше приближённого.
За год по методу 2 (французский) мы имеем наибольшее значение процентов равное 101,667 ден. ед.
Рабочий файл здесь: https://vk.com/wall-216984375_119
https://t.me/smys_l/91

Формула вычисления сложных процентов запишется в виде:
FV = PV*(1 + j/m)^m*n
PV = FV*(1 + j/m)^-m*n,
где PV - первоначальная сумма или современная величина, FV - накопленная сумма или будущая величина, n - величина периода в годах; j - ставка сложных процентов, m - число периодов в году присоединения процентов к основной сумме (m=1 - проценты присоединяются к основной сумме раз в году, m=2 - проценты присоединяются к основной сумме раз в полгода, m=4 - проценты присоединяются к основной сумме раз в квартал, m=12 - проценты присоединяются к основной сумме раз в месяц).
При m⟶∞ получаем формулу непрерывного начисления процентов:
FV=PV*e^jn
Очевидно, что чем больше величина m, тем больше будут начислены проценты за период. Для вкладчика будет выгодно наибольшее значение m, а для получателя кредита - наименьшее значение m.
(FV - PV) - величина процентов за рассматриваемый период.
Множитель (1 + j/m)^m*n называется коэффициентом наращения, а (1 + j/m)^-m*n- коэффициентом приведения.
Поставим задачу. Вкладчик хочет разместить в банке сумму 1000 ден. ед. по ставке сложных процентов 10%. Какова будет величина накопленных процентов за полгода и за год при полугодовом, ежеквартальном, ежемесячном начислении процентов?
Создаём таблицу с начальными условиями задачи.

Начальные условия задачи (сложные проценты)

Далее мы создаём таблицу для пользования инструментом "Таблица данных", в которой по столбцам ставим продолжительность вклада (полгода и год) , а по строкам количество периодов начисления процентов (2, 4, 12).

Вспомогательная таблица для инструмента "Таблица данных"(сложные проценты)

В ячейку B10 с помощью финансовой функции вводим формулу расчёта сложных процентов: =БС(D5/D4;D6*D4;;-D3)-1000. С помощью функции БС мы находим наращенную величину вклада, а отнимая 1000 (начальную величину вклада) получаем начисленные проценты за период.

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

Диалоговое окно функции БС

В результате в ячейке B9 получаем значение процентов за год при начислении процентов раз в году равное 100 ден. ед.
Далее выделяем ячейки с B9 по D12 и переходим на вкладку Данные - Анализ "что если", в которой выбираем инструмент "Таблица данных"

Инструмент "Таблица данных"

Заполняем диалоговое окно инструмента "Таблица данных". По столбцам в таблице будем подставлять число лет, что соответствует ячейке $D$6, а по строкам число периодов начисления процентов, что соответствует ячейке $D$4.

Диалоговое окно инструмента "Таблица данных" (сложные проценты)

Получаем значения процентов по вкладу за полгода и за год при начислении процентов раз в полгода, раз в квартал и ежемесячно.

Результат расчёта сложных процентов

Очевидно, что наибольшее значение процентов получается при ежемесячном начислении процентов. Сравнивая методики начисления простых и сложных процентов видим, что больший результат даёт методика сложных процентов.
Рабочий файл здесь: https://vk.com/wall-216984375_124
https://t.me/smys_l/91

Заявка на услуги

Укажите наиболее удобный для ВАС способ связи
и с Вами свяжутся в ближайшее время

Нажимая на кнопку, Вы соглашаетесь на обработку персональных данных в соответствии с Условиями.