Формула сложного процента в экселе

Формула сложного процента в экселе

Приветствую, читатели и посетители блога!

«Превратите 1$ в 1000000$!», «Самый простой способ стать миллионером!», «Вложи, ничего не делай и стань финансово свободным!». Вам попадались такие лозунги в Интернете? Мне да…

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

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

Для начала, давайте разберемся кое в чём. Если есть сложный процент, есть и простой, не так ли? В чем разница?

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

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

Например, если инвестор получает 20% в год на 10000$ — это 2000$ в год. И каждый год сумма прибыли будет ровно 2000$.

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

На тех же условиях, но с реинвестированием — в первый год инвестор получит 2000$, во второй — 2400$, в третий — 2880$. А через десять лет — 10320$! А через двадцать — невероятные 63896$. Что мы там говорили о том, как стать миллионером?

И это без учета дополнительных вложений.

Очень похоже на то, как катится с горы снежный ком — сначала понемногу, а со временем ком становится все больше и больше — и вот уже он огромен!

Для наглядности я сделал другой расчет сложных процентов в Excel и сравнил с простыми:

С каждым годом, с каждым месяцем разница все ощутимее и ощутимее

И чем дальше, тем сильнее заворачивается вверх красный график, устремляясь по параболе в финансовую бесконечность… Пусть и в теории, но зато как эффектно!

В принципе, особо тут разглагольствовать не о чем. В Интернет-инвестировании эффект сложного процента разрешен и чаще всего используется автоматически. Например, в ПАММ-счетах это так.

С другой стороны, инвесторам часто приходится рассчитывать сложный процент — подсчитать доходность, оценить инвестиционный горизонт… И решать другие прикладные задачи.

Поэтому дальше я покажу вам все необходимые формулы и помогу с расчетами.

  • Обзор задач и формул сложных процентов
  • Вспомогательные формул расчета сложных процентов
  • Расчет сложных процентов в Excel по формулам

Обзор задач и формул сложных процентов

Самая первая задача, с которой может столкнуться инвестор — «Сколько я получу денег, инвестируя»? Она решается, если известна начальная сумма и годовая процентная ставка доходности.

Для расчета используется формула сложных процентов с капитализацией:

  • К — начальный капитал,
  • К — результат инвестирования (финальный капитал)
  • R — годовая процентная ставка
  • m — период реинвестирования (в месяцах)
  • n— количество периодов реинвестирования (месяцев, кварталов, лет)

Чтобы работать чисто с годовыми периодами, нужно убрать из знаменателя 12, а из числителя — m. Но я этого делать не буду, так как ПАММ-счета удобнее всего анализировать через среднемесячную доходность.

Давайте рассмотрим пару примеров.

Пример № 1. Иван Иванов положил 7000$ на депозит в банке. Сейчас средняя ставка по долларовым депозитам в Украине — в районе 9% в год. Вклад будет переоформляться каждый год в течение 10 лет. Сколько в итоге будет денег на банковском счету?

Капитал вырос почти в три раза, несмотря на мизерную доходность по депозитам.

Впрочем, деньги на ПАММ-счетах реинвестируются автоматически и постоянно. Кроме того, гарантий стабильного годового дохода нет…

Поэтому рекомендую для расчетов сложного процента в ПАММ-счетах использовать ежемесячный реинвест.

Пример № 2. Пётр Петров вложил в ПАММ-счета 10000$. После анализа статистики выяснилось, что он может ожидать 30% чистой прибыли за год. И теперь Пётр хочет знать, каким будет размер капитала через полгода.

Вот, что получилось:

Для сравнения, без реинвестирования инвестор получил бы 11500$. То есть, на 97$ меньше, что почти не чувствуется — это всего лишь 0.97% от общего капитала.

Но давайте теперь посмотрим чуть дальше в следующем примере.

Пример № 3. Исидор Сидоров вложил в ПАММ-счета 5000$. Он собирается активно управлять портфелем и рассчитывает на 50% в год в течение 5 лет. Какой капитал получится в итоге?

Для расчета снова используется формула сложных процентов. Только теперь периодов не 6, а 60 — в 5 годах 60 месяцев. Сколько же получилось?

Без реинвестирования прибыль составила бы 50%*5=250%. Соответственно, капитал бы вырос до 35000$. А с учетом сложного процента — на целых 106 тысяч! Теперь разница ощущается очень сильно.

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

Я не раз видел, как эту «фишку» используют в фильмах. Например, сюжет может быть такой:

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

И вот, через 20-30 лет этот счет обнаруживают, а там лежат миллионы или даже миллиарды долларов. И начинается охота за паролями, поиск владельца, взлом хитромудрых защитных систем и т.д. Что придумают сценаристы 🙂

В прошлой статье я упоминал о том, что консервативные ПАММ-счета растут по параболе из-за сложного процента. Теперь вы на 100% понимаете, как это работает!

Читайте также:  Теле 2 тарифы и услуги тула

Кроме постоянного реинвестирования прибыли, инвестор может дополнительно «доливать» деньги в свой портфель. Эти деньги тоже будут приносить доход, поэтому формула сложных процентов немного усложняется.

Ну как немного… В общем, смотрите:

AI (AdditionalInvestments) — размер постоянного пополнения.

С левым слагаемым вы знакомы, а правое — расчет сложного процента по новым вложениям. Формула правильная, я проверял 🙂

Вспомогательные формул расчета сложных процентов

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

Например, иногда нужно найти не финальный, а начальный капитал.

Пример № 4. Аркадий Аркадьев интересуется, сколько ему нужно вложить денег, чтобы получить через 5 лет при ставке доходности 30% в год 100000$. Реинвест — каждый квартал.

Для этого мы используем такую формулу:

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

Оказалось, нужно почти в 5 раз меньше. Круто, не так ли?

Идем дальше. Давайте представим ситуацию — инвестор хочет вложить деньги на определенный срок. И он рассчитывает по итогам достичь определенной суммы капитала. Какую процентную ставку ему нужно получить?

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

Пример № 5. Начальные инвестиции Максима Максимова — 13000$. Через два года они должны превратиться в 18000$. Реинвест ежемесячный (m= 1). Под какую ставку доходности Максиму нужно собирать инвестиционный портфель, чтобы выйти на требуемую сумму?

Естественно, это годовая ставка. Её можно превратить в месячную, если из числителя убрать 12, и тогда получится около 1.5% в месяц — минимальный порог для ПАММ-счетов.

Что там можно еще найти? Ах, да — сколько нужно времени, чтобы получить определенную сумму при определенной ставке. Давайте попробуем 🙂

Если в прошлом примере у нас были корни, то теперь — логарифмы. Формулы кажутся огромными, но на самом деле их легко реализовать в программе. Чтобы рассчитать сложный процент, формула Excel нужна для одной ячейки — вот и выражаем одно через все остальное. И работает это отлично!

Итак, мы будем использовать такую формулу:

Пример № 6. У Елены Лениной — 4500$, которые она хочет инвестировать. Она понимает, что может рассчитывать на 50% в год, при этом хочет достичь первой цели — 20000$. Возможно, хочет купить со временем новую машину 🙂

Через сколько времени она достигнет своей цели с условием ежемесячного реинвестирования прибыли? Подставляем числа:

Довольно быстро, должен сказать.

Кстати, опытных инвесторов часто интересует не на сколько, когда и как вырастет капитал. Их больше интересует, когда деньги удвоятся. Другими словами — через сколько они «отобьют» вложения.

Чтобы это узнать, существует универсальное «правило 72». Суть его простая — делите 72 на процентную ставку за месяц (квартал, год). Результат — это и есть тот срок, за который инвестиции удвоятся (в тех же единицах времени, что и ставка доходности).

Пример № 7. Инвестор Владимир ВладимирОвич вкладывает деньги под 10% в месяц. Через сколько он отобьет вложения?

Ответ: через 72/10=7.2 месяца.

Вложения под 6% в месяц дают удвоение капитала за год. Под 3% — за 2 года.

Расчет сложных процентов в Excel по формулам

Многие из формул, которые я вам написал, на калькуляторе посчитать не то что неудобно — почти невозможно. Да и зачем это, если есть замечательная программа, о которой пишу чуть ли не в каждой статье 🙂

Например, в статье «Делаем собственный рейтинг ПАММ-счетов» я показал, как с помощью Excel проранжировать ПАММ-счета по нескольким показателям и автоматически найти самые выгодные варианты.

Поэтому я сделал для вас очередной полезный Excel-файлик — Калькулятор сложных процентов с капитализацией. Там вы найдете 5 табличек для расчетов по формулам из этой статьи.

Напоминаю, что мы искали:

  • финальный капитал;
  • финальный капитал с пополнениями;
  • начальный капитал;
  • процентную ставку;
  • срок достижения инвестиционных целей.

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

Скачать файл: «Расчет сложных процентов».

На всякий случай записал небольшую видеоинструкцию:

Если у вас нет Экселя или неудобно им пользоваться, можете попробовать поискать в Интернете сайты по запросам «калькулятор сложного процента с реинвестированием», «калькулятор сложного процента с капитализацией» или «калькулятор инвестора сложный процент». Вы найдете множество онлайн-сервисов, выбирайте, какой понравится.

И на этом я прощаюсь. По традиции, несколько вопросов к вам, читатели:)

Первоекак часто вам приходится считать сложный процент? И приходится ли? Лично мне не очень часто, но это потому что я считаю доходность ПАММ-счетов через интервалы — результат почти такой же выходит. Больше нигде применять не приходилось, кроме нескольких пар на втором курсе, когда мы учили «Финансовую математику».

Второеесть ли у вас какие-то инвестиционные цели? Дойти до энной цифры с энным количеством нулей? Получить определенную доходность? Может, стать рантье? 🙂

С уважением, Александр Дюбченко

Все статьи блога «Инвестируй в ЭТО»

Понравилась статья? Скажите «спасибо» лайком!

Нужно больше информации? Вот еще 4 статьи для вас:

Рассмотрим Сложный процент (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

Пример 1. Предприниматель получил ссуду в банковской организации на 1 год с эффективной процентной ставкой 23,5%. Определить значение номинальной ставки, если по условию договора выплаты по кредиту необходимо проводить ежемесячно.

Исходная таблица данных:

Связь между значениями эффективной и номинальной ставок описывается следующей формулой:

Проверим полученный результат, проведя пересчет эффективной ставки с помощью функции:

  • B4 – полученное выше числовое значение номинальной ставки;
  • B2 – число периодов погашения.

Полученное значение 0,235 соответствует 23,5% (значению эффективной ставки по условию). Расчет номинальной ставки также можно производить с помощью функции НОМИНАЛ.

Формула расчета процентов по вкладу в Excel

Пример 2. Вкладчику предложили сделать депозит в банк под 16% годовых (номинальная ставка), при этом расчете производится с использованием сложных процентов (эффективная ставка). По условиям договора вкладчик сможет снять только полученные проценты. Определить сумму к получению, если размер депозита – 1 млн. рублей, капитализация – ежемесячная.

Формула для расчета:

  • B2 – число периодов капитализации;
  • B3 – номинальная ставка;
  • B4 – сумма вклада.

Для сравнения, доход от вклада при использовании простых процентов составил бы 1000000*0,16=160000 рублей, поэтому для вкладчика выгодно использовать предложенный вариант со сложными процентами.

Как посчитать проценты на депозит в Excel для выбора вклада

Пример 3. Два банка предлагают сделать депозитный вклад на одинаковую сумму (250000 рублей) на 1 год при следующих условиях:

  1. Номинальная ставка – 24%, простые проценты, 12 периодов капитализации.
  2. Номинальная ставка 22%, сложные проценты, начисляемые по итогам каждого периода, 4 периода капитализации.

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

В первом случае таблица выплат выглядит так:

Проценты – постоянная величина, рассчитываемая по формуле:

Описание аргументов (для создания абсолютной ссылки используйте клавишу F4):

  • $B$2 – начальная сумма вклада;
  • $B$3 – годовая ставка;
  • $B$4 – число периодов капитализации вклада.

Сумма накопленных средств за каждый период рассчитывается как как сумма средств на счету за прошедший период и процентов, начисленных за текущий период. В итоге первый банк начислит 60000 рублей процентов, и вкладчик сможет забрать 310000 рублей.

Таблица начисления процентов по условиям второго банка:

В данном случае проценты не являются фиксированной величиной и зависят от итоговой суммы накоплений за предыдущий период (поэтому ссылка на ячейку L2 – абсолютная):

При расчете суммы за каждый период к текущему значению необходимо прибавить проценты за предыдущий период.

Для быстрого расчета итоговой суммы используем формулы:

  1. Первый банк:
  2. Второй банк:

Несмотря на то, что второй банк предлагает расчет с использованием сложных процентов, предложение первого банка оказалось выгоднее. Если бы число периодов капитализации совпадало (12), во втором банке вкладчик получил бы 310899,1 рублей, то есть больше денег, несмотря на более низкую номинальную процентную ставку.

Особенности использования функции ЭФФЕКТ в Excel

Функция имеет следующий синтаксис:

  • номинальная_ставка – обязательный аргумент, характеризующий числовое (десятичная дробь) или процентное значение номинальной годовой ставки;
  • кол_пер – обязательный аргумент, характеризующий числовое значения числа периодов за год, на протяжении которых начисляются сложные проценты.
  1. Аргумент кол_пер может принимать дробные числа, значения которых будут усечены до целого числа (в отличие от операции округления, при усечении отбрасывается дробная часть).
  2. Каждый из двух аргументов функции ЭФФЕКТ должен быть представлен числовым (или процентным для аргумента номинальная_ставка) значением либо текстовой строкой, которая может быть преобразована в число. При вводе не преобразуемых к числовым значениям текстовых строк и имен, а также данных логического типа функция ЭФФЕКТ будет возвращать код ошибки #ЗНАЧ!.
  3. Аргумент номинальная_ставка принимает значения из диапазона положительных чисел, а кол_пер – из диапазона от 1 до +∞. Если данные условия не выполняются, например, функции =ЭФФЕКТ(0;12) или =ЭФФЕКТ(12%;0) вернут код ошибки #ЧИСЛО!.
  4. Функция ЭФФЕКТ использует для расчетов формулу, которая может быть записана в Excel в виде: =СТЕПЕНЬ(1+(A1/A2);A2)-1, где:
  • A1 – номинальная годовая ставка;
  • A2 – число периодов, в которые происходит начисление сложных процентов.
  • Для понимания термина «сложные проценты» рассмотрим пример. Владелец капитала предоставляет денежные средства в долг и планирует получить прибыль, величина которой зависит от следующих факторов: сумма средств, которая предоставляется в долг; длительность периода кредитования (использования предоставленных средств); начисляемые проценты за использование.
  • Проценты могут начисляться различными способами: базовая сумма остается неизменной (простые проценты) и база изменяется при наступлении каждого последующего периода выплат (сложные). При использовании сложных процентов сумма задолженности (прибыли) увеличивается быстрее при одинаковых сумме и периоде кредитования, в сравнении с применением простых процентов (особенно, если периодов начисления процентов (капитализации) достаточно много.
  • Для получения результата в формате процентов необходимо установить соответствующий формат данных в ячейке, в которой будет введена функция ЭФФЕКТ.
Ссылка на основную публикацию
Adblock detector