Финансовый калькулятор. Как создать свой.

on 7 сентября, 2021

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

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

Итак, сегодня поговори об аннуитетных платежах и расчете будущей стоимости ваших инвестиций. С аннуитетными платежами сталкивались все, кто когда-либо размещал деньги на депозит или брал кредит. В чем их отличие от всех других видов платежей? Это платеж равными суммами через равные промежутки времени. Наверно, пока звучит сложно, но примеры все прояснят.

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

Этот шаблон мы будем использовать не раз, а сейчас об основных параметрах.

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

FV– это будущая стоимость ваших инвестиций. Именно ее мы сегодня и будем рассчитывать.

CF – регулярный поток платежей. Это для того случая, когда вы не просто вложили первоначальную сумму в самом начале и успокоились, а продолжаете регулярно довносить на счет некую сумму. Например, ежемесячно по 10000 рублей. Или по 30000 рублей ежеквартально. Так как рассчитываем пока мы именно аннуитетные платежи, то предполагаем, что суммы эти не меняются. Они могут быть и равны нулю. Такое тоже возможно.

r– ставка под которую инвестируются средства. Это самый «скользкий» момент во многих случаях, но о ставке мы поговорим отдельно.

n – количество лет, предположительный срок инвестирования

m — количество начислений в году. В аннуитетных платежах использую сложный процент при расчетах. Каждый раз, когда вы вносите деньги, происходит капитализация (то есть проценты прибавляются к основной сумме). И в следующем периоде проценты начисляются уже на новую основную сумму. Если вы не довносите деньги на счет, то капитализация буде раз в год, а m = 1.

А теперь простая задача:

10000 рублей помещены в банк на депозит под 6% годовых на 3 года. Сложные проценты начисляются раз в год в конце каждого периода. Найти величину депозита в конце срока?

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

Для расчета будущий стоимости в EXCELиспользуют функцию БС (или в англоязычной версии FV).

В этом примере деньги не довносят. Значит CF = 0. Начисления процентов раз в годm = 1. Начальная сумма на счету 10000 рублей PV = 10000. Ставка по депозиту равна 6% годовых r = 6%.

Для того, чтобы EXCEL считал корректно, все деньги, которые мы «выпускаем из рук» необходимо вводить со знаком «минус». В данном случае, размещая деньги на депозит, мы «выпускаем из рук». Поэтому вводим величина 10000 со знаком минус.

Ниже я разместила небольшое видео, как заполняется эта таблица «Расчет FV 1».

Теперь задачка посложнее.

10000 рублей помещены в банк на депозит под 6% годовых на 3 года.

Каждый месяц довносим по 500 рублей. Сложные проценты начисляются ежемесячно. Найти величину депозита в конце срока?

PV по прежнему 10000 рублей.

CF= 500 рублей

Ставка r = 6%

n = 3

m = 12

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

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

Как ее найти? Если годовая ставка равна 6%, то мы используем r/m = 6%/12 = 0,5%.

Кроме того, в этой задаче у нас появился регулярный платеж CF = 500 рублей. Помним, эти деньги мы «выпускаем из рук» и значит в табличке они тоже будут со знаком минус.

И еще одно замечание. Забивая значения в формулу, вы обратили внимание, что в первой задаче КПЕР (количество периодов) у нас было равно n. В этой задаче начисления происходят 12 раз в год в течение трех лет. Поэтому КПЕР = m*n = 36

В виде«Вычисление FV 2» заполняем эту табличку.

И еще один момент, последнее значение в функции «Тип». Оно определяет, когда начисляются проценты. 0 – в конце периода, 1 – в начале. Этот параметр необязателен. Если вы его пропустите, формула поставит значение 0. В качестве справки, для депозитов обычно используется начисление в конце периода. Для кредитов в начале. Так как сегодня мы рассматриваем депозиты, то для параметра «Тип» используем значение 0.