Уокенбах Формулы в Excel

Часто проблема возникает там, где платеж меньше суммы процента. В рассмотренном примере, неоплаченный долг равен $150 000, а процент в первом месяце составляет $675 ($150000 * 0,45%). Если платеж меньше, чем процент, то задолженность будет возрастать, а заем увеличится до бесконечности (скорее будет казаться растущим до бесконечности). Если это произойдет, функция К П Е Р возвратит сообщение об ошибке #число!.

ПРИМЕР 23

Соглашениео потребительском займе предоставляет мне кредит $1 000 с оплатой $100 в месяц в течение 12 месяцев. Какая процентная ставка?

Используемая функция: СТАВКА (кпер; плт; пс; бс; тип; предположение) Следующая формула возвращает 3,503153%:

=СТАВКА(12;-100;1000;0;1)

Перед тем как вы оцените, насколько это соглашение великодушно, запомните, что выплаты производятся ежемесячно. Таким образом, результат равен ежемесячной процентной ставке! Эффективная ежегодная ставка равна 51,16%, подсчитанная так, как указано ниже:

=((1+0/03503153)А12)-1

Ежегодная ставка, основанная на номинальной ежемесячной основе, возвращает 42,05%, вычисленной, как указано ниже:

=3,503153*12

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

ПРИМЕР 24

Я беру заем со вздутыми выплатами размером $300 000 на 15 лет, с выплатой только $100 000 на ежемесячной основе. В конце срока выплачивается баланс $200 000. Процентная ставка равна 0,4% в месяц, а платежи производятся ежемесячно. Какой размер ежемесячной выплаты?

Привычный тип займа (используемый для увеличения занимаемой суммы) это так называемый заем со вздутыми выплатами. Заем делится на две части: 1) выплачивается часть, которая полностью погашается до конца срока, 2) часть "вздутых выплат". На протяжении срока займа для "вздутой" части займа выплачивает только процент. Начальная стоимость "вздутой" части займа оплачивается, как сумма в конце займа.

Для проведения расчетов займа со вздутыми выплатами в формулах ПС, ПЛТ, СТАВКА и КПЕР используется правильно определенный параметр бс.

Используемая функция: ПЛТ (ставка; кпер; пс; бс; тип) Следующая формула возвращает -$1580,41:

=ПЛТ(0,4%;180;3 00000;-200000;0)

Обратите внимание, что сумма общего займа ($300 000) определяется параметром пс. Чтобы проверить полученное значение достаточно рассчитать значение начального займа

(пс). Следующая формула возвращает значение $299 999,43 (ошибка округления). =ПС(0,4%;180,-1580,41;-200000;0)

Выплаты при "вздутом" займе можно сравнить с выплатами при обычном займе. Следующая формула возвращает $202 509,64 (обычный заем).

=ПС(0,4%;180;-1580,41;0;0)

Выплаты при обычном займе $300 000 составляют -$2 341,24. Это значение рассчитывается по формуле.

=ПЛТ(0/ 4%;180;3 00000;0;0)

Последний пример амортизации можно адаптировать под "вздутый" заем, добавив в функции ПС, ПЛТ, КПЕР и СТАВКА ненулевой аргумент бс.

"Вздутый" заем также рассчитывается и с помощью функции БС. Используя ее, внимательно анализируйте знаки подставляемых в нее аргументов. Если функция БС возвращает положительное значение, то это значит, что заем переплачен. Отрицательное значение выражает "вздутую часть" займа.

Обычно подобные расчеты разбиваются на два этапа. Сначала рассчитывается нормальная амортизация. Затем подсчитывается размер "вздутой части" займа. В следующем примере эта операция рассмотрена детально.

Пример 25

Если банк настаивает на амортизации $200 000 по займу, то какую дополнительную сумму я могу одолжить при "вздутом" займе и ежемесячных выплатах $3 000? Срок займа 10 лет, а ставка 0,4% в месяц.

Используемая функция: ПЛТ (ставка; кпер; пс; бс; тип)

Первый шаг — вычислить оплату обычного амортизационного займа $200 000. Следующая формула возвращает -$2 101,81:

=ПЛТ(0,4%;120;200000;0;0)

Если вы можете выплачивать $3 000, то дополнительная сумма $898,19 может быть оплачена как процент "вздутого" займа (то есть, $3 000 - $2 101,81). Элемент вздутых выплат можно подсчитать по известной сумме процентной ставки. Следующая формула, представляющая собой элемент "вздутых выплат", возвращает $224 546,88:

=898,19/0,4%

Вычисление можно проверить, вычислив выплаты для полного займа $424 546,88 с элементом вздутых выплат $224 546,88. Следующая формула возвращает -$3 000:

=ПЛТ(0/4%;120;42454б/88;-22454б/88;0)

Преобразование процентных ставок

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

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

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

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

Методы указания процентных ставок

Существует три часто используемых метода указания процентных ставок:

•Номинальная ставка. Процент указывается на ежегодной основе, в зависимости от частоты выплат за год. Например, часто используемая годовая процентная ставка, равная 6%, при перерасчете равняется 0,5% в месяц (с регулярными выплатами).

•Ежегодная эффективная ставка. Процентная ставка, представляющая собой сумму, насчитываемую за один год. Например, при 10%-ой ежегодной эффективной ставке, с суммы $1 000 насчитывается $100 в конце каждого года.

•Периодическая эффективная ставка. Процентная ставка, представляющая собой сумму, насчитываемую на протяжении периода времени меньше года. Например, со ставкой 3% за полгода с суммы $300 насчитывается $9 каждые шесть месяцев.

Процентная ставка характерна тем, что использование одного из этих трех коэффициентов позволяет провести перерасчет для любого другого коэффициента. Например, рассмотрите процентную ставку 1% в месяц для вклада $100. В первом месяце на вклад начисляется процент, равный $1. Если процент кредита не изымается, то он добавляется к начальной сумме, и последующий процент основывается на новом балансе. 1% ежемесячная процентная ставка эквивалентна 12,6825% за год (эффективная годовая ставка). Она вычисляется с помощью следующей формулы:

=(1+0,01)Л 12-1

Другой пример номинальной ставки — процентная ставка, указываемая как 6% за год, насчитываемая раз в квартал. Это означает, что 1,5% (то есть, 6%/4) выплачивается или начисляется каждые три месяца.

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

Преобразование процентных ставок с помощью встроенных финансовых функций

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

Используя надстройку финансовых функций, можно вводить функцию на рабочий лист вручную или использовать диалоговое окно вставки функций Excel (функции размещаются в категории Финансовые). Табл. 11.1 содержит список 10 функций преобразований процентных ставок, содержащихся в надстройке финансовых функций. В таблице также показан эквивалент формуле Excel.

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

•Процентная ставка, которую вы имеете (Ef f, AnnEf f, или Nom).Обратите внимание, что частота исходных эффективных и номинальных ставок обозначается как х.

•Знак связи, который определяет направление преобразования, — это символ подчеркивания (_).

• Желаемая (конечная) процентная ставка (Ef f, AnnEf f, или Nom). Опять-таки, частота сложных процентов обозначается х (если она такая же, как и частота имеющейся ставки) или у (если другая).

Таблица 11.1.Пользовательские функции VBA преобразования обычной ставки

Упорядочение аргументов выполняется также просто:

•Первый аргумент — всегда имеющаяся процентная ставка.

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

•Если имеется вторая известная частота, отличная от х или ежегодная, то используется третий аргумент, Freqy.

Эффективная стоимость займов

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

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

В этом разделе представлено четыре примера, демонстрирующие вычисление эффективной стоимости займов.

Воздействие платежейнаэффективнуюставку

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

ПРИМЕР 26

Банк указывает номинальнуюставку займа 7% со сложнымипроцентами ежемесячно, и вы заинтересованы в займе $150 000 больше, чем на 10 лет, с ежемесячными выплатами. Банк предоставляет заем со ставкой 2% за открытиесчета и дополнительной оплатой $25 на обслуживание счета.Какая ежегоднаяэффективная стоимостьзайма?

Рабочий лист, который предоставляет решение этой задачи, содержится на Web-узле. Известные сведения вводятся в раздел данных рабочего листа. Табл. 11.2 представляет список ключевых формул, которые выполняют вычисления. Для ясности, формулы содержат действительные значения, а не ссылки наячейки.

Таблица 112 . Формулы, используемые нарабочем листе, показанном нарис. 11.5

В ячейке В19 используетсяпользовательская функция VBA.

Платежи основываются на сумме займа $150 000, но эффективная стоимость основывается на том факте, что после вычитания платы за открытие заемщик получает только $147 000. Точно также обстоят дела с фактическими платежами свыше суммы обслуживания счета.

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

Займы сединообразной ставкой

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

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

ПРИМЕР 27

Потребитель финансирует покупку автомобиляс одинаковой ставкой займа $15 000 на 18 месяцев. Процентная ставка займа 10% * 1,5 этой суммы добавляется к займу, и он ежемесячновыплачивает 1/18 полученнойсуммы в течение 18 месяцев. Какаяэффективная стоимостьзайма?

Наиболее легкий путь для решения этой задачи — использовать функцию Ef f x_AnnEf f (пользовательская функция VB А). Следующая формула возвращает 3,62%: =Effx_AnnEff(СТАВКА(18;-172 50/18;15000;0;1);12)

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

Беспроцентные займы

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

ПРИМЕР 28

Потребитель покупает аудиосистему с высоким качеством воспроизведенияпо цене в прейскуранте $3 000 на "беспроцентных" условиях кредита в 12 месяцев с платежами авансом. Он мог бы купить идентичнуюсистемуза $2 500 наличными или при обычномкредитовании. Какая эффективнаястоимостьэтого займа?

Опять-таки, пользовательская функция Ef fx_AnnEf f VBA обеспечивает наиболее простое решение. Эта формула возвращает 51,16%:

=Effx_AnnEff(CTABKA(12;-(3000/12); 2500;0;1);12)

Такие вычисления часто более сложные, когда эквивалентная цена в наличных денежных средствах субъективна (например, рынок б/у автомобилей).

Можно выполнить подобные вычисления для других типов соглашения, например: "Заплатите сегодня 25% и ничего не надо платить на протяжении 12 месяцев". Снова следует установить эквивалентную наличную цену, а потом сравнить вычисления с той ценой, которая скорее раздувается розничным торговцем, предлагающим "беспроцентный" кредит.

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

Заем с"ежегодными" выплатами

Практика, которая внедряется в настоящее время, — рассчитать платежи на основе "ежегодной ставки", и обязать заемщика выплачивать 1/12 полученной суммы ежемесячно. Расчет облегчен предварительно созданными таблицами ежемесячных платежей с кредита

$1 000. Эта практика возымела большой успех частично из-за того, что подобный кредит имеет низкую рекламируемую ставку, чем номинальная илиэффективная.

ПРИМЕР 29

Банк предлагаеткредит $100 000 со ставкой 7% на 10 лет, где ежемесячные платежи рассчитываются как 1/12 суммы, выплачиваемой ежегодно. Какая ежегодная эффективная стоимость ?

Следующая формула (которая использует функцию Ef fx_AnnEf f) возвращает 0,7522% (ежегодная эффективная ставка): Effx_AnnEff(СТАВКА(10*12;ПЛТ(7%;10;100000;0;0)/12;100000;0;0);12)

Вычисление процента иосновных платежей

В этом разделе обсуждаются четыре функции Excel, которые позволят:

•Вычислить процент или основные платежи (функции ОСПЛТ иПРПЛТ)

•Вычислить суммарный процент или основные платежи между любыми двумя периодами времени

Использование функций ОСПЛТ иПРПЛТ

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

Если вы уже создали план амортизации, эти функции не станут вам особенно полезны, так как вы можете просто обратиться к плану. Функции осплт и ПРПЛТ используются, когда надо определить соотношение процент /начальный вклад для конкретного платежа.

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

ОСПЛТ(ставка;период;кпер;пс;бс;тип) ПРЛТ(ставка;период;кпер;пс;бс;тип)

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

ПРИМЕР 30

Потребитель получает заем на покупку автомобиля (ежемесячные платежи)$20000 при ежегодной ставке8%. Какой процент и начальная сумма заключительного платежа займа?

На рис. 11.5 показано решение, открытое на рабочем листе.

тн

Puc. 11.5. На этом рабочем листе вычисляется процент и начальные параметры на протяжении любых периодов выплаты займа

Используемая функция: ПРПЛТ(ставка; период; кпер; пс; бс; тип) Эта формула вычисляет процент последнего платежа и возвращает -$4,15:

=ПРПЛТ(8%/12;36;3 6;20000;0;0)

Следующая формула вычисляет основную сумму заключительного платежа и возвращает -$622,58:

=ОСПЛТ(8%/12;36;36;20000;0;0)

К концу срока займа практически вся выплата равняется основному капиталу. Чтобы сравнить это с первым периодом займа, поменяйте аргумент срок на 1. После этого формула возвратит -$133,33 (процент) и -$493,39 (основная сумма).

Можно проверить вычисления с помощью функции плт (возвращает общую сумму: процент плюс основной капитал). Следующая формула возвращает -$626,73, что составляет сумму выплаты займа (и сумму результатов двух предыдущих формул):

= П Л Т ( 8 % / 1 2 ; 3 6 ; - 2 0 0 0 0 ; 0 )

Использование функций ОБЩДОХОД и ОБЩПЛАТ

Функции ОСНПЛАТ и ППЛАТ могут быть весьма полезны. Однако часто надо знать процент или начальный капитал последовательных выплат. В этом случае обычно используются функции ОБЩДОХОД и ОБЩПЛАТ. Эти функции нужны для создания ежегодных планов амортизации и установки правильного процента для налоговой декларации. Синтаксис этих функций показан ниже (обязательны все параметры):

ОБЩДОХОД(ставка;кпер;пс;начальн__период;конечн_период;тип)

ОБЩПЛАТ(ставка;кпер;пс;начальн_период;конечн_период;тип)

Эти функции доступны только приустановке надстройки "Пакет анализа".

ПРИМЕР 31

Потребительзанимает сумму $250 000, подлежащую выплате свыше 10 лет при 5,6% номинальной ежемесячнойставке со сложными процентами. Какая сумма процента и начальныйкапитал на первом году займа?

Следующая формула предназначена для определения начального платежа и возвращает $13 512,31:

=ОБЩПЛАТ(Ыотх_Е££х(5/б%;12);10*12;250000;1;12;0) Следующая формула возвращает $19 194,42 (выплата процента):

=OBIUflOXOfl(Noinx_Effx(5,6%;12) ; 10*12 ; 250000 ; 1; 12 ; 0 )

Можно проверить эти результаты, используя функцию ПЛТ для вычисления выплат. Следующая формула возвращает $32 706,74, что равно сумме предыдущих результатов:

=^T(Nomx_Effx(5,6%;12);10*12 /250000;0; 0) *12

Эти все формулы используют пользовательскую функцию Nomx_Ef fx.

Соответствие процентов и частоты выплаты

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

ПРИМЕР 32

Банк указывает номинальную ставку 6,3% в месяц, но позволяет проводить еженедельные платежи, эквивалентные процентной ставке. Если я занимаю $300 000 на 10 лет, какие должны быть еженедельные платежи?

Простой путь решить подобную задачу — использовать обычную функцию преобразования Nomx_Ef fy. Эта формула возвращает $777,51:

=^T(Nomx__Effy(6,3%;12,52) ; 10*52 ; 300000 ; 0 ; 0 )

ПРИМЕР 33

Мы открылимноголетний счет, с которого нужно ежемесячноснимать $12 500. Какой эквивалент ежегодной суммы при использовании депозитной ставки с номинальными процентами 7% в месяц за год? Ежемесячная выплата проводится наперед, а эквивалентная сумма подсчитывается в конце каждого года.

Сначала вычислите ежемесячную эффективную ставку (используя пользовательскую функцию VBA). Следующая формула возвращает 0,58333%:

=Nomx_Effx(7%;12)

Потом вычислите эквивалентную ежегодную сумму, используя функцию ВС. Эта формула возвращает -$154 907,29:

=-БС(0,5833 3%;12;-12 500;0;0)

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

Ограничения финансовых функций Excel

Основные финансовые функции (ПС, БС, ПЛТ, СТАВКА, КПЕР, ОБЩДОХОД и ОБЩПЛАТ) Excel очень полезны, но у них имеется два общих ограничения:

•Они обрабатывают только один уровень процентной ставки.

•Они обрабатывают только один уровень платежа.

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

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

•БЗРАСПИС.Возвращает будущую стоимость первоначальной основной суммы после применения ряда (набора) ставок сложных процентов.

•ВСД. Возвращает внутреннюю ставку доходности для ряда потоков денежных средств, представленных численными значениями.

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

•МВСД. Возвращает модифицированную внутреннюю ставку доходности для ряда периодических денежных потоков. МВСД учитывает как затраты на привлечение инвестиции, так и процент, получаемый от реинвестирования денежных средств.

•ЧИСТВНДОХ. Возвращает внутреннюю ставку доходности для графика денежных потоков, которые не обязательно носят периодический характер.

•ЧИСТНЗ. Возвращает чистую приведенную стоимость для денежных потоков, которые не обязательно являются периодическими.

Отсроченное начало ряда регулярных выплат

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

=ПС(ставка;кпер;плт;бс;тип)*(1+ставка)Л -ОТС__ПЕР

Здесь ОТС_ПЕР представляет число периодов, для которых отстрачивается первый платеж наличными.