Excel предоставляет пять основных финансовых функций: ПС , БС , ПЛТ , СТАВКА и КПЕР . Все эти функции описаны в настоящем разделе и сопровождаются практическими примерами.

Выбор времени первого платежа

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

В финансовых функциях Excel время первого платежа определяется аргументом Тип .

Если первый платеж должен совершаться в течение месяца, используется значение 0 (оно принято по умолчанию).
Если первый платеж нужно выполнять сразу, используется значение 1.

Совет

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

Вычисление приведенной стоимости

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

ПС(ставка ;кпер ;плт ;бс;тип)

Аргументы финансовых функций

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

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

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

Количество периодов должно быть выражено в тех же единицах, которые используются в аргументах Ставка и Плт . К примеру, 30-летний заем с помесячными выплатами будет содержать 360 периодов. Именно это значение следует подставлять в параметр Кпер , а не 30.

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

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

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

ПС . Текущая приведенная стоимость инвестиции. Это первая операция транзакции, например, получение займа или вклад денег на депозит. Если транзакция состоит только из платежей, в ней может не существовать приведенной стоимости.

Тип . Этот аргумент определяет время внесения платежей.

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

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

ПС(0.12;10;1200;0;0)

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

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

Примечание

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

В приведенном выше примере была использована процентная ставка 12%. В результате получилось, что инвестиция размером в 6800 долларов принесет тот же доход, что и десятилетнее ожидание платежей по 1 200 долларов. Если плательщик предложит вам 7000 долларов немедленно, вы можете вложить их и получить лучший финансовый результат.

Теперь давайте вернемся к таблицам и предположим, что у вас есть обязательства, по которым вы должны ежегодно выплачивать кому-то по 1200 долларов в течение 10 лет. Формула выглядит следующим образом:

ПС(0.12;10;-1200;0;0)

Вместо входящего, в этой формуле использован исходящий денежный поток. Результат (-$6780,27) также имеет знак, противоположный предыдущему примеру. В обоих примерах сумма платежей формирует всю транзакцию, поэтому будущей стоимости не существует. Также в примере использовано значение по умолчанию аргумента Тип . Аргументы БС и Тип не являются обязательными; они были включены в пример только для наглядности.

Примечание

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

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

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

В качестве примера представим себе, что некоторый богатый родственник решил дать вам 100 тысяч долларов, но вы не можете получить их до своего сорокалетия. Если сейчас вам 25 лет, приведенная стоимость будущего платежа составит 31524,17 долларов. Эта сумма получена с помощью следующей формулы:

ПС(.08;15;0;100000)

Таким образом, это будет единовременный платеж ровно через 15 лет. Если бы у вас были сейчас какие-либо деньги, вы смогли бы инвестировать их под 8% годовых. Так как периодических платежей не будет, аргумент Тип в формуле опущен.

Результат формулы свидетельствует о том, что если бы у вас было сейчас 31524,17 долларов и вы бы инвестировали их под 8%, то через 15 лет получили бы сотню тысяч долларов.

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

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

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

ПС(.1/12;60;200;60000;1)

Давайте внимательно посмотрим на каждый из аргументов.

Вы определили, что при любых обстоятельствах сможете получить прибыль в 10% годовых, используя свои деньги в течение пяти лет. По этой причине процентную ставку мы установили в 10%.
Все аргументы должны охватывать один и тот же временной период. Так как выплаты будут осуществляться ежемесячно, все аргументы должны быть преобразованы соответствующим образом:
> аргумент Ставка делим на 12 месяцев;
> аргумент Кпер получаем, умножив 5 лет на 12 месяцев в году;
> аргументы Плт и БС оставляем без изменений;
> аргументу Тип присваиваем значение 1, так как предполагается, что первый платеж будет совершен немедленно.

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

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

Вычисление будущей стоимости

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

БС(ставка ;кпер ;плт ;пс;тип)

Будущая стоимость платежей

В этом примере мы предположим, что открыли накопительный счет для своего ребенка, чтобы к окончанию школы насобирать ему сумму, необходимую для платного обучения в университете. Начиная со следующего месяца, вы будете ежемесячно откладывать 50 долларов на этот счет под 3% годовых. Следующая формула показывает, что к совершеннолетию у вашего сына на счету будет 14297,02 долларов:

БС(.03/12;18*12;-50;0;0)

Годовую процентную ставку 3% мы преобразуем в месячную; 18 лет также преобразуем в месяцы. Приведенная стоимость отсутствует, так как вы только что открыли счет. Аргумент Тип равен нулю, так как вносить суммы вы начинаете со следующего месяца.

Будущая стоимость суммы вклада

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

Предположим, что вы открыли пенсионный счет, внесли на него 20 тысяч долларов и планируете через 15 лет выйти на пенсию.

БС(.08;15;0;-20000;0)

В данном примере предполагается, что пенсионный вклад гарантирует получение 8% годовых. Значение -20000 представляет двадцать тысяч долларов, уходящих от вас в банк. В результате мы получаем 63443,38 долларов – эту сумму вы получите через 15 лет при выходе на пенсию.

Округление в финансовых формулах

При использовании финансовых формул проблема округления значений ощущается особенно остро. Excel предлагает несколько функций для выполнения этой задачи: ОКРУГЛ , ОКРУГЛВНИЗ И ОКРУГЛВВЕРХ .

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

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

В отдельных случаях вычисления базируются на приблизительных данных или данных, полученных в результате эмпирического анализа или подбора параметров. Поэтому уже давно обычной практикой стало применение округленных значений (чтобы не утруждать себя вводом длинных значений). Предположим, что вы арендуете торговое помещение площадью 1537 квадратных метров по цене 43,55 долларов за квадратный метр. Простое умножение приводит к получению суммы $66936,35. Однако вы знаете, что арендная плата может изменяться (скажем, в диапазоне от 42 до 45 долларов). В результате конечная сумма аренды будет колебаться в небольших пределах. Чтобы избежать излишней неточности, конечную сумму можно округлить до ближайших ста или даже тысячи долларов.

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

Будущая стоимость платежей и суммы вклада

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

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

БС(.0575/12;5*12;-900;150000;0)

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

Совет

Лучше представить себе данную задачу следующим образом. Некто одолжил вам 150 тысяч долларов, чтобы выкупить закладную на дом, хотя на самом деле этого не произошло. Вычисленное значение -137435,10 – это сумма выходного потока по истечении пяти лет.

Вычисление сумм платежей

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

ПЛТ(ставка ;кпер ;пс ;бс;тип)

Вычисление платежей по займу

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

В предлагаемом примере предполагается, что вы покупаете машину стоимостью 32 тысячи долларов в кредит, и вам нужно вычислить сумму ежемесячных платежей. Вы внесли авансовый платеж размером в 4 тысячи долларов, а дилер предложил воспользоваться кредитом на четыре года с процентной ставкой 2,1% годовых.

ПЛТ(.021/12;4*12;28000;0;0)

Эта формула возвращает значение 608,69 долларов. Таким образом, если вы в состоянии платить такие ежемесячные взносы, то можете получить в кредит 28 тысяч долларов и полностью погасить его за 48 месяцев.

Предупреждение

Если одна из финансовых функций вернула ошибку #ЧИСЛО! или заведомо неправильный результат, прежде всего нужно посмотреть на направление денежного потока. Обращайте внимание на знаки сумм в примерах настоящего раздела, и вы лучше поймете, какие знаки нужно присваивать аргументам.

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

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

В следующем примере предположим, что на пенсионном счету у вас имеется 700 тысяч долларов. Теперь предположим, что вам нужно рассчитать снимаемые с этого счета суммы на следующие 20 лет так, чтобы на нем в результате осталось 100 тысяч долларов. Именно такие суммы вы сможете тратить каждый месяц.

ПЛТ(.06/12;20*12;-700000;100000;0)

Если вы точно знаете банковский процент на вклад (6%), то сможете снимать с пенсионного счета ежемесячно по 4798,59 долларов в течение 20 лет, и в результате у вас останется еще 100 тысяч долларов.

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

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

СТАВКА(кпер ;ставка ;пс ;бс;тип;предположение)

Ставки краткосрочных займов

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

Если вы взяли в кредит 200 долларов и согласились через 14 дней отдать 260 долларов, процентную ставку можно вычислить по следующей формуле:

СТАВКА(1;0;200;-260;0;0.01)*365/14

Значение периода равно единице по той причине, что предполагается всего одна проплата. Этот единственный период охватывает 14 дней, поэтому результат нужно разделить на количество дней в году (365) и умножить на 14. Полученный результат (782%) такой большой потому, что заем краткосрочный.

Примечание

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

Темпы роста

Чаще всего функцию СТАВКА используют для вычисления темпов роста на пенсионном счету.

Предположим, что баланс на пенсионном счету составляет 40 тысяч долларов на начало года и 48,5 тысячи – на конец. В течение года с каждой получки (т.е. раз в две недели) вы клали на счет по 200 долларов (т.е. осуществили 26 платежей). Следующая формула показывает, как пополнялись ваши инвестиции:

СТАВКА(26;-200;-40000;48500;0.01)*26

В данном примере функция СТАВКА возвращает темпы роста за каждый период, поэтому для получения годовой процентной ставки следует умножить это число на 26. Результатом будет ставка 7,49%.

Примечание

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

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

Беспроцентные займы на самом деле редко таковыми являются, так как интерес заимодателя уже учтен в стоимости товара. Предположим, что вы хотите купить кухню за 3 тысячи долларов и оформляете на нее беспроцентную рассрочку на 12 месяцев. Если бы у вас было достаточно наличных, вы смогли бы купить эту же кухню за 2500 долларов – фактически вы переплачиваете за рассрочку 500 долларов. Рассчитанная по следующей формуле приведенная процентная ставка составляет 35,07%:

СТАВКА(12;-3000/12;2500;0;0;0.01)*12

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

Вычисление количества периодов

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

КПЕР(ставка ;плт ;пс ;бс;тип)

Количество лет до выхода на пенсию

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

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

КПЕР(.1/12;-100;-350000;500000;0)

Предположим, что вы получаете по своим инвестициям 10% годовых; в этом случае функция вернет значение 41,8 месяцев, т.е. три с половиной года. Если вы знаете, сколько денег вам будет достаточно на неделю, и хотите накопить такую сумму, чтобы обеспечить себя на 20 лет, то можете скомбинировать функции КПЕР и ПС , и вы узнаете, через какой срок накопите нужную для пенсии сумму.

КПЕР(.1/12;-100;-350000;ПС(.1/52;20*52;-1000;0;0);0)

Функция ПС используется в качестве аргумента БС ; предполагается, что вам каждую неделю нужно снимать в течение 20 лет по тысяче долларов и что вы имеете 10% годовых. При этих предположениях вы сможете выйти на пенсию через 2,4 года.

Обе формулы, описанные в настоящем разделе, показаны на рисунке ниже.

Досрочное погашение кредита

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

В следующем примере предполагается, что вы имеете закладную на 200 тысяч долларов под 7,5% годовых. Следующие 20 лет вам придется вносить по 1611,16 долларов в месяц. Если рефинансировать в 5,75%, но оставить сумму платежей без изменений, следующая формула поможет подсчитать, на сколько лет раньше вы сможете погасить заем.

=(20*12)-КПЕР(.0575/12;ПЛТ(0.075/12;20*12;200000/0);200000;0;0)

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

Примечание

Функция КПЕР может возвращать дробный результат (например, 4,26 месяцев), однако вас он, вероятно, не удовлетворит. В этом случае воспользуйтесь встроенными в Excel средствами подбора оптимального результата (команда Данные →Работа с данными →Анализ “что если” →Подбор параметра ).

Функция СТАВКА вычисляет процентную ставку по займу или инвестиции, базируясь на величине будущей стоимости. В транзакциях, в которых процентная ставка не задана жестко, эта функция может быть использована для вычисления неявной ставки (ставки, по которой можно было бы получить такой же доход). Синтаксис функции следующий: СТАВКА(клер;ставка;пс;бс;тип;предположение) .

Ставки краткосрочных займов

Краткосрочные займы обычно должны погашаться в течение 14 дней и, как правило, предполагают 15 долларов комиссии с каждых ста взятых взаймы долларов. Если вы взяли в кредит 200 долларов и согласились через 14 дней отдать 260 долларов, процентную ставку можно вычислить по следующей формуле: =СТАВКА(1;0;200;-260;0;,01)*365/14 . Значение периода равно единице по той причине, что предполагается всего одна проплата. Этот единственный период охватывает 14 дней, поэтому результат нужно разделить на количество дней в году (365) и умножить на 14. Полученный результат (782%) такой большой потому, что заем краткосрочный (рис. 1).

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

Темпы роста

Чаще всего функцию СТАВКА используют для вычисления темпов роста на пенсионном счету. Предположим, что баланс на пенсионном счету составляет 40 тысяч долларов на начало года и 48,5 тысячи - на конец. В течение года с каждой получки (т.е. раз в две недели) вы клали на счет по 200 долларов (т.е. осуществили 26 платежей). Следующая формула показывает, как пополнялись ваши инвестиции (рис. 2): =СТАВКА(26;-200;-40000;48500;,01)*26 .

В данном примере функция СТАВКА возвращает темпы роста за каждый период, поэтому для получения годовой процентной ставки следует умножить это число на 26. Результатом будет ставка 7,49%.

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

Беспроцентные займы на самом деле редко таковыми являются, так как интерес заимодателя уже учтен в стоимости товара. Предположим, что вы хотите купить кухню за 3 тысячи долларов и оформляете на нее беспроцентную рассрочку на 12 месяцев. Если бы у вас было достаточно наличных, вы смогли бы купить эту же кухню за 2500 долларов - фактически вы переплачиваете за рассрочку 500 долларов. Рассчитанная по следующей формуле приведенная процентная ставка составляет 35,07%: =СТАВКА(12;-3000/12;2500;0;0;,01)*12 .

Проверить результаты функции СТАВКА можно, создав таблицу погашения кредита (рис. 3). Если баланс стремится к нулю, значит, процентная ставка вычислена правильно.

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

Синтаксис СТАВКА (кпер; плт; пс; бс; тип; предположение). (2.11)

Аргументы функции означают:

кпер

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

пс - приведенная к текущему моменту стоимость или общая сумма, на текущий момент равноценна ряду будущих платежей;

бс

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

предположение - предполагаемая величина ставки.

Функция СТАВКА рассчитывается методом последовательного приближения и может не иметь решения или иметь несколько решений. Если после 20 итераций погрешность определения ставки превышает 0,0000001, то функция СТАВКА возвращает значение ошибки # ЧИСЛО! В таком случае можно пытаться задать другой аргумент предположение, по умолчанию равен 10%. В большинстве случаев не надо задавать аргумент предположение.

Если необходимо рассчитать процентную ставку по формуле (1.3) при известной текущей стоимости пс, будущей стоимости бс, числе периодов кпер, то формула в Excel в общем виде запишется: СТАВКА (кпер;; пс; бс;; предположение). При расчетах по формулам (1.9) - (1.12), (1.14) - (1.17) (фиксированные обязательные или обычные периодические платежи) процентная ставка за расчетный период в Excel рассчитывается так:

СТАВКА (кпер; плт;; бс; тип; предположение).

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

СТАВКА (кпер; плт; пс;;; предположение).

Пример 2.24. Компании через два года потребуется 100 тыс. Грн. Компания готова вложить 5000 грн. сразу и по 2500 грн. каждого следующего месяца. Каким должен быть процент на инвестиционные ресурсы, чтобы получить необходимую сумму в конце второго года?

Решение : По формуле (2.11):

СТАВКА (24; -2500, - 5000; 100000) = 3,28% за месяц. Годовая процентная ставка составит 3,28% 12 = 39,36%.

> Расчет периодических платежей в Excel

Функция ПЛТ рассчитывает сумму периодического платежа для аннуитета на основе постоянства сумм платежей и процентной ставки:

Синтаксис ПЛТ (ставка; кпер; пс; бс; тип). (2.12)

Аргументы функции означают:

ставка

кпер - общее число периодов платежей по аннуитету;

пс - приведенная к текущему моменту стоимость, или общая сумма, которая на текущий момент равноценна ряду будущих платежей, называемая также основной суммой;

бс - требуемое значение будущей стоимости или остатка средств после последней выплаты;

тип

Расчет R из формул (1.9) - (1.12), (1.14) - (1.21) осуществляется в Excel функцией ПЛТ.

Если погашение осуществляется в конце периода, то формула имеет вид ПЛТ (норма; кпер; пс), поскольку тип = 0.

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

Пример 2.25. Необходимо накопить 4000 грн. за 3 года, откладывая постоянную сумму в конце каждого месяца. Какой должна быть эта сумма, если норма процента по вкладу составляет 12% годовых?

Решение: ПЛТ (12% / 12, 12 * 3;; 4000) = - 92,86 грн.

Пример 2.26. Банк выдал кредит 200 тыс. Грн. на 4 года под 18% годовых. Заем издана в начале года, а погашение начинается в конце года одинаковыми платежами. Нужно определить величину ежегодного погашения займа.

Решение: ПЛТ (18%, 4, - 200) = 74,35 тыс. Грн. Для банка выдана сумма - отрицательная величина, а вычисленные ежегодные поступления - положительные значения.

Платежи по процентам за заданный период на основе периодических постоянных выплат и постоянной процентной ставки рассчитывается с помощью функции Excel ПРПЛТ:

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

Аргументы функции означают: ставка - процентная ставка за период;

период - задает период, для которого надо найти платежи по процентам, значение должно быть в интервале от 1 до "кпер";

кпер - общее число периодов платежей по аннуитету;

пс - приведенная к текущему моменту стоимость или общая сумма, которая на текущий момент равноценна ряду будущих платежей, называемая также основной суммой;

бс - требуемое значение будущей стоимости или остатка средств после последней выплаты;

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

Если данная функция недоступна или возвращает ошибку # ИМЯ?, то установите и загрузите надстройку «Пакет анализа". Для этого в меню Сервис Выбрать команду Надстройки Excel. В списке надстроек выберите Пакет анализа и нажмите кнопку ОК. Следуйте инструкциям программы установки, если это необходимо.

Решение: ПРПЛТ (10% / 12, 1, 12 * 3; 800) = - 6,667 тыс. Грн.

Пример 2.28. За счет ежегодных отчислений в течение 6 лет был сформирован фонд в 500 тыс. Грн. Надо рассчитать, какой доход приносили вложения владельцу за последний год, если годовая ставка составляла 17,5%.

Решение: Доход за последний год (6 периодов) составил:

ПРПЛТ (17,5%; 6; 6;; 500) = 66,48110268 тыс. Грн.

Ежегодно полагалось ПЛТ (17,5%; 6;; 500) = - 53,627 тыс. Грн.

Сумма основного платежа по займу (выплата задолженности), который погашается равными платежами в конце или в начале каждого расчетного периода, на указанный период рассчитывается с помощью функции Excel ОСПЛТ:

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

или находится как разница между фиксированной периодической выплатой и процентам по непогашенной части займа. Аргументы функции означают: ставка - процентная ставка за период;

период - задает период, значение должно быть в интервале от 1 до "кпер";

кпер - общее число периодов выплат годовой ренты;

пс - приведенная стоимость, то есть общая сумма, которая равноценна ряду будущих платежей;

тип - число 0 или 1, обозначающее, когда должна производиться выплата.

Пример 2.29. Определите сумму основного платежа с двухлетней займа 2000 грн. за первый месяц из расчета 10% годовых. Начисление процентов ежемесячно.

Решение: Основной платеж по займу за первый месяц:

ОСПЛТ (10% / 12, 1, 2 * 12; 2000) = - 75,62 грн.

Накопленный доход по займу (сумму платежей по процентам), которая погашается равными платежами в конце или в начале каждого расчетного периода, между двумя периодами выплат рассчитывает в Excel функция ОБЩПЛАТ.

Синтаксис ОБЩПЛАТ (ставка; кпер; пс;

начпериод; конпериод; тип). (2.15)

Аргументы функции означают: ставка - процентная ставка; кпер пс нач_период -

кон_период -

тип - это выбор времени платежа.

Пример 2.30. Изданная заем под покупку недвижимости размером 125 тыс. Грн. сроком на 30 лет под 9% годовых, проценты начисляются ежемесячно. Определить величину процентных платежей а) за второй год, б) за первый месяц.

Решение: Кумулятивная выплата по процентам за второй год (с 13-го периода по 24-й) составит:

ОБЩПЛАТ (9% / 12; 30 * 12; 125 000; 13; 24; 0) = - 11135,23 грн. Одна выплата за первый месяц составит:

ОБЩПЛАТ (9% / 12; 30 * 12; 125 000, 1, 1, 0) = - 937,50 грн. Это же значение будет получено при расчете по формуле:

ПРПЛТ (9% / 12, 1, 30 * 12; 125 000) = - 937,50 грн. В Excel функция ОБЩДОХОД рассчитывает кумулятивную (нарастающим итогом) сумму, выплачиваемую в погашение основной суммы займа в промежутке между двумя периодами:

Синтаксис ОБЩДОХОД (ставка; кпер;

пс; начпериод; конпериод; тип). (2.16)

Аргументы функции означают:

ставка - процентная ставка;

кпер - это общее количество периодов выплат;

пс - это стоимость инвестиции на текущий момент;

нач_период - это номер первого периода, включенного в вычисления. Периоды выплат нумеруются, начиная с 1;

кон_период - это номер последнего периода, включенного в вычисления;

тип - это выбор времени платежа.

Пример 2.31. Изданная заем размером 125 тыс. Грн. сроком на 30 лет под 9% годовых, проценты начисляются ежемесячно. Определить величину основных выплат: а) за первый месяц; б) второй год (платежи с 13-го периода по 24-й).

решение:

а) ОБЩДОХОД (9% / 12; 30 * 12; 125000, 1, 1, 0) = - 68,27827118 грн.;

б) если заем погашается равными платежами в конце каждого расчетного периода, то размер выплаты задолженности за второй год составит:

ОБЩДОХОД (9% / 12; 30 * 12; 125000; 13; 24; 0) = - +934,1071234 грн. Периоды с 13-го по 24-й составляют второй год.