До того как были изобретены приложения для ведения бюджета, люди подсчитывали доходы и расходы на бумаге или в электронных таблицах. Сегодня следить за тратами позволяют удобные программы для разных устройств. Если вам комфортнее работать в Excel, то эта статья для вас. Команда pro.finansy собрала функции в Excel, которые помогают управлять финансами (для Google Sheets они тоже подойдут).
Как рассчитать сложный процент: функция EFFECT (ЭФФЕКТ)
Эффект сложного процента позволяет быстрее копить: если процентный доход не снимать, а реинвестировать, в будущем можно получить доходность уже от него. Так деньги сами начинают зарабатывать деньги.
Представим ситуацию. Вы купили облигации. На купоны с нее вы планируете покупать новые облигации. Как понять, какой будет эффективная доходность, если реинвестировать купоны? Для этого нужна функция ЭФФЕКТ:
=ЭФФЕКТ(номинальная_ставка; кол_пер), где
- номинальная_ставка — доходность облигации, обещанная при ее выпуске;
- кол_пер — количество периодов в году, когда инвестору выплачивают купоны.
Возьмем ОФЗ с ISIN RU000A1007F4. Доходность бумаги — 7,73%, купоны выплачивают дважды в год. Результат расчетов — 22,7%. Это эффективная ставка.
Чтобы точнее определить эффективность вложений в облигацию, необходимо учитывать ее стоимость и сумму накопленного купонного дохода.
Как рассчитать ежемесячный платеж по кредиту: функция PMT (ПЛТ)
PMT поможет выбрать лучшее предложение по кредиту, поскольку высчитывает размер ежемесячного платежа:
=ПЛТ(ставка; кпер; пс; [бс]; [тип]), где
- ставка — процент по кредиту, который предлагает банк (ставку нужно записывать в виде процентов и в месячном выражении);
- кпер — количество выплат по кредиту (если заем, к примеру, на полтора года, их будет 18);
- пс — сумма, которая нужна заемщику;
- бс — будущая стоимость или желаемый остаток средств после последней выплаты. Если аргумент опущен, его приравнивают к нулю. Для займа значение будущей стоимости должно быть нулевым;
- тип — значение, указывающее, когда нужно платить — в начале периода (1) или в конце (0). Эту переменную можно не обозначать, по умолчанию она будет равна нулю.
Вы можете составить таблицу с информацией о кредитах в разных банках, с помощью функции высчитать размер ежемесячного платежа, сравнить результаты и выбрать лучшее предложение.
Допустим, мы хотим найти размер ежемесячного платежа по кредиту в 300 тыс. рублей со ставкой 14,5%, срок которого — 12 месяцев. Согласно расчетам, размер ежемесячного платежа — 27 006,76 рубля. Чтобы записать ставку в месячном выражении, в формуле мы поделили значение из ячейки A2 на 12.
Как рассчитать, сколько денег можно взять в долг: функция PV (ПС)
Предыдущая функция помогала понять, сколько денег придется отдавать банку за кредит каждый месяц. Функция PV (ПС) помогает вычислить сумму кредита, исходя из размера платежа, который вам будет комфортно отдавать каждый месяц:
=ПС(ставка; кпер; плт), где
- ставка — процент, под который банк дает заем (нужно записывать в виде процентов за месяц);
- кпер — количество выплат по кредиту (если заем на полтора года, то выплат будет 18);
- плт — размер комфортного платежа.
Допустим, что комфортный платеж по кредиту составляет 30 тыс. рублей. Ставку возьмем из предыдущего примера — 14,5%. Заем оформим на год. Согласно расчетам, в кредит можно взять 333 249,85 рубля. Чтобы записать ставку в месячном выражении, в формуле мы поделили значение из ячейки A2 на 12.
Как посчитать, сколько времени нужно копить: функция NPER (КПЕР)
Если хотите узнать, за какое время накопите определенную сумму, то эта функция вам поможет:
=КПЕР(ставка; плт; пс; [бс]; [тип]), где
- ставка — годовая процентная ставка, предлагаемая вкладчику;
- плт — выплата, производимая в каждый период (обычно это значение включает основной платеж и платеж по процентам, но не налоги и сборы);
- пс — начальная сумма на депозите;
- бс — сумма, которую вкладчик намерен получить в конце срока;
- тип — значение, указывающее, когда нужно платить — в начале периода (1) или в конце (0). Эту переменную можно не обозначать, по умолчанию она будет равна нулю.
Составим формулу и используем функцию, чтобы решить задачу. Мы хотим накопить миллион. Доходность инвестиционного инструмента — 7%. Вложено уже 100 тыс. рублей. Каждый месяц мы будем откладывать по 20 тыс. рублей. Как скоро мы накопим миллион? Если использовать формулу, указанную на картинке ниже, то выяснится, что для накопления понадобится чуть больше трех лет.
Рассчитать срок достижения цели можно с помощью калькулятора из Библиотеки инвестора — скачайте готовую таблицу в Excel, в которую нужно только подставить значения.
Какрассчитать общую прибыль инвестора: функция XNPV (ЧИСТНЗ)
Инвестор может вкладывать деньги в разные инструменты, которые приносят различную доходность. Если интересно узнать, сколько прибыли принесет портфель, можно применить такую функцию:
=ЧИСТНЗ(ставка; значения; даты), где
- ставка – ставка дисконтирования. Поскольку под влиянием инфляции деньги каждый год обесцениваются, то необходимо этот факт учитывать. Ставку дисконтирования можно взять, ориентируясь на доходность государственных облигаций или ставку RUONIA (безрисковая ставка);
- значения — сколько денег потрачено на инвестиции и сколько возвращается. Траты на покупку инвестиций указываем со знаком минус;
- даты — когда именно средства приходят или уходят. Например, дата выплаты купона или дивидендов.
Все данные можно занести в таблицу, потом вставить диапазоны значений в формулу. Тем самым мы получим чистую приведенную стоимость для денежных потоков. Пример вычислений — на изображении ниже.
За состоянием инвестиционного портфеля следить проще в специальных приложениях. В pro.finansy вы можете в онлайн-режиме видеть, как меняется цена акций и облигаций, находить информацию о ближайших выплатах купонов и дивидендов, следить за новостями мира инвестиций. Еще в pro.finansy можно вести бюджет, а записи о доходах и расходах скачивать в виде Excel-таблицы. Скачивайте приложение и становитесь частью сообщества инвесторов!
