Финансовые функции Microsoft Excel

НПЗ(8%, B1:B5, -5000) - 37000 = 3167.77 руб.

 

• Определение срока платежа. Функция КПЕР.

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

Синтаксис.    КПЕР(норма, выплата, нз, бс, тип).

Функция может применяться  в следующих расчетах.

1. Если рассчитывается общее число периодов начисления процентов, необходимых для того, чтобы начальная сумма размером НЗ достигла будущего значения БС, то функция используется в виде:

КПЕР(норма, , нз, бс).

2. Формула для расчета общего числа периодов, через которые совокупная величина фиксированных периодических выплат составит значение БС, получается в виде:

КПЕР(норма, выплата, , бс, 1)

если платежи производятся в  начале каждого расчетного периода, и в виде:

КПЕР(норма, выплата, , бс,)

для выплат в конце периода.

3. При погашении займа размером НЗ равномерными постоянными платежами в конце каждого периода число периодов, через которое произойдет полное погашение, равно

КПЕР( норма, выплата, нз).

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

 

Примеры.

Задание 1. Рассчитать, через сколько лет вклад размером 1 тыс. руб. достигнет величины 1 млн. руб., если годовая ставка процента по вкладу 16.79% и начисление процентов производится ежеквартально.

Решение. Функция КПЕР дает следующий результат:

КПЕР(16,79% / 4, , -1, 1000) = 168 - это число кварталов.

Число лет составит 168 / 4=42.

Задание 2. Для обеспечения будущих расходов создается фонд. Средства в фонд поступают в виде постоянной годовой ренты постнумерандо. Размер разового платежа 16 тыс. рублей. На поступившие взносы начисляется 11,18% годовых. Необходимо определить, когда величина фонда будет равна 100 тыс. рублей.

Решение. Для решения задачи необходимо вычислить величину n. В EXCEL этот расчет выглядит так

КПЕР(11.18%,-16,, 100)=5, т. е, через 5 лет совокупная величина выплат составит 100 тыс. руб.

Задание 3. Ожидается, что ежегодные доходы от реализации проекта составят 33 тыс. руб. Необходимо рассчитать срок окупаемости проекта, если инвестиции к началу поступления доходов составят 100 тыс руб., а норма дисконтирования 12.11%

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

КПЕР(12.11%,33,-100)=4 года.

• Расчет процентной ставки. Функция НОРМА.

Функция НОРМА определяет значение процентной ставки за один расчетный  период. Для нахождения годовой процентной ставки полученное значение следует умножить на число расчетных периодов, составляющих год.

Синтаксис.   НОРМА (кпер, выплата, нз, бс, тип, предположение).

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

Рассмотрим варианты практического  применения этой функции

1. Допустим, необходимо рассчитать процентную ставку при известной текущей стоимости НЗ, будущей стоимости БС, числе периодов КПЕР. Тогда формула в EXCEL в общем виде записывается так:

НОРМА (кпер, , нз, бс, , предположение).

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

НОРМА(кпер, выплата,, бс, тип, предположение).

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

НОРМА (кпер, выплата, нз, ,, предположение).

Примеры.

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

Решение. В этой задаче сумма 100 тыс. руб. (аргумент БС функции НОРМА) формируется за счет приведения к будущему моменту начального вклада размером 5 тыс. руб. (аргумент НЗ) и фиксированных ежемесячных выплат (аргумент выплата). Используем функцию:

НОРМА(24, -2,5, -5, 100)= 3,28%.

Ежемесячная процентная ставка составит 3,28%, годовая – 12*3,28%=39,36%

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

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

НОРМА(48, -250, 7000) =2.46%,

Годовая процентная ставка составит 2.46% * 12 = 29.5%,

 

• Расчет периодических платежей. Функция ППЛАТ.

Функция ППЛАТ вычисляет  величину выплаты за один период на основе фиксированных периодических выплат и постоянной процентной ставки. Выплаты, рассчитанные функцией ППЛАТ, включают основные платежи и платежи по процентам.

Синтаксис   ППЛАТ (норма, кпер, нз, бс, тип).

Функция ППЛАТ применяется в  следующих расчетах

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

ППЛАТ(норма, кпер., бс, тип).

2. Предположим, рассчитываются  равные периодические платежи  по займу величиной НЗ, необходимые для полного погашения этого займа через КПЕР число периодов. Текущая стоимость этих выплат должна равняться текущей сумме займа. Расчет в EXCEL выполняется по формуле;

ППЛАТ(норма, кпер, нз,, тип).

Обычно погашение происходит в  конце каждого расчетного периода. Для этого случая формула имеет вид:

ППЛАТ(норма, кпер, нз), так как аргумент тип - 0,

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

Примеры.

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

Решение. Общее число периодов начисления процентов кпер =3*12, норма = 12% / 12. Аргумент тип = 0, т.к. это вклады постнумерандо. Величина ежемесячных выплат будет равна:

ППЛАТ(12%/12, 12*3„4000)=-92,86руб.

Задание 2. Допустим, банк выдал ссуду 200 тыс. руб. на 4 года под 18% годовых. Ссуда выдана в начале года, а погашение начинается в конце года одинаковыми платежами. Определить размер ежегодного погашения ссуды

Решение. Ежегодные платежи составят ППЛАТ(18%, 4, -200) = 74,35 тыс. руб.

Контрольные вопросы

Для расчета каких значений используется функция БЗ?

Какие данные необходимо иметь для  того, чтобы произвести расчет по функции  НЗ?

В чем различие между аргументами  «Начальное значение» и «Выплата»?

Что означает «платеж пренумерандо»?

С помощью какой функции можно  определить процент начислений по кредиту?

ЗАДАНИЯ ДЛЯ САМОСТОЯТЕЛЬНОЙ РАБОТЫ

 

Задача 1. Пользователь Сбербанка внес вклад 5 млн. рублей. До какой суммы возрастет вклад через 7 лет, если процент годовых начислений равен а) 6 %, б) 10 %, в) 15%.

Задача 2. Вскоре после рождения сына родители решили внести в Сбербанк вклад с тем, чтобы через 22 года вклад вырос до суммы 30 млн. руб. Каков должен быть вклад, если процент годовых начислений равен 5 %, 8 %, 10%.

Задача 3. Через сколько лет начальный вклад 1000 $ увеличится до суммы 3000$, 5000 $ или 8000 $, если процент годовых начислений равен 6 %.

Задача 4. Какой процент годовых начислений должен обеспечить банк, чтобы первоначальный вклад 5000 $ увеличился втрое за 15, 18, 20 лет.

Задача 5. Рассчитать процентную ставку для 3-летнего займа размером 5000 руб. с ежеквартальным погашением по 1500 руб.

Задача 6. Определить эффективность инвестиции размером 200 млн, руб. по NPV, если ожидаемые ежемесячные доходы за первые пять месяцев составят соответственно: 20, 40, 50, 80 и 100 млн. руб. Издержки привлечения капитала составляют 13,5% годовых.

Задача 7. Определить, какие ежемесячные выплаты необходимо вносить по ссуде размером 200 тыс. руб., выданной на три года, при разных процентных ставках. Использовать Таблицу подстановки Excel.

Задача 8. Вкладчик Сбербанка решил ежегодно вносить вклад 1000$ в течение 10 лет. Сколько денег он сможет получить, если процент годовых начислений равен 5%, 10%, 15%.

Задача 9. Пусть вы откладываете 500 $ в конце каждого года на сберегательный счет при ставке 15 % годовых. Определить сумму накоплений к концу 6-го года.

Задача 10. Семья решила накопить для покупки дома сумму 20 000 $, вкладывая ежегодно сумму 1000$ (1500$ или 2000$). Сколько лет на это потребуется, если процент годовых начислений равен 6 %.

Задача 11. Какую сумму должен ежегодно вносить вкладчик, чтобы через 5 (10 или 15) лет накопить сумму 15000 $ при проценте годовых 3 %.

Задача 12. Каким должен быть процент годовых начислений, чтобы при ежегодном вкладе 1000 $ (1 500 $ или 3000 $) накопить сумму 20 000 $ за 15 лет.

Задача 13. Известно, что за 4 месяца вклад одного из вкладчиков Сбербанка увеличился с 4 млн. рублей до 6 млн. рублей. До какой суммы возрастет этот вклад через один год и через два года.

Задача 14. Вскоре после рождения сына родители решили внести в Сбербанк вклад с тем, чтобы через 22 года вклад вырос до суммы 30 млн. руб. Каков должен быть вклад, если процент годовых начислений равен 5 %, 8 %, 10%.

Задача 15. Рассматриваются два варианта покупки недвижимости: заплатить всю сумму сразу - 70 000 руб. или платить ежемесячно по 800 руб. в течение 12 лет при ставке 9% годовых. Какой вариант более выгоден (68 743)?

Задача 16. Вексель на 3 000 000 долл. с годовой учетной ставкой 10% с дисконтированием два раза в год выдан на два года. Найти исходную сумму, выданную под этот вексель.

Задача 17. На счет в банке вносится сумма 10 000 долл. в течение 10 лет равными долями в конце каждого года. Ежемесячная ставка 4%. Какая сумма будет на счете через 10 лет?

Задача 18. Сумма 2000 долл. размещена под 9% годовых на три года. Проценты начисляются раз в квартал. Какая сумма будет на счете на конец периода договора.

Задача 19. Банк принимает вклад на срок 3 месяца с объявленной годовой ставкой 100% (140 625) или на 6 месяцев под 110% (169 254). Как выгоднее будет вложить сумму в 100 000 руб. на полгода: дважды на три месяца (проценты начисляются в начале квартала) или один раз на 6 месяцев (проценты начисляются ежемесячно)?

Задача 20. Получена ссуда в 20 000 долл. дана на полтора года под ставку 28% годовых с ежеквартальным начислением. Определить какую сумму необходимо будет вернуть кредитору.

Задача 21. Выдан кредит в сумме 1 млн. долл. с 15.01.04 по 15.03.04 под 120% годовых. Рассчитать сумму погасительного платежа.

Задача 22. Под темпом инфляции понимают относительный прирост цен за период. Фраза «инфляция идет в темпе 10% в месяц» означает, что имеет место схема сложных процентов, этапом в которой является месяц, за каждый месяц цены увеличиваются на 10%. Рассчитать стоимость заданного товара через 1 год при темпе инфляции 10% в год, 3% в месяц и 1% в месяц, если в начале года цена товара составляла 50 000 рублей.