Формирование базы данных книг магазина на Java

 

Финансовые функции MS Excel в экономических расчетах

План:

1. Функции даты и времени для финансовых расчетов

. Финансовые функции для расчета ипотечной ссуды

. Функции для расчета годовой процентной ставки

.Функции для расчета эффективности капиталовложений

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

. Функции для расчета будущего значения вклада, процентной ставки и количества периодов выплаты долга

. Функции для расчета постоянных рент. Функции для расчета амортизации

. Финансовые функции для работы с ценными бумагами

1. Функции даты и времени для финансовых расчетов

финансовый функция процентный excel

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

Если рассчитывается точное число дней, то надо просто вычесть из конечной даты (даты завершения операции) начальную дату. Однако в финансовой практике есть правило, согласно которому в месяце считается 30 дней, а в году 360. В этом случае используется функция ДНЕЙ360. Ее назначение - расчет количества дней между двумя датами на основе 360-дневного года (12 30-дневных месяцев), аргументы: нач_дата - начальная дата сделки; кон_дата - конечная дата сделки; метод - логическое значение, если отсутствует, есть «ЛОЖЬ».

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

Для определения срока между датами в годах надо использовать функцию ДОЛЯГОДА с аргументами (нач_дата, кон_дата, базис). Эта функция устанавливается вместе с пакетом анализа (Сервис®Пакет анализа).

Первые два аргумента имеют то же значение, что и у функции ДНЕЙ360. Аргумент «базис» может принимать пять различных значений в зависимости от правила, установленного для расчета срока в годах:

или опущен - правило 30/360 (приближенный срок между датами в днях (американский метод расчета), приближенное число дней в году);

- АСТ/АСТ (точный срок между датами в днях, точное число дней в году);

- АСТ/360 (точный срок между датами в днях, приближенное число дней в году);

- АСТ/365 (точный срок между датами в днях, но в любом году 365 дней);

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

Пример.

Дата получения кредита - 15 января 2008 г., дата погашения - 15 марта того же года. Найти срок кредита в днях и годах.

Решение.

Точное число дней = «15/03/2008» - «15/01/2008» = 59.

Приближенное число дней = ДНЕЙ360(«15/01/2008», «15/03/2008») = 60.

Срок кредита в годах по правилу

АСТ/АСТ = ДОЛЯГОДА(«15/01/2008», «15/03/2008») = 0,161644.

Функции для вычисления простых и сложных процентов

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

Для расчета простой кредитной сделки используется схема простых процентов, согласно которой накопленная сумма (или полная сумма долга) S есть S=P(1+r), где r - процентная ставка за период сделки. Если i - годовая процентная ставка (процентная ставка за период 1 год), то r=it, где t - срок сделки, измеримый в годах, и

S=P(1+ it).

Если на основную сумму долга P начисляются сложные проценты, то формула для расчета наращенной суммы приобретает вид

S=P(1+ i)t,

где i - процентная ставка за период начисления процентов (базовый период); t - срок сделки в единицах базового периода. Если задана годовая (номинальная) процентная ставка j и кратность начисления процентов m, то i=j/m.

Полная сумма долга S вычисляется с помощью финансовой функции БС с аргументами (ставка; кпер; плт; пс; тип). Если расчет ведется по схеме простых процентов, то аргументы функции БС имеют следующий смысл: ставка = r - процентная ставка за период сделки, кпер = 1, пс = P - начальная сумма кредита. Аргументы «плт» и «тип» при расчете простой кредитной сделки должны быть опущены.

В случае расчета кредитной сделки по схеме сложных процентов аргумент «ставка» - это процентная ставка за базовый период (период начисления процентов), а кпер - срок сделки в единицах базового периода.

Пример.

Ссуда в размере 10 000 руб. выдана сроком на полгода под 12% годовых. Найти полную сумму долга: а) по схеме простых процентов; б) по схеме сложных процентов, если проценты начисляются ежемесячно.

Решение.

а) Простые проценты.

Ставка за период кредита r=12%/2=6%. Поэтому

S=БС(6%, 1,,10 000)=-10 600 руб.

б) Сложные проценты.

Базовый период = 1 месяц, поэтому ставка = 12%/12=1%, а кпер=6.

S=БС(12%/12, 6,,10 000)=-10 615,20 руб.

Результат вычислений получился отрицательный, так как в финансовых функциях учитываются знаки денежных сумм: сумма, которая взята, имеет знак «плюс», сумма, которую надо вернуть, - знак «минус».

Для расчета основной суммы долга P по известной наращенной сумме S используется функция ПС с аргументами (ставка; кпер; плт; бс; тип). Ее аргументы аналогичны соответствующим аргументам функции БС, аргументы «плт» и «тип» опускаются при расчете простой кредитной сделки.

Пример.

Какую суму надо положить в банк, выплачивающий ежеквартально 16% годовых, чтобы через полгода получить 15 000 руб.?

Решение.

Базовый период - один квартал, поэтому ставка = 16%/4.

Кпер = 2; P = ПС(16%/4, 2,, 15 000) = - 13 868,34 руб.

. Финансовые функции для расчета ипотечной ссуды

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

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

Синтаксис: ПЛТ(Ставка;Кпер;Пс;Бс;Тип).

Аргументы:

§Ставка - процентная ставка по ссуде,

§Кпер - общее число выплат по ссуде,

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

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

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

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

Например, если вы делаете ежемесячные выплаты по четырехгодичному займу из расчета 12 % годовых, то для задания аргумента Ставка используйте 12 %/12, а для задания аргумента Кпер - 4*12. Если вы делаете ежегодные платежи по тому же займу, то для задания аргумента Ставка используйте 12 %, а для задания аргумента Кпер - 4.

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

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

Например, депозит в банк на сумму 1000 руб. представляется аргументом - 1000, если вы вкладчик, и аргументом -1000, если вы - пpeдставитель банка.

Рассмотрим пример.

Пример 1. Вычислить 30-летнюю ипотечную ссуду покупки квартиры за 201900 руб. с годовой ставкой 8% и начальным взносом 20%. Сделать расчет для ежемесячных и ежегодных выплат (табл. 1).

Решение:

1.Откройте Лист 1 и переименуйте его в Задание 1.

2.Введите в ячейки A1:B6 данные, представленные на рис. 1.

Рис. 1. Форма для расчета ипотечной ссуды

3.Для выполнения расчетов в ячейки должны быть введены формулы, показанные на рис. 2.

Рис. 2. Формулы для расчета ипотечной ссуды

4.Результаты расчеты должны быть следующими (рис. 3):

Рис. 3. Расчет ипотечной ссуды

3. Функции для расчета годовой процентной ставки

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

Чистый текущий объем вклада - это сегодняшний объем будущих платежей (отрицательные значения) и поступлений (положительные значения).

Например, вам предлагают следующую сделку. У вас берут в долг некоторую сумму денег и предлагают через k1 лет вернуть сумму, равную Рk1, через k2 лет - Рk2 и т. д. и, наконец, через kn лет - Рkn. Кроме данной сделки, у вас есть альтернативный способ использования ваших денег, например, положить их в банк под i процентов годовых. Тогда чистым текущим объемом вклада является та сумма денег, которой вам нужно располагать начальный год, чтобы, положив их в банк под i % годовых, получили предлагаемую прибыль.

Синтаксис: ЧПС(Ставка;Значение1;Значение2;…..)

Аргументы:

§ставка - ставка дисконтирования за один период;

§значение 1, значение 2, - от 1 до 29 аргументов, представляющих расходы и доходы:

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

ЧПС использует порядок аргументов значение 1, значение 2, … для определения порядка поступлений и платежей. Убедитесь в том, что ваши платежи и поступления введены в правильном порядке.

Считается, что инвестиция, значение которой вычисляет функция ЧПС, начинается за один период до даты денежного взноса 1-го значения и заканчивается с последним денежным взносом в списке.

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

Функция ЧПС связана с функцией ВСД (внутренняя скорость оборота). ВСД - это скорость оборота, для которой ЧПС равняется нулю:

ЧПС(ВСД(…);…)=0.

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

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

Синтаксис: ВСД (Значения;Предположение).

Аргументы:

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

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

§предположение - величина, о которой предполагается, что она близка к результату ВСД.

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

В большинстве случаев нет необходимости задавать прогноз для вычислений с помощью функции ВСД. Если прогноз опущен, то он полагается равным 0,1 (10 %).

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

Рассмотрим пример.

Пример 2. Вас просят в долг 10000 руб. и обещают вернуть через год 2000 руб., через два года - 4000 руб., через три года - 7000 руб. При какой годовой процентной ставке эта сделка выгодна?

Решение.

. Откройте Лист 2 и переименуйте его в Задание 2.

. Введите в ячейки A1:B7 данные, представленные на рис. 4.

Рис. 4. Форма для расчета годовой процентной ставки

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

Рис. 5. Формулы для расчета годовой процентной ставки

. Первоначально в ячейку В10 введите произвольный процент, например 3 %.

В ячейку В11 введите формулу =ЧПС(В10;В5:В7) (см. рис. 5).

. В ячейку С8 введите формулу:

=ЕСЛИ(В8=1;»год»;ЕСЛИ(И(В8>=2;B8<=4) ;»года»;»лет»))

В результате должно получиться (см. рис. 6):

Рис. 6. Расчет чистого текущего объема вклада

. Затем выбираем команду Сервис / Подбор параметра и заполняем открывшееся диалоговое окно Подбор параметра, как показано на рис. 7.

Рис. 7. Диалоговое окно Подбор параметра при расчете годовой процентной ставки

7. В поле Установить в ячейке: укажите ссылку на ячейку В11, в которой вычисляется чистый текущий объем вклада по формуле:

=ЧПС(B10;B5:B7).

В поле Значение установить 10000 - размер ссуды.

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

После нажатия кнопки ОК средство подбора параметров определит, при какой годовой процентной ставке чистый текущий объем вклада равен 10000 руб. Результат вычисления выводится в ячейку В10.

. В нашем случае годовая учетная ставка равна 11,79 %.

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

. Результаты расчеты должны быть следующими:

Рис. 8. Расчет годовой процентной ставки

. Функции для расчета эффективности капиталовложений

Расчет эффективности капиталовложений осуществляется с помощью функции ПС.

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

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

Синтаксис: ПС(Ставка;Кпер;Плт;Бс;Тип)

Аргументы:

§Ставка - процентная ставка за период,

§Кпер - общее число периодов платежей по аннуитету,

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

§Бс - требуемое значение будущей стоимости или остатка средств после последней выплаты. Если аргумент опущен, он полагается равным 0 (будущая стоимость займа, например, равна 0),

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

Рассмотрим пример.

Пример 3. У вас просят в долг 10000 руб. и обещают возвращать по 2000 руб. в течение 7 лет. Будет ли выгодна эта сделка при годовой ставке 7 %?

Решение:

. Откройте Лист 3 и переименуйте его в Задание 3.

. Введите в ячейки A1:B6 данные, представленные на рис. 9.

Рис. 9. Форма расчета эффективности капиталовложений

. В ячейку В6 введите формулу:

=ПС(В5;В3;-В4)

. В ячейку С3 введите формулу:

=ЕСЛИ(В3=1; "год";ЕСЛИ(И(В3>=2;В3<=4); "года";"лет"))

. В ячейку В7:

=ЕСЛИ (В2<В6; "Выгодно дать деньги в долг"; ЕСЛИ(В6=В2; "Варианты равносильны"; "Выгоднее деньги положить под проценты"))

Рис. 10. Расчет эффективности капиталовложений

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

Часто бывает удобно проанализировать ситуацию для нескольких возможных вариантов параметров. Команда Сервис / Сценарии предоставляет такую возможность с одновременным автоматизированным составлением отчета.

Рассмотрим способ применения этой команды для следующих трех комбинаций срока и суммы ежегодно возвращаемых денег:

, 2000; 12, 1500 и 7, 1500.

Выберем команду Сервис / Сценарии. В открывшемся диалоговом окне Диспетчер сценариев для создания первого сценария нажмите кнопку Добавить (рис. 11).

Рис. 11. Диалоговое окно Диспетчер сценариев.

В диалоговом окне добавление сценария в поле Название сценария введите, например ПС1, а в поле Изменяемые ячейки - ссылку на ячейки В3 и В4, в которые вводятся значения параметров задачи (срок и сумма ежегодно возвращаемых денег) (рис. 12).

Рис. 12. Диалоговое окно добавление сценария.

После нажатия кнопки ОК появится диалоговое окно Значения ячеек сценария, в поля которого введите значения параметров для первого сценария (рис. 13).

Рис. 13. Диалоговое окно Значения ячеек сценария.

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

Рис. 10. Вывод сценариев на рабочий лист с помощью диалогового окна Диспетчер сценариев.

С помощью кнопки Вывести можно вывести результаты, соответствующие выбранному сценарию. Нажатие кнопки Отчет открывает диалоговое окно Отчет по сценарию (рис. 11).

Рис. 11. Диалоговое окно Отчет по сценарию.

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

На рис. 12. показан отчет по сценариям типа Структура.

Рис.12. Отчет по сценарию типа Структура

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

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

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

Синтаксис: ПРПЛТ (Ставка; Период; Кпер; Пс; Бс; Тип).

Функция ОСПЛТ возвращает величину выплаты за данный период на основе периодических постоянных платежей и постоянной процентной ставки.

Синтаксис: ОСПЛТ(Ставка; Период; Кпер; Пс; Бс; Тип).

Аргументы функций ПРПЛТ: и ОСПЛТ:

§Ставка - процентная ставка за период,

§Период - задает период, значение должно быть в интервале от 1 до «Кпер»,

§Кпер - общее число периодов выплат годовой ренты,

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

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

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

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

Функции ПРПЛТ и ОСПЛТ тесно связаны между собой, а именно ПЛПj= i Bj-1, ОСНПj = А - ПЛПj, Bj = Вj-1 - ОСНПj при j ?[0, n],

где j - номер периода,

п - КПЕР,

ПЛПj, ОСНПj и Bj - это ПРПЛТ, ОСПЛТ и остаток долга, соответственно, за j-й период,

ПЛПо = 0, ОСНПо = 0, Bо - Пс,

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

Рассмотрим пример.

Пример 4. Вычислить основные платежи, платы по процентам, общей ежегодной платы и остатка долга на примере ссуды 100000 руб. на срок 5 лет при годовой ставке 2 %.

Решение:

1.Откройте Лист 4 и переименуйте его в Задание 4.

2.Введите данные, представленные на рис. 13.

3.Ежегодная плата вычисляется в ячейке В4 по формуле:

=ПЛТ(процент; срок; -размер_ссуды),

где ячейки В2, В3 и В5 имеют имена: процент, срок и размер_ссуды, соответственно. Присвоение имени ячейке осуществляется с помощью команды Вставка / Имя / Присвоить.

4.За первый год плата по процентам в ячейке В8 вычисляется по формуле:

=D7*процент.

Рис. 13. Функции для вычисления основных платежей и платы по процентам.

5.Основная плата в ячейке С8 вычисляется по формуле:

=ежегодная_плата-В8,

где ежегодная_плата - имя ячейки В4.

Остаток долга в ячейке D8 вычисляется по формуле:

=D7-C8.

6.В оставшиеся годы эти платы определяются с помощью протаскивания маркера заполнения выделенного диапазона B8:D8 вниз по столбцам.

7.Данные результаты расчетов должны быть следующими (рис. 14.):

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

. Функции для расчета будущего значения вклада, процентной ставки и количества периодов выплаты долга

Функция БС вычисляет будущее значение вклада на основе периодических постоянных платежей и постоянной процентной ставки.

Функция БС подходит для расчета итогов накоплений при ежемесячных банковских взносах.

Синтаксис: БС (Ставка; Кпер; Плт; Пс; Тип).

Аргументы:

§Ставка - процентная ставка за период,

§Кпер - общее число периодов выплат,

§Плт - величина постоянных периодических платежей,

§Пс - текущее значение, то есть общая сумма, которую составят будущие платежи,

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

Если тип равен 0 или опущен, то оплата производится в конце периода, если 1 - в начале периода. Если тип = 0 и БС = 0, то функция БС вычисляется по формуле (6):

(6)

где А - Плт; i - Ставка; n - Кпер.

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

Синтаксис: КПЕР(Ставка; Плт; Пс; БС; Тип).

Аргументы:

§Ставка - процентная ставка за период,

§Плт - величина постоянных периодических платежей,

§Пс - текущее значение, т.е. общая сумма, которую составят будущие платежи,

§БС - будущая стоимость или баланс наличности, который нужно достичь после последней выплаты.

Если аргумент БС опущен, он полагается равным 0 (например, будущая стоимость займа равна 0), тип - число 0 или 1, обозначающее, когда должна производиться выплата. Если тип равен 0 или опущен, то оплата производится в конце периода, если 1 - то в начале периода. Если тип = 0 и БС = 0 функция КПЕР вычисляется по формуле (7):

(7)

где Р - ПС; i - Ставка; А - Плт.

Рассмотрим пример.

Пример 5. Вы хотите зарезервировать деньги для специального проекта, который будет осуществлен через год. Предположим, вы собираетесь вложить 1000 руб. при годовой ставке 6 %. Вы собираетесь вкладывать по 100 руб. в начале каждого месяца в течение года. Сколько денег будет на счете в конце 12 месяцев?

Решение:

1.Откройте Лист 5 и переименуйте его в Задание 5.

2.Ведите данные в ячейки А1:С6 (см. рис. 15).

Рис. 15. Расчет будущего значения вклада

3.В ячейку С8 введите формулу:

=БС(6 %/12; 12; -100; -1000; 1)

получаем ответ: 2 301,40 руб (см. рис. 16).

Рис. 16. Диалоговое окно Аргументы функции БС

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

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

Синтаксис: КПЕР(Ставка; Плт; Пс; БС; Тип).

Аргументы:

§Ставка - процентная ставка за период,

§Плт - величина постоянных периодических платежей,

§Пс - текущее значение, т.е. общая сумма, которую составят будущие платежи,

§БС - будущая стоимость или баланс наличности, который нужно достичь после последней выплаты.

Если аргумент БС опущен, он полагается равным 0 (например, будущая стоимость займа равна 0), тип - число 0 или 1, обозначающее, когда должна производиться выплата. Если тип равен 0 или опущен, то оплата производится в конце периода, если 1 - то в начале периода. Если тип = 0 и БС = 0 функция КПЕР вычисляется по формуле (7):

(7)

где Р - ПС; i - Ставка; А - Плт.

Рассмотрим пример.

Пример 6. Вы берете в долг 1000 руб. при годовой ставке 1% и собираетесь выплачивать по 100 руб. в год, Какое будет число выплат долга?

Решение:

1.Откройте Лист 6 и переименуйте его в Задание 6.

2.Ведите данные в ячейки А1:В6 (см. рис. 17).

Рис. 17. Расчет количества периодов выплат

3.В ячейку В6 введите формулу:

=КПЕР(B5; -B4; B3)

В результате получаем ответ: ?11. (см. рис. 18).

Рис. 18. Диалоговое окно Аргументы функции КПЕР

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

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

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

Синтаксис: СТАВКА(КПЕР; Плт; Пс; БС; Тип; Предположение).

Аргументы:

§КПЕР - общее число периодов платежей по аннуитету;

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

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

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

Если БС = 0 и тип = 0, функция СТАВКА является корнем уравнения (8):

(8)

Рассмотрим пример.

Пример 7. Определить процентную ставку для четырехлетнего займа размером в 8000 руб. с ежемесячной выплатой 200 руб.

Решение:

1.Откройте Лист 7 и переименуйте его в Задание 7.

2.Ведите данные в ячейки А1:В7 (см. рис. 19).

Рис. 19. Расчет процентной ставки

3.В ячейку В6 введите формулу:

=СТАВКА(B5*12;-B4;B3).

4.В ячейку В7 введите формулу:

= В6*12.

В результате получаем: месячная (так как период равен месяцу) процентная ставка равна 0,77 %. Процентная ставка годовая равна 9 % см. рис 20).

Рис.20. Результаты вычисления процентной ставки

. Функции для расчета постоянных рент. Функции для расчета амортизации

Последовательность регулярно повторяющихся платежей называется постоянной рентой. Ренты характеризуются величиной платежа С, периодом, процентной ставкой за период ренты i, количеством периодов n и тем, вносятся платежи в начале (авансированная рента, рента пренумерандо) или в конце периода (обыкновенная рента, рента постнумерандо). Постоянная рента может быть представлена потоком {-P, -C, …, S}, то есть начальный взнос P и последующие платежи C, дают наращенную сумму S, (будущее значение ренты).

Для расчетов в Excel применяется уравнение нулевого баланса:

.

Здесь type=0 для ренты постнумерандо и type=1 для ренты пренумерандо. Будущая стоимость ренты рассчитывается с помощью функции БС.

Назначение функции БС - вычисление будущей (накопленной) стоимости постоянной ренты.

Аргументы:

·ставка - процентная ставка за период ренты;

·кпер - период ренты (количество выплат по ренте);

·плт - величина платежа С, неизменная в иечение всей ренты;

·нс - начальная вложенная (или вложенная) сумма, если аргумент опущен, то он равен нулю;

·тип - 0 или опущен для обыкновенной ренты и 1 для авансированной ренты.

Для расчета текущей стоимости ренты используется функция ПС (ставка; кпер; плт; бс; тип). Необязательный аргумент «бс» имеет смысл будущей стоимости или остатка средств после последней выплаты, если он опущен, то полагается равный нулю.

Пример 1.

Найти текущую стоимость 10-летней ренты с ежеквартальными платежами 100 $ в начале квартала при ставке 12% годовых, вычисляемых ежеквартально.

Решение.

Период ренты - один квартал, поэтому ставка 12%/4, а количество периодов - . Текущая стоимость ренты рассчитывается с помощью функции ПС.

Для расчета продолжительности ренты используется функция КПЕР (ставка; плт; пс; бс; тип) (рис. 1).

Рис. 1. Пример расчета текущего значения авансированной ренты с использованием функции ПС

Пример. Отчисления в пенсионный фонд составляют 200 руб. ежемесячно. За какой период на пенсионном счете накопится 10 000 руб., если номинальная процентная ставка составляет 9,8% при ежемесячном начислении (рис. 2)?

Для расчета процентной ставки в схеме сложных процентов используются функции СТАВКА, НОМИНАЛ и ЭФФЕКТ.

Назначение функции СТАВКА - вычисление ставки сложных процентов за период ренты.

Аргументы:

üкпер - общее число периодов ренты 9количество выплат по ренте);

üплт - величина платежа С, неизменная в течение всей ренты;

üпс - начальная вложенная сумма, если аргумент опущен, то он равен нулю;

üтип - 0 или опущен для обыкновенной ренты и 1 для авансированной ренты;

üпредположение - предполагаемая величина ставки, если опущено, то равно 10%.

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

Пример. Кредит в размере $6000 погашается ежемесячными платежами в $100 в течение полутора лет. Найти процентную ставку по кредиту (рис. 3).

Рис. 3. Пример расчета процентной ставки обыкновенной ренты с использованием функции СТАВКА

Номинальная j (при m-кратном начислении процентов в год) и эффективная iэф ставки связаны между собой соотношением

.

Функция ЭФФЕКТ используется для нахождения эффективной ставки по известной номинальной процентной ставке, а функция НОМИНАЛ, наоборот, номинальную ставку находит по известной эффективной ставке.

Пример. С учетом условий предыдущего примера найти эффективную процентную ставку (рис. 4).

Рис. 4. Пример расчета эффективной процентной ставки обыкновенной ренты с использованием функции ЭФФЕКТ (в ячейке А1 находится результат вычисления по функции СТАВКА)

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

Таблица 1

ФункцияНазначениеАргументыПЛТОпределение величины платежа постоянной рентыставка - процентная ставка за период ренты; кпер - количество периодов всей ренты; пс - начальная вложенная сумма, если аргумент опущен, то он равен нулю; бс - будущее значение всех платежей, если аргумент опущен, то он равен нулю; тип - 0 или опущен для обыкновенной ренты и 1 для авансированной рентыОСПЛТНахождение размера платежа для погашения основной части долга за определенный периодТе же, что у функции ПЛТ, но добавляется аргумент «период» - порядковый номер периода, для которого вычисляются платежи (находится в интервале от 1 до кпер)ПРПЛТНахождение размера платежа для погашения процентов по кредиту за определенный периодТе же, что и ПЛТ

Пример.

Кредит в 1 млн руб. выдан на 10 лет под 10% годовых и погашается ежегодными платежами. Каков размер платежа? Какая часть платежа за второй год идет на погашение основного долга?

Решение.

Для нахождения размера ежегодных платежей используем функцию ПЛТ (рис. 5).

Рис. 5. Пример расчета погасительного платежа для возврата с использованием функции ПЛТ

Для нахождения части платежа, которая идет на погашение основного долга, используем функцию ОСПЛТ (рис. 6).

Рис. 6. Пример расчета платежа для возврата основного долга с использованием функции ОСПЛТ

Финансовые функции для расчета амортизации

Расчет амортизационных отчислений на предприятии служит нескольким целям:

üвычисление подлежащей налогообложению прибыли (амортизационные отчисления уменьшают сумму налога);

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

üнакопление собственных средств для инвестиций в в расширение и модернизацию производства;

üопределение балансовой стоимости имущества.

Существуют различные правила для расчета амортизации. при равномерной амортизации стоимость имущества уменьшается равномерно. величина амортизации (Am) вычисляется по формуле:

,

где Sнач - начальная стоимость имущества (фондов);

Sкон - остаточная (ликвидационная) стоимость;

T - срок существования фондов.

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

АПЛ (нач_стоимость; ост_стоимость; время_эксплуатации).

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

Рис. 7. Исходные данные для иллюстрации вычисления амортизации

Рассмотрим правило суммы лет. Поскольку срок существования фондов в нашем примере 10 лет, то составляется сумма 1+2+3+…+9+10=55. Амортизация за первый год вычисляется путем умножения (Sнач -Sкон) на последнее слагаемое суммы, деленное на сумму, то есть . Амортизация за второй год и т.д.

Для расчета амортизации по правилу суммы лет используется функция:

АСЧ (нач_стоимость; ост_стоимость; время_эксплуатации; период).

Аргумент «период» - это порядковый номер периода, за который вычисляются амортизационные отчисления за первые три года, используя две рассмотренные функции (рис. 8).

Рис. 8 а). Вычисление равномерной амортизации с использованием функции АПЛ

Рис. 8 б). Вычисление ускоренной амортизации с использованием функции АСЧ

Таким образом, получаем:

Рис. 9. Результаты вычислений равномерной и ускоренной амортизации

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

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

Расчет амортизации по методу фиксированного процента ведется с помощью функции:

ФОУ (нач_стоимость; ост_стоимость; время_эксплуатации; период; месяц), а по методу двойного процента:

ДДОБ (нач_стоимость; ост_стоимость; время_эксплуатации; период; коэффициент).

Аргумент «месяц» у функции ФОУ - это количество месяцев в первом году (если опущен, то равен 12). Аргумент «коэффициент» у функции ДДОБ - норма снижения балансовой стоимости, если опущен, то равен двум (метод двойного процента) (рис. 10).

Рис. 10 а). Вычисление ускоренной амортизации с использованием функции ФОУ

Рис. 10 б). Вычисление ускоренной амортизации с использованием функции ДДОБ

Таким образом, получаем:

Рис, 11. Результаты вычислений амортизации

Финансовые функции для работы с ценными бумагами

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

Наиболее распространенным видом ценных бумаг с фиксированным доходом являются облигации. Основные параметры облигации: номинальная цена, или номинал; выкупная цена или правило ее определения; дата выпуска; норма доходности, или купонная процентная ставка; даты выплат процентов и дата погашения.

Доходность облигации характеризуется несколькими показателями. Различают купонную, текущую и полную доходность.

Купонная доходность определена при выпуске облигации и является заданным параметром. Текущая доходность характеризует отношение поступлений по купонам к цене приобретенной облигации. Этот параметр не учитывает второй источник дохода - получение номинала или выкупной цены в конце срока. наиболее информативным является показатель полной доходности, который учитывает оба источника дохода.

Пример. Облигация со сроком на 5 лет выпущена 1 января 2003г. Проценты по облигации выплачиваются два раза в год по норме 8%. Номинальная стоимость облигации 1000 руб. Облигация была куплена 30 марта 2003 г. Определить цену облигации на момент покупки, если ожидаемая доходность составляет 12%; накопленный доход к дате покупки и накопленный доход к дате погашения. Определить доходность, если облигация была приобретена по цене 850 руб.

Решение.

Определение текущей цены за 100 руб.

Получаем:

Определение накопленного дохода к дате соглашения через функцию НАКОПДОХОД:

Определение накопленного дохода к погашению через функцию НАКОПДОХОДПОГАШ:

Определение доходности ценных бумаг через функцию ДОХОД:

. Финансовые функции для работы с ценными бумагами

Функции для расчетов по ценным бумагам

ФункцияНазначениеАргументыЦЕНАОпределение текущей цены за 100 руб. номинальной стоимости ценных бумагДата_согл - дата покупки ценных бумаг (облигации); Дата_вступл_в_силу - срок погашения ценных бумаг; Ставка - купонная процентная ставка; Доход - ожидаемая доходность по ценным бумагам; Погашение - выкупная цена ценных бумаг за 100 руб. номинальной стоимости; Частота - количество выплат по купонам в год (может принимать значения 1, 2, 4); Базис - используемое временное правилоНАКОПДОХОДОпределение накопленного дохода к дате соглашенияДата_выпуска - дата выпуска ценных бумаг; Первый_доход - дата первой выплаты по ценным бумагам; Дата_согл - то же, что у функции ЦЕНА; Ставка - то же, что у функции ЦЕНА; Частота - то же, что у функции ЦЕНА; Базис - то же, что у функции ЦЕНАНАКОПДОХОДПОГАШОпределение накопленного дохода к погашениюДата_выпуска - дата выпуска ценных бумаг; Дата_вступления_в_силу - срок погашения ценных бумаг; Ставка - купонная процентная ставка; Номинал - номинальная стоимость ценных бумаг; Базис - то же, что у функции ЦЕНАДОХОДОпределение доходности ценных бумагТе же, что у функции ЦЕНА, добавляется аргумент Цена - цена при покупке ценных бумаг за 100 руб. номинальной стоимости

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

1.Как рассчитать дату и временя с использованием финансовых функций?

2.Каким образом можно рассчитать ипотечную ссуду, годовую процентную ставку в Excel?

.Назовите функции расчета постоянных рент

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

Литература:

1.Информатика для экономистов / Под общ. Ред. В.М. Матюшка. - М.: ИНФРА-М, 2006.

2.Информатика. Базовый курс/ Симонович С.В. и др. - СПб: Издательство «Питер», 2001.

.Информатика / Под ред. проф. Н.В. Макаровой. - М.: Финансы и статистика, 2005.

Финансовые функции MS Excel в экономических расчетах План: 1. Функции даты и времени д

Больше работ по теме: