Практическая часть. Задание 1.Разработать форму для расчёта платежей по сложным процентам по десятигодичному займу

Задание 1. Разработать форму для расчёта платежей по сложным процентам по десятигодичному займу. Построить гистограмму соотношения выплат по процентам и основной суммы на протяжении всего периода выплат.

Для этого выполните действия:

14. Запустите Excel. Сохраните файл под именем «Ваша фамилия31», например, Иванов31.

15. Переименуйте Лист1, дав ему имя Расчет платежей по займу.

16. Создайте таблицу, представленную на рис. 3.1. Введите величину займа 1 500 000 руб. из расчета 22,50% годовых.

Рис. 3.1. Форма для расчёта ежегодного платежа по сложным процентам

Особенности форматирования:

- ячейки A4:G4 и D5:F5 объединены, выравнивание текста – по центру;

- в ячейках B6:E6 и G6 включен режим Перенос текста;

- в ячейке C1 установлен денежный формат;

- в ячейке C2 установлен процентный формат;

- для ячеек B1:B2, A6:G6 и B17:F17 выбрана заливка Белый, Фон 1, более темный оттенок 15% (фон заливки может быть другим).

17. Для переноса величины займа в таблицу введите в ячейку B8 формулу =C1.

18. Рассчитайте размер ежегодного платежа (столбец С). Установите курсор в ячейку С7. Осуществите вызов финансовой функции ПЛТ. Вызвать Мастер функций можно с помощью кнопки Вставить функцию на вкладке Формулы (рис. 3.2) или той же кнопкой в адресной строке (рис. 3.3).

Рис. 3.2. Вкладка Формулы

Рис. 3.3. Вкладка Главная

19. В диалоговое окно для ввода аргументов введите: Ставка – ежегодный процент – C$2 (знак $ означает, что номер строки при копировании формулы будет неизменным); Кпер – 10, так как процент начисляется ежегодно; ПС – сумма займа -C$1 руб. (значение представляем отрицательным числом, так как заем клиента для банка является расходом денежных средств); Бс – будущая стоимость или баланс наличности, который нужно достигнуть после последней выплаты, принимается равной 0, если значение не указано; Тип – логическое значение (0 или 1), обозначающее, должна ли производится выплата в конце периода (0 или отсутствие значения) или в начале периода (1) (рис. 3.4).

Рис. 3.4. Окно Аргументы функции ПЛТ

Таким образом, функция для вычисления ежегодного платежа имеет вид: =ПЛТ(C$2;10;-C$1;0;0).

20. Нажмите кнопку ОК для запуска расчета значения встроенной функции ПЛТ.

21. Скопируется формулу из С7 в ячейки С8:С16. Значение платежа на протяжении всего периода выплат остается неизменным 388 562,22 руб.

22. Рассчитайте размер выплат основной суммы (столбец D). Установите курсор в ячейку D7. Осуществите вызов финансовой функции ОСПЛТ. В диалоговое окно введите значения аргументов (рис. 3.5). При расчете следует обратить внимание на разницу аргументов функции ОСПЛТ: Кпер – общее число периодов выплат (в задании – 10 лет) и Период, для которого нужно определить сумму выплаты (для первого года 1, для второго 2 и т.д).

Рис. 3.5. Окно Аргументы функции ОСПЛТ

23. Нажмите кнопку ОК для запуска расчета значения встроенной функции ОСПЛТ. Скопируется формулы из D7 в ячейки D8:D16.

24. Рассчитайте размер выплат по процентам (столбец E). Установите курсор в ячейку E7. Осуществите вызов финансовой функции ПРПЛТ. В диалоговое окно введите значения аргументов (рис. 3.6).

Рис. 3.6. Окно Аргументы функции ПРПЛТ

25. Нажмите кнопку ОК для запуска расчета значения встроенной функции ПРПЛТ. Скопируется формулы из E7 в ячейки E8:E16.

26. Выполните проверку – сумма выплат по процентам и выплат основной суммы для каждого года (столбец F) составляет ежегодный платеж (столбец C). Для этого в ячейку F7 вставьте =D7+E7 и скопируйте ее в ячейки F8: F16.

27. Для нахождения баланса на конец года (столбец G) найдите разницу баланса на начало года (столбец B) и выплатой основной суммы (столбец D). В ячейку G7 вставьте =B7–D7 (получите значение 1 448 937,78 руб.) и скопируйте ее в ячейки G8:G16.

28. Так как выплата процентов и основной суммы рассчитываются из остатка суммы, то баланс на конец первого года служит балансом на начало второго года и так далее. Для заполнения ячейки баланса на начало второго года в ячейку B8 вставьте формулу = G7 и скопируйте ее в ячейки B9:B16.

29. Введите в ячейку C17 формулу =СУММ(C7:C16). Cкопируйте формулу из ячейки C17 в D17:F17. Результат расчетов представлен на рис. 2.2.7. Платеж по сложным процентам по десятигодичному займу в 1 500 000 руб. из расчета 22,50 % годовых составляет 3 885 622,19 руб., где выплаты основной суммы занимают 1 500 000 руб., а выплаты процентов – 2 385 622,19 руб.

Рис. 3.7. Форма для расчёта ежегодного платежа по сложным процентам

30. Постройте гистограмму по диапазону ячеек D7:E16, которая наглядно отразит соотношение по годам выплат основной суммы и выплат по процентам (рис. 3.8).

Рис. 3.8. Диаграмма соотношения выплат по процентам и основной суммы

Задание 2. Защита элементов листа. Включить защиту от изменения всех ячеек, кроме тех в которые вводятся величина займа и годовая ставка. Скрыть формулы, по которым рассчитываются ежегодные платежи.

Для этого выполните действия:

1. На листе Расчет платежей по займу доступными для изменения должны быть только ячейки C1 и С2. Чтобы разблокировать все ячейки или диапазоны, которые должны быть доступны другим пользователям для изменения, выполните действия:

– выделите мышкой ячейки C1 и С2;

– на вкладке Главная в группе Ячейки нажмите кнопку Формат, а затем выберите команду Формат ячеек (рис. 3.9).

Рис. 3.9. Вкладка Главная / Ячейки / Формат

– на вкладке Защита снимите флажок Защищаемая ячейка, а затем нажмите кнопку ОК (рис. 3.10).

Рис. 3.10. Окно Формат ячеек / Защита

2. Чтобы скрыть все формулы, которые не должны отображаться, выполните действия:

- выделите ячейки C7:F16, содержащие формулы, которые необходимо скрыть;

- на вкладке Главная в группе Ячейки нажмите кнопку Формат, а затем выберите команду Формат ячеек (рис. 3.9). Можно также нажать клавиши [Ctrl]+[Shift]+[F];

- на вкладке Защита установите флажок Скрыть формулы, а затем нажмите кнопку OK (рис. 3.10).

Рис. 3.11. Окно Формат ячеек / Защита

3. На вкладке Рецензирование в группе Изменения нажмите кнопку Защитить лист (рис. 3.12).

Рис. 3.12. Вкладка Рецензирование / Защитить лист

4. В окне диалога Защита листа (рис. 3.13) в списке Разрешить всем пользователям этого листа отметьте флажками элементы, изменение которых должно быть доступно пользователям.

Рис. 3.13. Окно Защита листа

5. В поле Пароль для отключения защиты листа введите пароль для защиты листа, нажмите кнопку ОК, а затем еще раз введите пароль для его подтверждения.

Примечание: пароль задавать необязательно. Однако если не задать пароль, любой пользователь сможет снять защиту с листа и изменить защищенные элементы. Убедитесь, что выбран пароль, который легко запомнить, поскольку если пароль будет утерян, получить доступ к защищенным элементам листа будет невозможно.

6. Проверьте успешность защиты листа. Введите величину займа 2 000 000 руб., а годовую ставку оставьте без изменения. Пересчет выплат произойдет автоматически, таким образом, изменяя величину займа и годовую ставку можно подобрать приемлемый вариант для заемщика (рис. 2.2.14). В строке формул не будут отображаться скрытые формулы, благодаря чему, разработчик может защитить свой интеллектуальный продукт. При попытке редактирования защищенных ячеек будет выводиться сообщение «Ячейка или диаграмма защищена от изменений» (рис. 3.15).

Рис. 3.14. Лист Расчет платежей по займу

Рис. 3.15. Информационное окно Microsoft Excel

Примечание: для снятия защиты с листа нужно на вкладке Рецензирование в группе Изменения выбрать команду Снять защиту листа (рис. 3.16), при необходимости ввести пароль.

Рис. 3.16. Кнопка Снять защиту листа

7. Сохраните книгу с включенной защитой и закройте MS Excel. Представьте файл для проверки преподавателю.

Контрольные вопросы

1. Какие действия необходимо выполнить, чтобы разблокировать все ячейки или диапазоны, которые должны быть доступны пользователям для изменения?

2. Какие действия необходимо выполнить, чтобы скрыть все формулы, которые не должны отображаться?

3. Как установить защиту листа в MS Excel 2010?

4. Как снять защиту листа в MS Excel 2010?

5. Какие действия может выполнять пользователь при установленной защите листа?

Поиск по сайту: