Рассчитать через сколько лет вклад размером 100000 достигнет 1000000 эксель
Рассчитаем в MS EXCEL сумму регулярного платежа в случае накопления определенной суммы. Сделаем это как с использованием функции ПЛТ() , так и впрямую по формуле аннуитетов. Также составим таблицу регулярных платежей с расшифровкой накопленной суммы и начисленных процентов.
Аннуитетная схема предусматривает пополнение вклада периодическими равновеликими платежами (например, ежемесячными). Такой равновеликий платеж называется аннуитет. В аннуитетной схеме предполагается неизменность процентной ставки по кредиту в течение всего периода накопления. Начисление процентов (капитализация), также происходит регулярно: период начисления процентов равен периоду внесения взносов.
Задача1
Требуется накопить за 5 лет сумму 1 000 000 руб. Начальная сумма вклада =0. Определить величину регулярных пополнений вклада, если процентная ставка составляет 10% годовых, пополнение вклада производится ежеквартально, капитализация процентов также производится ежеквартально. См. файл примера .
Расчет суммы регулярного пополнения вклада, произведем сначала с помощью финансовой функции MS EXCEL ПЛТ() .
Эта функция имеет такой синтаксис: ПЛТ(ставка; кпер; пс; [бс]; [тип]) PMT(rate, nper, pv, [fv], [type]) – английский вариант.
Примечание . Обзор всех функций аннуитета найдете здесь .
Первый аргумент – Ставка. Это процентная ставка именно за период, т.е. в нашем случае за квартал, т.е. 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, когда забираем деньги в конце срока. Альтернативная формула для расчета платежа: =(Пс*ставка*(1+ ставка)^ Кпер /((1+ ставка)^ Кпер -1)+ ставка /((1+ ставка)^ Кпер -1)* Бс)*ЕСЛИ(Тип;1/(ставка +1);1)
Если период начисления процентов и регулярных взносов не совпадает
Если проценты начисляются, например, ежегодно, а взносы делаются ежемесячно, то такой денежный поток не является аннуитетом. Следовательно, функцию ПЛТ() и другие функции для расчета параметров аннуитета применять нельзя.
Таблица пополнения вклада
Составим таблицу пополнения вклада.
Вклад пополняется из 2-х источников: первый – это регулярные взносы, второй – начисленные за период проценты (на накопленную к данному моменту сумму вклада). Для вычисления регулярно начисляемых процентов используется функция ПРПЛТ (ставка; период; кпер; пс; [бс]; [тип])
Таким образом, вклад регулярно пополняется на величину =-ПЛТ(10%/4; 20; 0;1000000; 0) + ПРПЛТ(10%/4; период; 20; 0; 1000000; 0) , где период – это номер периода, в который требуется подсчитать величину пополнения. Тот же самый результат дает формула =-ОСПЛТ(10%/4; период; 20; 0; 1000000; 0)
Соотношение величины взноса и начисленных процентов хорошо демонстрирует график, приведенный в файле примера .
Примечание . В статье Аннуитет. Расчет периодического платежа в MS EXCEL. Погашение ссуды (кредита, займа) показано как рассчитать величину регулярной суммы для погашения кредита или ссуды в случае применения аннуитетной схемы.
Задача2
Требуется накопить за 5 лет сумму 1 000 000 руб. Определить величину регулярных пополнений вклада, если процентная ставка составляет 10% годовых, пополнение вклада производится ежеквартально, капитализация процентов также производится ежеквартально, на счету уже содержится 100 000 руб.
Решение2 Накопить за счет взносов нам потребуется всего 900 000руб. (1 000 000-100 000). Ежеквартальный платеж может быть вычислен по формуле =ПЛТ(10%/4; 5*4; -100000;1000000; 0) , результат -32732,42р.
Все параметры функции ПЛТ() выбираются аналогично предыдущей задаче, кроме значения ПС = -100000р., который требует пояснения. Вспомним, что для аннуитета справедливо тождество: ПС+СУММ(ОСПЛТ())+БС=0, т.е. ПС+(-900000р.)+1 000 000=0. Отсюда получим ПС = -100000р.
Примечание . В файле примера также приведен расчет графика прироста вклада без использования формул аннуитета (см. столбцы K:O).
Рассчитать, через сколько лет вклад размером 100000 руб. достигнет 1000000 руб., если годовая процентная ставка по вкладу 13% годовых и начисление процентов производится ежеквартально.
При квартальном начислении процентов ставка процента за период начисления равна 13%/4. Чтобы определить общее число периодов выплат для единой суммы вклада, используем функцию КПЕР() со следующими аргументами: ставка = 13%/4; нз = -1; бс = 10. Тогда значение функции
= КПЕР (13,5%/4;;-1;10) = 72 - это число кварталов.
Число лет составит 72/4 = 18.
Следует обратить внимание на то, что все нули в текущей и будущей суммах можно не набирать, достаточно сохранить пропорциональность между этими суммами.
Для решения этой задачи можно воспользоваться формулой (1), где аргумент n и есть значение функции КПЕР().
Для покрытия будущих расходов фирма создает фонд. Средства в фонд поступают в виде постоянной годовой ренты постнумерандо. Сумма разового платежа 15 000 руб. На поступившие взносы начисляются 11,2% годовых. Необходимо определить, когда величина фонда будет равна 100 000 руб.
Для определения общего числа периодов, через которое будет достигнута нужная сумма воспользуемся функцией КПЕР() с аргументами: ставка = 11,2%; платеж = -15; бс = 100. Тогда имеем
= КПЕР (11,2%;-15;;100) = 5, т.е. через 5 лет величина фонда будет равна 100 000 руб.
Предположим, что некоторой компании требуется 1 млн. руб. через 2 года. Компания готова вложить 250 тыс. руб. сразу и каждый месяц по 25 тыс. руб. Определить годовую процентную ставку.
В данной задаче сумма в 1 млн. руб. формируется за счет приведения к будущему моменту времени начального вклада 250 тыс. руб. и фиксированных ежемесячных выплат.
Определим значение процентной ставки за месяц с помощью функции СТАВКА(), имеющей аргументы: кпер = 2*12 = 24(месяца); платеж = -25; нз = -250; бс = 1000. Тогда
= СТАВКА (24;-25;-250;1000) = 1,05%.
Годовая процентная ставка составит 1,05%*12 = 12,6%. Процент на вклад должен быть не меньше этой величины.
Обратим внимание на то, что функция СТАВКА() вычисляется методом последовательного приближения и может не иметь решения или иметь несколько решений. Сначала рассчитывается текущий объем инвестиции при ставке, задаваемой аргументом функции СТАВКА() предположение, по умолчанию равным 10%. Если результат получается больше нуля, то значение процентной ставки увеличивается и расчет текущего объема инвестиции повторяется. Если результат оказывается меньше нуля, то для следующего приближения значение процентной ставки уменьшается. Процесс завершается, когда решение получится с точностью до 0,0000001 или когда количество итераций превысит 20. В последнем случае считается, что решения нет и для повторного поиска решения следует изменить значение приближения, выбирая из интервала между 0 и 1.
10 предпринимателей кладут деньги в банк на депозитный счет (сумма вклада произвольная). Ставка банка по срочным валютным депозитам на начало года составляет 20% годовых, начисляемых 1 раз в квартал. Определить сумму депозита к концу года.
практически невозможно т. к. не известен курс валюты на конец года.
а 20% по вкладам в валюте не существуют.
Ну, а если тупо в лоб, то +20 % т. к. в квартал начисляется 5%
Главный вопрос: а проценты каждый квартал присоединяются к сумме вклада (капитализируются) или учитываются отдельно?
Если ДА, то в конце года будет сумма: =W*(1+20%/4)^4, где W - первоначальная сумма. А если нет, то просто W*120%
Можете пожалуйста ещё одну задачу решить? Я вообще не понимаю
Задание.
Рассчитать, через сколько лет вклад размером 100000 руб. достигнет 1000000 руб. в разных банках. Годовая процентная ставка по вкладу составляет:
ВУРСАБанке13,5%иначислениепроцентовпроизводится ежеквартально.
В Сбербанке 11% и начисления процентов ежемесячно.
В АлтайКапиталБанке 9% и начисления процентов ежемесячно.
В АлтайБизнесБанке 27% и начисления процентов ежегодно.
Полосатый жираф Алик Искусственный Интеллект (300743) Destiny Destiny, Ещё раз: увеличение депозита при сложных процентах (капитализации процентов) описывается формулой: Итог = W*(1+k)^N, где k - ставка депозита ЗА ПЕРИОД капитализации, а N - количество таких периодов. Делим всё на Итог и получим: Итог/W = (1+k)^N. У тебя Итог/W = 10 (от 100 тысяч нужно получить 1 миллион). Значит, нужно найти N из уравнения (1+k)^N = 10 Логарифмируем обе части: N*Lg(1+k) = 1 Или N = 1/Lg(1+k) Останется округлить результат вверх (ДО ГОДА). Для УРСАБанк N = 1/Lg(1+13,5%/4) - результат в кварталах Для Сбера N = 1/Lg(1+11%/12) - результат в месяцах Для АлтайКапиталБанка N = 1/Lg(1+9%/12) - результат в месяцах Для АлтайБизнесБанка N = 1/Lg(1+27%) - результат в годах. Вопросы есть?
Полосатый Жираф, можете мне описать пожалуйста как вы все вычисляли, я не понял
Почему в конце года получилось =W*(1+20%/4)^4, где W - первоначальная сумма?
Объясните если не трудно
Полосатый жираф Алик Искусственный Интеллект (300743) juice wrld, Нетрудно. Только что там непонятного? Проценты начисляются раз в квартал. Значит, за квартал набежит 20%/4 процентов (ведь в году ЧЕТЫРЕ квартала). То есть, за квартал сумма увеличится в (1 + 5%) раз. На эту сумму во втором квартале набежит ещё (1+5%). Всего увеличение будет (1+5%)*(1+5%). За четыре квартала (за год) набежит: (1+5%)*(1+5%)*(1+5%)*(1+5%) = (1+5%)^4. Что непонятного? Если бы начисление было ежемесячным, то формула была бы: (1+20%/12)^12.
Полученное значение можно также использовать как показатель срока окупаемости при анализе инвестиционного проекта. При этом предполагается, что поступление доходов происходит периодически равными величинами в конце или в начале каждого расчетного периода. Рассчитанное значение будет представлять число расчетных периодов, через которое сумма доходов, дисконтированных на момент завершения инвестиций, будет равна величине инвестиций.
Рассчитать, через сколько лет вклад размером 1 млн. руб. достигнет величины 1 млрд. руб., если годовая ставка процента по вкладу 16.79% и начисление процентов производится ежеквартально.
При квартальном начислении процентов размер процента за период равен 16.79%/4. Используем аргументы норма = 16,79%/4, нз = -1, бс = 1000 функции КПЕР:
КПЕР(16.79%/4„-1,1000) = 168 — это число кварталов.
Число лет составит 168/4 = 42.
Ожидается, что ежегодные доходы от реализации проекта составят 33 млн. руб. Необходимо рассчитать срок окупаемости проекта, если инвестиции к началу поступления доходов составят 100 млн. руб., а норма дисконтирования 12.11%.
В задаче требуется определить, через сколько лет текущая стоимость ожидаемых доходов будет равна величине инвестиций. Используем аргументы функции КПЕР нз = -100000000(значение затрат), выплата = 33000000 (ежегодные поступления), норма =
= 12,11% (норма дисконтирования):
КПЕР(12.11%, 33,-100) - 4 , то есть срок окупаемости 4 года.
Функция НОРМА определяет значение процентной ставки за один расчетный период. Для нахождения годовой процентной ставки полученное значение следует умножить на число расчетных периодов, составляющих год.
Синтаксис НОРМА(кпер, выплата, нз, бс, тип, предположение).
Рассмотрим варианты практического применения этой функции.
1. Допустим, необходимо рассчитать процентную ставку при известной текущей стоимости нз, будущей стоимости бс, числе периодов кпер. В этом случае формула в EXCEL в общем виде записывается так:
2. При фиксированных обязательных или обычных периодических платежах процентная ставка за расчетный период в EXCEL вычисляется так:
В предыдущем посте я обещала облегчить вашу учетную участь и рассказать вам о том, как обычный Excel может помочь с выбором депозита. Причем, выгодного вам, а не только банку. Обещала – выполняю. Следуя моей инструкции, вы легко сможете определить, какие условия по банковским вкладам принесут вам наибольший доход.
За расчет потенциальной доходности в Microsoft Excel отвечает специальная функция БС (Будущая Стоимость (Future Value (FV) – о ней мы говорили здесь). Для того, чтобы ее вызвать, нажмите на символ f x , слева от строки ввода значений и адресов ячеек.
В открывшемся Мастере функций в строке поиска функций введите БС и нажмите Ввод. Кликните мышью на подсвеченной синим цветом строке БС, как показано ниже.
Составляющим формулы расчета будущей стоимости FV = PV(1+r) n в Excel соответствуют следующие функции:
Общее название | Функция в Excel | Краткое описание |
---|---|---|
FV (Future Value) | БС (Будущая Стоимость) | Будущая сумма вклада |
PV (Present Value) | ПС (Текущая Стоимость) | Текущая стоимость вклада |
n | КПЕР (Количество Периодов) | Число периодов начисления процентов по вкладу |
r | СТАВКА | Процентная ставка по вкладу |
Заполняем (вручную или указав адреса соответствующих ячеек) поля данными из нашего примера. Напомню, что мы решили открыть депозит, разместив на нем 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 раз превышающая ваш годовой доход. Посчитайте и впечатлитесь полученной цифрой.
Но, как гласит народная мудрость, о деньгах и здоровье вспоминают тогда, когда они заканчиваются. И часто бывает так, что изменить что-либо уже поздно. Стоит ли рисковать? Когда все, что вам нужно сделать – это подумать о завтра сегодня.
Читайте также: