Рубрики
Формулы

Кредитный калькулятор «на коленке»

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

Это важно!

Методика расчета основана на классической теории финансирования. Современные банки могут применять другие методы начисления процентов, например, с учетом точного количества дней в каждом месяце, или не применять сложные процентные ставки, а использовать метод 360 дней. Если вам нужен точный расчет — стройте точную модель платежей и используйте функцию Goal seek (Подбор значения).

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

Калькулятор можно скачать по этой ссылке.

В приложенном файле корректируйте синие ячейки. Желтые ячейки содержат формулы.

Если вы захотите внести какие-то изменения, то снимите защиту листа. Лист защищен без пароля.

Если у вас остались какие-то вопросы, вы можете задать их в комментариях, написать в наших группах ВКонтакте или на Facebook, или написать мне электронное сообщение на edward@youcanexcel.ru.

Рубрики
Формулы

Функция PMT (ПЛТ)

Решили взять ипотечный кредит? Не хотите тратить время на поход в банк, чтобы узнать сумму платежа? Большинство банков размещают на своих сайтах информацию о ставке по кредиту. Зная сумму и срок займа, вы можете легко рассчитать сумму ежемесячного аннуитетного платежа. Математически это возможно только методом подбора. В Microsoft Excel это можно сделать двумя способами: построить таблицу платежей и подобрать значение (может быть очень удобно, если вы планируете досрочное погашение в определенные периоды) или пользуясь формулой PMT (ПЛТ). Если вы хотите воспользоваться готовым кредитным калькулятором, жмите сюда.

Синтаксис: =PMT(rate;nper;pv;[fv];[type])

rate — ставка займа, в процентах за период (следует выбрать период платежа, обычно это месяц, но могут быть и другие варианты). Указывается в процентах, например 5% или 0.05.
nper — количество периодов, в течение которых производятся аннуитетные платежи. Будьте очень внимательны с периодом: месяц это, квартал, или год?
pv — сумма займа — сколько мы берем в кредит в начале срока
[fv] — необязательное — остаток займа после истечения последнего периода, если его опустить, то считается, что остатка нет
[type] — необязательное — как платятся проценты: в начале периода или в конце. 0 (или не заполнять) — в конце, 1 — в начале.

Это важно:

Договор должен быть аннуитетным!

Методика расчета основана на классической теории финансирования. Современные банки могут применять другие методы начисления процентов, например, с учетом точного количества дней в каждом месяце, или не применять сложные процентные ставки, а использовать метод 360 дней. Если вам нужен точный расчет — стройте точную модель платежей и используйте функцию Goal seek (Подбор значения).

Пример:

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

Если у вас остались какие-то вопросы, вы можете задать их в комментариях, написать в наших группах ВКонтакте или на Facebook, или написать мне электронное сообщение на edward@youcanexcel.ru.

Рубрики
Формулы

Функция NPV (ЧПС)

Необходимо посчитать чистые приведенные потоки денежных средств (net present value — NPV)? Для этого в Microsoft Excel есть готовая встроенная функция. Не тратьте время на то, чтобы «нарисовать» метод расчета, ставки дисконтирования и т.п. Просто воспользуйтесь функцией NPV (ЧПС).

Синтаксис: =NPV(rate;value1;[value2]…)

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

value1;[value2]… — потоки денежных средств, которые могут быть представлены как диапазоны, конкретные значения через точку запятой или несколько диапазонов. Важно, что между потоками должен быть одинаковый период времени, например, месяц или год.

Пример:

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

При ставке дисконтирования (включая инфляцию) 15% такой проект выгоден предприятию. И принесет всего около 8 у.е. дохода за 5 периодов в дисконтированном выражении.

Важное замечание:

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

Другие полезности:

Годовую ставку дисконтирования можно легко перевести в месячную по следующей формуле

MR = (1+AR)^(1/12)-1
где MR — месячная ставка дисконтирования,
AR — годовая ставка дисконтирования, а
^ — возведение в степень.

Подробнее о различных формулах и функциях в Microsoft Excel вы можете прочитать в книге Формулы в Microsoft Excel 2010 Джона Уоренбаха. Книга описывает, как эффективно использовать различные функции и как эффективно сочетать их в формулах. В ней также описаны специальные формулы, такие как финансовые и формулы массива. Книга учит эффективно использовать сводные таблицы и диаграммы. Она определенно стоит того, чтобы иметь ее в своей библиотеке.

Рубрики
Формулы

Функция IRR (ВСД)

Вам необходимо рассчитать внутреннюю ставку доходности проекта или контракта? Если вы финансист или экономист, то вы знаете, что нет алгебраического метода расчета, который дал бы точный результат. Это делается двумя способами: (1) расчетом чистых дисконтированных потоков денежных средств при двух ставках дисконтирования и линейной интерпретацией данных (жаль, что дисконтирование носит нелинейную природу), или (2) методом подбора.

Первый метод используется если вы считаете ВСД на клочке бумаги, второй — если у вас есть компьютер под рукой. На современном компьютере расчет ставки доходности методом подбора занимает доли секунды. Для этих целей в Microsoft Excel есть встроенная функция IRR (ВСД).

Синтаксис: =IRR(values;[guess])

values — диапазон с потоками денежных средств.
guess — необязательное поле — ваше предположение, какая ставка приблизительно получится. Оно нужно для того, чтобы начать от этой ставки расчет. Обычно у вас есть понимание о том, какая ориентировочно ставка дисконтирования может быть заложена в проект (контракт).

Пример:

Купить или арендовать. Вы осмысливаете покупку или аренду, скажем, грузовика, который будет приносить вам прибыль (предположим, вы транспортная компания). Купить грузовик вы можете за 2,5 миллиона рублей (цифры взяты с потолка), аренда обойдется вам в 600 тысяч рублей/год. Вы знаете, что срок полезного использования грузовика — пять лет, после чего он обладает остаточной стоимостью, скажем, 400 тысяч. После аренды грузовик остается у арендодателя. Предположим, что оплата производится авансом на год вперед. Свободных средств на покупку у вас нет, но есть возможность привлечь финансирование под 18% годовых. Что выгоднее?

Постройте несложную таблицу потоков денежных средств и посчитайте внутреннюю ставку доходности.

Как видно из таблицы вверху, внутренняя ставка доходности (в данном случае не IRR (internal rate of return), а IIR (internal implicit rate)) составляет 15% годовых, что дешевле займа в банке.

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

Это важно:

Функция считает, что диапазон содержит денежные потоки через равные промежутки времени. Результат выдается в формате «процентов за период». Так как диапазон в данном примере был равен году, то результат получился в годовых процентах. Чаще такие модели рассчитываются помесячно (они точнее отражают потоки денежных средств).

Подробнее о различных формулах и функциях в Microsoft Excel вы можете прочитать в книге Формулы в Microsoft Excel 2010 Джона Уоренбаха. Книга описывает, как эффективно использовать различные функции и как эффективно сочетать их в формулах. В ней также описаны специальные формулы, такие как финансовые и формулы массива. Книга учит эффективно использовать сводные таблицы и диаграммы. Она определенно стоит того, чтобы иметь ее в своей библиотеке.

Рубрики
Формулы

Функции AND (И), OR (ИЛИ) и NOT (НЕ)

Любому программисту на уровне подкорки известно, как пользоваться функциями AND, OR и NOT. Они впитывают это знание с молоком матери. //в большинстве языков, правда, это не функции, а операторы, но все же…

Если вы не программист и вам страшно смотреть на любой программный код, вам сюда!

Небольшая вводная:

Эти функции предназначены для обработки логических значений TRUE (ИСТИНА) и FALSE (ЛОЖЬ). Если вы изучали логику в институте, то вы знаете, что любое суждение может быть либо истинным, либо ложным, но не может быть и истинным и ложным одновременно, а также обязательно должно быть или истинным или ложным (не может быть не истинным не ложным одновременным). Именно из этих предпосылок исходит компьютерная логика.

Так например выражение 2=2 — истинно, а 3<2 — ложно.

Преобразования логических значений в Excel производится с помощью функций AND (И), OR (ИЛИ) и NOT (НЕ).

Логика этих функций

Функция AND(logical1;logical2;…) проверяет, все ли значения logical1, logical2 и т.п. являются истинными. Если все они являются истинными, то она выдает TRUE (ИСТИНА), если хотя бы одно из них ложно, то она выдаст FALSE (ЛОЖЬ).

Функция OR(logical1;logical2;…) проверяет, является ли хоть одно из значений logical1, logical2 и т.п. истинным. Если хоть одно из них является истинным, то она выдает TRUE (ИСТИНА), если ни одно из них не истинно, то функция возвращает FALSE (ЛОЖЬ).

Функция NOT(logical) оборачивает логическое значение. Если внутрь функции NOT поставить TRUE (ИСТИНА), она вернет FALSE (ЛОЖЬ). Если внутрь поставить FALSE (ЛОЖЬ), она вернет TRUE (ИСТИНА).

Если у вас остались какие-то вопросы, вы можете задать их в комментариях, написать в наших группах ВКонтакте или на Facebook, или написать мне электронное сообщение на edward@youcanexcel.ru.

Рубрики
Формулы

Функция IFERROR (ЕСЛИОШИБКА)

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

Удаление формул, в которых есть ошибка вычисления — это прямой путь к ошибкам. Каждая строка таблицы ДОЛЖНА иметь абсолютно одинаковые формулы (для этого даже рекомендуют использовать ряды данных).

Я предлагаю встраивать проверку на ошибку с помощью функции IFERROR (ЕСЛИОШИБКА).

Описание: функция проверяет некоторое значение на то, является ли оно ошибкой — если нет, то выдает это значение; если да — то выдает другое значение, указанное пользователем.

Синтаксис: =IFERROR(value;value_if_error)

value — значение, в котором может быть ошибка; вы ожидаете, что функция может выполнять деление на ноль? функция VLOOKUP (ВПР) может не найти значение? любую другую ошибку — встраивайте эту функцию сюда. Если ошибка не случится, то функция IFERROR (ЕСЛИОШИБКА) вернет это значение без изменений.

value_if_error — значение, которое выдаст функция, если в предыдущем будет ошибка.

В отличие от комбинации функций IF(ISERROR(…);value_if_error;…) функция IFERROR не делает вычисления дважды, что может быть критично при обработке крупных массивов данных, особенно, если участвуют такие функции как VLOOKUP (ВПР), HLOOKUP (ГПР) или SUMIFS (СУММЕСЛИМН).

Если у вас остались какие-то вопросы, вы можете задать их в комментариях, написать в наших группах ВКонтакте или на Facebook, или написать мне электронное сообщение на edward@youcanexcel.ru.

Рубрики
Формулы

Функция IF (ЕСЛИ)

Редкая таблица обходится без условных формул — очень часто нам необходимо сделать различные расчеты в зависимости от выполнения того или иного условия. Например, разные отчисления делаются по разным ставкам в зависимости от возраста и пола «отчисляющего». Или разные виды затрат учитываются по разным ставкам. Как только появляется условие, функция IF (ЕСЛИ) становится полезной как никогда.

Другой вариант расчетов с условием описывается в этой статье.

Синтаксис: =IF(logical_test;[value_if_true];[value_if_false])

logical_test — это условие, что надо проверить; собственно, здесь может быть любое выражение (в том числе и функция), которое своим результатом имеет TRUE (ИСТИНА) или FALSE (ЛОЖЬ). Важно — никогда не используйте IF для проверки ошибки в функции VLOOKUP (ВПР) — это приведет к двойным вычислениям, что особенно ощутимо при работе с поисковыми функциями! Позвольте привести этот пример =IF(ISNA(VLOOKUP(…));0;VLOOKUP(…)) — НИКОГДА! Для этого есть функция IFERROR (ЕСЛИОШИБКА).

value_if_true — соответственно, значение, если логический тест равен TRUE (ИСТИНА), если его опустить, то функция возвращает ноль.

value_if_false — значение, если логический тест равен FALSE (ЛОЖЬ), если его опустить, то функция возвращает ноль.

Прочая информация:

Иногда бывает нужно проверить два, три и более условия — выполняются ли они все, или хотя бы одно из них. В этом вам помогут другие логические функции, а именно AND (И), OR (ИЛИ), NOT (НЕ).

Если необходимо реализовать дерево решений, то можно делать функцию IF (ЕСЛИ) внутри другой функции IF (ЕСЛИ). Excel позволяет сделать несколько уровней вложений — не скажу точно, сколько, что-то около 16. Это будет невероятно сложная формула! Не думаю, что обычному финансисту/бухгалтеру/экономисту/менеджеру/другому офисному сотруднику это может понадобиться (хоть и не исключаю, что это может быть необходимо, скажем, для расчета аэродинамической модели или чего-то в этом роде).

Если у вас остались какие-то вопросы, вы можете задать их в комментариях, написать в наших группах ВКонтакте или на Facebook, или написать мне электронное сообщение на edward@youcanexcel.ru.

Рубрики
Формулы

Функция SUBTOTAL (ПРОМЕЖУТОЧНЫЕ.ИТОГИ) для суммирования

Функция SUBTOTAL (ПРОМЕЖУТОЧНЫЕ.ИТОГИ) была предназначена для выполнения операций с диапазонами данных, которые содержат промежуточные итоги. Например, подитоги категорий.

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

Синтаксис: =SUBTOTAL(function_num;ref1;[ref2]…)

function_num — номер используемой функции — используйте 9 для суммы,
ref1; [ref2]… — ссылки на ячейки или диапазоны данных — те, которые мы хотим просуммировать.

Особенности:

(1) и самое главное, при расчете суммы не берет в учет другие функции, рассчитанные с помощью функции SUBTOTAL,
(2) при расчете суммы игнорирует ячейки, скрытые фильтром,
(3) вопреки расхожему мнению она суммирует ячейки скрытые форматом и группировкой.

Благодаря этим особенностям функция SUBTOTAL (ПРОМЕЖУТОЧНЫЕ.ИТОГИ) существенно отличается от обыкновенной суммы (SUM, СУММ) — об этом подробнее читайте в статье SUM (СУММ) против SUBTOTAL (ПРОМЕЖУТОЧНЫЕ.ИТОГИ).

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

Если у вас остались какие-то вопросы, вы можете задать их в комментариях, написать в наших группах ВКонтакте или на Facebook, или написать мне электронное сообщение на edward@youcanexcel.ru.

Рубрики
Формулы

Использование колонок вместо конкретных ячеек

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

Использование в функции

Microsoft Excel — это очень умная программа. Функции составлены таким образом, чтобы автоматически выбирать правильный вариант ссылки. Если функция предполагает ссылку на массив данных (например, функция SUM, COUNT и прочие) то ссылка будет восприниматься программой как колонка.

Если функция предполагает ссылку на конкретную ячейку, то ссылка будет восприниматься как ячейка на одну ячейку из той же строки. Например, в ячейке C4 стоит формула (=B:B), программа автоматически «догадается», что необходимо взять значение из ячейки B4 (та же строка, только из колонки B).

В некоторых функциях ссылки на колонки могут восприниматься одновременно и как ссылка на одну ячейки и как ссылка на целую колонку. Возьмем для примера функцию VLOOKUP (ВПР). Она предполагает несколько параметров:

lookup_value — искомое значение — поскольку предполагается конкретное значение, если прописать ссылку на колонку, то формула будет иметь только одну ячейку,

table_array — массив данных, в котором будет производиться поиск — поскольку имеется в виду ссылка на массив, то формула будет автоматически ссылаться на целые колонки.

Прямое использование в формуле

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

Коротко про строки

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

Ссылка на строку записывается следующим образом. Например, «=1:1», или «=$1:$1».

Рубрики
Формулы

Условия без функции IF (ЕСЛИ)

Позвольте начать эту статью с нескольких очевидных предпосылок:

во-первых, в эксель встроено несколько функций, которые выдают своим результатом TRUE (ИСТИНА) или FALSE (ЛОЖЬ),

во-вторых, TRUE (ИСТИНА) математически равно единице, а FALSE (ЛОЖЬ) — нулю,

в-третьих, эти значения можно использовать в вычислениях,

и последнее, чтобы получить значения TRUE (ИСТИНА) или FALSE (ЛОЖЬ), необязательно прибегать к помощи функций, достаточно сравнить два значения.

Так, например, такая формула многим может показаться дикой, но в экселе она работает: «=(2*2)=5». Соответственно, она будет выдавать результатом FALSE (ЛОЖЬ). Вместо цифр можно подставлять ссылки на другие ячейки. Для чего это может потребоваться и как это использовать. Позвольте сделать для вас пару иллюстрированных примеров.

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

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

В этом примере колонки A:D — даны, E:F — расчетные.

Формула, которая видна в строке формул — как раз-таки делает расчет для ячеек в колонках E:F. Давайте разберем, как она работает.

Выражение $D:$D=$1:$1 сравнивает значение в колонке D со значением в строке 1. Если оно совпадает, то формула выдает TRUE (ИСТИНА или единица), если нет — FALSE (ЛОЖЬ или ноль). Если умножить ее на сумму из колонки C, то мы получим сумму при совпадении вышеуказанных столбца и строки.

Пример второй. Значения с разным параметром обрабатываются по-разному

Давайте усложним предыдущий пример. Предположим, нам надо рассчитать сумму отнесенных затрат: если затраты прямые — берем всю сумму, если косвенные — только 30% от этой суммы.

Здесь используются два взаимоисключающих условия (значение в колонке D не может быть равно и «Прям» и «Косв» для одной строки), поэтому задвоения затрат не произойдет. Первая часть формулы проверяет на то, являются ли затраты прямыми: если да, то берет всю сумму. Вторая часть, соответственно, проверяет, являются ли они косвенными: если да, то она берет 30% от суммы.

Обратите внимание, что формула разбита на две строки. Это сделано, чтобы было удобнее ее читать. Подробнее о чтении сложных формул — в этой статье.

Пример третий. Взять значение, если есть совпадение в другом списке

Предположим, что у нас где-то отдельно есть список счетов, которые мы хотим взять в учет для дальнейших расчетов. Обычно такой счет будет на отдельном листе рабочей книги. Но для примера я его вставил на тот же лист. Пожалуйста, в работе не делайте так, старайтесь по жизни руководствоваться принципом «Один лист — одна таблица!». Всегда!

Небольшая вводная по использованным функциям. VLOOKUP (ВПР) — ищет значение соответствующее искомому в заданном диапазоне (подробнее об использовании этой функции читайте в этой статье). Она выдает значение или ошибку #N/A, если совпадения нет.

Функция ISNA выдает TRUE (ИСТИНА), если параметр равен #N/A, FALSE (ЛОЖЬ) в обратном случае.

Функция NOT делает из TRUE — FALSE и наоборот.

Таким образом, NOT(ISNA(VLOOKUP(B:B;F:F;1;FALSE))) проверяет, есть ли в колонке F значение из колонки B: если есть, она выдает TRUE, если нет — FALSE. А если это умножить на сумму, то в ячейке будет сумма, только если счет есть в «другом списке».

Если у вас остались вопросы по этой теме или есть вопрос по любой другой, пожалуйста, задавайте их в комментариях, на страницах наших групп ВКонтакте или на Facebook или присылайте их мне на E-mail edward@youcanexcel.ru.