Сложные проценты в эксель. Два простых способа сделать формулу расчета сложных процентов в Excel.

Многие, наверное, слышали про проценты, которые начисляют в банке на вложенный депозит.

Как говорил А. Энштейн: «Сложные проценты – самая мощная сила в природе». Разберем более подробно виды и механизмы начисления процентов.

Перед тем как приступить к вычислению процентов введем базовые понятия. Проценты начисляются на капитал и его, как правило, обозначают буквой P, от английского слова principal. Следующим понятием является частота начисления процентов - это отчетный временной период, в который банк увеличивает капитал вкладчика. Процентная ставка – величина самого процента, обозначается латинской буквой r от английского слова rate. Период вложения – время, на которое был сделан вклад в банк.

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

• Эффективные проценты
• Проценты на проценты
• Композиционный процент
• с учетом капитализации
• Норма доходности с учетом реинвестирования

Рассчитаем сложные и простые проценты на реальном примере:
Предположим, есть два брата, и у обоих на руках есть первоначальный капитал в размере 10000 рублей. Они решают сделать вклад на 20 лет, первый брат делает вклад на депозитный счет с простой процентной ставкой 9%, а второй делает вклад на счет со сложной процентной ставкой 8%. Начисление процентов происходит в конце каждого года. И так имеем:
P 0 =10000 руб;
r 1 =9%;
r 2 =7%;
t=20 лет.
Осуществим необходимые . Формула расчета простых процентов следующая:

Где:
P nt – капитала на конец n-го периода;
n – количество периодов начисления процентов;
P 0 - начальный капитал;
r - банковская процентная ставка.

Формула расчета сложных процентов следующая:

Где:
t – число реинвестиций (капитализаций) в течение одного года.

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

Если сравнить динамику роста, то можно заметить, что первые года вклад с простыми процентами превосходил вклад со сложными процентами, но к концу 20-го года капитал первого брата увеличился на 280%, тогда как рост депозита второго брата составил 466%. На рисунке, ниже показан рост капитала при различных видах процента.

Что бы получить высокую доходность при сложном проценте необходимо ждать длительное время, но так как российскую экономику постоянно штормит (например: кризис 1998 г., кризис 2008 г.) то о долгосрочных вложениях думать не приходится. Помимо кризисов и других экономических потрясений, ежегодный рост инфляции довольно высок и прибыль, полученная при помощи сложных процентов сводиться к нулю.

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

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

Автор : Жданов Иван
©

Предположим, у вас на счету 1000 долларов США. Процентная ставка за год - 8 процентов. Как вырастет сумма за три года? Есть два способа определить это.

Использование фиксированной формулы

Введя следующую формулу в ячейке на листе, вы получите нужную величину - 1259,71:

При этом все данные зафиксированы в формуле, и при изменении исходных данных формулу придется переписывать.

Создание макроса для вычисления сложных процентов

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

1. Запустите Microsoft Excel.
2. Нажмите клавиши ALT+F11, чтобы запустить редактор Visual Basic.
3. В меню Вставка выберите команду Модуль .
4. Наберите в новом модуле следующий код:

   Function Yearly_Rate(PV As Double, R As Double, N As Double) As Double

   Yearly_Rate = PV*(1+R)^N "Вычисление

   End Function
   

Для использования функции выполните следующие действия.

1. Введите на листе следующие значения:
   Ячейка Значение
   --------------
   A1 1000.00
   A2 .08
   A3 3.00
   Они соответствуют следующему.
   • A1: Текущая сумма на счете.
   • A2: Процентная ставка.
   • A3: Число периодов.
2. В пустой ячейке введите формулу

   Yearly_Rate(A1,A2,A3)

   Где A1, A2 и A3 - это соответствующие ячейки.

В ячейке появится значение 1259,71 . Это сумма, до которой увеличится исходная сумма в 1000 долларов США по прошествии трех лет, если годовая процентная ставка составляет 8 процентов.

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

За расчет потенциальной доходности в Microsoft Excel отвечает специальная функция БС (Будущая Стоимость (Future Value (FV) — о ней мы говорили ). Для того, чтобы ее вызвать, нажмите на символ f x , слева от строки ввода значений и адресов ячеек.

В открывшемся Мастере функций в строке поиска функций введите БС и нажмите Ввод. Кликните мышью на подсвеченной синим цветом строке БС, как показано ниже.

Составляющим формулы расчета будущей стоимости FV = PV(1+r) n в Excel соответствуют следующие функции:

Заполняем (вручную или указав адреса соответствующих ячеек ) поля данными из нашего примера. Напомню, что мы решили открыть депозит, разместив на нем 10 000 рублей сроком 5 лет и под 10% годовых.

Ставку по вкладу указываем в виде десятичной дроби, т.е. 10% превратятся в 0,1. В Кпер ставим количество лет — у нас вклад на 5 лет, значит 5. Поле Плт оставляем пустым. В поле ПС начальную сумму вклада указываем со знаком «минус», т.к. мы эти деньги отдаем, а не получаем.

Поле Тип заполняем с учетом того, как производится выплата процентов по нашему вкладу:

• если в конце срока (на языке финансистов такой поток платежей называется постнумерандо ), то ставим «0» или оставляем поле пустым;
• если в начале срока (на языке финансистов такой поток платежей называется пренумерандо ), то ставим «1».

В случае если проценты по вкладу начисляются ежемесячно или ежеквартально, то в поле Ставка годовую процентную ставку следует разделить на 12 или 4 соответственно в виде десятичной дроби. Вместе с этим нужно внести изменения в Кпер, пересчитав количество выплат: при ежемесячном начислении в течение 5 лет ставим 60 (12 мес. х 5 лет ); при квартальном — 20 (4 кв. х 5 лет ).

А теперь: внимание — вопрос. Как изменится доходность нашего вклада в случае начисления банком сложных процентов в конце каждого месяца, а не года, как мы считали до этого, на протяжении 5 лет? Давайте посмотрим. Напомню, до этого у нас получалась сумма в размере 16 105 руб. Заполняем поля и нажимаем «ОК».

Получаем 16 453 рубля. Как видите, разница 343 рубля. А главное: чем больше сумма вашего вклада и время его размещения, тем ощутимей будет прибавка. Такова магия сложных процентов. Отсюда — вывод. Проценты по вашему вкладу должны:

• капитализироваться;
• капитализироваться ежемесячно.

Чем чаще начисляются проценты и добавляются к сумме вашего вклада, тем лучше работают ваши деньги. Кстати, хотите узнать, как скоро ваш вклад удвоится? Нет ничего проще. Воспользуйтесь правилом 72 .

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

А сейчас (барабанная дробь ) испытайте чувство гордости за себя. Потому что теперь вы можете рассчитать это в Excel. Для этого вызовите функцию Кпер, заполните данные из нашего примера (10% годовых, 5 лет, выплата процентов в конце года ) и добавьте в поле БС ожидаемую сумму вклада в размере 20 000 руб. (10 000 руб. х 2 ). Вуаля!

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

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

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

Оксана Гафаити,
Первая русская женщина, торгующая Америку.

class="eliadunit">

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

Итак, представим такую обычную ситуацию. У нас есть денежные средства, например 1 000 $ и нами было принято решение отнести эту сумму в банк. Он предлагает следующие условия: берет у нас деньги на 1 год под процентную ставку 12% годовых при ежемесячной капитализации, т.е. начисление процентов происходит каждый месяц, но их вычисление происходит каждый день.

Сколько Вы думаете, у нас накопится денег через 1 год? Некоторые могут моментально ответить 1 120 $ (1000*12%=1120$) Это неправильный ответ. Другие будут ссылаться на ежемесячную капитализацию, т.е. в их понимании в первый месяц будет начислено 10$ (1000*1%), во второй 10,1 (1010*1%) и так далее. При таком подсчёте, который уже изображен на картинке получается 1126,8 $. Но это тоже не точный ответ.

Для продвинутых и любознательных, которые хотят просмотреть формулы, можно открыть / скачать Excel-файл . Всё дело в том, что процент от суммы высчитывается в банке, как правило, каждый день, а не каждый месяц. И рассчитать итоговую сумму таким методом, который указан на картинке, крайне нерационально, но можно. У нас в таком случае получится 365/366 заполненных колонок, что выглядит очень громоздко. А если нужно просчитать сумму вклада за большой период, на 5 или 10 лет!? Всё это решается в одной ячейке, при помощи функции БС .

Начинаем разбираться, как ей пользоваться. Итак, заходим в Microsoft Excel вводим формулу =БС и далее рекомендуется нажать Аргументы функции.

Вот теперь начнём заполнять поля исходя из нашего примера.

class="eliadunit">

Первое поле Ставка . В нашем примере сюда вписываем 12%/365, 12 процентов – это годовая ставка, а 365 количество дней, периодов по которым считается процент. Если процент высчитывается (не путать с выплатами) один раз в месяц, то делить придётся на 12 (месяцев).

Рассчитаем сложные и простые проценты на примере:
Предположим, есть два брата, и у обоих на руках есть первоначальный капитал в размере 10000 рублей. Они решают сделать вклад на 20 лет, первый брат делает вклад на депозитный счет с простой процентной ставкой 9%, а второй делает вклад на счет со сложной процентной ставкой 8%. Начисление процентов происходит в конце каждого года. Итак имеем:
P 0 =10000 руб;
r 1 =9%;
r 2 =8%;
t=20 лет.
Осуществим необходимые расчеты в Excel.

Формула расчета простых процентов следующая:

Где:
P nt – капитала на конец n-го периода;
n – количество периодов начисления процентов;
P 0 – начальный капитал;
r – банковская процентная ставка.

Формула расчета сложных процентов следующая:

Где:
t – число реинвестиций (капитализаций) в течение одного года.

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

Если сравнить динамику роста, то можно заметить, что первые года вклад с простыми процентами превосходил вклад со сложными процентами, но к концу 20-го года капитал первого брата увеличился на 280%, тогда как рост депозита второго брата составил 466%. На рисунке выше показан рост капитала при различных видах процента .

Что бы получить высокую доходность при сложном проценте необходимо ждать длительное время, но так как российскую экономику постоянно штормит (например: кризис 1998 г., кризис 2008 г.) то о долгосрочных вложениях думать не приходится. Помимо кризисов и других экономических потрясений, ежегодный рост инфляции довольно высок и прибыль, полученная при помощи сложных процентов сводиться к нулю.

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

Автор : Жданов Иван

Стоимость инвестиций при начислении сложных процентов. Расчет в Excel

Простые и сложные проценты. Варианты расчета при различных ситуациях (11 листов в Excel) :

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

Для скачивания excel-файла необходима регистрация