Как рассчитать DCF в Excel

Обновлено: 04.07.2024

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

В этом примере я сравниваю единовременный выигрыш в лотерею со сценарием, в котором вы получаете ежегодную сумму в течение 25 лет. Шаг первый — научиться рассчитывать текущую стоимость, о чем я расскажу дальше:

Вычисление предустановленного значения

Чтобы рассчитать текущую стоимость будущих денежных потоков, необходимо знать, какую ставку дисконтирования использовать. То, что вы можете использовать, — это ставка, которую вы можете заработать на типичных инвестициях. Например, если вы инвестируете в акции и предполагаете, что можете зарабатывать в среднем 5% в год, вы можете использовать это в качестве ставки дисконтирования. Если вы хотите быть более консервативным, вы можете использовать ставку 2%. Ниже вы увидите, как ставка дисконтирования может сыграть большую роль в ваших расчетах.

Это потому, что при расчете сегодняшней текущей стоимости вы должны использовать ставку дисконтирования, чтобы вернуть будущую стоимость к тому, что она стоила бы сегодня. Например, предположим, что вы должны были получить платеж в размере 10 000 долларов США через год, и ваша ставка дисконтирования составила 5%. Простой способ вычислить это выглядит следующим образом:

Вы можете встретить в Интернете другие формулы, использующие дроби для расчета приведенной стоимости, но достаточно просто использовать отрицательную степень. Этот расчет дает результат 9 523,81 доллара. Поскольку вы не получите платеж сегодня, стоимость этих денег меньше, чем полная сумма. Учтите, что если бы вы получили 10 000 долл. США сегодня, инвестировали их и заработали 5 %, то через год эта сумма будет стоить 10 500 долл. США — больше, чем если бы вы получили 10 000 долл. США через год.

Теперь предположим, что вы использовали ставку дисконтирования всего 2%. В этом сценарии платеж в размере 10 000 долларов через год будет стоить 9 803,92 доллара сегодня. Поскольку ставка дисконтирования ниже, ожидание платежа связано с меньшими затратами. Если бы ставка дисконтирования составляла 0%, у вас не было бы стимула вкладывать свои деньги, поскольку через год они по-прежнему будут стоить ту же стоимость, что и сегодня. Вот почему, когда процентные ставки падают и приближаются к нулю, люди менее склонны хранить свои деньги в банке, а спрос на золото возрастает, поскольку в этот момент это может быть лучшим способом сохранить богатство.

Создание шаблона для расчета дисконтированного денежного потока в Excel

Теперь, когда мы рассмотрели, как рассчитать дисконтированный денежный поток в Excel, мы можем настроить шаблон. Все, что здесь действительно необходимо, — это составить график платежей, в том числе указать, сколько денег вы будете получать каждый год. Вот примерный сценарий получения 100 000 долл. США на 25 лет:

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

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

Общая приведенная стоимость платежей составляет чуть более 1,4 млн долларов США. Несмотря на то, что общая сумма всех платежей за 25 лет составляет 2,5 млн долларов США, мы теряем большую часть этой суммы из-за временной стоимости денег со скоростью 5 % в год.

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

В этой ситуации мы выигрываем от начисления сложных процентов и заработка в размере 5 % от баланса на конец каждого года, который включает доход за предыдущий год. К концу 25 года, если мы инвестируем все эти платежи в размере 100 000 долларов США по ставке 5 %, наша будущая конечная стоимость составит 4 772 709,88 долларов США.

Теперь помните, что эквивалентом этих ежегодных платежей является приведенная стоимость 1 409 394,46 долларов США. Давайте предположим, что вместо того, чтобы получать ежегодные выплаты в размере 100 000 долларов США, мы просто получаем единовременную выплату в размере этой суммы и инвестируем ее, а также зарабатываем 5% каждый год. Вот как это будет выглядеть:

Конечная стоимость по прошествии 25 лет такая же: 4 772 709,88 долларов США. Это говорит нам о том, что если сегодня вам предоставляется возможность 25 ежегодных платежей в размере 100 000 долларов США или единовременной выплаты в размере 1 409 394,46 долларов США, для вас нет никакой разницы (если используемая вами ставка дисконтирования составляет 5%). Если ставка дисконтирования равна 2 %, приведенная стоимость возрастет до 1 952 345,65 долл. США.

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

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

Если вам понравился этот пост о том, как рассчитать дисконтированный денежный поток в Excel, поставьте лайк этому сайту на Facebook, а также обязательно ознакомьтесь с некоторыми из множества шаблонов, доступных для скачивания. Вы также можете подписаться на нас в Twitter и YouTube.

Дисконтированный денежный поток — это термин, используемый для описания вашего будущего денежного потока в сегодняшней стоимости. Это также известно как текущая стоимость (ТС) будущего денежного потока.

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

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

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

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

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

Zilculator помогает специалистам по недвижимости легко рассчитать дисконтированный денежный поток. Никогда больше не используйте электронные таблицы! Анализируйте свою собственность или создавайте инвестиционные отчеты для своих клиентов.

Фирменные инвестиционные отчеты профессионального уровня
Загрузка данных из MLS®, Zillow® и Rentometer Pro®.
Компенсации за продажу и аренду

Как рассчитать дисконтированный денежный поток (DCF)

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

Пример электронной таблицы Excel

Мы подготовили простой пример и расчет дисконтированного денежного потока в сфере недвижимости в виде таблицы Excel. Вы можете загрузить файл, ввести свои собственные числа и вычислить результаты в кратчайшие сроки. Единственное, о чем мы просим взамен, это чтобы вы поставили лайк на нашей странице в Facebook или подписались на нас в Twitter.

Ник Лиоудис – писатель, специалист по мультимедиа, консультант и контент-менеджер компании Bread. Он также проработал более 10 лет журналистом.

Эми является членом ACA, генеральным директором и основателем OnPoint Learning, компании по обучению финансовых специалистов, которая проводит обучение финансовых специалистов. Она имеет почти двадцатилетний опыт работы в финансовой отрасли и в качестве финансового инструктора для профессионалов отрасли и частных лиц.

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

Учетная ставка

Во-первых, давайте рассмотрим каждый шаг NPV по порядку. Формула:

В разбивке денежный поток после уплаты налогов за каждый период в момент времени t дисконтируется по некоторой ставке, показанной как r. Сумма всех этих дисконтированных денежных потоков затем компенсируется первоначальными инвестициями, которые равны текущей чистой приведенной стоимости. Любой NPV, превышающий 0 долларов США, является проектом с добавленной стоимостью. В процессе принятия решений среди конкурирующих проектов наличие самой высокой чистой приведенной стоимости должно иметь большое значение для выбора, если проекты сопоставимы.

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

Чистая приведенная стоимость, внутренняя норма доходности и ставка дисконтирования — взаимосвязанные понятия. Используя NPV, вы знаете сумму и время движения денежных средств, а также средневзвешенную стоимость капитала (WACC), которая обозначается как r при вычислении NPV. С IRR вы знаете те же детали и можете вычислить чистую приведенную стоимость, выраженную в процентах.

Большой вопрос: при какой ставке дисконтирования внутренняя норма доходности равна нулю? Это та же ставка, которая приведет к тому, что NPV будет равна нулю. Как вы увидите ниже, если ставка дисконтирования равна IRR, то NPV равна нулю. Или, другими словами, если стоимость капитала равна возврату капитала, проект будет безубыточным и его чистая приведенная стоимость равна 0.

=СТАВКА (nper, pmt, pv, [fv], [тип], [предположение])

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

Расчет ставки дисконтирования в Excel

В Excel можно определить ставку дисконтирования несколькими способами:

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

Чтобы проиллюстрировать первый метод, мы возьмем наш пример NPV/IRR. Используя гипотетические затраты, нашу безрисковую ставку WACC и ожидаемые денежные потоки после уплаты налогов, мы рассчитали чистую приведенную стоимость в размере 472 169 долларов США с внутренней нормой доходности 57%.

Поскольку мы уже определили ставку дисконтирования как WACC, при которой IRR равняется 0, мы можем просто взять рассчитанную IRR и поместить ее вместо WACC, чтобы получить NPV, равную 0. Эта замена показана ниже:

Теперь рассмотрим второй метод с использованием калькулятора "что, если" в Excel. Это предполагает, что мы не рассчитали IRR в размере 57%, как мы сделали выше, и не знаем, какова правильная ставка дисконтирования.

Чтобы перейти к решателю «что, если», перейдите на вкладку «Данные» —> меню анализа «что, если» —> поиск цели. Затем просто подставьте числа, и Excel найдет правильное значение. Когда вы нажмете «ОК», Excel пересчитает WACC, чтобы он равнялся ставке дисконтирования, при которой чистая приведенная стоимость равняется нулю (57%).

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

Несмотря на то, что концепция кажется простой, для каждого из компонентов, упомянутых выше, требуется немного технических знаний. Фундаментальным строительным блоком модели DCF является финансовая модель из трех отчетов (отчет о прибылях и убытках, балансовый отчет и отчет о движении денежных средств). Ниже приведен пример модели DCF, который поможет вам понять и позволит создать ее самостоятельно.

Что такое нераспределенный денежный поток?

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

Почему денежный поток дисконтируется?

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

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

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

Как построить прогноз движения денежных средств в модели DCF

Идея, стоящая за этим, заключается в прогнозировании эффективности бизнеса. Как правило, прогнозный период для модели DCF составляет 5 лет, за исключением отраслей с длительным сроком службы, таких как горнодобывающая промышленность, нефть и газ и инфраструктура, где технические отчеты могут использоваться для построения долгосрочного прогноза.

Прогнозируйте доход

Существует множество способов прогнозирования доходов, но в основном они делятся на два основных направления: на основе роста и на основе движущих сил.

Прогноз, основанный на росте, легче понять и применить для стабильных предприятий, где можно использовать базовый годовой показатель роста. Этого достаточно для многих моделей DCF

Прогноз на основе драйверов подробен и сложен в разработке. Это требует разделения доходов на многочисленные факторы, такие как цена, объем, продукты, клиенты, доля рынка и внешние факторы. Регрессионный анализ часто используется для установления взаимосвязи между базовыми факторами и ростом выручки.

Прогнозируйте расходы

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

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

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

Прогнозировать капитальные активы

После того, как отчет о прибылях и убытках будет частично заполнен, пришло время спрогнозировать капитальные активы. Прогнозирование капитальных активов обычно включает такие статьи баланса, как основные средства, технологии, исследования и разработки (НИОКР) и оборотный капитал.

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

Прогнозировать структуру капитала

Способ построения будет во многом зависеть от того, какой тип модели DCF вы создаете. Наиболее распространенный подход — сохранить текущую структуру капитала компании без каких-либо существенных изменений, кроме известных вещей, таких как срок погашения долга.

Конечная стоимость

Конечное значение — очень важная часть модели DCF. Он составляет более 50% чистой приведенной стоимости (NPV) бизнеса, как правило, если прогнозируемый период составляет пять лет или меньше. Существует два способа расчета конечной стоимости: метод множественного выхода и метод непрерывного роста.

Подход с постоянными темпами роста предполагает, что денежный поток, генерируемый в конце прогнозируемого периода, постоянно растет с постоянной скоростью. Например, денежный поток бизнеса составляет 10 миллионов долларов и постоянно растет на 2% при стоимости капитала 15%. Конечная стоимость равна 10 млн долларов США / (15–2 %) = 77 млн.

При использовании подхода с несколькими выходами предполагается, что бизнес продается разумному покупателю, который заплатит за него. Это означает, что мультипликатор EV/EBITDA равен или близок к текущим торговым значениям для сопоставимых компаний. Например, если бизнес имеет 6,3 млн EBITDA, а аналогичные компании торгуются с коэффициентом 8x, то конечная стоимость составит 6,3 млн x 8 = 50 млн. Затем эта стоимость дисконтируется до текущей стоимости, чтобы получить чистую приведенную стоимость конечной стоимости.

Время поступления денежных средств

Очень важно уделять пристальное внимание срокам денежных потоков в модели DCF, поскольку не все периоды времени обязательно равны. В начале модели часто бывает «заглушенный период», когда поступает только часть годового денежного потока. XNPV и XIRR — это простые способы указать время движения денежных средств при построении модели DCF. Лучше всего использовать эти функции над NPV и IRR в Excel.

Анализ чувствительности в модели DCF

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

Читайте также: