7 основных финансовых функций в Excel
Excel – мощный инструмент, особенно для финансовых аналитиков и бухгалтеров. Независимо от того, являетесь ли вы аналитиком-исследователем, инвестиционным банкиром или просто тем, кто пытается построить модель DCF, вы найдете эти формулы полезными.
1. ГУП
Formula: =PMT (rate, nper, pv, [fv], [type])Ставка : процентная ставка, начисляемая за каждый период.
КПЕР : общее количество платежей.
PV : сумма ссуды или приведенная стоимость всех платежей.
[fv] : это необязательный аргумент, в котором вы можете ввести целевой остаток денежных средств, который вы хотите получить после погашения ссуды; по умолчанию он установлен на 0.
[тип] : это необязательный аргумент, в котором вы можете выбрать оплату в начале (1) или в конце периода (0); по умолчанию он установлен на 0.
Функция PMT позволяет аналитикам по недвижимости построить финансовую модель для расчета периодических платежей для погашения основной суммы в течение заданного срока. Однако вы можете использовать эту функцию для любого типа ссуды.
Следовательно, аналитикам потребуется сумма основного долга, процентная ставка и периодичность выплат. Например, ниже приводится пример ссуды на сумму 200 000 долларов США с начислением процентов под 6% сроком на 5 лет.
Это говорит аналитику, что этот заем в размере 200 000 долларов, по которому ежегодно начисляются проценты по ставке 6%, потребует ежегодного платежа в размере 47 479,28 долларов в течение 5 лет для погашения кредита (т.е. основной суммы плюс проценты).
Здесь важно отметить, что если проценты начисляются ежемесячно, эффективная процентная ставка изменяется. Это будет показано в следующей формуле.
2. ЭФФЕКТ
Formula: =EFFECT (nominal_rate, npery)Nominal_rate : Заявленная процентная ставка.
Npery : Сколько раз увеличиваются проценты в год.
Функция ЭФФЕКТ вычисляет эффективную процентную ставку. Например, если указана процентная ставка 10% ежемесячно, эффективная ставка будет выше 10%. Вот пример, показывающий это вычисление с функцией ЭФФЕКТ.
3. XNPV
Formula: =XNPV (rate, values, dates)Ставка : ставка, по которой вы хотите дисконтировать денежные потоки.
Значения : диапазон ячеек, содержащий денежные потоки.
Даты : даты, соответствующие денежным потокам.
XNPV – это вариация NPV (чистой приведенной стоимости). Следовательно, вы также можете использовать XNPV для вычисления чистой приведенной стоимости. Однако разница в том, что XNPV не предполагает, что денежные потоки происходят через равные промежутки времени.
При использовании формулы XNPV помните, что аргумент ставки всегда должен указываться в процентах (т. Е. 0,20 для 20%). Вы должны использовать отрицательное значение для платежей и положительное значение для квитанций.
Ячейки, содержащие даты, должны быть отформатированы как дата, а не как текст. Также обратите внимание, что данные должны быть расположены в хронологическом порядке.
4. XIRR
Formula: =XIRR (values, dates, [guess])Значения : ссылки на ячейки, содержащие денежные потоки.
Даты : даты, соответствующие денежным потокам.
Предположение : необязательный аргумент, в который можно ввести ожидаемую внутреннюю норму доходности; по умолчанию он равен 0,1.
XIRR означает расширенную внутреннюю норму прибыли . Во многом так же, как XNPV, единственное отличие состоит в том, что XIRR не предполагает, что денежные потоки происходят через регулярные промежутки времени.
Если вам интересно, почему Excel требует, чтобы вы вводили предположение, это потому, что XIRR вычисляется с помощью итераций. Если вы дадите предположение, итерации начнутся с этого числа, в противном случае – 0,1.
Если Excel не может рассчитать скорость после определенного количества итераций, он возвращает ошибку #NUM . Excel также вернет ошибку #NUM, если в данных нет хотя бы одного отрицательного и одного положительного денежного потока.
5. MIRR
Formula: =MIRR (values, finance_rate, reinvest_rate)Значения : ссылки на ячейки, содержащие денежные потоки.
Finance_rate : Стоимость капитала.
Reinvest_rate : ожидаемая норма прибыли на реинвестированные денежные потоки.
Согласно XIRR, положительные денежные потоки реинвестируются по IRR. Однако модифицированная внутренняя норма прибыли ( MIRR ) предполагает, что они инвестируются за счет стоимости капитала компании или внешней нормы прибыли.
В отличие от функции XIRR, MIRR предполагает, что денежные потоки происходят периодически. Однако многие другие условия остаются прежними. В данных должен быть хотя бы один положительный и отрицательный денежный поток, а значения должны быть в хронологическом порядке.
6. СТАВКА
Formula: =RATE (nper, pmt, pv, [fv], [type], [guess])КПЕР : общее количество платежей до срока погашения.
PMT : Сумма платежа за каждый период.
PV : приведенная стоимость платежей в течение срока действия облигации, т. Е. Стоимость облигации.
[fv] : это необязательный аргумент, который вы можете установить на желаемый остаток наличных денег после окончательного платежа; по умолчанию установлено значение 0.
[тип] : это необязательный аргумент для установки платежа как подлежащего оплате в конце (0) или начале (1) периода; по умолчанию установлено значение 0.
[предположение] : это необязательный аргумент, в который вы можете ввести предполагаемую ставку; по умолчанию он равен 0,1.
Функция RATE позволяет аналитикам рассчитать доходность облигации к погашению. Функция использует итерации для вычислений, и если результаты не сходятся к 20- й итерации, она вернет ошибку #NUM .
Обратите внимание, что стоимость облигации должна быть отрицательным числом, иначе функция вернет ошибку #NUM .
7. НАКЛОН
Formula: =SLOPE (known_ys, known_xs)Known_ys : диапазон ячеек или массив, состоящий из точек данных зависимой переменной.
Known_xs : диапазон ячеек или массив, состоящий из точек данных независимых переменных.
Функция НАКЛОН вычисляет наклон линии регрессии, также известной как линия наилучшего соответствия. Это удобный инструмент, когда вы хотите вычислить бета-версию акции, используя набор данных, содержащий цены акции и дневные уровни индекса.
Ниже приведен пример того, как можно вычислить наклон линии регрессии с помощью функции НАКЛОН.
Если вы предоставите только одну зависимую и независимую точку данных, функция вернет ошибку # DIV / 0 . Если диапазоны, которые вы вводите в каждый аргумент, не имеют равного количества точек данных, функция вернет ошибку # N / A.
Теперь вы готовы со своим набором инструментов для финансовых формул
Финансовое моделирование может быть головокружительным, когда числа бегают по экрану. Эти финансовые функции Excel сделают вашу жизнь немного проще, и вам не придется использовать длинные сложные формулы для вычислений. Однако эти функции могут не помочь вам в уплате налогов.