Использование электронных таблиц MS EXCEL для решения экономических задач. Финансовый анализ в Excel (стр. 3 из 3)
Для решения данной задачи можно воспользоваться финансовой функцией ППЛАТ (PMT). Создаем таблицу со следующей структурой:
| Размер вклада | 21500 | Размер вклада | 21500 | |
| срок вклада | 3 | срок вклада | 3 | |
| Процентная ставка | 9,80% | процентная ставка | 0,098 | |
| Сумма выплаты | 516,14 грн. | Сумма выплаты | =ППЛАТ(B3/12;B2*12;0;-B1) |
Запускаем программу Подбор параметра через меню Сервис. Изменяемая ячейка - срок вклада, т.е. В2, в ячейке В4 должны получить результат - 200.
Появляется сообщение:
Искомое значение срока вклада - 6 лет - при ежемесячном отчислении 200 грн через 6 лет на счете будет 21500 грн.
Таблица после выполнения программы Подбор параметра:
| Размер вклада | 21500 |
| срок вклада | 6,4564557 |
| Процентная ставка | 9,8% |
| Сумма выплаты | 200,00 грн. |
Задание № 3. Таблицы подстановки с одной переменной. Вариант 8
| № | Размер вклада | Срок вклада | Процентная ставка |
| 8 | 180800 | 6 | 6,0% |
“Таблица подстановки” позволяет вычислять несколько величин сразу, выводит на экран несколько решений, позволяющих увидеть каким образом величины влияют друг на друга при их варьировании.
“Таблица данных” оперирует одной или двумя величинами одновременно.
Запишем исходные данные и формулу для расчета суммы выплат, как и в первом задании. Затем создаем таблицу с данными. Для этого пишем заголовки столбцов, затем - в ячейке С2 записываем формулу =В3, в ячейку В8 -формулу =В4, в ячейку С8 - формулу =В5. Для расчета процента от 6% до 11% с шагом 0,5% записываем формулу =A8+0,005 и копируем ее вниз по столбцу, пока не получим значение 11%. Затем выделяем диапазон ячеек A8:С10 щелкаем на пункте меню Данные → Таблица подстановки и в окошке Подставлять значения по строкам в записываем адрес ячейки с процентной ставкой:
После нажатия на ОК получим следующую таблицу:
| Размер | 180 800,00 грн. | 180800 |
| Срок вклада | 6 | 6% |
| процент. Ставка | 6% | 0,06 |
| коэффициент наращения | 1,418519112 | =B5/B1 |
| Сумма выплаты | 256 468,26 грн. | =БЗ(B3;B2;0;-B1;0) |
| процент. Ставка | Коэфф. | Сумма выплаты |
| 6,0% | 1,42 | 256 468,26 грн. |
| 6,5% | 1,46 | 263 812,93 грн. |
| 7,0% | 1,50 | 271 332,05 грн. |
| 7,5% | 1,54 | 279 028,92 грн. |
| 8,0% | 1,59 | 286 906,88 грн. |
| 8,5% | 1,63 | 294 969,33 грн. |
| 9,0% | 1,68 | 303 219,70 грн. |
| 9,5% | 1,72 | 311 661,49 грн. |
| 10,0% | 1,77 | 320 298,23 грн. |
| 10,5% | 1,82 | 329 133,50 грн. |
| 11,0% | 1,87 | 338 170,95 грн. |
Задание № 4. Таблицы данных с двумя переменными
Вариант 8
| № | Размер вклада | Срок вклада | Процентная ставка |
| 8 | 152567 | 6 | 6,0% |
Для создания процентных ставок, т.е. для создания таблицы данных для расчета суммы выплат по заданному проценту и заданному сроку вклада нужно по столбцу проставить значения процентов в нужном диапазоне, а по строке - значения сроков вклада - это будут переменные величины, затем на пересечении строки и столбца записать =В5 где в ячейке В5 записана формула для расчета Суммы выплаты:
Выделяем прямоугольную область начиная с формулы расчета суммы выплат – А7:Е18 и щелкаем на пункте меню Данные → таблица подстановки.
Проставляем адреса ячеек и нажимаем ОК.
Получим результат в виде таблицы данных:
| Размер | 152567 | 152567 | ||
| Срок | 6 | 6 | ||
| Процент | 6% | 0,06 | ||
| Коэффициент наращения | 1,42 грн. | =B5/B1 | ||
| Сумма выплаты | 216 419,21 грн. | =БЗ(B3;B2;0;-B1;0) | ||
| 216 419,21 грн. | 2 | 3 | 5 | 7 |
| 6,0% | 171424,3 | 181709,7 | 204169,1 | 229404,4 |
| 6,1% | 171747,9 | 182224,5 | 205133,9 | 230923,6 |
| 6,2% | 172071,8 | 182740,2 | 206102,5 | 232451,4 |
| 6,3% | 172396,0 | 183256,9 | 207074,6 | 233987,9 |
| 6,4% | 172720,5 | 183774,6 | 208050,5 | 235533,1 |
| 6,5% | 173045,3 | 184293,3 | 209030,0 | 237087,1 |
| 6,6% | 173370,4 | 184812,9 | 210013,2 | 238649,8 |
| 6,7% | 173695,9 | 185333,5 | 211000,1 | 240221,3 |
| 6,8% | 174021,6 | 185855,0 | 211990,7 | 241801,7 |
| 6,9% | 174347,6 | 186377,6 | 212985,1 | 243391,0 |