Использование Excel для решения финансово-экономических задач.
Программой по информатике для 10-11 классов экономического профиля предполагается использование Excel для решения финансово-экономических задач, что позволяет автоматизировать финансовые расчеты.
Особенностью всех финансовых расчетов является временная ценность денег, то есть принцип неравноценности денег, относящихся к разным моментам времени. Предполагается, что полученная сегодня сумма обладает большей ценностью, чем ее эквивалент, полученный в будущем. То есть будущие поступления менее ценны, чем современные.
Неравноценность одинаковых по абсолютной величине сумм связана прежде всего с тем, что имеющиеся сегодня деньги могут быть инвестированы и принести доход в будущем.
Основные понятия финансовых методов расчета
Процент — абсолютная величина дохода от предоставления денег в долг в любой его форме.
Процентная ставка — относительная величина дохода за фиксированный интервал времени, измеряемая в процентах или в виде дроби.
Период начисления — интервал времени, к которому приурочена процентная ставка.
Капитализация процентов — присоединение начисленных процентов к основной сумме.
Наращение — увеличение первоначальной суммы в связи с капитализацией;
Дисконтирование — приведение стоимостной величины, относящейся к будущему, на некоторый, обычно более ранний период времени (операция, обратная наращению).
Реальная стоимость денег – то количество потребительских благ, которое можно приобрести в обмен на определенную денежную сумму.
Индекс инфляции – среднегодовой индекс прироста потребительских цен.
Существуют различные способы начисления процентов от предоставления денег в долг в любой форме. За основу берется база начисления процентов. Применяется постоянная и последовательно изменяющаяся база для расчета. В последнем случае за базу принимается сумма, полученная на предыдущем этапе наращения.
При постоянной базе начисляются простые проценты, при переменной – сложные.
Простые проценты.
Введем следующие обозначения: P – первоначальная сумма долга;
I – проценты за весь срок ссуды;
S – наращенная сумма в конце срока;
i – ставка простого процента (в годовом исчислении);
T – срок ссуды;
t – период начисления;
n = T/ t – количество периодов начисления процентов.
Тогда P*i – начисленные проценты за один период.
Проценты за весь срок рассчитываются по формуле:
I = P*i*n
Сумма, образовавшаяся к концу срока, будет следующей:
S = P+P*i*n = P*(1+i*n)
Формула S = P(1+i*n) называется формулой простых процентов, множитель 1+i*n – множителем наращения простых процентов.
Разберем на примере 1. Предположим, Вы берете в банке ссуду 100 млн. руб. на два года. Ставка составляет 30%. Ставка чаще всего задается в годовом исчислении. Необходимо определить проценты за весь срок ссуды и сумму, образовавшуюся к концу срока ссуды.
P = 100000000
T = 2
i = 0,3
t = 1
n = 2 / 1 = 2
I = P*i*n = 100000000*0,3*2 = 60000000
S = P + I = 160000000
Практика расчета по простым процентам используется при выдаче кратковременных ссуд и на период не больше года, так что n может быть и меньшим 1.
В этом случае
n= z / y
где z – количество дней, на которые взята ссуда, у – количество дней в году.
Обе эти величины могут браться как точно, так и приближенно.
Величина Z (количество дней, на которые взята ссуда).
При точном определении числа дней подсчитывается количество дней между датой выдачи ссуды и датой ее возврата. Причем, день выдачи и день возврата считаются одним днем.
При приближенном количестве дней ссуды считается число полных месяцев между датой выдачи ссуды и датой ее возврата и количество дней сверх полного месяца. Количество дней в каждом месяце принимается равным 30.
Величина Y (количество дней в году).
Точное количество дней соответствует 365 или 366. Чаще всего в банковских операциях используется приближенное число дней, равное 360. Процент, посчитанный с таким числом дней называется коммерческим или обыкновенным. Таким образом, в коммерческой практике применяются три вида процентов:
1 - точные проценты с точным числом дней в году и точным числом дней ссуды;
2 - коммерческие проценты с точным числом дней ссуды;
3 - коммерческие проценты с приближенным числом дней ссуды.
Несмотря на кажущуюся простоту исчисления простых процентов, они используются и в довольно сложных банковских операциях, связанных с повторным инвестированием или частичным погашением средств.
Пример 2. Банк предоставил клиенту ссуду в 100 млн. руб. под 20% процентов годовых на 3 месяца.
Тогда через 3 месяца клиент вернет банку ссуду плюс проценты, которые мы рассчитаем методом коммерческих процентов с приближенным числом дней
P*i*n = 100000000*0,2*1/4 = 5000000
Клиент вернет банку 105 млн. руб.
Банк сдает эти деньги в виде ссуды на следующий такой же срок – 3 месяца и под те же проценты.
Через 3 месяца банк получит
105000000+105000000*0,2*1/4 = 110250000
Повторив ту же операцию с теми же условиями еще дважды, банк на третьем этапе, инвестировав 110250000, получит через 3 месяца 115762500 руб., а на четвертом этапе, инвестировав 115762500 руб., получит обратно 121550625 руб.
Таким образом, сумма в 100 млн. руб., находясь в обороте, принесет банку доход в сумме
5000000+10250000+15762500+21550626 = 52313125 руб.
Следовательно, реальная процентная ставка составила не 20%, а 52,3% годовых.
Общая формула для расчета суммы денег, образовавшейся к концу срока ссуды при осуществлении повторного инвестирования средств, такова:
S = P*(1+n1*i1)*(1+n2*i2)*…*(1+nn*in),
где n1, n2, …, nn – количество периодов начисления,
i1, i2,…, in – процентные ставки.
Пример 3. Вы взяли ссуду в банке на следующих условиях: первоначальная сумма – 200 тыс. руб., ставка простого процента – 170% годовых, срок ссуды – 2 года. Определить, во сколько раз сумма долга к концу срока ссуды превысит первоначальную сумму долга. (в 4,4 раза)
Пример 4. В невисокосном году Вы взяли ссуду 3 января и должны отдать ее 13 мая того же года на условиях 25% годовых при простом проценте. Во сколько раз возрастет ваш долг при расчете методом:
1) коммерческого процента с приближенным числом днем ссуды? (1.09)
2) коммерческого процента с точным числом дней ссуды? (1.091)
3) точных процентов с точным числом дней ссуды? (1.089)
Пример 5. Банк выдал ссуду в размере 10 млн. руб. на 3 года под 50% процентов годовых на условиях простых процентов с требованием равномерного ежемесячного погашения долга в течение этого срока. Какую сумму Вы должны возвращать каждый месяц?
Сложные проценты
В долгосрочных финансовых операциях часто применяются не простые, а сложные проценты.
С этим методом начисления процентов знакомы те, кто хранит свои деньги в сберегательном банке, а иными словами, дает банку ссуду под сложный процент. При этом проценты, полученные за год, прибавляются к первоначальной сумме вклада (долга банка перед клиентом), т.е. капитализируются, и в следующем году проценты начисляются уже на эту новую сумму. И так каждый год.
В принятых нами обозначениях наращенная сумма будет равна:
S = P*(1+i)*(1+i)*...*(1+i) = P*(1+i)n
где P – первоначальная сумма долга; i – процентная ставка; n – количество периодов начисления.
S = P*(1+i)n – формула сложных процентов.
(1+i)n – множитель наращения по сложным процентам.
Эта формула предполагает постоянную процентную ставку на протяжении всего срока погашения процентов. Однако часто используют плавающие или переменные процентные ставки. Тогда наращенная сумма рассчитывается так:
S = p*(1+i1)n1*(1+i2)n2*...*(1+ik)nk
где i1, i2,...,ik – последовательные во времени значения процентных ставок;
n1, n2,...,nk – длительность периодов, в течение которых используются соответствующие ставки.
Практическое задание
Пример 1. Вы положили 10 млн. руб. в банк. В течение первых полутора лет ставка процента по вкладу была 20%, затем ставку подняли до 40% – такая ставка была полгода, после чего поднялась до 50%.
1. Какой будет наращенная сумма по истечении четырех лет?
2. Какой будет наращенная сумма, если использовать формулу наращения простых процентов?
Пример 2. Банк выдал ссуду на сумму 1 млн. руб. клиенту А на срок 2 месяца, затем деньги, полученные от клиента А, выдал клиенту В на срок 3 месяца. Деньги, полученные от клиента В выдал клиенту С на 5 месяцев, и, наконец, полученные от клиента С, клиенту D на 2 месяца. Все ссуды были даны под 70% годовых. Какую сумму вернет банку клиент D и какой реальный процент это составляет? Произвести расчет методом простого коммерческого процента и методом сложных процентов.
Пример 3. Решить задачи, зная, что клиент с номером N+1 берет ссуду в размере суммы возвращенной клиентом с номером N:
Номер клиента Первоначальная величина ссуды
(млн. руб.) Процентная ставка (%год)
Срок ссуды (мес.)
Размер долга к концу срока
(млн. руб.)
1
2
3 10
Р2
РЗ 20
20
20 3
7
3 S1
S2
S3
1
2
3 45
Р2
58,27 20
40
50 3
Т2
3 S1
58,27
S3
1
2
3 100
Р2
136,17 40
i2
i3 8
2
2 S1
136,17
147,52
1
2
3 P1
253,33
P3 40
40
50 8
2
2 253,33
S2
S3
Дисконтирование.
Дисконтирование означает приведение стоимостного показателя, относящегося к будущему, на некоторый, более ранний момент времени, т.е. это процесс нахождения сегодняшней стоимости будущего платежа. По величине S определяется P.
В этом случае говорят, что сумма S дисконтируется или учитывается, сам процесс начисления процентов и их удержание называют учетом, а удержанные проценты – дисконтом.
Величину P, найденную с помощью дисконтирования, называют современной капитализированной стоимостью.
Из формулы простых процентов получим
P = S/(1+n*i)
1/(1+n*i) – дисконтный множитель,
D = S - P – дисконт.
Из формулы сложных процентов получим
P = S/(1+I)n
1/(1+i)n – дисконтный множитель,
D = S - P – дисконт.
Практическое задание
Пример 1. Известно, что через 150 дней клиент должен вернуть в банк сумму, равную 500 тыс. рублей. Кредит предоставлен на условиях простых процентов под 15% годовых. Временная база для расчета процентов равна 365 дням. Необходимо определить, какую сумму клиент получил на руки.
Пример 2. Вы хотите, чтобы через 10 лет на Вашем вкладе был 1 млн. рублей. В настоящий момент банк выплачивает по вкладам населения 60% годовых, ежегодно начисляя проценты. Какую сумму Вам необходимо сегодня положить в банк?
Пример 3. Банк обязуется выплачивать по вкладу 20% ежемесячно. Какой годовой процент Вы получите по своему вкладу, если:
1) будете забирать проценты ежемесячно и тратить их;
2) будете вкладывать проценты в тот же банк на тех же условиях?
Задания для самостоятельного решения.
Задание 1. Решить задачи, используя простые процентные ставки:
№п/п Первоначальная сумма долга (млн. руб.) Ставка простого процента
(% год) Срок ссуды (лет) Сумма долга к концу срока ссуды (млн. руб.)
1
2
3
4
5 150
20
230
Р4
Р5 130
80
i3
12
45 T1
2
1,5
6
0,5 832,5
S2
609,5
86
208,2
Задание 1. Решить задачи, используя сложные процентные ставки:
№п/п Первоначальная величина вклада (тыс. руб.) Процентная ставка
(% период) Количество периодов начисления Величина вклада
к концу срока
(тыс. руб.)
1
2
3
4
5
6 300
500
500
40
250
P6 12
60
45
i4
i5
10 12
5
n3
20
9
30 S1
S2
4650
133012
2650
8725
Финансовые функции Excel.
Финансовые функции Excel присутствуют в Пакете Анализа. Если это дополнение Excel на Вашем компьютере не установлено, то следует установить его из расширенной редакции Microsoft Office, а затем включить с помощью диспетчера настроек. Для этого в меню Сервис необходимо выбрать опцию Надстройки и в появившемся списке указать Пакет Анализа. Вот теперь Вы можете решать любые финансово-экономические задачи.
Определение будущей стоимости вклада (займа).
Понятие будущей стоимости основано на принципе неравноценности денег, относящихся к разным моментам времени. Вложения, сделанные сегодня, в будущем составят большую величину.
Расчеты на основе постоянной процентной ставки.
Функция БЗ.
Функция БЗ рассчитывает будущую стоимость периодических постоянных платежей и будущее значение единой суммы вклада или займа на основе постоянной процентной ставки.
Синтаксис:
БЗ(норма, кпер, выплата, нз, тип)
норма – процентная ставка за период. Например, если Вы взяли ссуду под 10%годовых и делаете ежемесячные выплаты, то процентная ставка за месяц составит 10%/12 или 0,83%. В качестве значения аргумента можно набирать 0, 83% или 0,0083.
кпер – общее число периодов выплат или начисления процентов. Например, Вы получили ссуду на 4 года и делаете ежемесячные платежи, то Ваша ссуда имеет 4*12 или 48 периодов выплат.
выплата – это выплата, производимая в каждый период и не меняющаяся за все время срока ссуды. Включает основные платежи плюс проценты.
нз – начальное значение вклада или займа.
тип – это число 0 или 1, обозначающее, когда должна производиться выплата:
0 или опущено – в конце периода,
1 – в начале периода.
Для вычисления будущего значения единой суммы используются аргументы норма, нз, кпер, норма:
=БЗ(норма; кпер; нз)
Для расчета будущей стоимости серии фиксированных периодических платежей, если они вносятся в начале каждого периода (так называемые “обязательные платежи” или пренумерандо), используется формула:
БЗ(норма; кпер; выплата; 1)
Для расчета будущей стоимости серии фиксированных периодических платежей, если выплаты происходят в конце периода (так называемые “обычные платежи” или постнумерандо), используется формула:
БЗ(норма; кпер; выплата; 0)
Замечание. Все аргументы, означающие деньги, которые Вы платите, представляются отрицательными числами; деньги, которые Вы получаете, представляются положительными числами.
Практическое задание
Пример 1. Рассчитать, какая сумма окажется на счете, если 27 тыс. рублей положены на 33 года под 13,5% годовых. Проценты начисляются каждые полгода.
Решение. Обратим внимание, что в условии задачи указан годовой процент и число лет. Если проценты начисляются несколько раз в год, то необходимо рассчитать общее количество периодов накопления процентов и ставку процента за период начисления.
норма = 13,5%/2
кпер = 33*2
нз = -27
= БЗ(13,5%/2, 33*2, , -27)
Пример 2. Предположим, Вы хотите зарезервировать деньги для специального проекта, который будет осуществлен через год. Вы собираетесь вложить 1 млн. рублей под 60% годовых. Вы собираетесь также вкладывать по 100 тыс. рублей в начале каждого месяца в течение 12 месяцев. Сколько денег будет на счете в конце 12 месяцев?
норма кпер выплата нз Тип
60%/12 12 -100000 -1000000 1
Пример 3. Есть два варианта инвестирования средств в течение 4 лет:
1) в начале каждого года под 26%гордовых;
2) в конце каждого года под 38% годовых.
Пусть ежегодно вносится 300 тыс. рублей. Определить, сколько денег окажется на счете в конце четвертого года для каждого варианта.
Подбор параметра
Вычислительные возможности Excel позволяют подбирать значения аргументов под заданное значение функции. Необходимость в этом обусловлена отсутствием некоторых “симметричных” финансовых функций.
Рассмотрим использование этого инструмента на примере решения задачи №1. Предположим, мы получили следующее решение:
Уточним условие задачи следующим образом: какой должна быть годовая процентная ставка, чтобы будущее значение суммы оказалось равным 5000 руб.?
Таким образом, нам необходимо подобрать процентную ставку, которая должна обеспечить определенное значение уже вычисленной функции.
Выполнение работы:
1. В меню Сервис выберем инструмент Подбор параметра.
2. В появившемся диалоговом окне сделаем следующие настройки:
- в поле Установить в ячейке – указать адрес ячейки с функцией;
- в поле Значение – указать предполагаемое значение функции;
- в поле Изменяя значение ячейки – указать адрес ячейки, содержащей подбираемый параметр.
3. Подтвердить настройки.
При нажатии кнопки ОК подобранное значение аргумента сохраняется в ячейке аргумента; при нажатии кнопки Отмена происходит восстановление значения аргумента. При неуспешном подборе параметра выдается соответствующее сообщение о невозможности подбора аргумента.
Расчеты на основе переменной процентной ставки.
Функция БЗРАСПИС.
Если процентная ставка меняется с течением времени, то для расчета будущего значения суммы вклада или займа после начисления сложных процентов используют функцию БЗРАСПИС.
Синтаксис.
БЗРАСПИС(основной_капитал; ставки)
основной_капитал – текущая стоимость инвестиции, начальное значение вклада (ссуды), номинал (облигации).
ставки – Массив применяемых процентных ставок; может набираться в виде последовательности дробей или диапазоном адресов ячеек.
Эта функция соответствует формуле расчета наращенной суммы вклада по методу сложных процентов при использовании переменных ставок.
Практические задания
Задача 1 По облигации номиналом 100 тыс. руб., выпущенной на 6 лет, предусмотрен следующий порядок начисления процентов: в первый год – 10%, в два следующих года – 20%, в оставшиеся три года – 25% годовых. Рассчитать будущую (наращенную) стоимость облигации.
Задача 2 Исходя из плана начисления процентов, приведенного в задаче №4, рассчитать номинал облигации, если известно, что ее будущая стоимость составила 1546880 руб. Для решения задачи использовать инструмент "подбор параметра".
Задача 3 Рассчитайте будущую стоимость облигации номиналом 300000 руб., выпущенной на 5 лет, если предусмотрен следующий порядок начисления : в первые два года - 13,5% годовых, в следующие два года - 15% и в последний год - 20% годовых.
Задача 4 Ожидается, что будущая стоимость инвестиции размером 1500 тыс. руб. к концу четвертого года составит 3 млн. руб. При этом за первый год доходность составит 15%, за второй - 17%, за четвертый - 23%. Рассчитать доходность инвестиции за третий год
Задача 5 Рассчитайте, какая сумма будет на счете, если сумма размером 5 млн. руб. размещена под 12% годовых на 3 года, а проценты начисляются каждые полгода.
Задача 6 Вы получили в банке ссуду на 1 год в размере 5 млн. руб. Ссуда принесла банку годовой доход 100 тыс. руб. Какому проценту годовых это соответствует? Используйте инструмент "Подбор параметра".
Задача 7 Вы хотите получить через 2 года 1 млн. руб. при норме процента 10% годовых. Проценты будут начисляться ежеквартально. Какую сумму Вам необходимо положить на счет?
Задача 8 Вы получили в банке ссуду 30 млн. руб. на 10 лет при норме процента 15% годовых. Начисление процентов – раз в полгода. Каков будет доход банка?
Задача 9 Банк выдал ссуду 10 млн. руб. из расчета 8% годовых. Каков будет доход банка за три месяца?
Задача 10 Облигация номиналом 50 тыс. руб. выпущена сроком на 4 года. Предусмотрен следующий порядок начисления процентов: в первый год – 15% , во второй и третий –20%, в четвертый год – 12%. Рассчитать будущую стоимость облигации.
Задача 11 Вы инвестировали в предприятие 2 млн. руб. Предполагается, что будущая стоимость этого предприятия через 5 лет составит 5 млн. руб. Доходность за первые два года составит 40%, за третий и пятый годы – 25%. Какова должна быть доходность за четвертый год?
Задача 12 На сберегательный счет вносятся платежи по 200 тыс. руб. в начале каждого месяца. Рассчитать, какая сумма окажется на счете через 4 года, при ставке процента 15% годовых. Какое значение получится, если платежи вносятся в конце каждого месяца?.
Задача 13 Будущая стоимость облигации через 4 года составит 2 млн. руб. при этом порядок начисления процентов предполагается следующий: за первый год – 15%, за второй – 20%, за третий и четвертый годы – 25%. Рассчитать номинал облигации.
Задача 14 Клиенту банка по вкладу размером 2 млн. руб. начисляются 10% годовых ежемесячно. Кроме того, ежемесячно клиент вносит на этот же счет по 50 тыс. руб. Какая сумма будет на вкладе по истечении 5 лет?
Задача 15 Банк выплачивает своим вкладчикам банковский процент 4% годовых и дает ссуды заемщикам под 10% годовых. Чему равна банковская прибыль за год, если средства вкладчиков составили 10 млн. руб., а ссуды были выданы заемщикам на сумму 5 млн. руб. на 1 год?
Задачи для самостоятельного решения.
Вариант №1
Задача 1
Определите, какая сумма окажется на счете, если вклад размером 900 тыс. руб. положен под 9% годовых на 19 лет, а проценты начисляются ежеквартально.
Задача 2
Какая сумма должна быть выплачена, если шесть лет назад была выдана ссуда 1500 тыс. руб. под 15% годовых с ежемесячным начислением процентов.
Задача 3
Рассчитайте будущую стоимость облигации номиналом 100 тыс. руб., выпущенной на семь лет, если в первые три года проценты начисляются по ставке 17%, а в остальные четыре года - по ставке 22% годовых.
Задача 4 Рассчитать номинал облигации, выпущенной шесть лет назад, если известно, что ее будущая стоимость составила 1 546 880 руб., а проценты начислялись следующим образом: в первый год - 10% годовых, в два последующих года - 20%, в оставшиеся три года - 25% годовых. Использовать инструмент Подбор Параметра.
Вариант №2
Задача 1
Взносы на сберегательный счет составляют 200 тыс. руб. в начале каждого года. Определите, сколько будет на счете через семь лет при ставке процента 10% годовых.
Задача 2
Предполагается, что в течение первых двух лет на счет откладывается по 800 тыс. руб. в конце каждого года, а в следующие три года - по 850 тыс. руб. в конце каждого года. Определите наращенную сумму в концу пятого года, если ставка процента - 11% годовых.
Задача 3 Рассчитайте будущую стоимость облигации номиналом 500 тыс. руб., выпущенной на пять лет, если предусмотрен следующий порядок начисления процентов: в первые два года - 13,3% годовых, в следующие два года - 15% и в последний год - 20% годовых.
Задача 4
Ожидается что будущая стоимость инвестиции размером 1500 тыс. руб. к концу четвертого года составит 3 млн. руб. При этом, за первый год доходность составит 15%, за второй 17%, за четвертый - 23%. Рассчитать доходность инвестиции за четвертый год. Использовать инструмент Подбор Параметра.
Вариант №3
Задача 1
Вклад размером 2000 тыс. руб. положен под 10% годовых. Рассчитайте, какая сумма будет на сберегательном счете через пять лет если проценты начисляются ежемесячно.
Задача 2"
На сберегательный счет вносятся обязательные ежемесячные платежи по 200 тыс. руб. Рассчитайте, какая сумма окажется на счете через четыре года при ставке процента 13% годовых.
Задача 3 Рассчитайте будущую стоимость облигации номиналом 300 тыс. руб., выпущенной на 5 лет, если предусмотрен следующий порядок начисления процентов: в первые два года - 13,5% годовых, в следующие два года -15% и в последний год - 20% годовых.
Задача 4 Ожидается что будущая стоимость инвестиции размером 1500 тыс. руб. к концу четвертого года составит 3 млн. руб. При этом, за второй год доходность составит 17%, за третий - 20%, за четвертый - 23%. Рассчитать доходность инвестиции за первый год. Использовать инструмент Подбор Параметра.
Определение текущей стоимости
Во многих задачах используется понятие текущей (современной) стоимости будущих доходов и расходов. Это понятие базируется на положении о том, что на начальный момент времени полученная в будущем сумма денег имеет меньшую стоимость, чем ее эквивалент, полученный в начальный момент времени.
Согласно концепции временной стоимости денег, расходы и доходы, не относящиеся к одному моменту времени, можно сопоставить путем приведения к одному сроку (т.е. путем дисконтирования). Текущая стоимость получается как результат приведения будущих доходов и расходов к начальному периоду времени.
Мы знаем три типа инвестиций:
1) денежные потоки равной величины с равными интервалами времени;
2) денежные потоки переменной величины с равными интервалами времени;
3) денежные потоки переменной величины с неравными интервалами времени.
Для расчета каждого типа инвестиций в Excel используется отдельная функция.
Функция ПЗ
Функция ПЗ предназначена для расчета текущей стоимости как единой суммы вклада (займа), так и будущих фиксированных периодических платежей. Этот расчет является обратным к определению будущей стоимости при помощи функции БЗ.
Синтаксис:
ПЗ(норма; кпер; выплата; бз; тип)
Для единой суммы вклада:
ПЗ(норма; кпер; бз)
Практические задания
Задача 16 Фирме потребуется 5 млн. руб. через 12 лет. В настоящее время фирма располагает деньгами и готова положить их на депозит единым вкладом, чтобы через 12 лет он достиг 5 млн. руб. Определить необходимую сумму текущего вклада, если ставка процента по нему составит 12% годовых.
Задача 17 Рассматриваются два варианта покупки дома: заплатить сразу 99 млн. руб. или в рассрочку – по 940 тыс. руб. ежемесячно в течение 15 лет. Какой вариант предпочтительнее, если ставка процента – 8% годовых?
Функция НПЗ
Функция ПЗ предназначена для расчета текущей стоимости периодических платежей переменной величины.
Синтаксис:
НПЗ(норма; сумма1; сумма2; …; суммаN)
норма - процентная ставка за период
сумма1, сумма1, … - аргументы (от 1 до 29), представляющие содой доходы и расходы; должны быть равномерно распределены по времени и осуществляться в конце каждого периода.
Считается, что инвестиция, текущую стоимость которой вычисляет функция НПЗ, начинается за один период до даты аргумента сумма1 и заканчивается с последним значением в списке.
Если первый денежный взнос приходится на начало первого периода, то первое значение следует добавить (вычесть, ели это затраты) к результату функции НПЗ, но не включать в список аргументов.
Практические задания
Задача 18 Инвестиции в проект к концу первого года его реализации составят 10000 рублей. В последующие три года ожидаются годовые доходы по проекту 3000 руб., 4200 руб. и 6800 руб. Издержки привлечения капитала (ставка процента) – 10%. Рассчитать текущую стоимость проекта.
Задача 19 Затраты по проекту в начальный момент его реализации составляют 3700 руб., а ожидаемые доходы за первые пять лет – 8000 руб., 9200 руб., 10000 руб., 13900 руб. и 14500 руб. На шестой год ожидается убыток в 5000 руб. Издержки привлечения капитала – 8% годовых. Рассчитать текущую стоимость проекта.
Функция ЧИСТНЗ
Функция ЧИСТНЗ предназначена для расчета текущей стоимости нерегулярных переменных расходов и доходов.
Синтаксис:
ЧИСТНЗ(ставка; {сумма0; сумма2; …, суммаN},{дата0; дата1; …; датаN})
Указанные даты операций должны соответствовать суммам выплат и поступлений.
Расчет производится на дату, когда осуществляется первая операция, т.е. на дату дата0 .
Практические задания
Задача 20 Рассмотрим инвестицию 10 млн. руб. на 1 июля 2000 года. Она принесет следующие доходы: 2750 тыс. руб. 15 сентября 2000 года, 4250 тыс. руб. 1 ноября 2000 года, 5250 тыс. руб. 1 января 2001 года. Норма дисконтирования 9% годовых. Определить чистую текущую стоимость инвестиции на 1 июля 2000 года и на 1 июня 1999 года.
Задача 21
Определить текущую стоимость обычных ежемесячных платежей размером 100 тыс. руб. в течение 5 лет, если процентная ставка составляет 12% годовых.
Задача 22
Определить текущую стоимость обычных ежемесячных платежей размером 50 тыс. руб. в течение двух лет при начислении 18% годовых.
Задача 23
Рассчитайте, какую сумму надо положить на депозит, чтобы через 4 года она достигла значения 20 млн. руб. при начислении 9% годовых.
Задача 24
Определить текущую стоимость обычных ежеквартальных платежей размером 350 тыс. руб. в течение 7 лет, если ставка процента 11% годовых.
Задача 25
Определить эффективность инвестиции размером 200 млн. руб. по NVP, если ожидаемые ежемесячные доходы за первые пять месяцев составят соответственного, 40, 50, 80 и 100 млн. руб. Издержки привлечения капитала (ставка процента) составляют 13,5% годовых.
Задача 26
Рассчитайте чистую текущую стоимость проекта, затраты по которому составят 400 млн. руб., а предполагаемые доходы за первые два года реализации проекта - 40 и 80 млн. руб. Начало реализации проекта -через два года. Норма дисконтирования - 15% годовых.
Задача 27
Определить чистую текущую стоимость проекта на 1.01.1998, затраты по которому на 20.12.1998 составят 100 млн. руб. Ожидается, что за первые полгода 1999 года проект принесет следующие доходы: на 01.03.1999 – 18 млн. руб.; на 15.04.1999 – 40 млн. руб.; на 30.06.1999 –51 млн. руб. Норма дисконтирования - 12% годовых.
Задачи для самостоятельного решения
Вариант №1
Задача 1.
Рассчитать текущую стоимость вклада, который через три года составит 7092,6 тыс. руб. Ставка процента - 12% годовых, проценты начисляются каждые полгода.
Задача 2.
Определить, какую сумму надо положить на депозит, чтобы через два года она достигла 1 млн. руб. при ежемесячном начислении процентов и ставке процента 38% годовых.
Задача 3. Определить текущую стоимость обязательных ежемесячных платежей размером 150 тыс. руб., если известно, что за четыре года они принесли доход в 10 млн. руб. при ставке процента 13,5% годовых.
Задача 4.
Определить текущую стоимость обычных ежегодных платежей размером 200 тыс. руб. в начале каждого года в течение 13 лет при норме процента 20% годовых
Задача 5.
Предположим, Вам предлагают два варианта оплаты: сразу заплатить 600 тыс. руб. или вносить по 110 тыс. руб. в конце каждого месяца в течение полугода. Вы могли бы обеспечить вложениям 9,7% годовых. Какой вариант предпочтительнее?
Задача 6.
Допустим, рассматривается проект стоимостью 100 млн. руб. Ожидается, что ежемесячные доходы по проекту составят 16, 25, 36, 49 млн. руб. за четыре месяца. Определите чистую текущую стоимость проекта, если годовая норма процента 19%.
Задача 7.
Определите текущую стоимость инвестиции, если 27 декабря 1996 года предполагалась выплата 5 млн. руб. и поступления составят соответственно 20 июня 1997 года – 1 млн. руб., 12 декабря 1997 года - 3,8 млн. руб. и 17 июля 1998 года - 4,6 млн. руб., если ставка процента - 13% годовых.
Вариант №2
Задача 1.
Какую сумму необходимо положить на депозит под 16% годовых, чтобы получить через три года 44 млн. руб. при полугодовом начислении процентов?
Задача 2.
Определите текущую стоимость обязательных ежемесячных платежей размером 120 тыс. руб. в течение четырех лет, если годовая процентная ставка - 14%
Задача 3.
Определить текущую стоимость обычных платежей в 1 млн. руб. в конце каждого из 10 лет, если ставка процента - 14,5% годовых
Задача 4.
Определить текущую стоимость обязательных ежемесячных платежей размером 141,7 тыс. руб. в течение 4 лет при ставке процента 16% годовых
Задача 5.
Оцените, что выгоднее: получить 100 тыс. руб. сразу или 50 тыс. руб. сейчас и 90 тыс. руб. через два года, если ставка процента 13% годовых.
Задача 6
Капитальные затраты по проекту составляют 470 млн. руб. и ожидается, что его реализация принесет следующие доходы за три года: 170, 230, 190 млн. руб. соответственно. Издержки привлечения капитала (ставка процента) равны 14% годовых. Определите чистую текущую стоимость проекта.
Задача 7.
Рассмотрим инвестицию 10 млн. руб. 1 июля 1998 года, которая принесла доходы: 2750 тыс. руб. 15 сентября 1998 года, 4250 тыс. руб. 1 ноября 1998 года, 5250 тыс. руб. 1 января 1999 года. Норма дисконтирования 9%. Определите текущую стоимость инвестиции.
Определение срока платежа и процентной ставки.
Функции этой группы позволяют находить величины, расчет которых весьма затруднен, если ведется вручную. К ним относятся:
1) общее число периодов постоянных выплат, необходимых для достижения заданного будущего значения; число периодов, через которые начальная сумма займа (вклада) достигнет заданного значения;
2) значение постоянной процентной ставки за один период для серии фиксированных периодических платежей; значение ставки процента по вкладу или займу.
Расчет срока платежа Функция КПЕР
Эта функция вычисляет общее число периодов выплат как для единой суммы вклада (займа), так и для периодических постоянных выплат на основе постоянной процентной ставки. Если платежи производятся несколько раз в год, найденное значение необходимо разделить на число расчетных периодов в году, чтобы найти число лет выплат.
Синтаксис:
КПЕР(норма; выплата; нз; бс; тип)
Существует несколько вариантов расчетов с использованием функции КПЕР:
Расчет числа периодов начисления процентов, необходимых для того, чтобы начальная сумма размером нз достигла указанного будущего значения бс:
КПЕР(норма; нз; бс).
Расчет числа периодов, через которое совокупная величина фиксированных периодических выплат составит указанное значение бс:
КПЕР(норма; выплата; бс; тип).
Расчет числа периодов, через которое произойдет полное погашение займа размером нз равномерными постоянными платежами в конце каждого расчетного периода:
КПЕР(норма; выплата; нз).
Полученное значение можно также использовать показатель срока окупаемости при анализе инвестиционного проекта. При этом предполагается, что поступление доходов происходит периодически равными величинами в конце или начале каждого расчетного периода.
Практические задания
Задача 28
Рассчитать, через сколько лет вклад размером 1 млн. руб. достигнет 1 млрд. руб., если годовая ставка процента по вкладу 16,79% и начисление процентов производится ежеквартально.
Задача 29 Для обеспечения будущих расходов создается фонд. Средства в фонд поступают в виде постоянной годовой ренты постнумерандо. Размер годового платежа 16 млн. руб. На поступившие взносы начисляется 11,18% годовых. Необходимо определить, когда величина фонда будет равна 100 млн. руб.
Задача 30
Ожидается, что ежегодные доходы от реализации проекта составят 3 млн. руб. Необходимо рассчитать срок окупаемости проекта, если инвестиции к началу поступления доходов составят 100 млн. руб., а норма дисконтирования 12,11%
Функция НОРМА
Функция НОРМА определяет значение процентной ставки за один расчетный период. Для нахождения годовой процентной ставки полученное значение следует умножить на число расчетных периодов, составляющих год.
Синтаксис:
НОРМА(кпер; выплата; нз; бс; тип; предположение)
Эта функция вычисляется методом последовательного приближения и может не иметь решения или иметь несколько решений.
Если после 20 итераций погрешность определения ставки превышает 0,0000001, то функция НОРМА возвращает значение ошибки #число!. В этом случае можно попытаться задать другой аргумент предположение, по умолчанию равный 10%. В большинстве случаев этого не требуется.
Существует три варианта использования функции НОРМА:
1) Расчет процентной ставки при известной текущей стоимости нз, будущей стоимости бс, числе периодов кпер:
НОРМА(кпер; нз; бс; предположение).
2) Расчет процентной ставки при фиксированных обязательных или обычных периодических платежах:
НОРМА(кпер; выплата; бс; тип; предположение).
3) Расчет процентной ставки по займу размером нз при равномерном погашении обычными периодическими платежами при условии, что заем полностью погашается:
НОРМА(кпер; выплата; нз; предположение).
Практические задания
Задача 31
Предположим, что компании Х потребуется 100000 тыс. руб. через 2 года. Компания готова вложить 5000 тыс. руб. сразу и по 2500 тыс. руб. каждый последующий месяц. Каким должен быть процент на инвестированные средства, чтобы получить необходимую сумму в конце второго года?
Задача 32
Предположим, что компания Х отказалась от ежемесячных выплат (см предыдущую задачу) и готова сегодня положить на депозит 40000 тыс. руб. Определим, как в этом случае изменится минимальная годовая ставка.
Задача 33
Рассчитайте процентную ставку для четырехлетнего займа в 7000 тыс. руб. с ежемесячным погашением по 250 тыс. руб. при условии, что заем полностью погашается.
Задачи для самостоятельного решения
Вариант №1
Задача 1
Рассчитайте, через сколько лет обязательные ежемесячные платежи размером 150 тыс. руб. принесут доход в 10 млн. руб. при ставке процента 13,5% годовых.
Задача 2
Рассчитайте, через сколько лет произойдет полное погашение займа размером 500 тыс. руб., если выплаты по 100 тыс. руб. производятся в конце каждого квартала, а ставка процента - 15% годовых.
Задача З
Рассчитайте, через сколько лет вклад размером 500 тыс. руб. достигнет величины 1 млн. руб. при ежемесячном начислении процентов и ставке 35-18% годовых.
Задача 4 Предполагается путем ежеквартальных взносов постнумерандо по 35 млн. руб. в течение 3 лет создать фонд размером 500 млн. руб. Какой должна быть годовая процентная ставка?
Задача 5
Какой должна быть годовая процентная ставка по вкладу размером 800 тыс. руб., если его величина к концу года составила 1200 тыс. руб., а проценты начислялись ежемесячно.
Задача 6
Рассчитайте процентную ставку для 3-летнего займа размером 5 млн. руб. с ежеквартальным погашением по 500 тыс. руб.
Вариант №2
Задача 1
Сколько лет потребуется, чтобы платежи размером 1 млн. руб. в конце каждого года достигли значения 10,897 млн. руб., если ставка процента 14,5% годовых?
Задача 2
Предполагается, что ссуда размером 5000 тыс. руб. погашается ежемесячными платежами по 141,7 тыс. руб. Рассчитайте, через сколько лет произойдет погашение, если годовая ставка процента 16%.
Задача 3
Определите, через сколько лет обычные ежегодные. платежи размером 200 тыс. руб. принесут фирме доход в 10 млн. руб. при норме процента -20% годовых.
Задача 4
Фонд размером 21 млн. руб. был сформирован за два года за счет отчислений по 770 тыс. руб. в начале каждого месяца. Определите годовую ставку процента.
Задача 5
Рассчитайте годовую ставку процента по вкладу размером 100 тыс. руб., если за 13 лет эта сумма возросла до 1 млн. руб. при ежеквартальном начислении процентов.
Задача 6
Заем в 980 тыс. руб. погашается равномерно периодическими платежами по 100 тыс. руб. каждые полгода в течение 7 лет. Определите годовую ставку процента.
Расчет постоянных периодических выплат
Функция ППЛАТ
Функция вычисляет величину выплаты за один период на основе фиксированных периодических выплат и постоянной процентной ставки. Выплаты, рассчитанные функцией ППЛАТ, включают основные платежи и платежи по процентам.
Синтаксис:
ППЛАТ(норма; кпер; нз; бс; тип)
Практические задания
Задача 34
Предположим, что необходимо накопить 4000 тыс. руб. за три года, откладывая постоянную сумму в конце каждого месяца. Какой должна быть эта сумма, если норма процента по вкладу составляет 12% годовых?
Задача 35
Допустим, банк выдал ссуду 200 млн. руб. на 4 года под 18% годовых. Ссуда выдана в начале года, а погашение начинается в конце года одинаковыми платежами. Определите размер ежегодного погашения ссуды.
Задача 36
Определите размер периодических взносов в фонд размером 100 млн. руб., сформированный за два года ежемесячными платежами, если процентная ставка составляет 20% годовых.
Расчет платежей по процентам Функция ПЛПРОЦ
Функция определяет платежи по процентам за данный период на основе периодических постоянных выплат и постоянной процентной ставки.
Синтаксис:
ПЛПРОЦ(норма; период; кпер; нз; бс; тип)
период - это период, для которого требуется найти прибыль; должен находиться в интервале от 1 до кпер.
Практические задания
Задача 37
Вычислите платежи по процентам за первый месяц от трехгодичного займа в 800 тыс. руб. из расчета 10% годовых.
Задача 38
Предположим, что за счет ежегодных отчислений в течение 6 лет был сформирован фонд в 5000 тыс. руб. Определить, какой доход приносили вложения владельцу фонда за последний год, если годовая ставка составляла 17,5% годовых.
Задача 39
Определите платежи по процентам по пятилетнему займу в 16 млн. руб., выданному под 22% годовых, за двенадцатый месяц, если проценты начисляются ежемесячно.
Оценка инвестиций на основе Таблицы подстановки
При оценке и анализе вариантов инвестиций часто требуется получить конечные значения для различных наборов исходных данных. Например, построить финансовую модель для различных значений процентных ставок и периодических выплат и выбрать оптимальное решение. Для решения подобных задач в Excel служит Таблица подстановки.
Принцип использования этого инструмента состоит в следующем.
Возможные значения одного или двух аргументов функции необходимо представить в виде списка или таблицы.
Для одного аргумента список исходных значений задается в виде строки или столбца таблицы. Excel подставляет эти значения в функцию, заданную пользователем, а затем выстраивает результаты соответственно в строку или столбец.
При использовании таблицы с двумя переменными значения одной из них располагаются в столбце, другой – в строке, а результаты вычислений – на пересечении столбца и строки.
Пример 1. Требуется определить, какие ежемесячные выплаты необходимо вносить по ссуде 200 млн. руб., выданной на 3 года, при разных процентных ставках.
Выполнение работы.
1. Подготовим исходные данные на рабочем листе:
2. Введем в ячейку D7
= ППЛАТ(C4/12, C3*12, C2)
3. Выделим диапазон ячеек, содержащий исходные значения процентных ставок, формулу для расчета и ячейки, где будут расположены результаты
С7:В13
4. В меню Данные выберем команду Таблица подстановки.
5. В диалоговом окне инструмента зададим адрес ячейки, на которую ссылается формула расчета.
6. Ok.
Аналогичным образом в этой же таблице в столбце E рассчитаем платежи по процентам за первый период для каждого значения процентной ставки. В ячейку E7 необходимо ввести формулу
= ПЛПРОЦ(C4/12, C3*12, C2)
и повторить все шаги.
Если в таблицу подстановки требуется включить больше формул, использующих значения процентных ставок, то дополнительные формулы вставляются справа от существующей в той же строке. Затем необходимо выделить всю таблицу, включая полученные ранее значения, и заполнить диалоговое окно инструмента.
Пример 2. Необходимо подсчитать ежемесячные выплаты по займу размером 300 млн. руб. для различных сроков погашения и разных процентных ставок.
Выполнение работы.
1. Подготовим исходные данные на рабочем листе:
a) введем первое множество входных значений в столбец;
b) введем первое множество входных значений в столбец;
c) введем формулу для расчета в угловую ячейку блока ячеек на пересечении столбца и строки.
2. Выделим диапазон ячеек, включающий исходные все исходные значения, формулу для расчета и ячейки, где будут расположены результаты B7:F13
3. В меню Данные выберем команду Таблица подстановки и заполним диалоговое окно инструмента.
4. Ok.
Практические задания
Задача 40
Определите эффективность инвестиций размером около 200 млн. руб., если ожидаемые ежемесячные доходы за первые пять месяцев составят соответственно 20, 40, 60, 80 и 100 млн. руб. Расчет произвести для разных годовых процентных ставок (13,5%, 14%, 14,5%, 15%), а также для разных значений инвестиций (170, 200, 230, 260 и 290 млн. руб.).
Задача 41
Рассчитайте, какую сумму надо положить на депозит, чтобы через четыре года она выросла до 20000 тыс. руб. при ежеквартальном начислении процентов и при разной годовой процентной ставке (9%, 11%, 13%, 15%).
Задача 42
Определите размеры ежемесячных взносов, формирующих фонд размером 200 млн. руб. для разных сроков формирования фонда (2, 2.5, 3 и 3.5 года) и при разной годовой процентной ставке (18%, 20%, 22% , 24%).
Задача 43
Рассчитать, через сколько лет вклад размером 1 млн. руб. достигнет величины 1 млрд. руб. при ежеквартальном начислении процентов и разной годовой процентной ставке (16%, 18%, 20%, 22%. 24%).
Задачи для самостоятельного решения
Задача 1.
В конце года капиталовложения по некоторому проекту составят 1280 млн. руб. Ожидается, что за последующие 4 года проект принесет следующие доходы: 420, 490, 550, 590 млн. руб. Рассчитайте чистую текущую стоимость проекта для различных норм дисконтирования (13%, 13.8%, 14%, 14.5%, 15%) и разных объемов капиталовложений (1250, 1270, 1290 и 1310 млн. руб.)
Задача 2.
Промоделируйте, какая сумма будет на счете, если вклад размером 5000 тыс. руб. положен на 3 года с полугодовым начислением процентов при разной годовой процентной ставке (12%, 13%, 14%, 15%).
Задача 3.
Определите ежемесячные выплаты по займу размером 400 млн. руб. для разных сроков платежа (3, 5, 8, 12, 15 лет) и при разной годовой процентной ставке (9%, 9.25%, 9.5%, 9.75%, 10%).
Задача 4.
Определить годовую процентную ставку при погашении займа в 1000 тыс. руб. периодическими ежеквартальными платежами в течение 7 лет для разных значений платежей (90, 100, 110, 120, 130 тыс. руб.)
Функции Excel для расчета амортизации
Амортизация – постепенное перенесение стоимости средств труда по мере их физического и морального износа на производственный продукт. Переносимая стоимость в денежной форме аккумулируется в амортизационном фонде.
Суммы амортизации включаются в издержки производства (себестоимость продукции) в виде амортизационных отчислений.
Функция AMP
Функция AMP вычисляет амортизацию имущества за один период равномерным методом. При использовании равномерного метода для каждого периода величина амортизационных отчислений одинакова, а совокупная величина отчислений к концу последнего периода равна стоимости амортизируемого имущества.
Синтаксис:
АМР(стоимость; ликвидная_стоимость; время_эксплуатации)
стоимость - первоначальная стоимость имущества;
ликвидная_стоимость - остаточная стоимость имущества в конце срока эксплуатации;
время_эксплуатации - срок эксплуатации имущества.
Функция АМГД
Функция АМГД позволяет рассчитать амортизационные отчисления за заданный период. Этот метод характеризуется постоянным понижением амортизационных отчислений и обеспечивает полное возмещение амортизируемой стоимости имущества.
Синтаксис:
АМГД(стоимость; ликвидная_стоимость; жизнь; период)
жизнь - срок эксплуатации имущества;
период - период, для которого требуется вычислить амортизацию.
Функция ДОБ
Функция ДОБ вычисляет величину амортизации имущества для заданного периода с использованием метода постоянного учета амортизации, использует фиксированную норму амортизации.
Синтаксис:
ДОБ(стоимость; остаточная_стоимость; время_эксплуатации; период;
месяц)
месяц - число месяцев в первом году эксплуатации имущества, по умолчанию равно 12.
Функция ДДОБ
Функция ДДОБ позволяет рассчитать сумму амортизации для заданного периода методом уменьшающегося остатка. Можно задать коэффициент ускоренной амортизации, по умолчанию равный двум (двукратный учет).
Синтаксис:
ДДОБ(стоимость; остаточная_стоимость; время_эксплуатации; период;
коэффициент)
Практическое задание
Задача 44 Определить величину ежегодной амортизации оборудования начальной стоимостью 8000 тыс. руб., если срок эксплуатации имущества - 10 лет, а остаточная стоимость - 500 тыс. руб. Расчеты сделать с использованием разных функций. Сравнить полученные результаты.
Финансовые расчеты по ценным бумагам
Финансовые инвестиции с целью получения дохода, сохранения и приумножения капитала являются обязательным видом деятельности в условиях рыночной экономики.
Объем купли-продажи на фондовом рынке – разнообразные виды ценных бумаг.
В зависимости от формы предоставления капитала и способа выплаты дохода бумаги делятся на:
- Долговые ценные бумаги – Облигации, векселя, сертификаты, …. Они имеют фиксированную процентную ставку и являются обязательством выплатить капитальную сумму долга на определенную дату в будущем.
- Недолговые бумаги – долевые ценные бумаги или акции. Они являются непосредственной долей держателя в реальной собственности для получения дивидендов неограниченное время.
Прочие ценные бумаги являются производными формами.
Функции для расчетов ценных бумаг с периодической выплатой процентов
Эти функции предназначены для расчетов по ценным бумагам с периодическими выплатами купонного дохода и погашением ценной бумаги в конце срока ее действия по номиналу или иной выкупной цене.
Функция ДОХОД
Функция позволяет рассчитать годовую ставку помещения по операциям с ценными бумагами при заданной купонной ставке и разности курсов покупки и погашения за указанный период действия ценной бумаги.
Синтаксис:
ДОХОД(дата_соглашения; дата_вступления_в_силу; ставка; цена; погашение; частота; базис)
дата_соглашения - календарная дата покупки (дата инвестиций);
дата_вступления_в_силу - календарная дата погашения или выкупа ценной бумаги у инвестора (дата окончания действия ценой бумаги);
ставка - годовая ставка выплат по купонам в %;
цена - рыночная цена (в абсолютном выражении) или курс (в относительном выражении) ценной бумаги при покупке инвестором;
погашение - цена или курс продажи ценной бумаги инвестором;
частота - количество выплат по купонам в течение года (1, 2 или 4);
базис - принятый способ исчисления временного периода (года, месяца), принимает значения 0 – 4.
0 – американский стандарт (30 дней в месяце, 360 дней в году);
1 – фактический (фактическое количество дней в месяце и в году);
2 – фактический/360 (количество дней в месяце – фактическое, а в году – 360);
3 – фактический/365 (количество дней в месяце – фактическое, а в году – 365);
4 – европейский стандарт (30 дней в месяце, 360 дней в году).
Практическое задание
Задача 45 Облигации приобретены (дата_соглашения) 6.09.1993 по курсу (цена) 89 и имеют купонный доход (ставка) в размере 9%, который выплачивается с периодичностью (частота) один раз в полугодие. Предполагаемая дата погашения облигации (дата_вступления_в_силу) – 12.09.97 по курсу (погашения) 100. Определить годовую ставку помещения.
С помощью таблицы подстановки можно проанализировать влияние на годовую ставку помещения аргументов функции, например, цена и купон облигации.
Выполним графическую иллюстрацию задачи с помощью мастера диаграмм.
Функция ЦЕНА
Функция рассчитывает курс (цену) покупки ценной бумаги с периодическими выплатами купонных процентов.
Синтаксис:
ЦЕНА(дата_соглашения; дата_вступления_в_силу; ставка; доход; погашение; частота; базис)
доход – годовая ставка помещения.
Практическое задание
Задача 46 Облигации приобретены 6.09.1993 и будут погашены 12.09.97г. Размер купонной ставки – 9% с выплатой раз в полугодие. Ожидаемая годовая ставка помещения – 12.57%, номинал облигации (погашение) – 100, базис расчета – 1. Определить цену покупки облигации.
Функция НАКОПДОХОД
Купонный доход накапливается в интервале времени между выплатами.
Функция НАКОПДОХОД вычисляет накопленный на момент приобретения ценной бумаги купонный доход.
Синтаксис:
НАКОПДОХОД(дата_впуска; дата_первой_выплаты; дата_соглашения; ставка; номинал; периодичность; базис)
номинал – цена ценной бумаги при погашении;
периодичность – частота.
Практическое задание
Задача 47
Облигации номиналом 1000 руб. с купонной ставкой 9%, периодичностью выплат 1 раз в полугодие выпущены 1.09.1996г. дата первой оплаты купонов – 1.03.1997г., базис расчетов – 1. Облигация приобретена 12.12.1996г. Определить накопленный купонный доход на момент приобретения.
Проанализировать влияние на величину накопленного дохода периодичности и купонной ставки с помощью таблицы подстановки.
Выполнить графическую иллюстрацию задачи с помощью построения диаграммы.
Задания для самостоятельного решения
Вариант №1
Задача 1 Облигация приносит 45% годового дохода, срок действия облигации с 20.09.96 по 20.09.98. Купон в размере 30% годовых выплачивается раз в полугодие. Определите приемлемую цену для приобретения облигации в период с 1.12.96 по 1.01.96.
Задача 2 Облигация номиналом 1 млн. руб. выпущена некой фирмой 1.01.96 с погашением через два календарных года. Установлен размер купонных выплат – 20% годовых, выплачиваемых ежеквартально. Облигация приобретена 1.0.96 по курсу 72. Определите целесообразность вложений в покупку облигации, если рыночный уровень доходности составляет 45%
Задача 3 Облигация номиналом 100000 руб. имеет купон 15% годовых с выплатой 1 раз в квартал. Облигация приобретена 30.4.96, выпущена – 1.01.96, Определите размер купонной выплаты при базисе расчета 1.
Вариант №2
Задача 1 Облигация приносит 39% годового дохода, срок действия облигации с 17.07.96 по 1.01.98. Купон в размере 20% годовых выплачивается раз в полугодие. Определите приемлемую цену для приобретения облигации при условии обеспечения доходности 66%
Задача 2 Сравните доходность облигаций двух типов, которые приобретены 17.07.96 и будут погашены 1.01.98, если:
1 облиг. 2 облиг.
Цена 50 80
Погашение 100 100
Купон 10% 20%
Периодичность 4 2
Базис 1 1
Задача 3 Облигация номиналом 500000 руб., выпущенная 1.07.96, приобретена 12.09.96. Периодичность купонных выплат в размере 40% годовых – ежеквартальная. Дата первой купонной выплаты – 1.10.96. Определите накопленный на момент приобретения купонный доход при базисе расчета 1.
Диспетчер сценариев
Диспетчер сценариев используют для финансовых расчетов, основанных на задании различных значений аргументов функции.
Сценарий – именованная совокупность значений изменяемых ячеек.
Например, необходимо решить следующую задачу (задача 51):
1. Вычислить значение функции ЦЕНА для заданного набора исходных данных.
2. Используя инструмент Диспетчер сценариев из меню Сервис, построить сценарии для следующих наборов аргументов:
1 2 3 4 5 6
купонная_ставка доход
частота 9% 12,57% 2 9% 12,57% 4 15% 12,57% 2 15% 12,57% 4 9% 15,00% 2 9% 15,00% 4
Выполнение работы
1. Выделить ячейки с аргументами, значения которых будут изменяться.
2. Из меню Сервис выбрать опцию Сценарии.
3. В появившемся диалоговом окне щелкнуть кнопку добавить.
4. Во втором диалоговом окне набрать имя сценария и подтвердить.
5. В третьем диалоговом окне записать необходимые значения изменяемых ячеек (аргументов) и подтвердить.
6. Для каждого следующего сценария выполнить пункты 3 – 5.
7. Щелкнуть по кнопке Отчет и заказать тип отчета.
Задания для самостоятельного решения
Вариант №1
Задача 1
Облигации приобретены 1.07.96 по курсу 79.123 и имеют купонный доход в размере 12%, который выплачивается с периодичностью - один раз в год. Предполагаемая дата погашения – 31.12.97 по курсу 100. Определите годовую ставку помещения.
Задача 2
Облигации приобретены 1.07.96 и будут погашены 31.12.97. Размер купонной ставки - 12% с выплатой один раз в год. Ожидаемая годовая ставка - 17.12%, номинал облигации - 100, базис расчета -1. Определите курс покупки ценных бумаг.
Задача 3
Облигации номиналом 1000 руб. с купонной ставкой 12%, периодичность выплат - один раз в год выпущены 1.07.96. Дата первой оплаты купонов -1.07.97, базис расчетов - 1. Облигация приобретена 15.11.96. Определить накопленный купонный доход.
1. Решить предложенные задачи.
2. Построить сценарии, используя следующие наборы исходных данных:
Задача 1 Задача 2 Задача 3
Сценар1 Сценар2 Сценар1 Сценар2 Сценар1 Сценар2
куп ставка 25% 40% куп ставка 15% 9% дата соглаш 15.12.96 15.01.97
цена 90 100 ставка 12,57% 15,00% ставка 10,5% 11%
частота 4 2 частота 4 2 периодичн. 4 2
Вариант №2
Задача 1
Облигации приобретены 1.07.96 и будут погашены 31.12.97. Размер купонной ставки - 11% с выплатой один раз в год. Ожидаемая годовая ставка - 15.34%, номинал облигации - 100, базис расчета - 1. Определите курс покупки ценных бумаг.
Задача 2
Облигации приобретены 1.07.96 по курсу 77.345 и имеют купонный доход в размере 11%, который выплачивается с периодичностью - один раз в год. Предполагаемая дата погашения - 31.12.97 по курсу 100. Определите годовую ставку помещения.
Задача 3 Облигации номиналом 1000 руб. с купонной ставкой 12%, периодичность выплат – два раза в год выпущены 1.07.96. Дата первой оплаты купонов – 3.01.97, базис расчетов - 1. Облигация приобретена 22.09.96. Определить накопленный купонный доход
1. Решить предложенные задачи.
2. Построить сценарии, используя следующие наборы исходных данных:
Задача 1 Задача 2 Задача 3
Сценар1 Сценар2 Сценар1 Сценар2 Сценар1 Сценар2
куп ставка 15% 15% куп ставка 25% 40% дата соглаш 15.12.96 15.01.97
ставка 12,57% 12,57% цена 90 100 ставка 10,5% 11%
частота 4 2 частота 4 2 периодичн. 1 4
Вариант №3
Задача 1
Облигации номиналом 1000 руб. с купонной ставкой 10,5%, периодичность выплат - четыре раза в год выпущены 1.08.96. Дата первой оплаты купонов - 1.02.97, базис расчетов - 1. Облигация приобретена 2.01.97. Определить накопленный купонный доход
Задача 2
Облигации приобретены 1.08.96 по курсу 64.456 и имеют купонный доход в размере 5%, который выплачивается с периодичностью - один раз в год. Предполагаемая дата погашения -1.02.98 по курсу 100. Определите годовую ставку помещения.
Задача 3
Облигации приобретены 1.08.96 и будут погашены 1.02.98. Размер купонной ставки - 5% с выплатой один раз в год. Ожидаемая годовая ставка -14.45%, номинал облигации -100, базис расчета -1. Определите курс покупки ценных бумаг.
1. Решить предложенные задачи.
2. Построить сценарии, используя следующие наборы исходных данных:
Задача 1 Задача 2 Задача 3
Сценар1 Сценар2 Сценар1 Сценар2 Сценар1 Сценар2
дата соглаш 15.12.96 15.01.97 куп ставка 25% 40% куп ставка 15% 9%
ставка 10,5% 11% цена 90 100 ставка 12,57% 15,00%
периодичн. 1 2 частота 4 2 частота 4 2