Используя функцию “Таблицы подстановки” создать систему для прогнозирования суммы выплаты при вариации процентной ставки.
Задание № 1
Известен размер вклада, который помещен на определенный срок под заданный процент. Вычислить коэффициент наращения и сумму выплат в конце периода.
Функция БЗ (FV) - возвращает будущее значение вклада на основе периодических постоянных платежей и постоянной процентной ставки.
Выполнение работы.
Записываем заголовки столбцов в ячейки А1, А2 и А3. В ячейку В1 записываем размер суммы вклада, в ячейку В2 - срок вклада, в ячейку В3 - процентная ставка, в ячейку В4 - формулу для расчета коэффициента наращения, в ячейку В5 формулу для расчета суммы выплат через 3 года: =БЗ(B3;B2;0;-B1;0)
Коэффициент наращения можно рассчитать так:
= В5/В1-
где в ячейке В1 - исходная сумма, в ячейке В5 - формула =БЗ(B3;B2;0;-B1;0)
Таблица с данными и с формулами:
| Значения: | Формулы: | |||
| Размер вклада | Размер вклада | |||
| Срок вклада | Срок вклада | |||
| Процентная ставка | 8,0% | Процентная ставка | 0,08 | |
| Коэффициент наращения | 1,259712 | Коэффициент наращения | =B5/B1 | |
| Сумма выплаты | 314 928,00грн. | Сумма выплаты | =БЗ(B3;B2;0;-B1;0) |
Таблица вариантов
| № | Размер вклада | Срок вклада | Процентная ставка | № | Размер вклада | Срок вклада | Процентная ставка |
| 9,5 | 8,5 | ||||||
| 8,5 | 7,5 | ||||||
| 7,5 | 6,5 | ||||||
| 6,5 | 5,5 | ||||||
| 7,5 | 6,5 | ||||||
| 8,5 | 7,5 | ||||||
| 9,5 | 8,5 | ||||||
Задание № 2
Используя функцию “Подбор параметра” создать систему для начисления сложных процентов при заданной сумме выплаты
Для построения системы можно использовать функцию ППЛАТ (PMT).
Требуется накопить 250 000 грн., накапливая постоянную сумму каждый месяц, с помощью этой функции можно определить размер откладываемых сумм. Изменяемая ячейка - ячейка с с количеством лет, используем функцию ППЛАТ, чтобы определить при процентной ставке 6% при определенной сумме выплат - в конце какого периода будет итоговая сумма - 250000. За ежемесячные отчисления - возьмем 200 грн.
Для решения данной задачи можно воспользоваться финансовой функцией ППЛАТ (PMT). Создаем таблицу со следующей структурой:
| Размер вклада | Размер вклада | |||
| срок вклада | срок вклада | |||
| Процентная ставка | 6,0% | процентная ставка | 0,06 | |
| Сумма выплаты | 6 355,48грн. | Сумма выплаты | =ППЛАТ(B3/12;B2*12;0;-B1) |
Запускаем программу Подбор параметра через меню Сервис. Изменяемая ячейка - срок вклада, т.е. В2, в ячейке В4 должны получить результат - 200.
Появляется сообщение:
Искомое значение срока вклада - 33 года - при ежемесячном отчислении 200 грн через 33 года на счете будет 250000грн.
Таблица после выполнения программы Подбор параметра:
| Размер вклада | |
| срок вклада | 33,09915784 |
| Процентная ставка | 6,0% |
| Сумма выплаты | 200,00грн. |
Таблица вариантов
| № | Размер вклада | Сумма выплаты | Процентная ставка | № | Размер вклада | Сумма выплаты | Процентная ставка |
| 7,0% | 7,2% | ||||||
| 7,4% | 172,4 | 7,6% | |||||
| 7,8% | 174,8 | 8,0% | |||||
| 8,2% | 177,2 | 8,4% | |||||
| 8,6% | 179,6 | 8,8% | |||||
| 9,0% | 9,2% | ||||||
| 9,4% | 184,4 | 9,6% | |||||
| 9,8% | 186,8 | 10,0% | |||||
| 10,2% | 189,2 | 10,4% | |||||
| 9,8% | 191,6 | 10,0% | |||||
| 9,5% | 9,7% | ||||||
| 9,1% | 196,4 | 9,3% | |||||
| 8,8% | 198,8 | 9,0% | |||||
| 8,4% | 201,2 | 8,6% | |||||
| 8,0% | 203,6 | 8,2% |
Задание № 3
Таблицы подстановки с одной переменной
Используя функцию “Таблицы подстановки” создать систему для прогнозирования суммы выплаты при вариации процентной ставки.
“Таблица подстановки” позволяет вычислять несколько величин сразу, выводит на экран несколько решений, позволяющих увидеть каким образом величины влияют друг на друга при их варьировании.
“Таблица данных” оперирует одной или двумя величинами одновременно.
Создание таблицы подстановки с одной переменной.
1. Следует сформировать таблицу подстановки с одной переменной так, чтобы введенные значения были расположены либо в столбце (ориентированные по столбцу), либо в строке (ориентированные по строке).
2. Формулы, используемые в таблицах подстановки с одной переменной должны ссылаться на ячейку ввода - ячейку, в которую подставляются значения из таблицы данных.
3. В отдельный столбец или в отдельную строку нужно ввести список значений, которые следует подставлять в ячейку ввода.
4. Если значения расположены в столбце, то ввести формулу в ячейку, расположенную на одну строку выше и на одну ячейку правее первого значения. Правее первой формулы ввести любые другие формулы.
5. Если значения расположены в строке, то ввести формулу в ячейку, расположенную на один столбец левее и на одну строку ниже первого значения. В том же столбце, но ниже набрать любые другие формулы.
6. Выделить диапазон ячеек, содержащий формулы и значения подстановки.
7. Выбрать команду Таблицаподстановки в меню Данные.
8. Если таблица подстановки данных ориентирована по столбцам, то ввести ссылку на ячейку ввода в поле Подставлять значения по столбцам в.
9. Если же таблица подстановки данных ориентирована по строкам, то ссылка на ячейку ввода вводится в поле Подставлять значения по строкам в.
Таблицы подстановки можно редактировать. Формулы и переменные таблицы данных можно изменять (такие ячейки расположены в самой верхней строке и в самом левом столбце таблицы данных). Excel рассматривает все вычисляемые величины в таблице данных как массив и не позволяет изменить или удалить часть этого массива. Чтобы очистить все вычисляемые ячейки в таблице данных, следует выделит весь массив целиком, щелчок на правой кнопке мыши и выбрать очистить содержимое.
Выполнение работы
Записываем исходные данные и формулу для расчета суммы выплат, как и в первом задании. Затем создаем таблицу с данными. Для этого пишем заголовки столбцов, затем - в ячейке С2 записываем формулу =В3, в ячейку В8 -формулу =В4, в ячейку С8 - формулу =В5. Для расчета процента от 15% до 20% с шагом 0,5% записываем формулу =A8+0,005 и копируем ее вниз по столбцу, пока не получим значение 20%. Затем выделяем диапазон ячеек A8:c10 щелкаем на пункте меню Данные → Таблица подстановки и в окошке Подставлять значения по строкам в записываем адрес ячейки с процентной ставкой:
После нажатия на ОК получим следующую таблицу:
| А | В | Формулы: | |
| Размер | 850 000грн. | ||
| Срок вклада | |||
| процент. Ставка | 10% | 0,1 | |
| коэффициент наращения | 4,177248169 | =B5/B1 | |
| Сумма выплаты | 3 550 660,94грн. | =БЗ(B3;B2;0;-B1;0) | |
| процент. Ставка | Коэфф | Сумма выплаты | |
| 15,0% | 4,18 | 3 550 660,94грн. | |
| 15,5% | 8,68 | 7 381 569,09грн. | |
| 16,0% | 9,27 | 7 875 692,74грн. | |
| 16,5% | 9,88 | 8 400 551,65грн. | |
| 17,0% | 10,54 | 8 957 913,24грн. | |
| 17,5% | 11,23 | 9 549 638,35грн. | |
| 18,0% | 11,97 | 10 177 685,70грн. | |
| 18,5% | 12,76 | 10 844 116,66грн. | |
| 19,0% | 13,59 | 11 551 100,08грн. | |
| 19,5% | 14,47 | 12 300 917,48грн. | |
| 20,0% | 15,41 | 13 095 968,34грн. |
Таблица вариантов
| № | Размер вклада | Срок вклада | Процентная ставка | № | Размер вклада | Срок вклада | Процентная ставка |
| 10,0% | 9,0% | ||||||
| 9,0% | 8,0% | ||||||
| 8,0% | 7,0% | ||||||
| 7,0% | 6,0% | ||||||
| 6,0% | 5,0% | ||||||
| 5,0% | 4,0% | ||||||
| 4,0% | 3,0% | ||||||
| 5,5% | 4,5% | ||||||
| 7,0% | 6,0% | ||||||
| 8,5% | 7,5% | ||||||
| 10,0% | 9,0% | ||||||
| 11,5% | 10,5% | ||||||
| 10,5% | 9,5% | ||||||
| 9,5% | 8,5% | ||||||
| 8,5% | 7,5% |
Задание № 4