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

А что если первоначальная сумма не нулевая

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

В этой формуле А — первоначальная сумма вклада, а вторая часть слагаемого — это формула сложных процентов(процент с капитализацией)

Уже из этой форумлы нужно выразить PMT — ежемесячный взнос. Но это уже дело математики вам нужно постараться самому(ой). Если не получится, пишите в комментариях, я ее приведу.

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

Что такое простой и сложный проценти чем они отличаются

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

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

Допустим, вы открыли депозит 10000$ под 10% годовых, проценты начисляются раз в год. По схеме простого процента каждые 12 месяцев вы будете получать 1000$ прибыли, но она не остаётся на депозите и сразу же выводится. В итоге прирост прибыли будет выглядеть так:

Всё «просто» — каждый год плюс тысяча в карман. Простой процент используется в случаях, когда база начисления процентов не изменяется. Это могут быть специальные банковские депозиты, проценты по кредиту. Также простой процент используется, когда инвестор регулярно выводит прибыль — в каждый период времени работает первоначальная сумма.

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

Для сравнения пусть будет тот же депозит 10000$ под 10%, но банк в этот раз разрешает оставить прибыль на счёте. Вот что произойдёт с вкладом за 10 лет:

В первый год разницы нет — всё та же тысяча, но поскольку сумма на депозите теперь растёт, уже на втором году прибыль увеличивается: 2100$ вместо 2000$, за третий год 3310$ вместо 3000$ и так далее. За 10 лет доходность нашего депозита составила 159% вместо 100% когда мы выводили прибыль. Неплохая прибавка, не так ли? А вот что случится еще через несколько десятилетий:

Впечатляет! Чем дольше открыт депозит, тем сильнее работает эффект сложного процента — за 50 лет можно увеличить депозит не в 6, а более чем в 100 раз. Вот как это выглядит на графике:

без капитализации депозит растёт линейно, а с капитализацией — по экспоненте

Теперь киношные истории про забытые банковские счета, на которых накопились миллионы долларов выглядят вполне реальными 🙂 Конечно, 50 лет это много, но правило сложного процента неплохо работает и на более коротких промежутках времени — всё зависит от доходности вклада.

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

EFFECT (ЭФФЕКТ) — позволяет рассчитать сложный процент

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

Россия занимает деньги через множество облигаций федерального займа (ОФЗ). У каждого выпуска таких бумаг есть номинальная доходность, определяющая, какой процент годовых от вложенной суммы получит инвестор. Например, по ОФЗ 26209 обещают 7,6%, а по ОФЗ 26207 ещё больше — 8,15%.

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

Анализ пенсионных отчислений с использованием функции ставка в excel

Пример 2. Определить темпы роста пенсионных отчислений (процентную ставку), если баланс средств на конец года составляет 12000 долларов, а в начале года – 2400 долларов. Еженедельные платежи на протяжении года составляли 150 долларов (то есть, количество периодов – 52).

Исходные данные:

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

=СТАВКА(B2;B3;B4;B5;0;0,1)*52

Описание аргументов:

• B2 – количество периодов выплат;
• B3 – сумма платежа (расходная операция, поэтому отрицательное значение);
• B4 – сумма средств до наступления первого периода выплат;
• B5 – сумма по окончанию последнего периода выплат;
• 0 – выплаты в конце периода;
• 0,1 – произвольное значение из интервала от 0 до 1;
• 52 – количество периода выплат для пересчета размера ставки в годовых.

Результат вычислений:

То есть, пенсионные отчисления выполняются под 7% годовых.

Выбор формата представления для процентных ставок

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

Бухгалтер одного из предприятий при расчете начислений в один из обязательных фондов перепутал ставку 0,06% со ставкой 0,06 и в течение года перевыполнил план по данному сбору на 99 лет вперед. А по налогу на прибыль заработал пеню.

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

• Выделите форматируемую область, нажмите правую кнопку мыши и выберите в контекстном меню команду Формат ячеек.
• В диалоговом окне Формат ячеек перейдите на вкладку Число. В списке Числовые форматы выделите элемент Процентный (рис. 3.16), задайте необходимое число десятичных знаков (например, 2) и нажмите кнопку ОК.

Источник

Задача1

Требуется накопить за 5 лет сумму 1 000 000 руб. Начальная сумма вклада =0. Определить величину регулярных пополнений вклада, если процентная ставка составляет 10% годовых, пополнение вклада производится ежеквартально, капитализация процентов также производится ежеквартально. См. файл примера .

Расчет суммы регулярного пополнения вклада, произведем сначала с помощью финансовой функции MS EXCEL ПЛТ() .

Эта функция имеет такой синтаксис: ПЛТ(ставка; кпер; пс; [бс]; [тип]) PMT(rate, nper, pv, [fv], [type]) – английский вариант.

Примечание . Функция ПЛТ() входит в надстройку «Пакет анализа». Если данная функция недоступна или возвращает ошибку #ИМЯ?, то включите или установите и загрузите эту надстройку (в MS EXCEL 2007/2022 надстройка «Пакет анализа» включена по умолчанию).

Примечание . Обзор всех функций аннуитета найдете здесь .

Первый аргумент – Ставка. Это процентная ставка именно за период, т.е. в нашем случае за квартал, т.е. 10%/4 (в году 4 квартала). Кпер – общее число периодов платежей по аннуитету, т.е. 20 (4 кв. в году*5 лет) Пс — Приведенная стоимость , т.е. стоимость приведенная к текущему моменту.

В нашем случае, это начальная сумма на расчетном счету, т.е. 0. Бс — Будущая стоимость вклада в конце срока (по истечении числа периодов Кпер). Бс — требуемое значение остатка средств после последнего взноса. В нашем случае Бс = 1 000 000. Тип — число 0 или 1, обозначающее, когда должно производиться начисление %. 0 – в конце периода, 1 – в начале. Если этот параметр опущен, то он считается =0 (наш случай).

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

Если проценты начисляются в начале периода (каждого квартала), то тогда же производится пополнение вклада (аргумент ТИП=1). Т.е., в первый день первого квартала мы пополнили счет на величину регулярного взноса, но так как процент по вкладу начисляется также в первый день, то за первый квартал будет начислено 0.

Решение1 Итак, ежеквартальный платеж может быть вычислен по формуле =ПЛТ(10%/4; 5*4; 0;1000000; 0), т.е. -39147,13р. Знак минус показывает, что мы имеем разнонаправленные денежные потоки: накапливаем деньги (тем самым отнимаем их из нашего бюджета), и получаем от банка 1000000, когда забираем деньги в конце срока.

Если период начисления процентов и регулярных взносов не совпадает

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

Таблица пополнения вклада

Составим таблицу пополнения вклада.

Вклад пополняется из 2-х источников: первый – это регулярные взносы, второй – начисленные за период проценты (на накопленную к данному моменту сумму вклада). Для вычисления регулярно начисляемых процентов используется функция ПРПЛТ (ставка; период; кпер; пс; [бс]; [тип])

Таким образом, вклад регулярно пополняется на величину =-ПЛТ(10%/4; 20; 0;1000000; 0) ПРПЛТ(10%/4; период; 20; 0; 1000000; 0) , где период – это номер периода, в который требуется подсчитать величину пополнения. Тот же самый результат дает формула =-ОСПЛТ(10%/4; период; 20; 0; 1000000; 0)

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

Примечание . В статье Аннуитет. Расчет периодического платежа в MS EXCEL. Погашение ссуды (кредита, займа) показано как рассчитать величину регулярной суммы для погашения кредита или ссуды в случае применения аннуитетной схемы.

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

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

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

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

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

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

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

Даже если оставить удачный бескризисный отрезок 2022-2020 годов, доходность банковского вклада с учётом инфляции была в районе 1-2% годовых в рублях. Не говоря уже о доходности в долларах, которая после 2022 года, очевидно, находится в еще большем минусе.

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

Такое часто встречается при инвестициях в ETF, где комиссия за управление достигает несколько процентов от депозита в год. Один из самых старых ETF под тикером SPY (инвестиционная стратегия — следование за индексом S&P 500) работает с 1993 года и берет с клиентов 0.

09% в год — немного, по сравнению с другими биржевыми фондами. Эта ставка со временем может меняться, но давайте для эксперимента представим что она всегда была такой — и сравним, как будет отличаться доходность инвестиций при комиссиях от 0 до 2% в год:

Как видите, даже из-за несчастных 0.09% инвестор на дистанции 27 лет потерял 25% прибыли. А вроде бы небольшая комиссия в 2% годовых срезает доходность почти в 3 раза — с 723% до 270%, и это еще не учтена инфляция. По причине скрытых комиссий высокая доходность активов на самом деле может оказаться в разы ниже, поэтому перед принятием решения об инвестировании важно учитывать даже мизерные расходы.

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

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

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

Какие данные нужны

Формула состоит из трёх компонентов:

=ЧИСТНЗ(ставка;значения;даты)

Второй и третий достаточно ясны:

2. Значения — сколько денег потрачено на инвестиции и сколько возвращается.3. Даты — когда именно средства приходят или уходят.

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

Если инвестор хочет не просто сохранить деньги, но и заработать, ему нужно учесть постепенное обесценивание валюты. Есть много способов это сделать, но самый простой — посмотреть доходность по надёжным облигациям: к примеру, ОФЗ 26234 — 4,5%.

Калькулятор вкладов онлайн — расчет вкладов со множеством параметров.

Калькулятор вкладов поможет вам правильно выбрать вклад. Просто нужно посчитать и сравнить несколько вкладов. Доходность какого будет лучше, тот и выбрать.Если у вас один рублевый вклад, а один валютный, то придется провести конверсию по курсу ЦБ.

Также доступна онлайн версия депозитного калькулятора — калькулятор вкладов онлайн

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

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

Далее нажимаем рассчитать и получаем график платежей по вкладу и сумму к получению. Аналогично, если сумма вклада зависит от суммы — устанавливаем переключатель «Плавающая в зависимости от размера» для процентной ставки. Отдельный интерес составляет учет налогов — все вклады, ставка по которым превышает ставку рефинансирования ЦБ 5 процентов. Для них происходит начисление налога по вкладу в размере 35 процентов от налогооблогаемой части.

Определение реальной процентной ставки по кредиту

Пример 3. Ноутбук одной и той же модели можно приобрести за 1200 долларов в рассрочку (беспроцентную, судя по рекламе в первом магазине) или за 1050 долларов в другом магазине. Рассрочка выдается на 1 год с 12 периодами выплат.

Исходные данные:

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

=СТАВКА(B2;-B3/B2;B4;0;0;0,01)*B2

Описание аргументов:

• B2 – число периодов выплат;
• -B3/B2 – выражение для расчета размера ежемесячного платежа;
• B4 – реальная стоимость ноутбука (используется как начальная стоимость финансового инструмента, цена которого повысится до 1200 к окончанию последнего периода выплат);
• 0 – остаток по окончанию последнего периода выплат;
• 0 – выплаты в конце периода;
• 0,01 – произвольное значение предполагаемой ставки.

Результат расчетов:

То есть, фактически в первом магазине клиенту предложили кредит на ноутбук под 25,4% годовых.

Примеры решения задач по сложным процентам

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

Задача №1. Рассчитать прибыль по вкладу на 5 лет под 10% годовых, начальная сумма вложений 100000 рублей (с капитализацией).

Находим конечную сумму вклада по формуле сложных процентов:

Результат: инвестор через 5 лет получит 61051 рублей прибыли.

Задача №2. Рассчитать прибыль по вкладу на 10 лет под 10% годовых с капитализацией. Начальная сумма вложений 50000 рублей, дополнительно каждый год начиная с первого счёт пополняется на 10000 рублей.

Сначала находим конечную сумму по формуле сложного процента с регулярными пополнениями:

Учитывая, сколько инвестировано за 10 лет (50000 сразу и еще 9 раз по 10000), вычисляем прибыль:

Результат: инвестор через 10 лет получит 139061 рубль прибыли, инвестировав 140000 рублей.

Задача №3. Рассчитать, сколько времени понадобится инвестору, чтобы увеличить капитал с 500000 до 1000000 рублей. Средняя доходность портфеля — 12% годовых, прибыль реинвестируется.

У нас есть все необходимые данные, используем одну из производных формул сложных процентов:

Решение: инвестору понадобится чуть больше 6 лет.

Задача №4. Посчитать среднюю процентную ставку, которая позволит превратить 100000 рублей в 500000 рублей за 10 лет путём инвестирования. Прибыль реинвестируется.

Используем одну из производных формул сложных процентов:

Решение: инвестору нужно вложить деньги под 17.5% годовых (довольно сложно на практике, кстати).

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

Таблица расчета процентов по вкладу

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

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

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

Процентные ставки будут располагаться в столбце Процент области вычислений, а периоды времени — в строке, озаглавленной как Годы. В области вычислений должны отображаться суммы, величина которых зависит от срока размещения вклада и процентной ставки (рис. 3.14 и 3.15).

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

Сумма вклада на конец периода рассчитывается по такой формуле:

где Р0 — сумма, размещенная на депозите, r — ставка по депозиту, n — число периодов (лет).

Рис. 3.14. Фрагмент таблицы для расчета суммы на депозита (с формулами)

Рис 3.15. Таблица для расчета суммы на депозите (с числовыми значениями)

Нам кажется, что у вас не возникнет проблем с оформлением столбцов и строк, содержащих исходные данные для расчета. Вам требуется вставить две строки (после строк 2 и 8) и ввести имена и значения параметров в область ввода. А вот процесс создания основной расчетной формулы мы опишем более подробно. Выделите диапазон В10:К19 и введите в ячейку В10 формулу для расчета, выполнив следующие действия:

• Введите знак равенства, выделите ячейку D2 и нажатиями функциональной клавиши [F4] задайте абсолютную ссылку.
• Введите знак «*» (умножить), круглую открывающую скобку, цифру 1 и знак « ».
• Выделите ячейку А10 и три раза нажмите функциональную клавишу [F4] (будет создана абсолютная ссылка на имя столбца), затем введите круглую закрывающую скобку.
• Переключитесь на английский шрифт и введите знак возведения в степень «/» путем нажатия комбинации клавиш [Shift 6].
• Выделите ячейку В9 и дважды нажмите функциональную клавишу [F4] (будет создана абсолютная ссылка на номер строки). Затем введите круглую открывающую скобку.
• Завершите ввод формулы нажатием комбинации клавиш [Ctrl Enter].

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

Формула расчета сложных процентов с пополнением

В нашем случае имеем следующие данные

Мы можем выразить из формулы нужный нам ежегодный взнос

Подставив в эту формулу наши значения получим

94118,09232 — именно эту сумму мы должны вкладывать каждый год, чтобы получить через 10 лет 1.5 млн. рублей.

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

Выше приведен расчет депозита на 10 лет с 2 июля 2009. Ежегодное пополнение 94118,09232 Сумма получилась примерно такой(разница 35 рублей не существенна)

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

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

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

где D — сумма регулярных пополнений банковского депозита. Обратите внимание, степень N-1 означает, что доливки начинаются со второго инвестиционного периода (если сумма дополнительных инвестиций вносится сразу, то N-1 меняется на N).

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