Финансовая математика

Финансовая математика (компьютерный практикум) Версия для печати электронного курса EduArea Этот документ является автоматически сгенерированной печатной версией электронного курса EduArea. В связи с неизбежными ограничениями печатных носителей некоторые элементы этого курса не могут быть воспроизведены корректно. Для полноценной работы с Вам необходимо обратиться к электронной версии курса, доступной по следующему адресу: http://ru.eduarea.com/course/edu1a09m Получив данную печатную версию, Вы со своей стороны обязуетесь: использовать предоставленные Вам ресурсы только лично; не обращаться к помощи третьих лиц или технических средств для улучшения результатов взаимодействий; использовать ресурсы системы исключительно в образовательных целях; соблюдать требования действующего законодательства Вашей страны, в частности, в области защиты авторского права. Авторы и редакторы Документ автоматически сгенерирован Ср, 1 июн 2016 22:56:21 +0400 Подготовлено для двусторонней печати на бумаге формата A4. © EduArea s.?.r.l. & Circumflex 2011 © eduarea.com // Подготовлено для пользователя «140763» 2 Финансовая практикум) математика (компьютерный Оглавление 3 ПРЕДИСЛОВИЕ 4 РЕАЛИЗАЦИЯ ЗАДАЧ НА ПРОСТЫЕ ПРОЦЕНТЫ В EXCEL 5 СЛОЖНЫЕ ПРОЦЕНТЫ В EXCEL 11 КАТЕГОРИЯ «ФИНАНСОВЫЕ» МАСТЕРА ФУНКЦИЙ В EXCEL 13 2011 © eduarea.com // Подготовлено для пользователя «140763» ПРЕДИСЛОВИЕ ПРЕДИСЛОВИЕ Уважаемые студенты! Согласно учебному плану института после изучения курса «Финансовая математика» вам предстоит выполнение компьютерного практикума. В практикуме вам будет предложено изучить возможности отдельных финансовых функций Мастера функций программного пакета MS Excel. В ходе практикума мы будем ссылаться на вопросы теории, рассмотренные в теоретическом курсе «Финансовая математика», и обращаться к ее формулам. 2011 © eduarea.com // Подготовлено для пользователя «140763» 4 РЕАЛИЗАЦИЯ ЗАДАЧ НА ПРОСТЫЕ ПРОЦЕНТЫ В EXCEL РЕАЛИЗАЦИЯ ЗАДАЧ НА ПРОСТЫЕ ПРОЦЕНТЫ В EXCEL Итак, как мы уже выяснили в теоретическом курсе, для начисления процентов по вкладам (депозитам, кредитам) существует два метода – метод простых процентов и метод сложных процентов. Метод простых процентов заключается в том, что проценты начисляются все время на одну и ту же сумму – начальный долг. Формула простых процентов в соответствии с формулой 2.3 теоретического курса имеет вид S=K?(1+i?n) Рассмотрим решение задачи 2.1теоретического курса (часть 1) средствами MS Excel. Содержание задачи: коммерческий банк предлагает открытие счетов под 12% годовых. Вы решили вложить имеющиеся у вас 200 тыс. руб. на 8 месяцев. Определим сумму, которая будет получена вами к концу срока. Для расчетов мы использовали уже отмеченную ранее формулу 2.3. Для вычислений вам необходимо вспомнить основные математические операторы и способы их обозначений в MS Excel. Кроме того, напоминаем, что все вычисления в ячейке Excel начинаются введением в эту ячейку знака равно (=), а затем вводится формула, позволяющая осуществлять вычисления. Как вам уже известно из других курсов, при изучении которых вам приходилось работать с электронными таблицами, интерфейс окна открытой книги Excel будет отличаться в зависимости от версии (в нашем случае будем использовать 2003 и 2007 гг.). Итак, загрузив программный пакет MS Excel, активизируем, например, Лист1 открытой книги. Запишем условие задачи на Листе 1 в виде, представленном на рис. 1. 5 2011 © eduarea.com // Подготовлено для пользователя «140763» РЕАЛИЗАЦИЯ ЗАДАЧ НА ПРОСТЫЕ ПРОЦЕНТЫ В EXCEL ? Рисунок 1. Лист Excel 2003 Если же вы используете Excel 2007, то Лист будет выглядеть так, как представлено на рисунке 2. Рисунок 2. Лист Excel 2007 Завершив ввод формулы нажатием на клавиатуре клавиши Return, получаем 216 000 руб. Обратите внимание, что в ячейках А3:А5 мы поместили условные обозначения данных задачи, а в ячейках В3:В5 мы поместили числовые значения, соответствующие этим обозначениям. Ячейку А6 мы заняли для размещения в ней вопроса задачи – наращенной суммы. Как вы успели заметить, в столбце значений В мы не вводим единицы измерения, мы указываемые только числа безразмерными величинами, в противном случае мы не сможем ссылаться на 2011 © eduarea.com // Подготовлено для пользователя «140763» 6 РЕАЛИЗАЦИЯ ЗАДАЧ НА ПРОСТЫЕ ПРОЦЕНТЫ В EXCEL ячейки В3:В5 для работы с ними в формуле наращенной суммы. Процентную ставку мы сразу представили в виде десятичной дроби, срок мы оставляем в месяцах (об этом мы также сказали в ячейке А4). Ввод формулы в ячейке В6 начинаем знаком равно (=). Далее, кликнув на ячейке В3 (вводим ее содержимое в эту формулу), выставляем с клавиатуры знак умножения (*), открываем скобку и прописываем в ней сумму, как показано на рисунках 1 или 2. Обращаем ваше внимание, что 8 месяцев, которые известны нам по условию задачи, мы переводим в доли года. Делаем мы это непосредственно в самой формуле (8 делим на 12), можно было это сделать в ячейке условия. Умножение на процентную ставку i мы осуществляем так же кликом на ячейку В5, содержащую значение процентной ставки. Следует сказать о преимуществах такого способа расчета наращенной суммы в ячейке В6, когда мы обращаемся к ссылке на ячейки, участвующие в расчете формулы, а не непосредственно подставляем в формулу значения данных задачи. При таком способе записи формулы ее можно уже больше не прописывать, а только изменять условие задачи, т. е. менять значения диапазона В3:В5. И автоматически будет пересчитываться наращенная сумма. Итак, проделаем это. Например, вклад в размере 100 тыс. руб. открывается на полгода в коммерческом банке под 9,5% годовых. Возвращаемся на Лист1 открытой книги и вводим значения, как на рисунке 3. В результате в ячейке В6 будет определена наращенная сумма. ? Рисунок 3. Определение наращенной суммы Далее рассмотрим решение задачи 2.4 первой части. Строительная компания решила приумножить свой начальный капитал в 30 млн. 7 2011 © eduarea.com // Подготовлено для пользователя «140763» РЕАЛИЗАЦИЯ ЗАДАЧ НА ПРОСТЫЕ ПРОЦЕНТЫ В EXCEL руб. Какова будет наращенная сумма компании через 5 месяцев, если банки предлагают следующие ставки: а) ежегодная ставка 30 %; б) ежемесячная ставка 3 %; в) квартальная ставка 5 %. Решение этой задачи выполним средствами пакета Excel, прописав в ячейках формулы, позволяющие вычислить значения наращенной суммы. Формулы для вычислений разобраны в части 1, где рассматривали решение этой задачи вручную. Мы сейчас не рассматриваем теоретический аспект решения этой задачи, т. к. в теоретическом курсе это мы уже сделали. Нас интересует, как технически это реализовать наиболее четко, быстро и компактно. Условие задачи 2.4 мы размещаем на том же первом Листе книги Excel. Следует остановиться на том моменте, где речь идет о представлении расчетов на Листе открытой книги Excel 2003 в режиме отображения формул. Для этого в меню «Сервис» выбираем вкладку «Параметры», в ней «Вид» и в командах «Параметры окна», выставляем флажок на «формулы». Нажимаем кнопку ОК диалогового окна «Параметры», рисунок 4. 2011 © eduarea.com // Подготовлено для пользователя «140763» 8 РЕАЛИЗАЦИЯ ЗАДАЧ НА ПРОСТЫЕ ПРОЦЕНТЫ В EXCEL ? Рисунок 4 Обратите внимание на рисунок 5, где теперь в режиме отображения формул просчитаны значения наращенной суммы при разных типах ставок: ежегодной, ежеквартальной, ежемесячной, условно мы их обозначили S1, S2, S3 . ? Рисунок 5 Чтобы убедиться в правильности решений, достаточно завершить ввод каждой из трех сумм, вычисляемых в ячейках Е8:Е10, нажатием на клавиатуре клавиши ввода (Return). А теперь снова выйдем из режима отображения формул, чтобы не делать скриншоты громоздкими. В ячейках будут отображены значения, как на рисунке 6. Проверьте это. ? Рисунок 6 9 2011 © eduarea.com // Подготовлено для пользователя «140763» РЕАЛИЗАЦИЯ ЗАДАЧ НА ПРОСТЫЕ ПРОЦЕНТЫ В EXCEL Далее рассмотрим реализацию финансовых задач на сложные проценты. 2011 © eduarea.com // Подготовлено для пользователя «140763» 10 СЛОЖНЫЕ ПРОЦЕНТЫ В EXCEL СЛОЖНЫЕ ПРОЦЕНТЫ В EXCEL Задача 1 Ссуда в 20 000 долл. дана на полтора года под 28% годовых с ежеквартальным начислением процентов. Определить сумму конечного платежа. Оформим решение этой задачи так же на первом листе. Поместим условие задачи в ячейки А13:В15, В16 – расчетная ячейка, рисунок 7. Рассуждаем, что в этом случае наращенная сумма будет определяться по формуле 3.1 (часть 1) сложных процентов %%S=K(1+i)^n%%, т. к. срок ссуды полтора года. Записать ввод формулы для определения наращенной суммы для вас уже не составляет сложности. Выясняем, как правильно ввести в формулу аргументы. Так как годовая ставка 28%, а начисление ежеквартальное, то именно потому мы содержимое ячейки В15 делим на 4. Ввиду того, что базовым периодом является квартал, то срок ссуды составляет 6 периодов (в году 4 квартала). Это мы отражаем в показателе степени, т. е. когда ячейку В14 (полтора года) умножаем на 4 (количество кварталов в году), рисунок 7. Рисунок 7 Завершите ввод этой формулы нажатием на клавиатуре клавиши ввода (Return). В ячейках будут отображены значения, как на рисунке 8. 11 2011 © eduarea.com // Подготовлено для пользователя «140763» СЛОЖНЫЕ ПРОЦЕНТЫ В EXCEL Напомним, что в строке формул (белая строка вверху диалогового окна, см. рисунок 7) будет отображаться содержание той формулы, которая введена в выделенную вами ячейку. В случае рисунка 8 такой ячейкой является В16. ? Рисунок 8 Итак, задача нами решена с помощью программного пакета Excel прописыванием формулы в выбранную вами ячейку. Но, как мы уже и говорили в курсе «Экономико-математические методы и модели», этот программный пакет обладает Мастером функций (встроенных функций), позволяющих определять различные финансовые показатели прямо в диалоговом окне этой функции. 2011 © eduarea.com // Подготовлено для пользователя «140763» 12 КАТЕГОРИЯ «ФИНАНСОВЫЕ» МАСТЕРА ФУНКЦИЙ В EXCEL КАТЕГОРИЯ «ФИНАНСОВЫЕ» ФУНКЦИЙ В EXCEL МАСТЕРА Для начала работы с Мастером функций нажмите на кнопку располагается рядом с формульной строкой, рисунок 9 для Excel 2003. , которая ? Рисунок 9 Для офисного пакета 2007 обращение к Мастеру функций также можно осуществить чрез формульную строку, как показано на рисунке 10. ? Рисунок 10 Для офисного пакета 2003 и 2007 к Мастеру функций можно обратиться, зайдя в меню «Вставка» и выбрав в нем команду «Функция». Независимо от способа обращения в появившемся окне Мастера функций 13 2011 © eduarea.com // Подготовлено для пользователя «140763» КАТЕГОРИЯ «ФИНАНСОВЫЕ» МАСТЕРА ФУНКЦИЙ В EXCEL выберите категорию – «Финансовые» и функцию – БС, см. рисунок 11. ? Рисунок 11 Поясним, что позволяет вычислить эта функция. Функция БС рассчитывает будущую стоимость периодических постоянных платежей или будущее значение единой суммы вклада или займа на основании постоянной процентной ставки. Синтаксис (правило ввода данных) функции следующий: БС (Ставка; Кпер; Плт; Пс; Тип). Что означает каждый из пяти предложенных аргументов этой функции? Ставка – процентная ставка за период; Кпер – общее число периодов выплат; Плт – величина постоянных периодических платежей; Пс – общая сумма, которую составят будущие платежи; Тип – число 0 или 1, обозначающее, когда должна производиться выплата (если тип равен 0 или опущен, то оплата или начисление процентов производится в конце периода, если равен 1, то в начале). Итак, после того как вы выбрали функцию БС, перед вами появляется окно под названием «Аргументы функций». В этом окне построчно представлен синтаксис функции. Необходимо ввести нужные данные и произвести вычисления. 2011 © eduarea.com // Подготовлено для пользователя «140763» 14 КАТЕГОРИЯ «ФИНАНСОВЫЕ» МАСТЕРА ФУНКЦИЙ В EXCEL Напомним, что находим мы конечную сумму платежа для ссуды в 20 000 долл., взятой на полтора года под 28% годовых с ежеквартальным начислением. В нашем случае Ставка равна 28%/4, Кпер равен 1,5*4, Плт составляет 0, Пс равняется -20 000 (знак «–» означает, что деньги отдаются банком клиенту), рис. 12. ? Рисунок 12 Как вы уже успели заметить, в этом диалоговом окне уже определено значение суммы конечного платежа. Оно совпадает с тем, которое мы нашли в ячейке В16 рисунка 8 вычислениями по формуле. Но здесь мы сразу ввели тысячи долларов. Нажав кнопку «ОК» этого диалогового окна, найденный результат будет вписан в выделенную для этой функции ячейку. В нашем случае это будет ячейка D13, рисунок 13. 15 2011 © eduarea.com // Подготовлено для пользователя «140763» КАТЕГОРИЯ «ФИНАНСОВЫЕ» МАСТЕРА ФУНКЦИЙ В EXCEL ? Рисунок 13 Далее рассмотрим функцию, которая определяет продолжительность ссуды при заданных современных и будущих значениях и процентной ставке. Задача 2 Определить, за какой срок в годах сумма, равная 75 000 руб., достигнет 200 000 руб. при начислении по сложной процентной ставке 15% раз в году и поквартально. Если обратиться к теории, т. е. теоретическому материалу, то это будет означать, что требуется найти значение показателя степени из формулы %%S=K?(1+i)^n%%. (3.1) Если рассуждать чисто математически, то значение показателя степени мы будем искать последовательно. Вначале выражаем степень, т. е. %%(1+i)^n=\frac{S}{K}.%% Далее берем логарифм по любому основанию от обеих частей равенства (чтобы опустить показатель степени n в качестве сомножителя). В результате получаем выражение в виде: %%n?ln(1+i)=ln(\frac{S}{K})%%. Из этого равенства находим, что %%n=\frac{ln(\frac{S}{K})}{ln(1+i)}%% . Запишем условие этой задачи на том же листе книги Excel. Назовем ее «Задача 2», 2011 © eduarea.com // Подготовлено для пользователя «140763» 16 КАТЕГОРИЯ «ФИНАНСОВЫЕ» МАСТЕРА ФУНКЦИЙ В EXCEL см. рисунок 14. Т. к. мы сейчас ведем речь уже о задачах на сложные проценты, то скроем верхние строки, содержащие задачи на простые проценты (выделив скрываемые строки листа, в меню «Формат» выбираем команду «Строка» ? «Скрыть»). Мы сделали это с целью экономии места в практикуме. ? Рисунок 14 Как вы уже заметили, при записи условий задачи мы пользуемся теми же обозначениями, что и в предыдущих задачах. Для определения срока при ежегодном начислении процентов мы используем символ n%%_e%%, а для обозначения срока при поквартальных начислениях процентов мы используем символ %%n_k%%. Обратите внимание, что мы сразу в ячейке В22 вычислили значение =0,15/4 (т. к. речь идет о начислении процентов 4 раза в год). Далее в ячейке В23 листа книги Excel прописываем формулу в виде, представленном на рисунке 14. Завершите ввод этой формулы нажатием на клавиатуре клавиши ввода (Return). В ячейке будет отображено значение срока в годах, рисунок 15. Для вычисления срока при поквартальных начислениях процентов мы формулу расчетов пропишем в ячейке В24, рисунок 15. 17 2011 © eduarea.com // Подготовлено для пользователя «140763» КАТЕГОРИЯ «ФИНАНСОВЫЕ» МАСТЕРА ФУНКЦИЙ В EXCEL ? Рисунок 15 Как вы считаете, почему мы в этом случае выполняем деление всей большой скобки на число 4? Если этого не сделать, то мы определим количество кварталов, а по условию задания нужно определить срок в годах. Если вы ответили на этот вопрос так, как и мы, тогда все хорошо. Чтобы придать границы вычисленным ячейкам, достаточно их выделить и выбрать в контекстном меню (кликнув правой кнопкой мыши) команду «Формат ячеек», выбрать в этом диалоговом окне команду «Граница». Затем в окне этой команды кликнуть на «Внешние» и «Внутренние» и нажать на кнопку «ОК», рисунок 16. 2011 © eduarea.com // Подготовлено для пользователя «140763» 18 КАТЕГОРИЯ «ФИНАНСОВЫЕ» МАСТЕРА ФУНКЦИЙ В EXCEL ? Рисунок 16 Как вы уже успели заметить, во втором случае срок существенно меньше. В программном продукте Excel для решения задач на определение срока ссуды также есть встроенная функция, которая называется КПЕР. Она также содержится в Мастере функций в категории «Финансовые». Эта функция вычисляет общее число периодов выплат как для единой суммы вклада или займа, так и для периодических постоянных платежей на основе постоянной процентной ставки. Если платежи производятся несколько раз в год, то найденное значение необходимо разделить на число расчетных периодов в году для того, чтобы найти число лет выплат (ведь мы то же самое делали, когда вручную определяли срок вклада при ежеквартальном начислении процентов). Синтаксис Мастера функций имеет вид: КПЕР (Ставка; Плт; Пс; Бс; Тип). Значение Мастера функций КПЕР – это аргумент n формулы составных платежей. Мастер функций КПЕР может применяться в следующих вариантах: 1. Рассчитывается общее число n периодов начисления процентов, необходимых для того, чтобы начальная сумма достигла указанного будущего значения. Тогда синтаксис Мастера функций принимает вид: =КПЕР (Ставка; Пс; Бс). 2. 19 2011 © eduarea.com // Подготовлено для пользователя «140763» КАТЕГОРИЯ «ФИНАНСОВЫЕ» МАСТЕРА ФУНКЦИЙ В EXCEL 2. Рассчитывается общее число n периодов, через которое совокупная величина фиксированных периодических платежей составит указанное значение БС. При этом возможны варианты: а) если платежи производятся в начале каждого расчетного периода. В этом случае синтаксис функции принимает вид: = КПЕР (Ставка; Плт; Бс; 1); б) если платежи производятся в конце каждого расчетного периода. В этом случае синтаксис Мастера функций принимает вид: =КПЕР (Ставка; Плт; Бс). 3. Погашение займа равномерными постоянными периодическими платежами в конце каждого расчетного периода. Тогда число периодов, через которое произойдет полное погашение займа, будет равно: =КПЕР (Ставка; Плт; Пс). Возвращаясь к задаче 2, видим, что нам подходит первый вариант синтаксиса этой функции: КПЕР (Ставка; Пс; Бс). Еще не забывайте о том, где вы хотите отобразить результат работы этой функции. Так как мы будем вычислять срок в годах при ежегодном начислении процентов, то для отображения результаты работы функции КПЕР выберем ячейку С23. Затем, обратившись к Мастеру функций (рисунок 9 или рисунок 10), в категории «Финансовые» выбираем функцию КПЕР, рисунок 17. Свой выбор не забывайте подтверждать нажатием кнопки ОК в этом диалоговом окне. ? Рисунок 17 Далее начинаем вводить аргументы этой функции, рисунок 18. 2011 © eduarea.com // Подготовлено для пользователя «140763» 20 КАТЕГОРИЯ «ФИНАНСОВЫЕ» МАСТЕРА ФУНКЦИЙ В EXCEL ? Рисунок 18 При вводе ставки обязательно указывайте символы процента (%). При заполнении строки ПС указываем знак минус, т. к. деньги отдаются. И еще один момент, на который мы обращаем ваше внимание при заполнении диалогового окна этой функции. Мы не указывали здесь все нули при вводе начальной, т. е. современной и будущей, стоимости – мы сохранили между ними пропорциональность (речь идет о сотнях тысяч в одном и другом случае). И как вы уже заметили, в этом окне результат такой же, как и при расчетах по формуле. Нажатие кнопки ОК в этом окне подтвердит сохранение результата вычислений в ячейке С23. Для ответа на второй вопрос задачи 2, т. е. определение срока платежа при ежеквартальном начислении процентов, мы не сможем сразу все сделать в одной ячейке. Тогда в ячейке С24 мы с помощью функции КПЕР, найдем количество кварталов, рисунок 19. Обращаем ваше внимание на заполнение строки Ставка. Деление в ней 15% на число 4 показывает, что идет ежеквартальное начисление процентов. А если бы проценты начислялись два раза в год, тогда мы выполняли бы деление годовой процентной ставки на число 2. 21 2011 © eduarea.com // Подготовлено для пользователя «140763» КАТЕГОРИЯ «ФИНАНСОВЫЕ» МАСТЕРА ФУНКЦИЙ В EXCEL ? Рисунок 19 Свой выбор не забывайте подтверждать нажатием кнопки ОК в этом диалоговом окне. По условию задания нужно определить срок в годах. Потребуется еще одна ячейка, в которой результат, вычисленный функцией КПЕР, нужно будет разделить на 4, чтобы определить срок платежа в годах. В ячейке С25 пропишем эту очень простую формулу, рисунок 20. 2011 © eduarea.com // Подготовлено для пользователя «140763» 22 КАТЕГОРИЯ «ФИНАНСОВЫЕ» МАСТЕРА ФУНКЦИЙ В EXCEL ? Рисунок 20 Завершите ввод этой формулы нажатием на клавиатуре клавиши ввода (Return). Результат этой формулы будет полностью совпадать с результатом, который мы получили ранее в ячейке В24, см. рисунок 21. ? Рисунок 21 Рассмотрим задачу 4: 23 2011 © eduarea.com // Подготовлено для пользователя «140763» КАТЕГОРИЯ «ФИНАНСОВЫЕ» МАСТЕРА ФУНКЦИЙ В EXCEL выдан кредит 200 000 руб. на два с половиной года. Проценты начисляются раз в полгода. Определить величину процентной ставки за период, если известно, что возврат составит 260 000 руб. Воспользуемся формулой 3.2 первой части: . В нашем случае K=200000 руб., S=260000 руб., m=2 , n=2,5 . Требуется найти j. Снова проведем чисто математические рассуждения: . %%~%%Или . Взяв от обеих частей натуральные логарифмы, получим: Разделив обе части равенства на 5 и воспользовавшись свойством логарифма степени, получим: или . Из равенства логарифмов с одинаковыми основаниями следует равенство их подлогарифмических выражений. На основании этого заключаем, что или , тогда . Теперь задача свелась к вычислению последнего выражения. А для этого введем данные задачи, как показано на рисунке 22. 2011 © eduarea.com // Подготовлено для пользователя «140763» 24 КАТЕГОРИЯ «ФИНАНСОВЫЕ» МАСТЕРА ФУНКЦИЙ В EXCEL ? Рисунок 22 Чтобы не запутаться в своих выводах, еще раз обратимся к вопросу задачи. А он заключался в том, чтобы определить величину процентной ставки за период. Если же мы найдем выражение %%j=2\cdot(1,3^\frac{1}{3}-1)%%, то тем самым мы найдем годовую процентную ставку. А так как проценты начисляются раз в полгода, то ставка за период будет найдена делением величины %%j=2\cdot(1,3^\frac{1}{3}-1)%% на число 2. А это значит, величина процентной ставки за период будет равна %%j=1,3^\frac{1}{3}-1%% . Пропишем формулу для вычисления процентной ставки в ячейке Е22, ее результат представлен на рисунке 23. 25 2011 © eduarea.com // Подготовлено для пользователя «140763» КАТЕГОРИЯ «ФИНАНСОВЫЕ» МАСТЕРА ФУНКЦИЙ В EXCEL ? Рисунок 23 Уважаемые студенты, обращаем ваше внимание на строку формул рисунка 23. В положении выделенной ячейки Е22 (как и показано на рисунке 23) в строке формул отображается та зависимость, которая и привела к результату, указанному ячейкой Е22. Напомним, что мы определяем величину процентной ставки, значит, содержимое ячейки Е22 следует перевести в процентный формат. Сделать это можно разными способами. Один из них с помощью контекстного меню. Выделив ячейку Е22, кликом правой кнопки мыши открываем контекстное меню и выбираем в нем команду «Формат ячеек», а в открывшемся диалоговом окне вкладку «Число». В этой вкладке выбираем «Процентный» формат, рис. 24. По умолчанию нам предлагают 2 десятичных знака, согласимся с этим предложением. 2011 © eduarea.com // Подготовлено для пользователя «140763» 26 КАТЕГОРИЯ «ФИНАНСОВЫЕ» МАСТЕРА ФУНКЦИЙ В EXCEL ? Рисунок 24 Нажатие кнопки «ОК» в этом диалоговом окне отобразит в ячейке Е22 результат, рисунок 25. 27 2011 © eduarea.com // Подготовлено для пользователя «140763» КАТЕГОРИЯ «ФИНАНСОВЫЕ» МАСТЕРА ФУНКЦИЙ В EXCEL ? Рисунок 25 Но т. к. в договорах часто указывается именно годовая ставка, даже если период меньше года, то полученный результат в задаче 4 следует обработать функцией НОМИНАЛ. Эта функция есть в Мастере функций пакета Excel 2007, в версии 2003 г. она отсутствует. Рассмотрим синтаксис функции НОМИНАЛ (фактическая ставка, количество периодов в году). Что делает эта функция? По заданной ставке для периода эта функция определяет эквивалентную годовую ставку. Возвращаясь к задаче 4, определяем эквивалентную годовую ставку. Делаем это очень просто. В ячейке D24 введем символы j год, а вычисление этой величины будем производить в ячейке Е24. Значит, далее переходим в ячейку Е24, обращаемся к Мастеру функций уже любым из известных нам способов. В диалоговом окне выбираем категорию «Финансовые», а в ней функцию «НОМИНАЛ». Для заполнения аргументов функции (а их всего два) поступаем следующим образом: 2011 © eduarea.com // Подготовлено для пользователя «140763» 28 КАТЕГОРИЯ «ФИНАНСОВЫЕ» МАСТЕРА ФУНКЦИЙ В EXCEL в строке Факт_ставка вводим 5,39% (обязательно вводить символ %); в сроке кол-пер вводим 2, т. к. год составляют два полугодия. Нажатие кнопки «ОК» в этом диалоговом окне отобразит в ячейке Е24 результат, рисунок 26. ? Рисунок 26 Самостоятельно переведите содержимое ячейки Е24 в процентный формат. Результат должен совпадать с тем, что представлен на рисунке 26. Обращаем ваше внимание на рисунок 26. Мы обрабатывали функцией НОМИНАЛ задачу №4 в Excel 2007, а результат ее скопировали в ячейку Е24 Excel 2003 г., потому и такой скриншот (рисунок 27). 29 2011 © eduarea.com // Подготовлено для пользователя «140763» КАТЕГОРИЯ «ФИНАНСОВЫЕ» МАСТЕРА ФУНКЦИЙ В EXCEL ? Рисунок 27 С функцией НОМИНАЛ тесно связана функция ЭФФЕКТ, ее синтаксис ЭФФЕКТ (номинальная ставка, количество периодов в году). По заданной годовой ставке эта функция определяет ставку для периода. Чтобы лучше уяснить понятие номинальной и эффективной ставок, рассмотрим следующий любопытный пример. Определить эффективную ставку при номинальной ставке 100% и начислении процентов 10 000 000 раз в год, и при ежедневном начислении. Для этого воспользуемся функцией ЭФФЕКТ, которая также входит в Excel 2007, рисунок 28. 2011 © eduarea.com // Подготовлено для пользователя «140763» 30 КАТЕГОРИЯ «ФИНАНСОВЫЕ» МАСТЕРА ФУНКЦИЙ В EXCEL ? Рисунок 28 Заполнив окно аргументов этой функции как на рисунке 29, получим результат. ? Рисунок 29 Для ежедневного начисления процентов в строке Номинальная_ставка мы вводим 1, т. к. номинальная ставка 100%. Можно было в строке Номинальная_ ставка ввести 100%, результат не будет меняться. В строке Кол_пер введем число 365 (количество дней в году). Если вы уже выполнили расчет с помощью этой функции при начислении процентов 10 000 000 раз в году и при ежедневном начислении, то результаты в разряде сотых долей будут одинаковыми (1,71828169 и 1,71456748), они начнут разниться только с разряда тысячных, рисунки 29 и 30. 31 2011 © eduarea.com // Подготовлено для пользователя «140763» КАТЕГОРИЯ «ФИНАНСОВЫЕ» МАСТЕРА ФУНКЦИЙ В EXCEL ? Рисунок 30 Почти одинаковые ставки для периодов объясняются тем, что номинальная ставка составляет 100% и начисление процентов происходит почти непрерывно. Продолжим рассмотрение финансовых функций. Напомним, что с помощью функции БС мы определяли будущую стоимость, т. е. величину S. А в Мастере функций существует функция, которая решает обратную задачу – вычисляет текущий объем вклада на основе постоянных периодических платежей. Такая функция называется ПС (Excel 2003), рисунок 31. Функция ПС допускает, чтобы платежи производились либо в конце, либо в начале каждого периода. Синтаксис функции ПС выглядит следующим образом: ПС (Ставка; Кпер; Плт; Бс; Тип). Здесь аргумент Бс – будущая стоимость или баланс наличности, который будет достигнут после последней выплаты. Если аргумент Бс опущен, то он полагается равным 0. 2011 © eduarea.com // Подготовлено для пользователя «140763» 32 КАТЕГОРИЯ «ФИНАНСОВЫЕ» МАСТЕРА ФУНКЦИЙ В EXCEL ? Рисунок 31 Рассмотрим задачу 5. Вы планируете приобрести однокомнатную квартиру стоимостью 1 200 000 руб. Есть два варианта покупки: либо внести необходимую сумму сразу, либо в рассрочку. Удастся ли вам совершить покупку квартиры, если будете ежемесячно вносить по 32 000 руб. в течение 5 лет с банковской ставкой 18% годовых. Для ответа на вопрос задачи, вызвав Мастер функций ПС, заполним окно аргументов этой функции. 33 2011 © eduarea.com // Подготовлено для пользователя «140763» КАТЕГОРИЯ «ФИНАНСОВЫЕ» МАСТЕРА ФУНКЦИЙ В EXCEL ? Рисунок 32 На рисунке 32 в строке Ставка вводим 18% (символ % набирать обязательно, деление на 12 ввиду того, что погашение ежемесячное). В строке Кпер указываем общее количество периодов платежей (5 лет умножаем на 12 – число выплат в году). В строке Плт со знаком минус указываем размер ежемесячной выплаты (-32 000, знак минус показывает, что деньги отдаются). Как видно из рисунка 32, при такой ежемесячной оплате и банковском годовом проценте в течение 5 лет вам удастся погасить требуемую сумму. Итак, круг задач, которые мы с вами рассмотрели в практикуме, сводится к определению первоначальной суммы ссуды, наращенной суммы, срока ссуды и процентной ставки. Ранее мы определяли процентную ставку за период расчетным путем, т. е. прописыванием формулы в ячейке по данным задачи (задача 4). Но в курсе «Экономико-математические методы и модели» мы вели речь о функции «СТАВКА», которая позволяет определять размер процентной ставки ссуды. Эта функция также относится к категории «Финансовые», рисунок 33. Напомним эту функцию и ее возможности. Мастер функций СТАВКА определяет значение i процентной ставки за один расчетный период. Для нахождения годовой процентной ставки полученное значение следует умножить на число расчетных периодов, составляющих год. 2011 © eduarea.com // Подготовлено для пользователя «140763» 34 КАТЕГОРИЯ «ФИНАНСОВЫЕ» МАСТЕРА ФУНКЦИЙ В EXCEL Синтаксис Мастера функции СТАВКА выглядит следующим образом: СТАВКА (Кпер; Плт; Пс; Бс; Тип; Предположение). ? Рисунок 33 Функция СТАВКА вычисляется методом последовательного приближения и может не иметь решения или иметь несколько решений. Если после 20 итераций погрешность определения ставки превышает 0,000 000 1, функция СТАВКА возвращает значение ошибки #ЧИСЛО! В этом случае можно попытаться задать другой аргумент «предположение», по умолчанию равный 10%. В большинстве случаев не требуется задавать аргумент предположение. Мастер функций СТАВКА может применяться в следующих вариантах: 1. Известны следующие величины: настоящая стоимость K – (Пс), будущая стоимость S – (Бс), число периодов n – (Кпер). Тогда синтаксис Мастера функций принимает вид: = СТАВКА (Кпер; Пс; Бс). 2. Известны следующие величины: будущая стоимость S – (Бс), число периодов n – (Кпер); периодические платежи R – (Плт). Тогда синтаксис Мастера функций принимает вид: = СТАВКА (Кпер; Плт; Бс; Тип; Предположение). 3. Известны следующие величины: число периодов n – (Кпер); периодические платежи R – (Плт); начальные затраты или размер займа K – (Пс). Тогда синтаксис Мастера функции принимает вид: = СТАВКА (Кпер; Плт; Пс; Предположение). Рассмотрим решение задачи 4 с помощью функции СТАВКА. 35 2011 © eduarea.com // Подготовлено для пользователя «140763» КАТЕГОРИЯ «ФИНАНСОВЫЕ» МАСТЕРА ФУНКЦИЙ В EXCEL В нашем случае количество периодов будет равно 2,5*2 (т. к. 2,5 года с полугодовыми начислениями процентов). Настоящая стоимость 200 000 руб. (берем со знаком минус, т. к. деньги отдаются), будущая стоимость 260 000 руб. Фактически можно говорить, что мы рассматриваем вариант 1. Необходимо отметить, что в условии даны пропорциональные денежные единицы (сотни тысяч), а потому в диалоговом окне ввода аргументов функции СТАВКА мы спокойно вводим 200 и 260, рисунок 34. ? Рисунок 34 В диалоговом окне вычислена величина процентной ставки, которую следует перевести в процентный формат, т. е. умножить на 100%. Таким образом, величина процентной ставки, округленная до разряда сотых, составляет 5,39%. Сравните этот результат с тем, что представлен рисунком 25. Они совпадают. Уважаемые студенты, рассмотрением этой функции мы завершаем практикум. Но обращаем ваше внимание, что в компьютерном практикуме к курсу «Экономико-математические методы и модели» были рассмотрены и другие финансовые функции Мастера функций Excel. Вы можете обратиться к этому практикуму и теперь уже более осознанно посмотреть, как можно реализовывать предлагаемые в нем функции. Более того, каждая из новых версий электронных таблиц Excel дополняет 2011 © eduarea.com // Подготовлено для пользователя «140763» 36 КАТЕГОРИЯ «ФИНАНСОВЫЕ» МАСТЕРА ФУНКЦИЙ В EXCEL категорию «Финансовые» новыми функциями. Важно научиться читать диалоговое окно ввода аргументов функции и при необходимости грамотно пользоваться справочной информацией, т. е. учиться добывать новые знания. В завершение компьютерного практикума желаем вам успехов в его освоении и выполнении заданий контрольного практикума. 37 2011 © eduarea.com // Подготовлено для пользователя «140763» Для заметок Для заметок Для заметок Для заметок Для заметок