Как посчитать ставку дисконтирования в excel

Функция СТАВКА

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше

В этой статье описаны синтаксис формулы и использование функции СТАВКА в Microsoft Excel.

Описание

Возвращает процентную ставку за период аннуитета. Ставка вычисляется с помощью итерации и может иметь ноль или больше решений. Если последовательные результаты rate не сходятся в пределах 0,00000001 после 20 итерации, то ставка возвращает результат #NUM! значение ошибки #ЗНАЧ!.

Синтаксис

СТАВКА(кпер; плт; пс; [бс]; [тип]; [прогноз])

Примечание: Полное описание аргументов «кпер», «плт», «пс», «бс» и «тип» см. в разделе, посвященном функции ПС.

Аргументы функции СТАВКА описаны ниже.

Кпер — обязательный аргумент. Общее количество периодов платежей по аннуитету.
Плт Обязательный. Выплата, производимая в каждый период и не меняющаяся на протяжении всего периода ежегодного платежа. Обычно аргумент «плт» состоит из основного платежа и платежа по процентам, но не включает других налогов и сборов. Если он опущен, аргумент «пс» является обязательным.
Пс — обязательный аргумент. К настоящему моменту — общая сумма, на которую сейчас стоит ряд будущих платежей.
Fv Необязательный. Будущая стоимость или баланс, который вы хотите достичь после последнего платежа. Если значение «ок» опущено, предполагается значение 0 (например, будущая стоимость займа — 0). Если аргумент «пс» опущен, необходимо включить аргумент «pmt».
Тип Необязательный. Число 0 или 1, обозначающее, когда должна производиться выплата.

Когда нужно платить

В конце периода

В начале периода

Прогноз Необязательный. Предполагаемая величина ставки.
- Если аргумент «прогноз» опущен, предполагается, что его значение равно 10 %.
- Если функция СТАВКА не сходится, попробуйте изменить значение аргумента «прогноз». Функция СТАВКА обычно сходится, если значение этого аргумента находится между 0 и 1.
Замечания

Убедитесь, что единицы измерения, выбранные для аргументов «прогноз» и «кол_пер» соответствуют друг другу. При ежемесячных выплатах по четырехгодичному займу под 12 процентов годовых используйте значение 12%/12 для аргумента «прогноз» и 4*12 — для аргумента «кол_пер». При ежегодных платежах по тому же займу используйте значение 12% для аргумента «прогноз» и 4 —для аргумента «кол_пер».

Пример

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

Срок займа в годах

Ежемесячная сумма платежа

Как при помощи Excel считать величину дисконтированного (приведенного) денежного потока

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

Анализ затрат

Анализ затрат является довольно простым методом. В этом случае вы определяете стоимость производства продукта (которым в нашем случае является проект) и сопоставляете ее с ожидаемыми выгодами. Если выгоды перекрывают затраты, то, скорее всего, данный проект будет принят к исполнению.

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

Период окупаемости инвестиций

Период окупаемости инвестиций — это количество времени, которое требуется для того, чтобы окупились первоначальные инвестиции в данный проект. Совокупная стоимость проекта сравнивается с получаемыми доходами и вычисляется время, которое требуется для того, чтобы полученные доходы превысили затраты на реализацию данного проекта. Когда выполняется сравнение двух или большего числа проектов сходного масштаба и сложности, как правило, выбирается проект с наименьшим периодом окупаемости инвестиций. У этого метода нет «универсальной» формулы, которая позволяла бы быстро найти требуемое решение. Если, например, себестоимость проекта равняется 100 000 долл., а ожидаемые доходы составляют 25 000 долл. в квартал, то период окупаемости инвестиций составит один год.

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

Если вам предложат 1 000 долл. сегодня или те же 1 000 долл. через два года, какой вариант вы предпочтете? Ответ предсказуем, поскольку вложив сейчас эту сумму в банк или какое-либо предприятие, через два года вы будете иметь с нее прибыль. Например, под 6% годовых такая инвестиция на двухлетний период составит 1 123,60 долл. (в нынешних долларах, разумеется).

Метод дисконтированного (приведенного) денежного потока сравнивает стоимость будущих денежных потоков с нынешними долларами. Иными словами, он выполняет операцию, противоположную той, которую мы только что объяснили. Зная, что ваш проект принесет через два года сумму, равную 1 123,60 долл. (это так называемая будущая стоимость — Future Value, или FV), вы бы смогли с помощью метода дисконтированного (приведенного) денежного потока определить нынешнюю стоимость этой суммы. Ответ, конечно же, таков: 1 000 долл.

Чтобы иметь представление о дисконтированных денежных потоках, вы должны знать стоимость соответствующих инвестиций в нынешних долларах, иначе говоря, приведенную стоимостью (Present Value, или PV), которая вычисляется следующим образом: PV=FV/(1+i) n . Эта формула говорит о том, что приведенная стоимость равняется будущей стоимости инвестиций, деленной на один, плюс процентная ставка, возведенная в степень, равную количеству периодов, на которые мы инвестируем нашу сумму.

Вам не нравится математика? Но это же так просто! В Excel предусмотрена встроенная функция для вычисления приведенной стоимости (наряду со множеством других функций, позволяющих выполнять финансовые расчеты). На рисунке ниже показана группа Function Library (Библиотека функций), предусмотренная на вкладке Formulas (Формулы), и часть списка финансовых функций, встроенных в Excel.

Рис. 1. Финансовые функции, представленные в библиотеке функций и перечень финансовых функций

Вернемся, однако, к нашей формуле для вычисления приведенной стоимости инвестиций. Выберите в списке функций элемент PV (в русифицированной версии Excel — ПС (Приведенная стоимость)). На экране появится диалоговое окно Function Arguments (Аргументы функции), показанное на рис. 2.

Рис. 2. Диалоговое окно Function Arguments для функции PV

Диалоговое окно Function Arguments предназначено для ввода значений отдельных элементов выбранной вами функции, которые необходимы для вычисления приведенной стоимости. В текстовом поле Rate (Ставка) этого диалогового окна следует ввести величину процентной ставки за определенный временной период. Вы можете ввести 6% или 0,06 (предполагается, что процент начисляется ежегодно по методу сложных процентов). Если бы процент начислялся ежеквартально (по тому же методу), тогда вам нужно было бы разделить указанную величину процентной ставки на 4, а затем ввести полученный результат в поле Rate (Ставка).

Ниже находится поле Nper (Кпер), в котором вводят количество временных периодов. Мы инвестируем нашу сумму на два года. Величина выплаты (поле Pmt (Плт)) равняется 0, поскольку мы не производим выплат по этой инвестиции, а просто хотим знать величину всей этой суммы в нынешних долларах. Далее находится поле FV (Бс), в котором вводят значение будущей стоимости. В нашем примере будущая стоимость инвестиции равняется -1 123,60 долл. Если в поле FV (Бс) ввести положительное число, то результат вычисления этой функции будет отрицательным. На рис. 3. показано диалоговое окно Function Arguments со значениями аргументов функции PV (Приведенная стоимость), введенных в соответствующие поля.

Рис. 3. Аргументы функции PV

Вместо числовых значений в полях диалогового окна Function Arguments (Аргументы функции) можно дать адрес ячейки, в которой введено нужное вам значение. Предположим, например, что в ячейке С1 введено число 0,06. В этом случае в текстовом поле Rate (Процентная ставка) диалогового окна Function Arguments достаточно указать только адрес упомянутой выше ячейки, т.е. С1. Непосредственно под текстовыми полями диалогового окна Function Arguments представлен результат наших вычислений функции PV (Приведенная стоимость). В нашем случае PV=1000. Помимо диалогового окна Function Arguments аргументы данной функции отображены в строке формул программы Excel, а также в активизированной ячейке (А1 в данном случае) (см. рис. 3.).

Как видите, сначала следует значение процентной ставки, затем количество периодов и будущая стоимость. Обратите внимание, что в данной функции отсутствует значение между двумя запятыми. Это означает, что один из аргументов функции равен нулю (в нашем случае величина выплаты (поле Pmt (Плт)). (В русифицированной версии программы Excel аргументы функций следует отделять друг от друга точкой с запятой (;)) Как только вы
щелкнете на кнопке ОК, в ячейке А1 появится результат вычисления функции, в нашем случае — 1 000 долл.

Для того чтобы воспользоваться функцией PV (ПС), не обязательно перебирать ряд интерфейсных элементов программы. Для этого достаточно просто ввести =pv() в ячейке А1. В результате ваших действий на экране появится экранная подсказка, в которой приведен синтаксис данной функции, т.е. сокращенные названия и очередность ее аргументов (рис. 4).

Рис. 4. Всплывающая подсказка с перечнем аргументов функции PV

Если вы не знаете точно, какие значения следует вводить в качестве аргументов функции, откройте окно справочной системы Excel. В единственном текстовом поле этого окна введите PV (ПС для русифицированной Excel) и нажмите клавишу Enter. Справочная система немедленно отобразит всю необходимую информацию по интересующей вас функции.

Если вы, как и большинство других пользователей, раздражаетесь из-за того, что окно справочной системы Excel время от времени скрывается за вашей электронной таблицей (когда вы пытаетесь выполнять пошаговые инструкции, приведенные в этом окне), выполните следующее: скопируйте, а затем вставьте информацию, представленную в окне справки, в электронную таблицу, а затем, когда вы введете нужные значения в формулу, удалите эту информацию.

Допустим, что ваш комитет по отбору проектов рассматривает три проекта, из которых необходимо выбрать самый подходящий. Ожидается, что проект А принесет через два года 130 000 долл. прибыли; проект В — 140 000 долл. через три года; а проект С — 148 000 долл. через четыре года. Какому из этих проектов должен отдать предпочтение комитет, если свое решение он основывает лишь на использовании метода дисконтированного (приведенного) денежного потока, полагая, что процентная ставка равняется 8%? Самую высокую прибыль обеспечивает проект А. На рис. 5 показаны расчетные формулы по каждому проекту и полученные с их помощью результаты.

Рис. 5. Сравнение проектов с помощью метода дисконтированного (приведенного) денежного потока

NPV function

Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 for Mac Excel 2016 Excel 2016 for Mac Excel 2013 Excel 2010 Excel 2007 Excel for Mac 2011 Excel Starter 2010 More. Less

This article describes the formula syntax and usage of the NPV function in Microsoft Excel.

Description

Calculates the net present value of an investment by using a discount rate and a series of future payments (negative values) and income (positive values).

Syntax

The NPV function syntax has the following arguments:
- Rate Required. The rate of discount over the length of one period.
- Value1, value2, . Value1 is required, subsequent values are optional. 1 to 254 arguments representing the payments and income.
  - Value1, value2, . must be equally spaced in time and occur at the end of each period.
  - NPV uses the order of value1, value2, . to interpret the order of cash flows. Be sure to enter your payment and income values in the correct sequence.
  - Arguments that are empty cells, logical values, or text representations of numbers, error values, or text that cannot be translated into numbers are ignored.
  - If an argument is an array or reference, only numbers in that array or reference are counted. Empty cells, logical values, text, or error values in the array or reference are ignored.
  Remarks
  - The NPV investment begins one period before the date of the value1 cash flow and ends with the last cash flow in the list. The NPV calculation is based on future cash flows. If your first cash flow occurs at the beginning of the first period, the first value must be added to the NPV result, not included in the values arguments. For more information, see the examples below.
  - If n is the number of cash flows in the list of values, the formula for NPV is:
  - NPV is similar to the PV function (present value). The primary difference between PV and NPV is that PV allows cash flows to begin either at the end or at the beginning of the period. Unlike the variable NPV cash flow values, PV cash flows must be constant throughout the investment. For information about annuities and financial functions, see PV.
  - NPV is also related to the IRR function (internal rate of return). IRR is the rate for which NPV equals zero: NPV(IRR(. ), . ) = 0.
  Example
  
  Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.
  
  Description
  
  Annual discount rate
  
  Initial cost of investment one year from today
  
  Return from first year
  
  Return from second year
  
  Return from third year
  
  Description
  
  =NPV(A2, A3, A4, A5, A6)
  
  Net present value of this investment
  
  Example 2
  
  Description
  
  Annual discount rate. This might represent the rate of inflation or the interest rate of a competing investment.
  
  Initial cost of investment
  
  Return from first year
  
  Return from second year
  
  Return from third year
  
  Return from fourth year
  
  Return from fifth year
  
  Description
  
  Net present value of this investment
  
  =NPV(A2, A4:A8, -9000)+A3
  
  Net present value of this investment, with a loss in the sixth year of 9000
  
  Need more help?
  
  Want more options?
  
  Explore subscription benefits, browse training courses, learn how to secure your device, and more.
  
  Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.
  
  Инвестиционные показатели NPV, IRR: Excel на службе у финансового директора
  
  Как рассчитать NPV и IRR, оценить эффективность инвестиционных проектов, рассчитать сумму аннуитета и проверить банк на честность. Финансовых формул в Excel много. Часть из них предназначена для расчета амортизации разными способами. Другие – для определения стоимости ценных бумаг. Третьи для чего-то еще. Здесь мы разберем самые главные и «животрепещущие» (на мой взгляд).
  
  Это формулы, которые позволят рассчитать:
  — NPV (Net Present Value) — чистую приведенную стоимость.
  — IRR (Internal Rate of Return) — внутреннюю ставку доходности.
  — Аннуитеты – равномерные платежи.
  
  Также рассмотрим некоторые нюансы использования этих формул. Все расчеты можно найти в приложенном файле. Основной акцент сделан на функции Excel, поэтому саму финансовую математику разбирать особо не будем.
  
  Оценка целесообразности проекта с помощью NPV
  
  Есть проект, который ежегодно в течении 5 лет будет приносить 250 000 руб. Нужно потратить 1 000 000 руб. Предположим, что ставка дисконтирования равна 10%.
  
  Оцениваем NPV проекта. Напомню формулу этого показателя:
  
  Если денежные потоки, приведенные к текущему периоду, больше инвестированных денег (NPV > 0), то проект выгодный. В противном случае – нет. Другими словами, нам потребуется сделать в Excel следующее:
  
  Добавить порядковые номера лет: 0 – стартовый год, к нему приводятся потоки. 1, 2, 3 и т.д. – это годы реализации проекта. В формуле на рисунке выполнены действия, которые прописаны выше после знака суммы (Σ): денежный поток за период делится на сумму 1 и ставки дисконтирования, возведенную в степень соответствующего года.
  
  Рассчитанная строка представляет собой дисконтированный денежный поток. Чтобы получить значение NPV, достаточно найти общую сумму всей строки.
  
  Получается «-52 303». Проект невыгоден.
  
  Чтобы определить NPV, на самом деле необязательно готовить такую таблицу. Достаточно воспользоваться формулой Excel ЧПС. Синтаксис формулы такой (здесь и далее будет написано не как в справке Excel, а в переводе на понятный язык):
  
  ЧПС(Ставка дисконтирования; Диапазон дисконтируемых значений)
  
  То есть достаточно указать ячейку с процентом и с денежными потоками. Но при использовании этой формулы с непривычки финансисты часто допускают ошибку:
  
  Вообще-то дисконтированный поток и расчет по ЧПС должны совпадать. Почему же здесь разные значения? Дело в том, что ЧПС начинает дисконтировать с первого же значения. Т.е. она на самом деле ищет приведенную стоимость. А стартовые инвестиции нужно отнимать после. Правильная запись формулы в нашем случае будет иметь следующий вид:
  
  Стартовые инвестиции «выведены» за пределы дисконтируемого диапазона и вычтены: т.к. стартовые инвестиции уже идут с минусом, то D8 нужно прибавлять. Теперь результаты одинаковые.
  
  Оценка целесообразности проекта с помощью IRR
  
  Как еще можно оценить проект? Можно посмотреть на него с точки зрения ставки дисконтирования. Задать вопрос: а какая должна быть ставка, чтобы NPV стала = 0? Вот этой ставкой как раз и является IRR. Если Ставка дисконтирования < IRR, то проект стоит принять, если нет – отказаться. Рассчитать IRR с помощью Excel очень просто: подставляем в функцию ВСД итоговый денежный поток.
  
  IRR оказался меньше ставки доходности. Проект невыгодный (тот же вывод, что и при NPV).
  
  NPV и IRR по праву считаются главными экономическими критериями. Их используют и для инвестиционной оценки проектов, и для оценки стоимости существующего бизнеса. В том числе, показатель EVA (Economic Value Added) считается хорошим критерием в том числе потому, что при правильном расчете он равен NPV.
  
  Но кроме всего прочего, NPV и IRR могут быть использованы финансистами в более прикладных вопросах, например, при общении с банками на тему реальной кредитной ставки. Как – давайте посмотрим.
  
  Аннуитеты – любимая банковская цифра
  
  Сначала поговорим о волнующем вопросе – как банки рассчитывают сумму равномерного платежа, как их проверить и как это понимать. Допустим, вы собираетесь взять кредит 1 000 000 руб. на 5 лет под 10% годовых. Платить будете раз в год равными платежами. Формулу из учебника по финансовому менеджменту здесь приводить не будем. Приведем формулу Excel:
  
  ПЛТ(Ставка дисконтир; Количество периодов; Сумма кредита которую вы берете)
  
  В формуле есть еще два необязательных пункта: сумма, которая должна остаться (по умолчанию ноль), и как высчитывать сумму – на начало месяца, и тогда ставят 1, или на конец – ставят ноль. В 90% случаев эти пункты не нужны, поэтому их можно не ставить вообще. Итого аннуитет определяется так:
  
  Сумма ежегодного платежа получается сразу с минусом. Эту сумму нужно каждый год платить банку.
  
  В ней содержатся две части: 1) платеж по кредиту, 2) тело кредита.
  
  Ниже они показаны. Платеж по кредиту берется как 10% (процент по кредиту) от суммы задолженности на начало периода. Тело – как разность между ежегодным платежом и платежом по процентам (в Excel можно найти формулы, которые рассчитают вам и эти платежи). Задолженность на конец рассчитывается как разность между Задолженностью на начало и платежом по телу кредита.
  
  Если платежи не ежегодные, а ежемесячные или ежеквартальные, то нужно ставку и период приводить к этим значениям. Так если бы у нас платеж был каждый месяц, формула выглядела бы так:
  
  Мы бы годовую ставку разделили на 12 (привели к ежемесячному), и взяли не 5 периодов, а 5 • 12 = 60 месяцев. И получили ежемесячный платеж в 21 247 руб.
  
  Нюансы и тонкости
  
  А теперь обсудим, как проверять банки на честность. Любой поток платежей по кредиту подразумевает под собой, что все выбытия денег приведены к поступлениям на ставку кредитования. Теперь по-русски: если мы построим денежный поток из полученного нами кредита и последующих наших аннуитетных платежей, то затем мы можем посчитать по ним NPV и IRR. NPV при этом должно принять нулевое значение, а IRR, что интереснее, — показать нам реальную процентную ставку.
  
  Когда кредит и платежи по нему рассчитаны правильно, то NPV, взятый по той же процентной ставке, равен нулю. А IRR показывает ставку. Когда банк делает предложение, от которого невозможно отказаться и которое увеличит кредитную ставку «всего» на несколько процентов – не верьте и пересчитывайте! Например, в нашем случае банк предложил страховку «всего» 2 % от суммы кредита в год. Думаете это прирост всего в 2%? Нет! Дело в том, что настоящий кредит в начале каждого года уменьшается:
  
  В результате видно, что NPV не равен нулю. А реальный процент не 10, а 12,9%! Обратите внимание: здесь же выросла сумма переплаты. Если вас это смутит, вам могут предложить «еще более выгодные условия» — заплатить переплату сейчас, а остальное потом, меньшими платежами, или в нашем примере просто заплатить больше, а потом меньше. Сумма переплаты не изменится, а вот процент…
  
  Что здесь сделано? Из каждого последующего платежа взята сумма 43 797 руб. и добавлена к первому же платежу (а бывает выкручивают сумму в момент выдачи кредита). Если для реального сектора финансовая математика «деньги вчера – деньги завтра» кажется несколько отдаленной от жизни, для банков это реальная прибыль. Поэтому всеми силами нагружают первый платеж. А вы с помощью простых формул сможете подготовить основу для дальнейших переговоров.
  
  Да, не забудьте, если речь идет про ежемесячные платежи, умножать на 12.
  Похожие публикации: