22/07/2021
7 функций Excel, которые помогут управлять финансами
Рассчитывайте проценты по вкладу, оптимальную сумму кредита, прибыльность облигаций и многое другое. Эти функции Excel подходят и для Google Docs.
1. PMT (ПЛТ) — рассчитывает сумму ежемесячных платежей по долгамЭто сэкономит время, когда есть несколько кредитных предложений от разных банков и не хочется обращаться в каждый за подробностями.В Telegram-канале «Лайфхакер» только лучшие тексты о технологиях, отношениях, спорте, кино и многом другом. Подписывайтесь! В нашем Pinterest только лучшие тексты об отношениях, спорте, кино, здоровье и многом другом. Подписывайтесь! Допустим, человек переехал в новую квартиру и решает отремонтировать её прямо сейчас. Свободных денег не осталось, поэтому он собирается занять их у банка.Какие данные нужныДля начала надо правильно написать формулу — в любой свободной ячейке.=ПЛТ(ставка;кпер;пс) В скобках стоят три обязательных аргумента, без которых не получится ничего посчитать: Ставка — процент по кредиту, который предлагает банк. Пусть будет 9,5%. Кпер — количество выплат по займу. Ремонт дорогой, но не смертельно, так что возьмём на полтора года: это 18 ежемесячных платежей. Пс — сумма, которая нужна на обновление жилья. Оценим это дело в 300 000 рублей. Как всё посчитатьНадо занести известные данные в таблицу, а потом напечатать формулу через знак «=». Вместо каждого из аргументов подставляем свои данные.Важно следить за оформлением: десятичные дроби отбиваются запятой, а не точкой. А ещё каждое значение в формуле нужно разделять точкой с запятойНичего не мешает одновременно внести в таблицу несколько предложений с разными процентными ставками и сроками кредита и сравнить условия. Каждый раз переписывать формулу необязательно, её можно просто растянуть за уголок.Главное — не перепутать местоположение ячеек: все значения остаются в одних и тех же строках2. EFFECT (ЭФФЕКТ) — позволяет рассчитать сложный процентФункция подойдёт инвестору, который выбирает облигации для своего портфеля и хочет понять, какую годовую доходность получит на самом деле.Россия занимает деньги через множество облигаций федерального займа (ОФЗ). У каждого выпуска таких бумаг есть номинальная доходность, определяющая, какой процент годовых от вложенной суммы получит инвестор. Например, по ОФЗ 26209 обещают Параметры облигации федерального займа SU26209RMFS5 / Московская биржа 7,6%, а по ОФЗ 26207 ещё больше Параметры облигации федерального займа SU26207RMFS9 / Московская биржа — 8,15%.Но если человеку не нужны деньги в ближайшее время, то он не станет забирать прибыль по облигациям. А, скорее всего, вложит её в те же бумаги, то есть реинвестирует. И тогда вырастет эффективная доходность облигаций. Это произойдёт из‑за механизма сложного процента: прибыль начисляется не только на первоначальные инвестиции, но и на последующие.Какие данные нужныФормула расчёта довольно простая:=ЭФФЕКТ(номинальная_ставка;кол_пер) В ней всего две переменные: Номинальная_ставка — та доходность, которая обещана облигацией при выпуске. Это 7,6% и 8,15% в нашем примере. Кол_пер — количество периодов в году, когда инвестору начисляется прибыль (в облигациях её называют купоном). Как всё посчитатьПринцип сохраняется: вносим исходные данные в таблицу. Номинальную доходность и периодичность выплат по купонам обязательно публикуют для каждой облигации на Мосбирже в разделе «Параметры инструмента». Теперь легко всё посчитать:Чтобы было проще понимать результат, можно переключить отображение ячейки на проценты. А затем растянуть формулу дальше и сравнивать доходностьТолько заметим, что облигации устроены очень хитро, инвестору нужно учитывать и другие факторы, которые влияют на прибыльность. Например, номинал бумаги равен 1 000 рублей, а её продают за 996 — реальная доходность будет выше. С другой стороны, инвестору придётся заплатить ещё и накопленный купонный доход — автоматически рассчитываемая компенсация предыдущему владельцу облигации. Эта сумма может быть равна 20–30 рублям, из‑за чего доходность опять упадёт. Одной формулой здесь не обойтись.3. XNPV (ЧИСТНЗ) — вычисляет общую прибыль инвестораПорой люди накапливают много активов, каждый из которых нерегулярно приносит деньги: проценты по вкладам, выплаты купонов по облигациям, дивиденды от акций. У всех инструментов разная прибыль, поэтому полезно понимать, сколько выходит в сумме.Функция позволяет рассчитать, какое количество денег вернётся через определённое время, например спустя четыре года. Так владелец активов поймёт, сможет ли реинвестировать доходы или купить что‑нибудь дорогое.Какие данные нужныФормула состоит из трёх компонентов:=ЧИСТНЗ(ставка;значения;даты) Второй и третий достаточно ясны:2. Значения — сколько денег потрачено на инвестиции и сколько возвращается. 3. Даты — когда именно средства приходят или уходят.Первый компонент формулы — ставка дисконтирования. Обычно деньги со временем обесцениваются, и на одну и ту же сумму в будущем можно купить меньше, чем сейчас. Это значит, что нынешние 100 рублей равны, допустим, 120 рублям в 2025 году.Если инвестор хочет не просто сохранить деньги, но и заработать, ему нужно учесть постепенное обесценивание валюты. Есть много способов это сделать, но самый простой — посмотреть доходность по надёжным облигациям: к примеру Параметры облигации федерального займа SU26234RMFS3 / Московская биржа , ОФЗ 26234 — 4,5%. Смысл в том, что инвестор почти гарантированно получит такую прибыль в будущем, это «безрисковая ставка». Оценивать потенциал инвестиций имеет смысл с поправкой на этот процент.Как всё посчитатьСо знаком минус нужно внести затраты — в нашем случае деньги, израсходованные на ценные бумаги. Следом укажем поступления, которые для отдельных инвестиций доступны заранее.Чтобы было проще осознавать результат, можно указать отображение «Валюта» для ячейкиИтоговое значение — фактическая прибыль инвестора через четыре года с учётом ставки дисконтирования. Она совсем маленькая, несмотря на 92 тысячи инвестиций: для больших поступлений нужно подбирать более рискованные, но доходные инструменты.4. XIRR (ЧИСТВНДОХ) — оценивает доходность инвестиций по притокам денегОбычно у любого инвестора есть выбор между разными финансовыми инструментами. Каждый обещает какую‑то прибыль, но не всегда понятно, что выгоднее.Функция помогает сравнить доходность, если мы заранее не знаем процент годовых. К примеру, ставка по банковскому вкладу равна 6%. Можно вложить деньги туда, а можно в бизнес знакомого, который обещает раз в квартал платить плавающую сумму в зависимости от успехов.Какие данные нужныЧтобы определить более выгодное предложение, применим формулу:=ЧИСТВНДОХ(значения;даты) Достаточно знать всего две переменные: Значения — сколько денег инвестор вложит и сколько ему обещают вернуть. Даты — график платежей, по которым будут выплачивать прибыль. Как всё посчитатьДопустим, человек вложил 100 000 рублей и получил четыре платежа, по одному в квартал. В конце года инвестор знает их размер и может вычислить доходность — больше 40%. Это на 37% выгоднее банковского вклада, хотя и рискованнее.5. RATE (СТАВКА) — вычисляет месячную или годовую процентную ставку по займамБывают и такие ситуации, что заём уже есть, а процент не оговорён. Допустим, если человек взял в долг 100 000 рублей у знакомого и пообещал в течение полугода возвращать по 20 тысяч ежемесячно. Кредитор может захотеть узнать, какова выходит ставка.Какие данные нужныПолезной будет эта формула:=СТАВКА(кпер;плт;пс) Три переменных в ней означают следующее: Кпер — количество выплат. В нашем примере заём полугодовой, то есть их будет шесть. Плт — размер платежей. Считаются и основной долг, и проценты. Пс — общая сумма займа. В нашем примере это 100 000 рублей. Как всё посчитатьНужно внести значения каждой переменной в свою ячейку и применить формулу. Главное — не забыть поставить перед суммой займа знак минуса, потому что это деньги, которые ушли.6. PV (ПС) — подсказывает, сколько денег можно взять в долгЛюди иногда делают большие покупки. Например, приобретают автомобили. Они стоят дорого, и для машин берут автокредит, обслуживать который тоже недёшево. Если человек не готов отдавать всю зарплату на ежемесячные платежи, то может заранее прикинуть, какой заём будет комфортным.Какие данные нужныПригодится формула расчёта текущей стоимости:=ПС(ставка; кпер; плт) Для этого потребуется информация, которая есть на сайте любого банка: Ставка — под какой процент придётся брать деньги на покупку. Допустим, 9% годовых, или 0,75% в месяц. Кпер — сколько времени предстоит выплачивать кредит. Например, четырёхлетний заём равен 48 ежемесячным переводам средств. Плт — размер комфортного платежа. Как всё посчитатьПредположим, что человеку будет по силам отдавать от 40 до 50 тысяч рублей в месяц. В этом случае нужны два столбца: ставка и срок постоянны, меняется только значение платежа. В результате увидим, что машина должна стоить не больше 1,6 или 2 миллионов рублей.Автомобили с такой ценой не утянут в долговую яму. Значит, можно сокращать себе пространство для выбора и искать подходящие модели.7. NPER (КПЕР) — помогает рассчитать время накопленийОбычно банки объясняют, какой процент человек получит по их депозиту и сколько денег заработает. Но иногда у вкладчика другая цель — накопить конкретную сумму к определённой дате. Функция поможет высчитать этот срок.Какие данные нужныЧтобы узнать, за какое время соберутся деньги, используем формулу количества периодов:=КПЕР(ставка/периоды_капитализации;плт;пс;бс) Она состоит из четырёх основных значений и одного дополнительного: Ставка — годовая процентная ставка, которую предлагают вкладчику. Предположим, что 7%. Периоды_капитализации — количество раз в году, когда банк начисляет проценты. Это часто делают ежемесячно, поэтому пишем «12». Плт — ежемесячный платёж. Скажем, вклад непополняемый, так что показатель будет равен нулю. Пс — начальная сумма на депозите. Допустим, 100 000 рублей. Бс — сумма, которую вкладчик намерен получить в конце срока. Например, 200 000 рублей. Как всё посчитатьЧеловек собирается положить на депозит 100 000 рублей под 7% и хочет однажды забрать вдвое больше.Для этого придётся подождать два с лишним года. Либо искать более доходную инвестицию, которая сократит срок.
