Компьютерные технологии MS EXEL
Пример 5. Финансовая функция БЗ
Функция БЗ(БС) вычисляет будущее значение вклада на основе периодических постоянных платежей и постоянной процентной ставки. Функция БЗ(БС) подходит для расчета итогов накоплений при ежемесячных банковских взносах.
Синтаксис:
БЗ(БС) (ставка; кпер; выплата; нз; тип)
Аргументы:
ставка Процентная ставка за период
кпер Общее число периодов выплат
выплата Величина постоянных периодических платежей
нз Текущее значение, т. е. общая сумма, которую составят будущие платежи
тип Число 0 или 1, обозначающее, когда должна производиться выплата. Если тип равен 0 или опущен, то оплата производится в конце периода, если 1 — в начале периода
Пример использования функции БЗ(БС) . Предположим, вы хотите зарезервировать деньги для специального проекта, который будет осуществлен через год. Предположим, вы собираетесь вложить 1000 руб. при годовой ставке 6%. Вы собираетесь вкладывать по 100 руб. в начале каждого месяца в течение года. Сколько денег будет на счете в конце 12 месяцев?
С помощью формулы
=БЗ(б%/12; 12; -100; -1000; 1)
получаем ответ: 2 301.40р.
Провести расчет, когда общее число периодов выплат –годовое.
Варианты заданий упражнения 4
Пример 1. Вычислить n-годичную ипотечную ссуду покупки квартиры за P руб. с годовой ставкой i%и начальным взносом А%. Сделать расчет для ежемесячных и ежегодных выплат.
Варианты n Р i A
7 70000 5 100
8 200000 6 100
9 220000 7 200
10 300000 8 200
11 350000 9 150
7 210000 10 150
8 250000 11 300
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
3 17000 5000 7000 8000
4 20000 6000 6000 9000 7000
5 22000 5000 8000 8000 7000 5000
3 30000 5000 10000 18000
4 35000 5000 9000 10000 18000
5 21000 4000 5000 8000 10000 11000
3 25000 8000 9000 10000
4 31000 9000 10000 10000 15000
5 32000 8000 10000 10000 10000 11000
3 36000 10000 15000 21000
4 20000 6000 6000 9000 7000
5 22000 5000 8000 8000 7000 5000
3 30000 5000 10000 18000
4 35000 5000 9000 10000 18000
5 21000 4000 5000 8000 10000 11000
3 25000 8000 9000 10000
4 31000 9000 10000 10000 15000
5 32000 8000 10000 10000 10000 11000
3 36000 10000 15000 21000
20 3 36000 10000 15000 21000
Пример 3. Вас просят дать в долг Р руб. и обещают возвращать по А руб. в течение п лет. При какой годовой процентной ставке эта сделка имеет смысл?
Вариант п Р А
7 170000 30000
8 200000 31000
9 220000 33000
10 300000 34000
11 350000 41000
7 210000 32000
8 250000 37000
9 310000 40000
9 10 320000 35000
10 11 360000 41000
11 7 170000 30000
12 8 200000 31000
13 9 220000 33000
14 10 300000 34000
15 11 350000 41000
16 7 210000 32000
17 8 250000 37000
18 9 310000 40000
19 10 320000 35000
20 11 360000 41000
Пример 4. Вычислить основные платежи, плату по процентам, общую ежегодную выплату и остаток долга на примере ссуды Р руб. под годовую ставку i'% на срок п лет.
Вариант п Р i
8 200000 6
9 220000 7
10 300000 8
11 350000 9
7 210000 10
8 250000 11
9 310000 12
9 10 320000 13
10 11 360000 14
11 8 200000 6
12 9 220000 7
13 10 300000 8
14 11 350000 9
15 7 210000 10
16 8 250000 11
17 9 310000 12
18 10 320000 13
19 11 360000 14
20 8 250000 11
Задание 5
Создание таблиц заданной структуры на примерах решений практических задач
Пример 1. Конвертирование валюты.
Создать таблицу конвертирования цены товара. При внешнеторговых операциях расчет с поставщиками выполняется в долларах, а расчет с внутренними покупателями - в рублях. Ввиду этого нужно иметь возможность конвертирования в рубли исходной (в момент поступления) и текущей ("на сегодня") цены товара. Вся необходимая информация хранится в двух таблицах: таблице курса доллара и таблице расчета рублевого эквивалента товара.
Рис. Лист Курс
Для удобства пользователя этот материал мы разнесем на два листа рабочей книги – лист Курс и лист Товар
Рис.1 Лист Товар
Содержимое первого (на рисунке изображен только фрагмент листа) очевидно, - это последовательные значения дат и стоимости доллара. На листе Товар в клетке В1 предъявляется текущая дата (функция СЕГОДНЯ()), а также содержатся сведения о дате закупки товара и о закупочной цене (столбцы А и В). В столбце Е вычисляется рублевый эквивалент этой цены, но прежде формируются два столбца, нужные только для сведения оператора. В столбце С предъявляется ближайшая, найденная в листе Курс, дата (СЗ=ВПР (АЗ;Курс!А$2:В$240;1;1)), по курсу которой (столбец D) и производится конвертирование доллара в рубли. Делается это ради того, чтобы пользователь мог контролировать правильность конвертирования. Так, например, если обнаруживается, что дата конвертирования сильно отстает от даты закупки, можно предположить, что курсовая таблица содержит не все данные и ее следует просмотреть и, возможно, дополнить.
В столбце D показывается найденный курс доллара, по которому будет осуществляться конвертирование цены товара DЗ=ВПР(А3; Курс!А$2:В$240;2;1). На основании его и находится закупочная цена товара в рублях E3=D3*B3.
В столбце F определяется рублевый эквивалент цены “товара на сегодня” F3=ВПР(B$1;Курс!A$2:B$240;2;1)).
Рис.2 Лист Товар
В обоих выражениях используется функция ВПР() с четвертым аргументом равным 1, т.е. поиск даты в таблице курса доллара будет не точным, а интервальным, поскольку некоторых дат там нет (валютная биржа не работает в выходные дни) и стоимость доллара тогда берется равной курсу ближайшей предыдущей даты, для которой она имеется. В качестве области поиска определена область листа Курс, содержащая два столбца А и В и число строк, равное числу рабочих дней в году (около 240).
29