Методические указания по выполнению задания. Для решения задачи сделайте постановку задачи: определите: исходные и результативные данные

Для решения задачи сделайте постановку задачи: определите: исходные и результативные данные. Оформите их на листе «Задание 2» рабочего файла в разных зонах (например, как показано на рис.2 [5]).

Рисунок 2

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

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

1. Для автоматизации составления таблицы в ячейку С5 введите формулу, которая позволит использовать правильную форму слова «год», в зависимости от их количества, при этом целесообразно использовать функцию ЕСЛИ.

2. Для расчета годовой учетной ставки можно использовать процедуру Подбор параметра. Первоначально в ячейку В6 вводится произвольный процент, например 3%. После этого выберите команду Сервис / Подбор параметра и заполните открывшееся диалоговое окно Подбор параметра.

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

=ЧПС (Годовая учетная ставка; ежегодные платежи)

В поле Значение введите 10000 (размер ссуды). В поле Изменяя значение ячейки укажите ссылку на ячейку В6, в которой вычисляется годовая процентная ставка. После нажатия кнопки ОК средства подбора параметров определит, при какой годовой процентной ставке чистый текущий объем вклада равен 10000 руб. Результат вычисления выводится в ячейку В6. В нашем случае годовая процентная ставка равна 11, 79%. Если банки предлагают большую процентную ставку, то предлагаемая сделка не выгодна.

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

Формат:

ЧПС (ставка; 1-е значение; 2-е значение; …)

Аргументы:

ставка Процентная ставка за период

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

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

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

Если n - это количество денежных взносов в списке значений, Р_{J- ЗНАЧЕНИЕ И} i - ставка, то функция ЧПС вычисляется по формуле:

n

S =

j=1

3. Данную задачу можно решить другим способом – используя функцию ВCД. Для этого в ячейку В2 надо ввести 10000р., а в ячейку В6 - функцию ВCД(В1: В4), которая и найдет минимальную годовую учетную ставку.

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

Формат:

ВCД (значения; прогноз)

Аргументы:

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

Прогноз Величина, о которой предполагается, что она близка к результату ВCД.

Если n+1- количество значений в списке, Р_J - J-е значение, то ВCД является корнем относительно i (ставки) следующего уравнения.

n

S =

j=0

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

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

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

4. Проверьте результаты расчета по рисунку 2.

5. Выполните индивидуальное задание.

Индивидуальное задание №2.

Рассчитать при какой процентной ставке выгодно дать в долг Р рублей, если обещают вернуть через год Р1., через два – Р2 руб., через n - Р n руб.

Задание 3

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

Задание.

1. У вас просят в долг 10000руб. Определить, будет ли выгодна эта сделка при годовой ставке 7%, если обещают возвращать

a) по 2000руб. в течение 6 лет?

b) По 1500руб. в течение 12 лет?

c) По 1500руб. в течение 7 лет?

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

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

Методические указанияпо выполнению задания.

Задача 1.

Для решения задачи 1, в первую очередь необходимо сделать постановку задачи. При постановке задачи определите: исходные данные, результаты. Оформите их на листе «Задание 3» рабочего файла в разных зонах (например, как показано на рис.3 ^[6] ).

Рисунок 3. Постановка задачи.

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

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

1. Для автоматизации составления таблицы в ячейку С3 введите формулу, которая позволит использовать правильную форму слова «год», в зависимости от их количества, при этом целесообразно использовать функцию ЕСЛИ.

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

Формат:

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

Аргументы:

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

кпер Общее число периодов выплат.

выплата Величина постоянных периодических платежей[7]

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

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

Если тип = 0 и бз = 0, то функция ПЗ вычисляется по следующей формуле:

А ,

где А - выплата, i- ставка, n- кпер.

3. Для автоматизации составления таблицы в ячейку В8 введите формулу, которая позволит сделать правильный вывод, в зависимости от полученного результата (при В2< В7 – деньги выгоднее положить под проценты, при В2> В7 – выгодно дать в долг, если В2=В7 – варианты равносильны), при этом целесообразно использовать функцию ЕСЛИ.

4. В данной задаче рассматривается ситуация с двумя результирующими функциями: числовой - чистым текущим объемом вклада и качественной, оценивающей, выгодна ли сделка. Эти функции зависят от нескольких параметров, которыми можно управлять, например, сроком и суммой ежегодно возвращаемых денег. Часто бывает удобно проанализировать ситуацию для нескольких возможных вариантов параметров. Команда Сервис, Сценарии предоставляет такую возможность с одновременным автоматизированным составлением отчета. Рассмотрим способ применения этой команды для следующих трех комбинаций срока и суммы ежегодно возвращаемых денег: 6, 2000; 12, 1500 и 7, 1500.

Оформите исходные данные для всех трёх комбинаций срока и суммы, ежегодно возвращаемых денег на этом же листе в столбцах D, E, F. Подготовьте ячейки для результатов.

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

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

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

Аналогично, используя кнопку Добавить последовательно создайте сценарий по каждому условию.

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

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

формат:

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

Аргументы:

ставка Процентная ставка за период.

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

нз Текущее значение, т.е. общая сумма, которую составят будущие платежи[8].

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

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

Если тип =0 и БЗ=0 функция КПЕР вычисляется по следующей формуле:

log_1+i ,

гдн Р - нз, i- ставка, А - выплата.

6. Проверьте результаты расчета по рисунку 3.

Задача 2.

1. Для решения задачи 2 сделайте её постановку, определите: исходные и результативные данные. Оформите их самостоятельно на листе «Задание 3» рабочего файла в разных зонах.

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

формат:

СТАВКА (кпер, выплата, нз, бз, тип, нач_прибл.)

Аргументы:

КПЕР Общее число периодов выплат.

Выплата Величина постоянных периодических платежей.

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

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

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

нач_прибл. Предлагаемая величина ставки. Если нач_прибл опущено, то оно полагается равным 10%. Если функция СТАВКА не сходится, следует попытаться использовать различные значения нач_прибл имеет значение между 0 и 1.

Если БЗ = 0 и тип = 0, функция НОРМА является корнем следующего уравнения.

Р= ;

гдеА - выплата; i- НОРМА; n- кпер, Р- нз.

3. Выполните индивидуальное задание.

Индивидуальное задание 3.

Определить при какой процентной ставке выгодно дать в долг Р рублей, если обещают возвращать по А рублей в течении n лет. Оцените и другие условия возврата денег. Определите количество выплат.