Задание 10. Сравнение вкладов

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

Т.к. задача выбора банка и вклада представляет собой многокритериальный выбор, в рамках учебного задания не представляется возможным учесть все «за» и «против». Поэтому в задании взят один вид срочного вклада – «Пенсионный» в шести различных банках. Вклад размещается на 2 года с ежемесячной капитализацией.

Суть задания состоит в сравнении вкладов «Пенсионный» в различных банках по критерию возможности «довложения» в течение срока хранения. Если минимальная сумма пополнения вклада не достигнута пенсионером и пополнение не принято, предполагается, что он все равно сохранит сэкономленные средства дома «в банке».

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

Задание выполняется на листе Сравнение вкладов.

Технология выполнения задания

1. Откройте файл-заготовку.

2. Откройте лист Сравнение вкладов. Цветом выделены ячейки, в которые следует ввести формулы для расчетов.

3. В ячейки С20:С25 введите функцию ЕСЛИ(), анализирующую условие о минимальной сумме первого взноса (ячейка J9), расположенное в ячейках B9:B14 (>=)

Если сумма удовлетворяет условию - вклад оформляется, если нет – вклад 0 (будет храниться в домашней "банке").

4. В ячейку D8 введите формулу подсчета вклада после капитализации процентов в конце 1-ого месяца:

=C20+C20*$C9/12

Смешанная ссылка $C9 позволит при копировании формулы вправо сохранить ссылку на столбец C в таблице с исходными данными, в котором хранится обещанный банком годовой %, а при копировании вниз – брать % для соответствующего банка.

5. Распространите формулу автозаполнением сначала вправо на диапазон D20:I20, затем диапазон D20:I20 вниз на D20: I25.

6. В ячейку J20 введите формулу подсчета возможности пополнения вклада по сроку: если не достигнут срок окончания договора, выставляется 1, иначе - 0 (функция ЕСЛИ()).

Если последний месяц вклада (ячейка I19) меньше 24, то значение надо взять из соответствующей ячейки таблицы исходных данных (столбец D), иначе – 0. Внимательно продумайте, какие надо использовать типы адресов ячеек, с учетом того, что в дальнейшем эти формулы придется копировать в ячейки столбца R. Можно построить формулу так, чтобы не надо было делать никаких исправлений после копирования.

7. Скопируйте формулу вниз, затем подправьте формулу для Сбербанка, у которого пополнение вклада возможно только в 1-ый год:

для ячейки J24 логическое выражение $I$19<12.

8. В столбец К введите формулу подсчета суммы возможного пополнения вклада: если выполняется сложное условие И(пополнение >= минимальной сумме пополнений, установленной данным банком; признак возможности пополнения вклада по сроку =1), то довложение состоится в сумме из ячейки J10, в противном случае накопления перекочуют в домашнюю «банку», т.е. сумма довложения - 0 (функция ЕСЛИ() со сложным условием).

для ячейки К20: =ЕСЛИ(И($J$10>=$E9;J20=1);$J$10;0)

9. В столбце L подсчитайте сумму с учетом пополнения (сумма значений с толбце I и К).

10. Скопируйте формулу ежемесячного прироста вклада из ячейки D20 и вставьте ее в ячейку M20. Распространите формулу автозаполнением сначала вправо до R20 включительно, затем диапазон M20:R20 – вниз на все банки.

11. Скопируйте ячейки J20:L20, формулы в которых рассчитывалось 1-ое довложение, и вставьте копию в ячейки S20:U20, в которых будет рассчитываться 2-ое довложение.

Формулы с абсолютными ссылками следует откорректировать, т.к. сумма довложения ($J$11) другая.

12. Скопируйте формулы диапазона J20:L20 вниз, распространив их на другие банки. Не забудьте исправить условие для Сбербанка (в ячейке S24): 24 месяца на 12.

13. Как это делалось в п. 11, заполните диапазон V19:AG24 суммами вкладов с 13-го по 24-й месяц хранения.

14. В ячейку AH20 введите формулу для суммы, накопленной пенсионером в банке и домашней «банке». Для этого к сумме, находящейся в банке по окончании срока хранения (ячейка AG20) следует добавить разницу между суммой, которую пенсионер намеревался сохранить и суммой, которую принял банк с учетом ограничений: ($J$9+$J$10+$J$11-C20-K20-T20).

Т.о. для ячейки AH20: =AG20+($J$9+$J$10+$J$11-C20-K20-T20) или

=AG20+(+($J$9+$J$10+$J$11)-(C20+K20+T20)

15. Скопируйте формулу в нижестоящие ячейки.

16. В ячейку AI20 введите формулу реального прироста денежных средств пенсионера: отношение разницы между окончательной суммой наличности и тем, что было бы у пенсионера, ни прибегни он к услугам банка, к окончательной сумме. Скопируйте формулу в нижестоящие ячейки. Формат ячеек – процентный.

17. В ячейки AJ20:AJ25 для наглядности сравнения обещанного и полученного прироста перенесите данные из C9:C14 (для AJ20: =C9).

18. В ячейки AK20:AK25 найдите разницу между реальным приростом денежной массы в кошельке пенсионера и приростом, обещанным банком.

19. В ячейку A27 введите текстом выводы по отрицательным значениям последнего столбца – чем он обусловлен.

20. Сохраните файл с выполненным заданием.