Функция БС или как рассчитать в Excel сложный процент?

Функция БС или как рассчитать в Excel сложный процент? Вклады Газпромбанк

Аннуитет

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

Аннуитет. Определяем в MS EXCEL Приведенную (Текущую) стоимость

, где приведен расчет с помощью функции

ПС()

, а также вывод альтернативной формулы).

Здесь разберем другую задачу (см.

файл примера

):


Клиент открыл вклад на срок 1 год под ставку 12% годовых с ежемесячным начислением процентов в конце месяца. Клиент также в конце каждого месяца вносит дополнительные взносы в размере 20000р. Стоимость вклада в конце срока достигла 1000000р. Какова первоначальная сумма вклада?

Решение может быть найдено с помощью функции

ПС()

:

=ПС(12%/12;12;20000;-1000000;0)

= 662 347,68р.

Аргумент

Ставка

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

Кпер

– это количество периодов, т.е. 12 (месяцев), т.к. клиент открыл вклад на 1 год.

Аргумент

Плт

– это 20000р., т.е. величина дополнительных взносов. Аргумент

Бс

– это -1000000р., т.е. будущая стоимость вклада. Знак минус указывает на направление денежных потоков: дополнительные взносы и первоначальная сумма вклада одного знака, т.к. клиент

перечисляет

эти средства банку, а будущую сумму вклада клиент

получит

от банка.

Это очень важное замечание касается всех

функций аннуитета

, т.к. в противном случае можно получить некорректный результат. Результат функции

ПС()

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

В этом можно убедиться подсчитав Приведенную стоимость дополнительных взносов. Всего дополнительных взносов было 12, общая сумма 20000р.*12=240000р. Понятно, что при действующей ставке 12% их Приведенная стоимость будет меньше

=ПС(12%/12;12;20000)

= -225 101,55р. (с точностью до знака). Т.к.

эти 12 платежей, сделанные в разные периоды времени, эквивалентны 225 101,55р. на момент открытия вклада, то их можно прибавить к рассчитанной нами первоначальной сумме вклада 662 347,68р. и подсчитать их общую Будущую стоимость

= БС(12%/12;12;; 225 101,55 662 347,68)

= -1000000,0р., что и требовалось доказать.

Правильный калькулятор вклада с капитализацией

напечатать

10 авг. 2021

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

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

Преимущества использования
калькулятора для оценки прибыльности вклада:

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

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

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

Общий алгоритм вычисления:

1. В форму ввести основные
параметры вклада:

Функция БС или как рассчитать в Excel сложный процент?

2. Определить порядок
начисления процентов, т.е. периодичность капитализации, и возможность внесения средств на счет вклада.

Функция БС или как рассчитать в Excel сложный процент?

3. Нажать кнопку «Рассчитать»
и оценить результат.

Функция БС или как рассчитать в Excel сложный процент?

Вывод: если
вкладчик разместит на депозитный счет в банке 50 000 р. с капитализацией под
13% годовых, то его доход за полгода составит 3360 р.

Читайте также:  Аннуитет. Расчет периодического платежа в EXCEL. Срочный вклад. Примеры и описание

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

Функция БС или как рассчитать в Excel сложный процент?Важно! Доходы по ставке более 15,5% подлежат налогообложению в размере 35%. В некоторых программах предусмотрена опция расчета суммы, которая облагается
налогом, и размера удержанной прибыли с депозита.

При размещении вклада с
капитализацией на 6 месяцев в размере 50 000 р., но по ставке 17%, доход
составит 4290 р., 136 р. – сумма удержанных налогов.

Функция БС или как рассчитать в Excel сложный процент?

Узнать итоговую сумму
прибыли по депозиту с капитализацией получится с помощью табличного редактора MS Excel.

Пример расчета депозита на 50 000
р. со сроком 6 мес. по ставке 13%. Нужно:

  1. Открыть программу Excel и активировать любую ячейку.
  2. Перейти в закладки «Формулы»/«Финансовые».
  3. Из списка выбрать формулу
    «БС» – будущая стоимость.

Функция БС или как рассчитать в Excel сложный процент?

4. В открывшейся форме
ввести данные депозита:

  • Ставка –
    отображается в виде десятичной дроби из расчета на 1 месяц, то есть – 0,13/12;
  • КПер – количество
    месяцев (периодов капитализации) – 6;
  • Пс – сумма депозита: 50 000.

Функция БС или как рассчитать в Excel сложный процент?

Редактор автоматически
отображает общую сумму инвестиций на конец периода: вклад начисленные
проценты.

Из расчета видно, что
вкладчик за полгода получит прибыть с депозита в размере 3339 р. Погрешность
результата по сравнению с вычислением калькулятором-онлайн составляет 21 р. –
то есть менее 1%.

Недостаток использования
табличного редактора – программа не рассчитывает налог на доход от вклада, ставка
по которому превышает 15,5%.

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

  • ежемесячная;
  • ежедневная;
  • ежеквартальная;
  • ежегодная.

Наиболее распространен первый вариант – начисление сложных процентов ежемесячно. В этом случае
применяется формула:

Функция БС или как рассчитать в Excel сложный процент?

,

где:

  • Сумма вклада – размер сбережений,
    размещенных на депозитном счете;
  • Период – количество дней в
    расчетном периоде;
  • Ставка – годовой процент;
  • n – количество периодов капитализации за весь
    срок вклада.

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

Пример расчета. Параметры депозита: 

Алгоритм вычислений:

  1. Доход за первый месяц = 50000*30*13/(100*365)=534,24
    р.
  2. Доход за второй месяц = (50000 534,24)*29*13/(100*365)=521,95
    р.
  3. Доход за третий месяц = (50534,24 521,95)*30*13/(100*365)=545,53
    р.
  4. Доход за четвертый месяц = (51056,19 545,53)*28*13/(100*365)=551,36
    р.
  5. Доход за пятый месяц = (51601,72 551,36)*30*13/(100*365)=557,25
    р.
  6. Доход за шестой месяц = (52153,08 557,25)*28*13/(100*365)=525,66
    р.
  7. Общая сумма начисленных процентов =
    534,24 521,95 545,53 551,36 557,25 525,66=3235,99 р.

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

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

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

Сопоставление доходности
вкладов Байкал Банка. Нужно:

  1. Открыть Калькулятор
    вкладов.
  2. Задать интересующие вас параметры. Например, 100 тыс. р. на срок до 400 дней включительно. Основное условие –
    ежемесячная капитализация процентов. 
  3. Нажать «Рассчитать доход».

Функция БС или как рассчитать в Excel сложный процент?

4. Оценить результат.

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

На размер доходности вклада
оказывают влияние следующие критерии:

Читайте также:  Депозитный калькулятор вкладов в Excel. Расчет вклада в Excel.

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

Если вкладчик еще не
определился с банком, то возможно воспользоваться одной из программ: Функция БС или как рассчитать в Excel сложный процент?

1. Калькулятор вклада инфопортала о банках. Сервис имеет несколько плюсов:

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

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

3. Депозитные калькуляторы банка Россия.

4. Калькулятор вклада АК Барс Банка.

5. Инвестиционный калькулятор Бизнес журнала с отображением динамики роста начисляемых процентов. 

Рассчет суммы наращения

Рассчитаем сумму наращения при постоянных начислениях. Для этого выведем соответствующую формулу. В статье

Сложные проценты в MS EXCEL. Постоянная ставка

приведена формула наращения для сложных процентов при капитализации m раз в год в течение n лет. S = Р*(1 i/m)^(n*m) i/m – это ставка за период.

Увеличение частоты начисления процентов приводит к росту годового множителя наращения, который при непрерывном начислении процентов (m→∞) достигает своего предельного значения:

Отсюда можно записать формулу наращенной суммы для n лет:

Ставку непрерывных процентов называют силой роста (force of interest) и обозначают символом
Функция БС или как рассчитать в Excel сложный процент?
Примечание

:

эффективная годовая процентная ставка

при непрерывном начислении =
Функция БС или как рассчитать в Excel сложный процент?Функция БС или как рассчитать в Excel сложный процент?Функция БС или как рассчитать в Excel сложный процент?


Пример

. Определим будущую стоимость инвестиции объемом 150 000 руб., вложенную под 10% годовых, проценты начисляются непрерывно на протяжении 5 лет. Решение:

=150000*EXP(0,1*5)

Эта формула вернет значение 247308,19 руб., что всего на 0,007% больше по сравнению с аналогичной инвестицией, но с ежедневным начислением (см.

файл примера

) по формуле

=150000*(1 0,1/365)^(365*5)

.

Расчет прибыли от депозита с ежемесячными начислениями в excel

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

Рассмотрим эту схему на предыдущем примере: вклад 10 тыс. у.е., 5% годовых, срок – 6 месяцев. В первом месяце дивиденды составят 10 000 * 5% /12 = 41,67 у.е. Во втором месяце начисления уже будут такими: (10 000 41,67) * 5% / 12 = 41,84 у.е.

Я предлагаю пользоваться функцией Эксель для расчета будущей стоимости вклада: =БС(Ставка; Кпер; Плт; [Пс]; [Тип]). Здесь такие аргументы:

  • Ставка – Процентная ставка на один период
  • Кпер – общее количество периодов
  • Плт – ежемесячный взнос (если есть), указываем отрицательным
  • Пс – первый взнос (если есть), указываем отрицательным
  • Тип – тип начисления процентов: «0», если начисляются в конце периода, «1» — в начале периода

Давайте применим функцию для нашего вымышленного депозита: =БС(5%/12;6;;-10000). Получим 10 252,62 у.е., т.е. на 2,62 у.е. больше, чем в первой схеме начислений.

Сумма депозита с ежемесячными начислениями
Сумма депозита с ежемесячными начислениями

Расчеты для вклада без капитализации

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

  1. Период с 1.02.13 по 28.02.13
  1. Период с 1.03.13 по 31.03.13 делим на два периода до пополнения и после пополнения:

1.03-6.03 рассчитываем сумму процентов до пополнения:
Функция БС или как рассчитать в Excel сложный процент?
7.03-31.03 рассчитываем сумму процентов после пополнения на сумму 5000 руб.

Сумма процентов:

  1. Период с 1.04.13 по 30.04.13 делим на два периода до пополнения и после пополнения:

1.04-27.04 рассчитываем сумму процентов до пополнения:

28.04-30.04 рассчитываем сумму процентов после пополнения на сумму 10 000 руб.
Функция БС или как рассчитать в Excel сложный процент?
Функция БС или как рассчитать в Excel сложный процент?
Функция БС или как рассчитать в Excel сложный процент?

  1. Период с 1.05.13 по 31.05.13
  1. Период с 1.06.13 по 30.06.13 делим на два периода до снятия и после снятия:
Читайте также:  Калькулятор вклада онлайн — рассчитать депозит с капитализацией и пополнением в 2021 – 2022 году

1.06-13.06 рассчитываем сумму процентов до снятия:
Функция БС или как рассчитать в Excel сложный процент?

14.06-30.06 рассчитываем сумму процентов после снятия на сумму 7000 руб.

Функция БС или как рассчитать в Excel сложный процент?
Сумма процентов:
Функция БС или как рассчитать в Excel сложный процент?
Сумма процентов:
Функция БС или как рассчитать в Excel сложный процент?

  1. Период с 1.07.13 по 31.07.13
  1. Период с 1.08.13 по 31.08.13 делим на два периода до снятия и после снятия:

В период 1.08-20.08 рассчитываем сумму процентов до снятия:
Функция БС или как рассчитать в Excel сложный процент?
21.08-31.08 рассчитываем сумму процентов после снятия на сумму 4000 руб.Сумма процентов:
Функция БС или как рассчитать в Excel сложный процент?
21.08-31.08 рассчитываем сумму процентов после снятия на сумму 4000 руб.Сумма процентов:
Функция БС или как рассчитать в Excel сложный процент?

  1. Период с 1.09.13 по 30.09.13
  1. Период с 1.10.13 по 31.10.13 делим на два периода до пополнения и после пополнения:

1.10-16.10 рассчитываем сумму процентов до пополнения:

17.10-31.10 рассчитываем сумму процентов после пополнения на сумму 14 000 руб.

Сумма процентов:
Функция БС или как рассчитать в Excel сложный процент?

  1. Период с 1.11.13 по 31.01.14
  1. Общая сумма процентов равна:
  1. Сумма к получению по окончании срока вклада:

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

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

Расчеты с капитализацией по вкладу.

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

  1. Период с 1.02.13 по 28.02.13
  1. Период с 1.03.13 по 31.03.13 делим на два периода до пополнения и после пополнения:

За период 1.03 — 6.03 рассчитываем сумму процентов до пополнения:
Функция БС или как рассчитать в Excel сложный процент?

7.03-31.03 рассчитываем сумму процентов после пополнения на сумму 5000 руб.

Таким образом, сумма денежных средств на вкладе в конце данного периода будет следующей:

  1. Период с 1.04.13 по 30.04.13 делим на два периода до пополнения и после пополнения:

1.04-27.04 рассчитываем сумму процентов до пополнения:

28.04-30.04 рассчитываем сумму процентов после пополнения на сумму 10 000 руб.

Сумма денежных средств на вкладе:

  1. Период с 1.05.13 по 31.05.13
  1. Период с 1.06.13 по 30.06.13 делим на два периода до снятия и после снятия:

1.06-13.06 рассчитываем сумму процентов до снятия, с учетом того, что сумма по вкладу будет одна:

14.06-30.06 рассчитываем сумму процентов после снятия на сумму 7000 руб. После снятия сумма на счете по вкладу пополняй уменьшится на 7 тыс. рублей, соответственно проценты за эти дни будут меньше

Сумма денежных средств на вкладе:

  1. Период с 1.07.13 по 31.07.13
  1. Период с 1.08.13 по 31.08.13 делим на два периода до снятия и после снятия:

1.08-20.08 рассчитываем сумму процентов до снятия:
Функция БС или как рассчитать в Excel сложный процент?

21.08-31.08 рассчитываем сумму процентов после снятия на сумму 4000 руб.

Теперь рассчитываем снова сумму денежных средств на депозите

  1. Период с 1.09.13 по 30.09.13
  1. Период с 1.10.13 по 31.10.13 делим на два периода до пополнения и после пополнения:

1.10-16.10 рассчитываем сумму процентов до пополнения:

17.10-31.10 рассчитываем сумму процентов после пополнения на сумму 14 000 руб.

Сумма денежных средств на вкладе:

  1.  Период с 1.11.13 по 30.11.13
  1.  Период с 1.12.2021 по 31.12.2021
  1.  Период 1.01.2021 по 31.01.2021

 Общая сумма процентов по вкладу:
Она равна пополнения-снятия сумма вклада и плюс начисленные проценты.
Функция БС или как рассчитать в Excel сложный процент?

  1.  Сумма к получению по окончании срока вклада:

Функция БС или как рассчитать в Excel сложный процент?
Таким образом, в данном случае для вклада с капитализацией процентов, сроком на 1 год, с тремя пополнениями и с двумя снятиями сумма денежных средств на вкладе будет составлять 176771.74 руб.

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

Оцените статью
Adblock
detector