Excel Data Финансовый анализ

Вы можете легко выполнить финансовый анализ в Excel. Excel предоставляет вам несколько финансовых функций, таких как PMT, PV, NPV, XNPV, IRR, MIRR, XIRR и т. Д., Которые позволяют быстро получить результаты финансового анализа.

В этой главе вы узнаете, где и как вы можете использовать эти функции для анализа.

Что такое аннуитет?

Аннуитет — это серия постоянных денежных выплат, осуществляемых в течение непрерывного периода. Например, сбережения на пенсию, страховые выплаты, ипотечный кредит, ипотека и т. Д. В функции аннуитета —

• Положительное число представляет полученные денежные средства.
• Отрицательное число представляет выплаченные деньги.

Приведенная стоимость серии будущих платежей

Приведенная стоимость — это общая сумма, которую сейчас стоит серия будущих платежей. Вы можете рассчитать текущую стоимость с помощью функций Excel —

• PV — рассчитывает текущую стоимость инвестиций с использованием процентной ставки и серии будущих платежей (отрицательные значения) и дохода (положительные значения). По крайней мере, один из денежных потоков должен быть положительным, и, по крайней мере, один должен быть отрицательным.

• NPV — Расчет чистой приведенной стоимости инвестиций с использованием ставки дисконтирования и серии периодических будущих платежей (отрицательные значения) и дохода (положительные значения).

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

PV — рассчитывает текущую стоимость инвестиций с использованием процентной ставки и серии будущих платежей (отрицательные значения) и дохода (положительные значения). По крайней мере, один из денежных потоков должен быть положительным, и, по крайней мере, один должен быть отрицательным.

NPV — Расчет чистой приведенной стоимости инвестиций с использованием ставки дисконтирования и серии периодических будущих платежей (отрицательные значения) и дохода (положительные значения).

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

Обратите внимание, что —

• Денежные потоки PV должны быть постоянными, тогда как денежные потоки NPV могут быть переменными.

• Денежные потоки PV могут быть в начале или в конце периода, тогда как денежные потоки NPV должны быть в конце периода.

• Денежные потоки NPV должны быть периодическими, тогда как денежные потоки XNPV не должны быть периодическими.

Денежные потоки PV должны быть постоянными, тогда как денежные потоки NPV могут быть переменными.

Денежные потоки PV могут быть в начале или в конце периода, тогда как денежные потоки NPV должны быть в конце периода.

Денежные потоки NPV должны быть периодическими, тогда как денежные потоки XNPV не должны быть периодическими.

В этом разделе вы поймете, как работать с PV. Вы узнаете о NPV в следующем разделе.

пример

Предположим, вы покупаете холодильник. Продавец говорит, что цена холодильника составляет 32000, но у вас есть возможность выплатить сумму через 8 лет с процентной ставкой 13% годовых и ежегодными выплатами 6000. У вас также есть возможность совершать платежи. либо в начале или в конце каждого года.

Вы хотите знать, какой из этих вариантов выгоден для вас.

Вы можете использовать функцию Excel PV —

PV (rate, nper, pmt, [fv ], [type])

Чтобы рассчитать текущую стоимость с выплатами в конце каждого года, опустите тип или укажите 0 для типа.

Чтобы рассчитать текущую стоимость с выплатами в конце каждого года, укажите 1 для типа.

Вы получите следующие результаты —

Следовательно,

• Если вы делаете платеж сейчас, вам нужно заплатить 32 000 от текущей стоимости.
• Если вы выбираете ежегодные платежи с оплатой в конце года, вам нужно заплатить 28 793 от приведенной стоимости.
• Если вы выберете ежегодные платежи с оплатой в конце года, вам нужно будет заплатить 32 536 от текущей стоимости.

Вы можете ясно видеть, что вариант 2 выгоден для вас.

Что такое EMI?

Приравниваемый ежемесячный платеж (EMI) определяется Investopedia как «Фиксированная сумма платежа, сделанная заемщиком кредитору на определенную дату каждого календарного месяца. Приравниваемые ежемесячные платежи используются для выплаты процентов и основной суммы каждый месяц, так что в течение По истечении указанного количества лет кредит погашается полностью ».

EMI на займе

В Excel вы можете рассчитать EMI по ссуде с помощью функции PMT.

Предположим, вы хотите взять ипотечный кредит на 5000000 с годовой процентной ставкой 11,5% и сроком кредита на 25 лет. Вы можете найти свой EMI следующим образом —

• Рассчитать процентную ставку в месяц (Процентная ставка в год / 12)
• Рассчитать количество ежемесячных платежей (количество лет * 12)
• Используйте функцию PMT для расчета EMI

Как вы видите,

• Приведенная стоимость (PV) — это сумма кредита.
• Будущая стоимость (FV) равна 0, так как в конце срока сумма кредита должна быть 0.
• Введите 1, поскольку EMI оплачиваются в начале каждого месяца.

Вы получите следующие результаты —

Ежемесячная выплата основной суммы и процентов по кредиту

EMI включает в себя как процентную, так и частичную оплату основного долга. По мере увеличения времени эти два компонента EMI будут меняться, уменьшая баланс.

Получить

• Процентная часть ваших ежемесячных платежей, вы можете использовать функцию Excel IPMT.

• Оплата основной части ваших ежемесячных платежей, вы можете использовать функцию Excel PPMT.

Процентная часть ваших ежемесячных платежей, вы можете использовать функцию Excel IPMT.

Оплата основной части ваших ежемесячных платежей, вы можете использовать функцию Excel PPMT.

Например, если вы взяли кредит в размере 1 000 000 на срок 8 месяцев по ставке 16% годовых. Вы можете получить значения для EMI, уменьшающиеся суммы процентов, увеличивающуюся выплату основных сумм и уменьшающийся остаток займа в течение 8 месяцев. По истечении 8 месяцев остаток займа будет равен 0.

Следуйте процедуре, приведенной ниже.

Шаг 1 — Рассчитайте EMI ​​следующим образом.

Это приводит к EMI Rs. 13261,59.

Шаг 2 — Затем рассчитайте проценты и основные части EMI за 8 месяцев, как показано ниже.

Вы получите следующие результаты.

Проценты и основная сумма выплачиваются между двумя периодами

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

• Вычислите совокупные проценты, выплаченные между 2 и 3 месяцами, используя функцию CUMIPMT.

• Проверьте результат, суммируя значения процентов за 2- ^й и 3- ^й месяцы.

• Вычислите совокупную основную сумму, выплаченную между 2- ^м и 3- ^м месяцами, используя функцию CUMPRINC.

• Проверьте результат, суммируя основные значения за 2- ^й и 3- ^й месяцы.

Вычислите совокупные проценты, выплаченные между 2 и 3 месяцами, используя функцию CUMIPMT.

Проверьте результат, суммируя значения процентов за 2- ^й и 3- ^й месяцы.

Вычислите совокупную основную сумму, выплаченную между 2- ^м и 3- ^м месяцами, используя функцию CUMPRINC.

Проверьте результат, суммируя основные значения за 2- ^й и 3- ^й месяцы.

Вы получите следующие результаты.

Вы видите, что ваши расчеты совпадают с вашими результатами проверки.

Расчет процентной ставки

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

Найти процентную ставку с помощью функции Excel RATE —

Вы получите результат как 8%.

Расчет срока кредита

Предположим, вы берете кредит в размере 100 000 с процентной ставкой 10%. Вы хотите максимальный ежемесячный платеж в размере 15 000. Возможно, вы захотите узнать, сколько времени вам потребуется, чтобы очистить кредит.

Найти количество платежей с помощью функции Excel NPER

Вы получите результат в течение 12 месяцев.

Решения об инвестициях

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

Сначала рассмотрим случай регулярных периодических денежных потоков .

Чистая приведенная стоимость последовательности денежных потоков, полученных в разные моменты времени в течение n лет (n может быть дробью), составляет 1 / (1 + r) ⁿ , где r — годовая процентная ставка.

Рассмотрим следующие две инвестиции в течение 3 лет.

На первый взгляд, Инвестиция 1 выглядит лучше, чем Инвестиция 2. Однако вы можете решить, какая инвестиция лучше, только если вы знаете истинную стоимость инвестиций на сегодняшний день. Вы можете использовать функцию NPV для расчета прибыли.

Денежные потоки могут возникнуть

• В конце каждого года.
• В начале каждого года.
• В середине каждого года.

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

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

Вы получите следующие результаты —

Как вы видите, NPV для Инвестиции 2 выше, чем для Инвестиции 1. Следовательно, Инвестиция 2 — лучший выбор. Вы получили этот результат, так как потоки вывода средств для Инвестиции 2 относятся к более поздним периодам по сравнению с Инвестициями 1.

Денежные потоки в начале года

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

Вы получите следующие результаты —

Денежные потоки в середине года

Предположим, что денежные потоки происходят в середине каждого года. В таком случае вам нужно умножить NPV, полученную из денежных потоков, на $ \ sqrt {1 + r} $, чтобы получить чистую приведенную стоимость.

Вы получите следующие результаты —

Денежные потоки на нерегулярных интервалах

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

Однако в Excel вы можете легко сделать такой расчет с помощью функции XNPV.

• Упорядочите свои данные с датами и денежными потоками.

Примечание . Первая дата в ваших данных должна быть самой ранней из всех дат. Другие даты могут встречаться в любом порядке.

• Используйте функцию XNPV для расчета чистой приведенной стоимости.

Вы получите следующие результаты —

Предположим, что сегодняшняя дата — 15 марта 2015 года. Как вы заметили, все даты движения денежных средств относятся к более поздним датам. Если вы хотите найти чистую приведенную стоимость на сегодняшний день, включите ее в данные вверху и укажите 0 для денежного потока.

Вы получите следующие результаты —

Внутренняя норма доходности (IRR)

Внутренняя норма доходности (IRR) инвестиции — это процентная ставка, при которой NPV равен 0. Это значение ставки, для которой приведенные значения положительных денежных потоков точно компенсируют отрицательные. Когда ставкой дисконтирования является IRR, инвестиции совершенно безразличны, то есть инвестор не получает и не теряет деньги.

Рассмотрим следующие денежные потоки, различные процентные ставки и соответствующие значения NPV.

Как вы можете наблюдать между значениями процентной ставки 10% и 11%, знак NPV меняется. Когда вы настраиваете процентную ставку до 10,53%, NPV составляет почти 0. Следовательно, IRR составляет 10,53%.

Определение IRR денежных потоков для проекта

Вы можете рассчитать IRR денежных потоков с помощью функции Excel IRR.

IRR составляет 10,53%, как вы видели в предыдущем разделе.

Для данных денежных потоков IRR может:

• существуют и уникальны
• существует и несколько
• не существует

Уникальный IRR

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

• Если первый денежный поток отрицателен, это означает, что у инвестора есть деньги и он хочет инвестировать. Затем, чем выше IRR, тем лучше, поскольку он представляет процентную ставку, которую получает инвестор.

• Если первый денежный поток положительный, это означает, что инвестору нужны деньги и он ищет кредит, чем ниже IRR, тем лучше, поскольку он представляет процентную ставку, которую платит инвестор.

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

Если первый денежный поток положительный, это означает, что инвестору нужны деньги и он ищет кредит, чем ниже IRR, тем лучше, поскольку он представляет процентную ставку, которую платит инвестор.

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

Как вы заметили, IRR имеет уникальное значение для различных значений догадки.

Несколько IRR

В некоторых случаях у вас может быть несколько IRR. Рассмотрим следующие денежные потоки. Рассчитать IRR с различными значениями догадки.

Вы получите следующие результаты —

Вы можете наблюдать, что есть два IRR — -9,59% и 216,09%. Вы можете проверить эти два IRR, вычисляя NPV.

Как для -9,59%, так и для 216,09%, NPV равно 0.

Нет IRR

В некоторых случаях у вас может не быть IRR. Рассмотрим следующие денежные потоки. Рассчитать IRR с различными значениями догадки.

Вы получите результат как #NUM для всех значений догадки.

Результат #NUM означает, что IRR для рассматриваемых денежных потоков отсутствует.

Модели денежных потоков и IRR

Если в денежных потоках есть только один признак изменения, например, с отрицательного на положительный или с положительного на отрицательный, то гарантируется уникальный IRR. Например, в капитальных вложениях первый денежный поток будет отрицательным, а остальные денежные потоки будут положительными. В таких случаях уникальный IRR существует.

Если в денежных потоках изменилось более одного знака, IRR может не существовать. Даже если он существует, он не может быть уникальным.

Решения, основанные на IRR

Многие аналитики предпочитают использовать IRR, и это популярный показатель прибыльности, потому что в процентном отношении его легко понять и легко сравнить с требуемым доходом. Тем не менее, есть определенные проблемы при принятии решений с IRR. Если вы ранжируете с IRR и принимаете решения на основе этих рангов, вы можете принять неправильные решения.

Вы уже видели, что NPV позволит вам принимать финансовые решения. Однако IRR и NPV не всегда приводят к одному и тому же решению, когда проекты являются взаимоисключающими.

Взаимоисключающие проекты — это те, для которых выбор одного проекта исключает принятие другого. Когда сравниваемые проекты являются взаимоисключающими, может возникнуть конфликт ранжирования между NPV и IRR. Если вам нужно выбрать между проектом A и проектом B, NPV может предложить принятие проекта A, тогда как IRR может предложить проект B.

Этот тип конфликта между NPV и IRR может возникнуть по одной из следующих причин:

• Проекты очень разных размеров, или
• Сроки движения денежных средств разные.

Проекты значительной разницы в размерах

Если вы хотите принять решение с помощью IRR, проект A дает доход 100, а проект B — 50. Следовательно, инвестиции в проект A выглядят выгодно. Однако это неправильное решение из-за разницы в масштабах проектов.

Рассмотрим —

• У вас есть 1000, чтобы инвестировать.

• Если вы инвестируете все 1000 в проект А, вы получите возврат 100.

• Если вы инвестируете 100 в проект B, у вас все еще будет 900, которые вы можете инвестировать в другой проект, скажем, в проект C. Предположим, вы получаете доход в размере 20% по проекту C, а затем общий доход по проекту B и проекту C 230, что значительно опережает рентабельность.

У вас есть 1000, чтобы инвестировать.

Если вы инвестируете все 1000 в проект А, вы получите возврат 100.

Если вы инвестируете 100 в проект B, у вас все еще будет 900, которые вы можете инвестировать в другой проект, скажем, в проект C. Предположим, вы получаете доход в размере 20% по проекту C, а затем общий доход по проекту B и проекту C 230, что значительно опережает рентабельность.

Таким образом, NPV является лучшим способом для принятия решений в таких случаях.

Проекты с разными сроками движения денежных средств

Опять же, если вы считаете, что IRR решит, проект B будет выбором. Тем не менее, проект А имеет более высокую NPV и является идеальным выбором.

IRR нерегулярно расположенных денежных потоков (XIRR)

Ваши денежные потоки иногда могут быть расположены нерегулярно. В таком случае вы не можете использовать IRR, поскольку IRR требует одинаково распределенных временных интервалов. Вместо этого вы можете использовать XIRR, который учитывает даты движения денежных средств вместе с денежными потоками.

Внутренняя норма прибыли, которая в результате составляет 26,42%.

Модифицированный IRR (MIRR)

Рассмотрим случай, когда ваша ставка финансирования отличается от вашей ставки реинвестирования. Если вы рассчитываете внутреннюю норму прибыли с IRR, она предполагает одинаковую ставку как для финансирования, так и для реинвестирования. Кроме того, вы также можете получить несколько IRR.

Например, рассмотрим денежные потоки, приведенные ниже —

Как вы заметили, NPV больше 0, чем один раз, что приводит к нескольким IRR. Кроме того, ставка реинвестирования не учитывается. В таких случаях вы можете использовать модифицированный IRR (MIRR).

Вы получите результат 7%, как показано ниже —

Примечание. В отличие от IRR, MIRR всегда будет уникальным.