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

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

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

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

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

Расчет составных процентов за внутригодный период в 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

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

A = P(1 + r/n) нт

• A: Окончательная сумма
• P: Начальный основной
• r: Годовая процентная ставка
• n: количество периодов начисления сложных процентов в год.
• т: количество лет

Если инвестиции начисляются ежедневно , то мы можем использовать 365 для n :

А = Р(1 + г/365) 365t

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

Пример: ежедневная формула сложных процентов в Excel

Предположим, мы инвестируем 5000 долларов в инвестиции, которые начисляются по ставке 6% в год. Предположим, что инвестиции складываются ежедневно.

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

Эта инвестиция будет стоить 9 110,14 долларов США через 10 лет.

На следующем снимке экрана показано, как рассчитать конечные инвестиции после каждого года в течение 10-летнего периода.

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

Из вывода мы видим:

• В конце первого года инвестиции составляют 5 309,16 долларов США .
• В конце 2-го года инвестиции составляют 5 637,43 доллара .
• В конце 3-го года инвестиции составляют 5 986,00 долларов США .

А к концу 10 года инвестиции составляют 9 110,14 долларов .

Чтобы визуализировать рост инвестиций с течением времени, просто выделите ячейки в диапазоне E2:E11 , затем щелкните вкладку « Вставка » на верхней ленте, затем выберите параметр « Двухмерная столбчатая диаграмма » в группе « Диаграммы »:

Ось X показывает год, а высота столбцов представляет собой стоимость инвестиций в конце каждого года.

Дополнительные ресурсы

В следующих руководствах объясняется, как выполнять другие распространенные задачи в Excel:

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

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

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

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

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

Как работает сложный процент?

Чтобы понять, как работают сложные проценты, мы должны сначала объяснить, что такое простые проценты. Простые проценты рассчитываются на основе первоначальной суммы денег, которую вы сэкономили, иначе называемой суммой накопления основная сумма . Чтобы продемонстрировать это, вы можете положить $20 на сберегательный счет в банке. Если вы получаете проценты по ставке 5%, то через год ваш вклад будет стоить 20 x 1.05, оставив вам дополнительно $1.00.

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

В нашем исходном примере банк добавил бы $0.50 процентов, которые вы получаете каждый год к первоначальной основной сумме $20. Через два года периодов компаундирования при процентной ставке 5% ваш депозит будет стоить $21.53 и будет продолжать расти с той же скоростью.

Как рассчитать сложные проценты с помощью Excel

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

Шаг 1: Настройте рабочий лист

Цель расчета сложных процентов заключается в том, чтобы иметь возможность спрогнозировать общую сумму инвестиций с учетом сложных процентов в течение нескольких лет. Чтобы правильно настроить рабочий лист для формулы процентов, вам нужно начать с создания строки данных. Столбец A представляет собой первоначальные инвестиции для вашей строки данных. Колонка B — процентная ставка. Между тем, столбцы C, D и E отражают остатки за год 1, год 2 и год 3.

В этом примере начальные инвестиции составляют 100 долларов, а процентная ставка — 8% процентов. Поэтому в первой ячейке вашей строки в столбце А будет написано $100. Для простоты будем считать, что в A2. Во второй ячейке в строке, столбце B, будет написано 8% в B2. Колонки C, D и E останутся пустыми, пока мы не закончим вычисления.

Шаг 2: Создайте формулу в C2, которая рассчитывает простые проценты

В первый год проценты просто рассчитываются путем умножения инвестиции на процент, чтобы найти сумму процентов, начисленных в первый год, и затем добавить ее к инвестиции. Об этом можно позаботиться, запрограммировав ячейку Год 1 уравнением =A2 x 1.08. Здесь рассчитаны простые проценты, начисляемые в первый год. Результат формулы составляет $101.80, или может выглядеть как 101.8, если вы не отформатировали ячейку для отображения денежных значений.

Шаг 3: Рассчитайте сложные проценты для последующих лет

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

Давайте представим, что это происходит в ячейке D2, которая помогает завершить ряд данных, который мы собирались создать в самом начале. В ячейке D2 для расчета сложных процентов нужно ввести = C2 x 1.08. В данном случае ячейка C2 содержит значение $101.80, поэтому рабочая таблица Excel вычислит $101.80 x 1.08 и отобразит $109.17.

Следуя этой же логике, вы можете создать формулу для расчета процентов за 3-й год в ячейке E2, = D2 x 1.08. На выходе этого уравнения будет выведено $117.90. В последующие годы при экспоненциальном росте благодаря сложным процентам инвестор получил 17 долларов.90.

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

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

Преимущества

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

Недостатки

Недостатки сложных процентов в основном относятся к процентам, начисляемым по займам. Когда вы берете кредит, велика вероятность того, что вы будете выплачивать сложные проценты до тех пор, пока ваш долг не будет погашен. Даже если вы планировали выплачивать минимальные платежи, вы можете не справиться с увеличением суммы, если проценты будут регулярно начисляться. Лучший способ избежать этой проблемы — платить немного больше минимального платежа каждый месяц, чтобы компенсировать проценты, которые будут продолжать начисляться.

Пример сложного процента в Excel

Ниже приведен пример сложного процента в Excel.

Happy Corp инвестировала $1 800 в одноранговый кредит, который приносит 12% годовых. Исходя из приведенного выше примера, ваш рабочий лист изначально будет выглядеть так, потому что мы еще не начали рассчитывать проценты:

Инвестиции Процентная ставка Год 1 Год 2 Год 3

$1800	12%

Чтобы найти сложные проценты по этой инвестиции, нам нужно сначала рассчитать простые проценты за первый год, введя следующую формулу в ячейку Год 1 . Для простоты в данном примере эта строка представляет собой 2-ю строку электронной таблицы, а заголовки столбцов начинаются с Инвестиции в столбце А. Таким образом, значение $1 800 появляется в ячейке A2.

Инвестиция Процентная ставка Год 1 Год 2 Год 3

$1800	12%	$1800*12

После расчета этот показатель будет выглядеть следующим образом. В ячейках D2 и E2, или Год 2 и Год 3, вам нужно будет выполнить тот же процесс, основываясь на значении процентов за предыдущий год:

Инвестиции Процентная ставка Год 1 Год 2 Год 3

$1800	12%	$1800*12	C2*12	D2*12

Ключевые слова:

• indeed.com