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

Депозитный калькулятор вкладов в Excel. Расчет вклада в Excel. Вклады Газпромбанк

Вычисление суммы процентов, которую необходимо выплатить в промежутке между 2-мя периодами

Способ 1. Функция ОБЩПЛАТ()

Функция ОБЩПЛАТ(ставка; кол_пер; нз; нач_период; кон_период; тип)

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

нач_период

и

кон_период

).


Примечание

.

Английская версия: CUMIPMT(rate, nper, pv, start_period, end_period, type) returns the CUMulative Interest paid on a loan between start_period and end_period.

Аргументы функции: Ставка – обязательный аргумент. Процентная ставка за период. Кол_пер – обязательный аргумент (кол_пер – это аргумент кпер в других функциях аннуитета, например в

ПЛТ()

). Общее количество периодов выплат.

Нз – обязательный аргумент (нз – это аргумент пс в других функциях аннуитета, например в

ПЛТ()

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

Примечание

. Убедитесь, что аргумент “Ставка” соответствуют ставке за период (период не обязательно = году). Например, при ежемесячных выплатах по 4-х летнему займу из расчета 12 процентов годовых используйте значение 12%/12 для аргумента “Ставка”.

Аргумент “Кол_пер” будет равен 4*12. При ежегодных платежах по тому же займу используйте значение 12% для аргумента “ставка” и 4 — для аргумента “Кол_пер”. При ежеквартальных платежах по тому же займу используйте значение 12%/4 для аргумента “ставка” и 4*4 — для аргумента “Кол_пер”.

Примечание

.

Функция

ОБЩПЛАТ()

возвращает значение ошибки #ЧИСЛО! если: “Ставка” ≤ 0, “кол_пер” ≤ 0 или “нз” ≤ 0, “нач_период” < 1, “кон_период” < 1 “нач_период” > “кон_период” “тип” является любым числом, отличным от 0 и 1

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

Из анализа альтернативной формулы ясно, что функция

ОБЩПЛАТ()

может использоваться, только если БС=0, т.е. когда предполагается, что по прошествии количества периодов “Кол_пер” займ полностью погашается.

Также обратите внимание, что в определении функции

ОБЩПЛАТ()

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

ОБЩПЛАТ()

не получится (для этого см. Способ 2).


Способ 2. Функция ПРПЛТ()

Примечание

.

Английский вариант функции: IPMT(rate, per, nper, pv, [fv], [type]), т.е. Interest Payment – выплата процентов.

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

ПРПЛТ()

. Например, вычислим сумму долга, выплаченную в 3-м и 4-м периоде: =ПРПЛТ(ставка; 3; кпер; пс; [бс]; [тип]) ПРПЛТ(ставка; 4; кпер; пс; [бс]; [тип])

Выражение СТРОКА(ДВССЫЛ(нач_период&”:”&кон_период)) создает

массив последовательных чисел

, например 2:3:4:5 (если нач_период=2 и кон_период=5). Функция

Читайте также:  Какие формулы помогают считать деньги в эксель-таблице.

СУММПРОИЗВ()

суммирует результаты

ПРПЛТ()

, т.е. вышеуказанная формула эквивалентна формуле:

Использование эффективной ставки для сравнения кредитных договоров с разными схемами погашения

Представим себе ситуацию, когда в 2-х разных банках нам предлагают взять в кредит одинаковую сумму на одинаковых условиях, но выплата кредита в одном будет осуществляться

дифференцированными платежами

, а в другом по

аннуитетной схеме

(равновеликими платежами).

В

файле примера на листе

Сравнение схем погашения (1год)

приведен расчет для 2-х различных графиков погашения (сумма кредита 250 т.р., срок =1 год, выплаты производятся ежемесячно, ставка = 15%).

В случае дифференцированных платежей Эффективная ставка по кредиту = 16,243%, а в случае аннуитета – 16,238%. Разница незначительная, чтобы на ее основании принимать решение. Необходимо определиться какой график погашения больше Вам подходит.

При увеличении срока кредита разница между Эффективными ставками практически не изменяется (см.

файл примера Лист

Сравнение схем погашения (5лет)

).


Примечание

.

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

ЭФФЕКТ()

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

Примечание

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

приведено в этой статье

.

Примечание.

Эффективную ставку по кредиту можно рассчитать и без функции

ЧИСТВНДОХ()

– с помощью Подбора параметра. Для этого в

файле примера на Листе

Кредит

создан столбец I (Дисконтированный денежный поток (для Подбора параметра)). В окне инструмента

Подбор параметра

введите значения указанные на рисунке ниже.

После нажатия кнопки ОК, в ячейке

I18

будет рассчитана Эффективная ставка совпадающая, естественно, с результатом формулы

ЧИСТВНДОХ()

.

Как всё посчитать

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

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

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

Главное — не перепутать местоположение ячеек: все значения остаются в одних и тех же строках

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

напечатать

10 авг. 2021

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

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

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

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

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

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

Читайте также:  Чистая текущая стоимость (NPV)

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

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

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

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

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

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

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

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

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

Функция БС или как рассчитать в 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 – количество периодов капитализации за весь
    срок вклада.

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

Читайте также:  Пример функции ПЛТ для расчета аннуитетного платежа в Excel

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

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

  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 сложный процент?

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. Расчет вклада в Excel.

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

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

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

Функция БС или как рассчитать в Excel сложный процент?
Сумма процентов:
Функция БС или как рассчитать в 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. Расчет вклада в 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 руб.

Сумма процентов:
Функция БС или как рассчитать в 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 руб.

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

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

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