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

Старт:22.10.2024

Срок обучения:2 месяца

Excel: от основ до анализа данных

Курс Excel в Нетологии: освоите продвинутые функции Excel, Power Query и Power Pivot для анализа данных. Научитесь работать с большими объемами информации, строить дашборды, сводные таблицы и визуализировать данные. Получите реальные проекты в портфолио и сертификат о повышении квалификации.

25 130 ₽35 900 ₽

2 094₽/мес рассрочка

Подробнее

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

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

Вручную

Для этого нам понадобится такая формула:

• FC - это наша конечная столица или конечный результат. В общем, сумму, которую мы получим на финише, с учетом совокупного эффекта сложных процентов. Кстати, действительно готовит к регулярным вложениям! Полезно увидеть собственными глазами, во что превращаются даже небольшие вложения через 5–10 или 20 лет
• Ко - начальный капитал, который мы инвестируем некоторое время по принципу «вложил - не трогай»
• R - годовая процентная ставка по акциям (например, 12% годовых будет выглядеть как 0,12)
• м - срок реинвестирования в месяцах. Проще говоря, как часто будут начисляться проценты по депозиту и добавляться к общей сумме. Если ставка по банковскому депозиту рассчитывается каждый месяц, то m будет равно 1, если ежеквартально - то 3, если раз в год - то 12
• n - количество периодов реинвестирования. Например, если проценты реинвестируются один раз в месяц, вы получаете 12 периодов реинвестирования в год, и n будет равно 60 в течение пяти лет

Теперь осталось сформировать простую таблицу в Excel - пять строк и два столбца.

• Строка №1 - стартовый капитал (Со)
• Строка №2 - годовая процентная ставка (R)
• Строка №3 - период реинвестирования (м)
• Ряд №4 - количество периодов (n)
• Строка № 5 - окончательная сумма капитала (FC)

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

А потом в ячейку с последней заглавной буквы забиваем формулу (для номеров строк): = N. 1 * (1+ (N. 2 * N. 3/12)) ^ N. 4. На всякий случай, значок «^» в Excel выглядит так: «Вставить» - «Символ» - «^» - «Вставить» или с помощью комбинации клавиш «Shift + 6» в английской раскладке.

Вот и все, простейшая таблица Excel готова! Теперь вы можете поиграть с размером стартового капитала, годовой ставкой и количеством периодов. И посмотрите, как увеличивается (или уменьшается) стоимость конечного капитала.

С помощью специальной функции

Excel - настолько универсальная программа, что специальная функция поможет нам рассчитать потенциальную доходность депозита. Сначала перейдите на вкладку «Формула» (вверху страницы) и нажмите на символ FX или «Вставить функцию» (в верхнем левом углу).

Окно «Мастер» открывается сразу. В строке поиска введите BS (для тех, кто не знает, BS - это будущее значение) и нажмите Enter. Выпадает целый список непонятных имен - выбираем ту же БС. Или вы можете просто выбрать вручную из категории «Финансы».

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

• Поле «Ставка» - это та же годовая процентная ставка в акциях. Если проценты начисляются ежемесячно, мы делим годовые проценты на 12, если ежеквартально, то на 4 и т.д.
• Поле "Nper" - это количество лет инвестирования. Если выплаты производятся раз в месяц, мы умножаем количество лет на 12 и т.д.
• Поле «Pt» - оставить пустым
• Поле «PS» - это начальная сумма депозита. Здесь это должно быть написано со знаком минус, так как мы отдаем свои деньги «насытившимися», но не получаем их
• В поле «Тип» учитывается способ оплаты с учетом процентов по депозиту
• Если проценты выплачиваются в конце срока депозита, введите «0» или оставьте это поле пустым
• Если в начале срока - то «1».

Нажимаем ОК - и вуаля! Размер нашей будущей столицы уже отображается в ячейке!

Тестовый пример

Например, я взял сумму в размере 10 000 долларов, внесенную на депозит с годовой ставкой 6% в течение 4 лет.

Оба варианта дали одинаковый результат: через 4 года мой взнос увеличится до 12 704,89 долларов. Это, конечно, при условии ежемесячного начисления процентов.

Могу сказать, что первый способ расчета занимает немного больше времени, но он более четкий и «продуманный».

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

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