Как рассчитать сложный процент в excel

Рассмотрим Сложный процент (Compound Interest) – начисление процентов как на основную сумму долга, так и на начисленные ранее проценты.

Немного теории

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

Существуют различные методы начисления процентов. Основное их различие сводится к определению исходной суммы (базы), на которую начисляются проценты. Эта сумма может оставаться постоянной в течение всего периода или меняться. В зависимости от этого различают метод начисления по

простым

и сложным процентам.

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

простых процентов

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

В

файле примера

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

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

читайте здесь

.

Начисление процентов 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 придем к выводу, что заменив знак у ставки на противоположный, мы можем для расчета дисконтированной величины использовать все три способа вычисления наращения по сложным процентам, рассмотренные в разделе статьи

Начисление процентов несколько раз в год

.

Расчет сложных процентов в случае регулярного пополнения вклада

В

файле примера

(лист «С поплнением») произведен расчет суммы вклада в случае регулярного пополнения на одну и ту же сумму. Для этого использована функция

БС()

.

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

Приветствую! Не знаю как Вы, а я люблю все за всеми перепроверять. Поэтому и свои расчеты по инвестициям для себя веду в Excel на домашнем компьютере. Ну, не доверяю я всем этим онлайн-калькуляторам в Сети! Да и вообще, когда вводишь все цифры руками, управление личными финансами становится каким-то более осознанным, что ли…

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

Рассмотрим самый простой вариант – один раз вложили куда-нибудь деньги, и они там потихоньку «размножаются» без допвливаний. Простейший расчет в Excel можно сделать двумя способами: вручную и с помощью специальной функции.

Вручную

Для этого нам понадобится вот эта формула:

• ФК – это наш финальный капитал или конечный результат. В общем, та сумма, которую мы получим на финише с учетом накопительного эффекта сложных процентов. Кстати, очень настраивает на регулярные инвестиции! Полезно своими глазами увидеть, в какие суммы превращаются даже небольшие вложения через 5,10 или 20 лет
• Ко – это начальный капитал, который мы инвестируем на длительный срок по принципу «вложили – и не трогаем»
• R – годовая процентная ставка в долях (например, 12% годовых будут выглядеть как 0,12)
• m – период реинвестирования в месяцах. Проще говоря, как часто будут начисляться проценты по вкладу и плюсоваться к общей сумме. Если ставка по банковскому вкладу начисляется каждый месяц, то m будет равно 1, если ежеквартально – то 3, если раз в году – то 12
• n – количество периодов реинвестирования. Например, если проценты реинвестируются раз в месяц, то за год получается 12 периодов реинвестирования, а за пять лет n будет равно 60

Теперь осталось сформировать простенькую табличку в Excel: из пяти строчек и двух столбцов.

• Строчка №1 – начальный капитал (Ко)
• Строчка №2 – годовая процентная ставка ( R )
• Строчка №3 – период реинвестирования (m)
• Строчка №4 – количество периодов (n)
• Строчка №5 — финальный размер капитала (ФК)

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

А дальше в ячейке с финальным капиталом забиваем формулу (по номерам строчек): =№1*(1+(№2*№3/12))^№4. На всякий случай, значок «^» в Excel находится так: «Вставка» — «Символ» — «^» — «Вставить», или с помощью комбинации клавиш «Shift+6» в английской раскладке.

Все, простейшая таблица в Excel готова! Теперь можно «играться» с размером начального капитала, годовой ставкой и количеством периодов. И видеть, как растет (или уменьшается) величина финального капитала.

С помощью специальной функции

Excel настолько универсальная программа, что потенциальную доходность по вкладу нам поможет рассчитать специальная функция. Для начала заходим на вкладку «Формула» (в самом верху страницы) и кликаем на символ fx или «Вставить функцию» (в левом верхнем углу).

Тут же открывается окно «Мастер функций». В строке поиска вводим БС (для тех, кто не в курсе, БС – это будущая стоимость) и нажимаем Enter. Выпадает целый список непонятных названий – мы выбираем все тот же БС. Или можно просто выбрать вручную из категории «Финансовые».

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

• Поле «Ставка» – все та же годовая процентная ставка в долях. Если проценты начисляются ежемесячно, то делим годовой процент на 12, если ежеквартально – то на 4 и т.д.
• Поле «Кпер» – количество лет инвестирования. Если выплаты производятся раз в месяц, то умножаем количество лет на 12 и т.д.
• Поле «Плт» — оставляем пустым
• Поле «ПС» — начальный размер вклада. Здесь его нужно записать со знаком минус, так как свои «кровные» мы отдаем, а не получаем
• Поле «Тип» учитывает способ выплаты процентом по вкладу
• Если проценты выплачиваются в конце срока действия вклада, то ставим «0» или оставляем поле пустым
• Если в начале срока – то «1».

Кликаем на ОК – и вуаля! Размер нашего будущего капитала уже отображен в ячейке!

Тестовый пример

Для примера я взял сумму в $10 000, размещенную на вкладе со ставкой 6% годовых сроком на 4 года.

Оба варианта дали один и тот же результат – через 4 года мой вклад вырастет до $12 704,89. Это, конечно, при условии, что капитализация процентов будет ежемесячной.

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

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

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

Подписывайтесь на обновления и не забывайте делиться постами в социальных сетях!

In this guide, we’re going to show you how to calculate compound interest in Excel.

Download Workbook

Compound interest

Compound interest means «interest on the interest», which defines the interest calculation based on both the initial principal and the accumulated interest from previous periods.

For example, if you get interest on $100 at 4% for the year, you will have $104 ($100 * 1.04) at the end of the year. If you re-invest your entire $104 again, you will see $108.16 ($104 * 1.04) in your account the year after.

Here is comparison between simple interest and compound interest by years:

Calculating compound interest

You can calculate compound interest using the formula below or Excel’s FV function.

The formula

Where:

• FV: The future value of the investment. This is the amount you will get at the end.
• PV: The present value of the investment. This is your initial value.
• i: The interest rate by period.
• n: The number of periods.

For example; our $100 investment becomes 100 * (1 + 0.04)² = $108.16 at 4% after 2 years.

There is only one tricky part to implement this formula in Excel: exponential calculation. You can use either the caret (^) character or the POWER function to calculate the result of (1 + i) to the number of periods.

Excel Formula

The FV function can return the future value of a loan or an investment, based on given constant payments and interest rate.

The rate, nper, and pv arguments are required. The pmt in this example is 0 since there are no payments. The type argument is also related with payments, so feel free to omit it from the function.

One important thing here is the sign of the pv value. Typically, the present value (pv) should be a negative value, because you give it as part of an investment or deal.

Based on the information above, a generic syntax will be like below.