Сложные проценты в EXCEL. Переменная ставка
При начислении по методу сложных процентов, проценты в конце каждого периода начисления не выплачиваются, а присоединяются к основной сумме и полученная величина становится исходной для начисления процентов в следующем периоде. Присоединение начисленных процентов к сумме, которая служила базой для их определения, называют капитализацией процентов. Расчет начисления сложных процентов в случае постоянной ставки рассмотрено в статье Сложные проценты в MS EXCEL. Постоянная ставка . Здесь рассмотрим ситуации, когда процентная ставка изменяется в течение срока действия договора.
Решим задачу . Договором на открытие вклада предусмотрено, что вклад открывается на 3 года. В первый год ставка составляет 10%, в последующие – увеличивается на 2%. Период капитализации процентов – год. Сумма вклада 20т.р. Определить сумму вклада в конце срока (наращенную сумму).
Решение . В случае переменной ставки, формула наращения для сложных процентов: S = Р*(1+i)^n где S — наращенная сумма, i — годовая ставка сложных процентов, n — срок ссуды должна быть изменена. Для 3-х периодов капитализации она примет вид: S = Р*(1+i1) *(1+i2) *(1+i3)
В нашем случае период капитализации =1 году, поэтому итоговая формула будет выглядеть так: S = 20000*(1+10%) *(1+12%) *(1+14%)=28 089,6р.
Тот же результат можно получить с помощью функции БЗРАСПИС() (английский вариант FVSCHEDULE(principal, schedule)) S =БЗРАСПИС(20000;) – использован массив констант (0,1=10% и т.д.). Если ставки введены в диапазон C14:C16 , то формулу можно переписать без массива констант = БЗРАСПИС(20000;C14:C16) (см. файл примера ). Каждая ставка действует один период (в нашем примере — 1 год), т.е. размерность массива определяет количество периодов начисления процентов (в нашем примере – 3 периода).
Если период капитализации =1 месяцу, то формула наращения для сложных процентов будет выглядеть так: S = 20000*((1+10%/12)^12 )*((1+12%/12)^12)*((1+14%/12)^12) Если ставки введены в диапазон C31:C66 , то формулу можно записать так =БЗРАСПИС(20000; C31:C66) Размер массива со ставками должен соответствовать общему количеству периодов капитализации (12*3=36), а ставки должны быть указаны за период, т.е. 10%/12, 12%/12 и 14%/12 (для первого года каждая из 12 ставок =10%/12, для 2-го =12%/12, для 3-го =14%/12).
Расчет составных процентов за внутригодный период в Excel
Будущая стоимость суммы в рублях, обычно называемая составной стоимостью, включает применение составных процентов к сумме к сумме к настоящей стоимости. Результатом будет будущая сумма в рублях. Три типа составных соединений
год, интра-год и составные аннуитеты. В этой статье рассмотрены вычисления в течение года по сложным интересам.
Дополнительные сведения о ежегодном соединении можно получить в следующей статье:
Вычисление будущей стоимости Intra-Year сложных процентов
Intra-year compound interest is interest that is compounded more frequently than once a year. Финансовые учреждения могут вычислять проценты на основе полугодных, квартальных, ежемесячных, еженедельных и даже ежедневных периодов времени.
Microsoft Excel включает функцию ЭФФЕКТ в надстройку «Надстройка анализа» для более старых версий, чем 2003. Уже загружено средство анализа. Функция ЭФФЕКТ возвращает составную процентную ставку на основе годовой процентной ставки и количества периодов в году.
Формула для вычисления в течение года сложных процентов с помощью функции ЭФФЕКТ:
=P+(P*EFFECT(EFFECT(k,m)*n,n))
Общее уравнение для вычисления составных процентов
=P*(1+(k/m))^(m*n)
где верно следующее:
P = начальная основной
k = уплаченная годовая процентная ставка
m = количество раз за период (как правило, месяцы) проценты являются составными
n = количество периодов (обычно лет) или срок займа
Примеры
В примерах этого раздела используются функция ЭФФЕКТ, общая формула и данные из следующего примера:
Intra-Year сложные ставки
Количество периодов в году, за которые начисляются сложные проценты
Расчет Будущей стоимости в EXCEL
Будущая стоимость (Future Value), является суммой, в которую в будущем превратится определенная сумма денег, инвестированная ранее под известную процентную ставку. Она рассчитывается на базе концепции стоимости денег во времени: деньги, доступные в настоящее время, стоят больше, чем та же самая сумма в будущем, вследствие их потенциала обеспечить доход. Расчет Будущей стоимости, также как и Приведенной (Текущей) стоимости важен, так как, платежи, осуществленные в различные моменты времени, можно сопоставлять (сравнивать, складывать, вычитать) лишь после приведения их к одному временному моменту.
Будущая стоимость инвестиций зависит от того, каким методом начисляются проценты: простые проценты , сложные проценты или аннуитет (в файле примера приведено решение задачи для каждого из методов).
Простые проценты
Сущность метода начисления по простым процентам состоит в том, что проценты начисляются в течение всего срока инвестиции на одну и ту же сумму (проценты, начисленные за предыдущие периоды, не капитализируются, т.е. на них проценты в последующих периодах не начисляются).
При начислении простых процентов формула для расчета будущей стоимости (FV) инвестиций имеет следующий вид:
где PV — настоящая (приведенная) стоимость (сумма, которая инвестируется в настоящий момент и на которую начисляется процент); i — процентная ставка за период начисления процентов (например, если проценты начисляются раз в год, то годовая; если проценты начисляются ежемесячно, то за месяц (=годовая ставка/12)); n – количество периодов времени, в течение которых начисляются проценты.
Как видно из формулы FV (в MS EXCEL используется аббревиатура БС) это ни что иное, как наращенная сумма с использованием простых процентов. Про вычисление наращенной суммы при постоянной и переменной ставке можно прочитать в статье Простые проценты в MS EXCEL .
Примечание . В MS EXCEL нет отдельной функции для расчета Будущей стоимости по методу Простых процентов. Функция БС() используется только для расчета в случае сложных процентов и аннуитета. Хотя, указав в качестве аргумента Кпер значение 1, а в качестве ставки указать i*n, то можно заставить БС() рассчитать Будущую стоимость и по методу простых процентов (см. файл примера ).
Сложные проценты
При использовании сложных ставок процентов процентные деньги, начисленные после каждого периода начисления, присоединяются к сумме долга. Таким образом, база для начисления сложных процентов в отличие от использования простых процентов изменяется в каждом периоде начисления. Присоединение начисленных процентов к сумме, которая послужила базой для их начисления, называется капитализацией процентов. Иногда этот метод называют «процент на процент» (см. файл примера ). Формула для расчета Будущей стоимости в случае начисления по сложным процентам имеет следующий вид:
где PV — настоящая (приведенная) стоимость (сумма, которая инвестируется в настоящий момент и на которую начисляется процент); i — процентная ставка за период начисления процентов (например, если проценты начисляются раз в год, то годовая; если проценты начисляются ежемесячно, то за месяц). Предполагается, что процентная ставка не изменяется в течение всего срока инвестирования; n – количество периодов времени, в течение которого начисляются проценты.
Про вычисление Будущей стоимости по сложным процентам можно прочитать в статье Сложные проценты в MS EXCEL . В этой статье разобраны случаи применения функции БС() и формула капитализации m раз в год. В статье Сложные проценты в MS EXCEL. Переменная ставка разобраны случаи применения функции БЗРАСПИС() для расчета будущей стоимости при переменной ставке. В статье Непрерывные проценты Будущая стоимость рассчитывается при непрерывном начислении процентов.
Аннуитет
Если, помимо начальной инвестиции, через равные периоды времени производятся дополнительные равновеликие платежи (дополнительные инвестиции), то расчет Будущей стоимости существенно усложняется См. статью Аннуитет. Определяем в MS EXCEL Будущую Стоимость .
В случае, если сумма начальной инвестиции =0 и нужно определить Будущую стоимость периодических равновеликих платежей, то это можно сделать по формуле (см. файл примера ):
FV = PMT * (((1+i)^n)-1) / i
где PMT – размер платежа при аннуитете; i — процентная ставка за период начисления процентов (например, если проценты начисляются раз в год, то годовая; если проценты начисляются ежемесячно, то за месяц). В формуле предполагается, что проценты начисляются в конце периода; n – количество периодов времени, в течение которых начисляются проценты (и делаются платежи).
Сложные проценты в EXCEL. Постоянная ставка
Владелец капитала, предоставляя его на определенное время в долг, рассчитывает на получение дохода от этой сделки. Размер ожидаемого дохода зависит от трех факторов: от величины капитала, предоставляемого в кредит, от срока, на который предоставлен кредит, и от величины ссудного процента или иначе процентной ставки.
Существуют различные методы начисления процентов. Основное их различие сводится к определению исходной суммы (базы), на которую начисляются проценты. Эта сумма может оставаться постоянной в течение всего периода или меняться. В зависимости от этого различают метод начисления по простым и сложным процентам.
При использовании сложных ставок процентов процентные деньги, начисленные после каждого периода начисления, присоединяются к сумме долга. Таким образом, база для начисления сложных процентов в отличие от использования простых процентов изменяется в каждом периоде начисления. Присоединение начисленных процентов к сумме, которая послужила базой для их начисления, называется капитализацией процентов. Иногда этот метод называют «процент на процент».
В файле примера приведен график для сравнения наращенной суммы с использованием простых и сложных процентов.
В этой статье рассмотрим начисление по сложным процентам в случае постоянной ставки. О переменной ставке в случае сложных процентов читайте здесь .
Начисление процентов 1 раз в год
Пусть первоначальная сумма вклада равна Р, тогда через один год сумма вклада с присоединенными процентами составит =Р*(1+i), через 2 года =P*(1+i)*(1+i)=P*(1+i)^2, через n лет – P*(1+i)^n. Таким образом, получим формулу наращения для сложных процентов: S = Р*(1+i)^n где S — наращенная сумма, i — годовая ставка, n — срок ссуды в годах, (1+ i)^n — множитель наращения.
Начисление процентов несколько раз в год
В рассмотренном выше случае капитализация производится 1 раз в год. При капитализации m раз в год формула наращения для сложных процентов выглядит так: S = Р*(1+i/m)^(n*m) i/m – это ставка за период. На практике обычно используют дискретные проценты (проценты, начисляемые за одинаковые интервалы времени: год (m=1), полугодие (m=2), квартал (m=4), месяц (m=12)).
В MS EXCEL вычислить наращенную сумму к концу срока вклада по сложным процентам можно разными способами.
Рассмотрим задачу : Пусть первоначальная сумма вклада равна 20т.р., годовая ставка = 15%, срок вклада 12 мес. Капитализация производится ежемесячно в конце периода.
Способ 1. Вычисление с помощью таблицы с формулами Это самый трудоемкий способ, но зато самый наглядный. Он заключается в том, чтобы последовательно вычислить величину вклада на конец каждого периода. В файле примера это реализовано на листе Постоянная ставка .
За первый период будут начислены проценты в сумме =20000*(15%/12) , т.к. капитализация производится ежемесячно, а в году, как известно, 12 мес. При начислении процентов за второй период, в качестве базы, на которую начисляются %, необходимо брать не начальную сумму вклада, а сумму вклада в конце первого периода (или начале второго). И так далее все 12 периодов.
Способ 2. Вычисление с помощью формулы Наращенных процентов Подставим в формулу наращенной суммы S = Р*(1+i )^n значения из задачи. S = 20000*(1+15%/12)^12 Необходимо помнить, что в качестве процентной ставки нужно указывать ставку за период (период капитализации). Другой вариант записи формулы – через функцию СТЕПЕНЬ() =20000*СТЕПЕНЬ(1+15%/12; 12)
Способ 3. Вычисление с помощью функции БС(). Функция БС() позволяет определить будущую стоимость инвестиции при условии периодических равных платежей и постоянной процентной ставки, т.е. она предназначена прежде всего для расчетов в случае аннуитетных платежей . Однако, опустив 3-й параметр (ПЛТ=0), можно ее использовать и для расчета сложных процентов. =-БС(15%/12;12;;20000)
Или так =-БС(15%/12;12;0;20000;0)
Примечание . В случае переменной ставки для нахождения Будущей стоимости по методу сложных процентов используется функция БЗРАСПИС() .
Определяем сумму начисленных процентов
Рассмотрим задачу: Клиент банка положил на депозит 150 000 р. на 5 лет с ежегодным начислением сложных процентов по ставке 12 % годовых. Определить сумму начисленных процентов.
Сумма начисленных процентов I равна разности между величиной наращенной суммы S и начальной суммой Р. Используя формулу для определения наращенной суммы S = Р*(1+i )^n, получим: I = S – P= Р*(1+i)^n – Р=P*((1+i)^n –1)=150000*((1+12%)^5-1) Результат: 114 351,25р. Для сравнения: начисление по простой ставке даст результат 90 000р. (см. файл примера ).
Определяем Срок долга
Рассмотрим задачу: Клиент банка положил на депозит некую сумму с ежегодным начислением сложных процентов по ставке 12 % годовых. Через какой срок сумма вклада удвоится? Логарифмируя обе части уравнения S = Р*(1+i)^n, решим его относительно неизвестного параметра n.
В файле примера приведено решение, ответ 6,12 лет.
Вычисляем ставку сложных процентов
Рассмотрим задачу: Клиент банка положил на депозит 150 000 р. с ежегодным начислением сложных процентов. При какой годовой ставке сумма вклада удвоится через 5 лет?
В файле примера приведено решение, ответ 14,87%.
Примечание . Об эффективной ставке процентов читайте в этой статье .
Учет (дисконтирование) по сложным процентам
Дисконтирование основывается на базе концепции стоимости денег во времени: деньги, доступные в настоящее время, стоят больше, чем та же самая сумма в будущем, вследствие их потенциала обеспечить доход. Рассмотрим 2 вида учета: математический и банковский.
Математический учет . В этом случае решается задача обратная наращению по сложным процентам, т.е. вычисления производятся по формуле Р=S/(1+i )^n Величину Р, полученную дисконтированием S, называют современной, или текущей стоимостью, или приведенной величиной S. Суммы Р и S эквивалентны в том смысле, что платеж в сумме S через n лет равноценен сумме Р, выплачиваемой в настоящий момент. Здесь разность D = S — P называется дисконтом.
Пример . Через 7 лет страхователю будет выплачена сумма 2000000 руб. Определить современную стоимость суммы при условии, что применяется ставка сложных процентов в 15% годовых. Другими словами, известно: n = 7 лет, S = 2 000 000 руб., i = 15% .
Решение. P = 2000000/(1+15% )^7 Значение текущей стоимости будет меньше, т.к. открыв сегодня вклад на сумму Р с ежегодной капитализацией по ставке 15% мы получим через 7 лет сумму 2 млн. руб.
Тот же результат можно получить с помощью формулы =ПС(15%;7;;-2000000;1) Функция ПС() возвращает приведенную (к текущему моменту) стоимость инвестиции и рассмотрена здесь .
Банковский учет . В этом случае предполагается использование сложной учетной ставки. Дисконтирование по сложной учетной ставке осуществляется по формуле: Р = S*(1- dсл )^n где dcл — сложная годовая учетная ставка.
При использовании сложной учетной ставки процесс дисконтирования происходит с прогрессирующим замедлением, так как учетная ставка каждый раз применяется к сумме, уменьшенной за предыдущий период на величину дисконта.
Сравнив формулу наращения для сложных процентов S = Р*(1+i )^n и формулу дисконтирования по сложной учетной ставке Р = S*(1- dсл )^n придем к выводу, что заменив знак у ставки на противоположный, мы можем для расчета дисконтированной величины использовать все три способа вычисления наращения по сложным процентам, рассмотренные в разделе статьи Начисление процентов несколько раз в год .
Расчет сложных процентов в случае регулярного пополнения вклада
В файле примера (лист «С поплнением») произведен расчет суммы вклада в случае регулярного пополнения на одну и ту же сумму. Для этого использована функция БС() .
Если сумма вклада пополняется нерегулярно и/или различными платежами, то для расчета необходимо использовать таблицу, которая также приведена в файле примера. Естественно, в случае регулярных и равновеликих платежей итоговые суммы вычисленные с помощью таблицы и функции БС() — совпадают.