Компьютерные технологии MS EXEL (стр. 3 из 4)
Рис. 4. Диалоговое окно Подбор параметра при расчете годовой процентной ставки
В поле Значение указываем 10000 — размер ссуды. В поле Изменяя значение ячейки даем ссылку на ячейку В7, в которой вычисляется годовая процентная ставка. После нажатия кнопки ОК средство подбора параметров определит, при какой годовой процентной ставке чистый текущий объем вклада равен 10000 руб. Результат вычисления выводится в ячейку В7. В нашем случае годовая учетная ставка равна 11,79%. Вывод: если банки предлагают большую годовую процентную ставку, то предлагаемая сделка не выгодна.
Пример 3 расчета эффективности капиталовложений с помощью функции ПЗ(ПС)
Постановка задачи. Допустим, что у вас просят в долг 10000 руб. и обещают возвращать по 2000 руб. в течение 6 лет. Будет ли выгодна эта сделка при годовой ставке 7%?
На рабочем листе ( рис.5) в ячейку В5 введена формула
=ПЗ(ПС)(В4;В2;-В3)
На рабочем листе введем исходные данные в диапазон A1:B4.
В ячейки введем следующие формулы:
· [B5]=ПЗ(B4;В2;-В3);
· =ЕСЛИ(В2=1;"год";ЕСЛИ(И(В2>=2;В2<=4);"года";"лет"));
· =ЕСЛИ (В1<В5; "Выгодно дать деньги в долг"; ЕСЛИ (В5=В1; "Варианты равносильны"; "Выгоднее деньги положить под проценты")).
·
Рис. 5. Расчет эффективности капиталовложений
Функция ПЗ(ПС)возвращает текущий объем вклада на основе постоянных периодических платежей. Функция ПЗ(ПС) аналогична функции ПЗ(ПС). Основное различие между ними заключается в том, что функция ПЗ(ПС) допускает, чтобы денежные взносы происходили либо в конце, либо в начале периода. Кроме того, в отличие от функции ПЗ(ПС), денежные взносы в функции ПЗ(ПС) должны быть постоянными на весь период инвестиции.
Синтаксис:
ПЗ(ПС) (ставка; кпер; выплата; остаток; тип)
Аргументы:
ставка Процентная ставка за период
кпер Общее число периодов выплат
выплата Величина постоянных периодических платежей
остатокБудущая стоимость или баланс наличности, который нужно достичь после последней выплаты. Если аргумент бз опущен, он полагается равным 0 (например, будущая стоимость займа равна 0)
типЧисло 0 или 1, обозначающее, когда должна производиться выплата. Если тип равен 0 или опущен, то оплата производится в конце периода, если 1 — то в начале периода
В данном разделе была рассмотрена задача с двумя результирующими функциями: числовой — чистым текущим объемом вклада и качественной, оценивающей, выгодна ли сделка. Эти функции зависят от нескольких параметров. Некоторыми из них вы можете управлять, например, сроком и суммой ежегодно возвращаемых денег. Часто бывает удобно проанализировать ситуацию для нескольких возможных вариантов параметров. Команда Сервис, Сценарии предоставляет такую возможность с одновременным автоматизированным составлением отчета. Рассмотрим способ применения этой команды для следующих трех комбинаций срока и суммы ежегодно возвращаемых денег: 6, 2000; 12, 1500 и 7, 1500.
Выберем команду Сервис, Сценарии. В открывшемся диалоговом окне Диспетчер сценариев для создания первого сценария нажмите кнопку Добавить (рис.6).
Рис.6.Диалоговое окно Диспетчер сценариев
В диалоговом окне Добавление сценария в поле Название сценария введите, например ПЗ1, а в поле Изменяемые ячейки — ссылку на ячейки В2 и ВЗ, в которые вводятся значения параметров задачи (срок и сумма ежегодно возвращаемых денег) (рис. 7).
После нажатия кнопки ОК появится диалоговое окно Значения ячеек сценария, в поля которого введите значения параметров для первого сценария (рис.8).
Рис.7.Диалоговое окно Добавление сценария
С помощью кнопки Добавить последовательно создайте нужное число сценариев. После этого диалоговое окно Диспетчер сценариев будет иметь вид, показанный на рис. 9.
Рис.8.Диалоговое окно Значения ячеек сценария
Рис.9.Вывод сценариев на рабочий лист с помощью диалогового окна Диспетчер сценариев
С помощью кнопки Вывести можно вывести результаты, соответствующие выбранному сценарию. Нажатие кнопки Отчет открывает диалоговое окно Отчет по сценарию (рис. 10).
Рис.10.Диалоговое окно Отчет по сценарию
В этом окне в группе Тип отчета необходимо установить переключатель в положение Структура или Сводная таблица , а в поле Ячейки результата дать ссылку на ячейки, где вычисляются значения результирующих функций. После нажатия кнопки ОК создается отчет. На рис. 11 показан отчет по сценариям типа Структура.
Рис.11.Отчет по сценарию типа Структура
Пример 4 Финансовые функции ПЛПРОЦ и СНПЛАТ
Постановка задачи. Вычислить основные платежи, платы по процентам, общей ежегодной платы и остатка долга на примере ссуды 100000 руб. на срок 5 лет при годовой ставке 2% (рис. 12).
Рис. 12. Вычисление основных платежей и платы по процентам
Ежегодная плата вычисляется в ячейке ВЗ по формуле:
В3=ППЛАТ(В1;В2;-В4).
За первый год плата по процентам в ячейке В7 вычисляется по формуле:
В7=D6*0,02.
Основная плата в ячейке С7 вычисляется по формуле:
С7=$B$3-B7.
Остаток долга в ячейке D7 вычисляется по формуле:
=D6-C7
В оставшиеся годы эти платы определяются с помощью протаскивания маркера заполнения выделенного диапазона B7:D7 вниз по столбцам. Отметим, что основную плату и плату по процентам можно было непосредственно найти с помощью функций оснплат (ррмт) и плпроц (ipmt), соответственно.
Функция плпроц возвращает платежи по процентам за данный период на основе периодических постоянных выплат и постоянной процентной ставки.
Синтаксис:
ПЛПРОЦ(ставка; период; клер; нз; бз; тип)
Функция оснплат возвращает величину выплаты за данный период на основе периодических постоянных платежей и постоянной процентной ставки.
Синтаксис:
ОСНПЛАТ(ставка; период; кпер; нз; бз; тип)
Аргументы функций плпроц: и оснплат:
Период Период, за который требуется найти прибыль (должен находиться в интервале от 1 до кпер)
Ставка Процентная ставка за период
кпер Общее число периодов выплат
нз Текущее значение, т. е. общая сумма, которую составят будущие платежи
бз Будущая стоимость или баланс наличности, который нужно достичь после последней выплаты. Если аргумент бз опущен, он полагается равным 0 (например, будущая стоимость займа равна 0)
тип Число 0 или 1, обозначающее, когда должна производиться выплата. Если тип равен 0 или опущен, то оплата производится в конце периода, если 1 — то в начале периода
Пример 5. Финансовая функция БЗ
Функция БЗ(БС) вычисляет будущее значение вклада на основе периодических постоянных платежей и постоянной процентной ставки. Функция БЗ(БС) подходит для расчета итогов накоплений при ежемесячных банковских взносах.
Синтаксис:
БЗ(БС) (ставка; кпер; выплата; нз; тип)
Аргументы:
ставка Процентная ставка за период
кпер Общее число периодов выплат
выплата Величина постоянных периодических платежей
нз Текущее значение, т. е. общая сумма, которую составят будущие платежи
тип Число 0 или 1, обозначающее, когда должна производиться выплата. Если тип равен 0 или опущен, то оплата производится в конце периода, если 1 — в начале периода
Пример использования функции БЗ(БС) . Предположим, вы хотите зарезервировать деньги для специального проекта, который будет осуществлен через год. Предположим, вы собираетесь вложить 1000 руб. при годовой ставке 6%. Вы собираетесь вкладывать по 100 руб. в начале каждого месяца в течение года. Сколько денег будет на счете в конце 12 месяцев?
С помощью формулы
=БЗ(б%/12; 12; -100; -1000; 1)
получаем ответ: 2 301.40р.
Провести расчет, когда общее число периодов выплат –годовое.
Варианты заданий упражнения 4
Пример 1. Вычислить n-годичную ипотечную ссуду покупки квартиры за Pруб. с годовой ставкой i%и начальным взносом А%. Сделать расчет для ежемесячных и ежегодных выплат.
Варианты n Р iA1
7 70000 5 1002 8 200000 6 100
3 9 220000 7 200
4 10 300000 8 200
5 11 350000 9 150
6 7 210000 10 150
7 8 250000 11 300
8 9 310000 12 300
9 10 320000 13 250
10 11 360000 14 250
11 7 300000 8 100
12 8 200000 6 100
13 9 220000 7 200
14 11 300000 8 200
15 10 350000 9 150
16 12 210000 10 150
17 8 250000 11 300
18 7 310000 12 300
19 10 320000 13 250
20 11 360000 14 250
Пример 2. Вас просят дать в долг Р руб. и обещают вернуть Р1руб. через год, P2руб. — через два года и т. д., наконец, Рпруб. — через п лет. При какой годовой процентной ставке эта сделка имеет смысл?
Варианты п Р Р1 Р2 Р3Р4Р5
1 3 17000 5000 7000 8000
2 4 20000 6000 6000 9000 7000
3 5 22000 5000 8000 8000 7000 5000
4 3 30000 5000 10000 18000
5 4 35000 5000 9000 10000 18000
6 5 21000 4000 5000 8000 10000 11000
7 3 25000 8000 9000 10000
8 4 31000 9000 10000 10000 15000
9 5 32000 8000 10000 10000 10000 11000
10 3 36000 10000 15000 21000
11 4 20000 6000 6000 9000 7000
12 5 22000 5000 8000 8000 7000 5000
13 3 30000 5000 10000 18000
14 4 35000 5000 9000 10000 18000
15 5 21000 4000 5000 8000 10000 11000
16 3 25000 8000 9000 10000
17 4 31000 9000 10000 10000 15000
18 5 32000 8000 10000 10000 10000 11000