Главная страница

Лабораторная работа 7 Кредит и депозит Цель работы Совершенствование практических навыков работы в Excel


Скачать 57.33 Kb.
НазваниеЛабораторная работа 7 Кредит и депозит Цель работы Совершенствование практических навыков работы в Excel
Дата24.03.2019
Размер57.33 Kb.
Формат файлаdocx
Имя файлаLaboratornaya_rabota_7.docx
ТипЛабораторная работа
#71375

Лабораторная работа № 7
Кредит и депозит

Цель работы:

  1. Совершенствование практических навыков работы в Excel

  2. Самостоятельная работа с реальными данными.

Задание 1: Кредит

Представим себе, что Вы заняли $1000 под 10% в месяц на 2 года. Предположим, что Ваш бизнес дает Вам 20% прибыли в месяц. Возможны два варианта выплаты: выплата только процентов с выплатой кредита в конце срока и погашение кредита равными долями вместе с процентами

Требуется подсчитать, какой вариант выгоден Вам.
Вариант 1: ежемесячная выплата процентов.
Подготовьте таблицу как показано на Рис.19.



Рисунок 19


  • Внесите в ячейку С1 цифру 1000.

Измените формат данной ячейки:

  • по меню ФОРМАТ, ЯЧЕЙКИ, вкладка ЧИСЛО, ВСЕ ФОРМАТЫ, выберите тип # ##0.00, в строке с наименованием ТИП исправьте содержимое в этой строке на $# ##0,00, ОК. и в ячейке С1 должно появится ($1 000,00)

  • введите в ячейку С2 - 0,1

  • в ячейку СЗ - 0,2

  • задайте ячейкам С2 и СЗ процентный формат,

  • сделайте обрамление.

Начните выплаты со 2-го месяца, и по этому внесите в С5 - 2.

  • Внесите в ячейки формулы:

С6 - =24-С5+1

D7 - =АДРЕС(9+$С$5;2) - в этой ячейке находится ссылка на ячейку, в которой находится сумма денег, с которой Вы начнете платить проценты, в зависимости от месяца начала выплаты в ячейке С5.

С7 - =ДВССЫЛ(D7) - в ячейке находится значение из ячейке, ссылка на которую вычислена в ячейке C5.

А10 - 0

А11 - 1

А12 - 2

В10 - =С1

В11 - =С1+С1*С2+С11 - означает, что в ячейке В11 находится сумма долга, равная сумме, полученного за предыдущий месяц+% на эту сумму за месяц+выплаченные %. Все выплаты отразятся со знаком (-).

С11 - =ЕСЛИ($С$5>А11;0;-$С$7*$С$2) - если текущий месяц А11 меньше, чем месяц начала выплаты процентов, то формула равна нулю, в противном случае вычисляются проценты по формуле - $С$7*$С$2.

D11 - =$С$1+$С$1*$С$3+С11
В12 - =В11+В11*$С$2+С12

С12 - =ЕСЛИ($С$5>А12;0;-$С$7*$С$2)

D12 - =D11+D11$С$3+С12
А13 - 3

В13 - =В12+В12*$С$2+С13

С13 - =ЕСЛИ($С$5>А13;0;-$С$7*$С$2)

D13 - =D12+D12*$С$3+С13

  • Перенесите формат ячейки С1 (т.е. формат доллара) на ячейки В10:D13. для этого установите курсор на ячейке С1, нажмите кнопку на панели инструментов (формат по образцу), после чего курсор примет форму кисточки, выделите этим курсором ячейки В10:D13

Вы уже, наверное, обратили внимание на то, что формулы в строке 12 идентичны строке 13, следовательно, формулы в остальных строках можно просто скопировать

    • Выделите диапазон А13:D13, и ухватив курсором мыши за маркер в правом нижнем углу рамки обрамляющей выделение, отведите мышь в ячейку D34. Ячейки автоматически заполнятся значениями формул для 24 месяцев

  • Выровняйте данные в ячейках А10:D34 по центру.

  • Введите в ячейку C36 слово Прибыль.

  • Введите в ячейку D36 формулу =D34-B34, в результате появится результат $42510,79 прибыли - если начать выплачивать проценты со второго месяца и вернуть кредит в конце срока.

После всего введенного Ваша таблица будет иметь вид показанный на Рис.20.

  • Введите в ячейку С5 цифру 5 (т.е. если начать выплачивать процент за кредит, начиная с 5 месяца) В результате в ячейке D36 получится прибыль $50699,76

  • Измените значение в С5 на 24. Получится $69647,11 прибыли и этого можно сделать вывод, что выгоднее выплатить процент за кредит в конце срока.




Сумма кредита

 

$1 000,00

 

Проценты по кредиту в месяц

 

10%

 

Прибыль в месяц

 

20%

 

 

 

 

 

Начало выплат

 

2

 

Число выплат

 

23,00

 

 

 

1 100,00

$B$11

 

 

 

 

Месяцы

Сумма в конце месяца

Ежемесячные выплаты

Сумма в обороте

0

$1 000,00

 

 

1

$1 100,00

$0,00

$1 200,00

2

$1 100,00

-$110,00

$1 330,00

3

$1 100,00

-$110,00

$1 486,00

4

$1 100,00

-$110,00

$1 673,20

5

$1 100,00

-$110,00

$1 897,84

6

$1 100,00

-$110,00

$2 167,41

7

$1 100,00

-$110,00

$2 490,89

8

$1 100,00

-$110,00

$2 879,07

9

$1 100,00

-$110,00

$3 344,88

10

$1 100,00

-$110,00

$3 903,86

11

$1 100,00

-$110,00

$4 574,63

12

$1 100,00

-$110,00

$5 379,55

13

$1 100,00

-$110,00

$6 345,47

14

$1 100,00

-$110,00

$7 504,56

15

$1 100,00

-$110,00

$8 895,47

16

$1 100,00

-$110,00

$10 564,56

17

$1 100,00

-$110,00

$12 567,48

18

$1 100,00

-$110,00

$14 970,97

19

$1 100,00

-$110,00

$17 855,17

20

$1 100,00

-$110,00

$21 316,20

21

$1 100,00

-$110,00

$25 469,44

22

$1 100,00

-$110,00

$30 453,33

23

$1 100,00

-$110,00

$36 433,99

24

$1 100,00

-$110,00

$43 610,79

 

 

 

 

 

 

Прибыль

$42 510,79


Рисунок 20
Вариант 2: ежемесячное погашение кредита.

Предположим: что кредитор настаивает на ежемесячном погашении кредита и процентов равными долями. Но если можно немного оттянуть срок выплаты, то надо рассчитать, насколько это будет выгодно.

Допустим, договорились с кредитором, что будем выплачивать с пятого месяца.

  • Сделайте копию введенной Вами ранее таблицы на Лист2 для ее редактирования по условиям второго варианта.

Со вторым вариантом работайте на Листе 2.

В столбце Ежемесячные выплаты будем использовать функцию:

= ПЛТ (процентная ставка; число выплат; сумма кредита), а именно, в С11 внесите формулу
=ЕСЛИ($C$5>A11;0;ПЛТ($C$2;C$6;$C$7;0;0))

  • Скопируйте введенную формулу в ячейки С12:С34

Так как из предыдущего варианта в ячейке С5 осталось значение 24, то в результате получилась прибыль равная $69647,11 (такая, как и в предыдущем варианте). Поскольку все выплаты происходят за один раз в конце срока в обоих вариантах. Обратите внимание: в конце 24 месяца долг будет равен нулю.

  • Установите в ячейке С5 значение 5. Получится, что при погашении кредита на пятый месяц, получим прибыль $47391,62, Рис.21.




Сумма кредита

 

$1 000,00

 

Проценты по кредиту в месяц

 

10%

 

Прибыль в месяц

 

20%

 

 

 

 

 

Начало выплат

 

5

 

Число выплат

 

20,00

 

 

 

1 464,10

$B$14

 

 

 

 

Месяцы

Сумма в конце месяца

Ежемесячные выплаты

Сумма в обороте

0

$1 000,00

 

 

1

$1 100,00

$0,00

$1 200,00

2

$1 210,00

$0,00

$1 440,00

3

$1 331,00

$0,00

$1 728,00

4

$1 464,10

$0,00

$2 073,60

5

$1 438,54

-$171,97

$2 316,35

6

$1 410,42

-$171,97

$2 607,64

7

$1 379,49

-$171,97

$2 957,20

8

$1 345,46

-$171,97

$3 376,67

9

$1 308,04

-$171,97

$3 880,03

10

$1 266,87

-$171,97

$4 484,06

11

$1 221,58

-$171,97

$5 208,90

12

$1 171,77

-$171,97

$6 078,71

13

$1 116,97

-$171,97

$7 122,48

14

$1 056,70

-$171,97

$8 375,00

15

$990,39

-$171,97

$9 878,03

16

$917,46

-$171,97

$11 681,66

17

$837,23

-$171,97

$13 846,02

18

$748,99

-$171,97

$16 443,25

19

$651,91

-$171,97

$19 559,93

20

$545,13

-$171,97

$23 299,95

21

$427,67

-$171,97

$27 787,96

22

$298,46

-$171,97

$33 173,58

23

$156,34

-$171,97

$39 636,33

24

$0,00

-$171,97

$47 391,62

 

 

 

 

 

 

Прибыль

$47 391,62


Рисунок 21


  • Проанализируйте результаты вычислений обоих вариантов.

  • Сохраните Ваши труды и покажите их преподавателю.


Задание 2: Депозит

Ситуация!

Через 5 лет Вы хотите приобрести завод стоимостью 100 миллионов рублей. Какую сумму следует положить в банк на депозит под 100% годовых? В конце каждого года Вы хотите забирать 12 миллионов на текущие расходы.

  • Подготовьте таблицу, показанную на Рис.22.




Рисунок 22
В ячейке С1 число -10000000 это приблизительный вклад (с которым далее будем работать), а знак "-" показывает, что деньги Вы не получаете, а отдаете.

  1. установите форматы ячеек: С1, СЗ -финансовый с показом денежных единиц, С2 -процентный.

  2. В ячейку С5 внесите формулу для вычисления будущего значения вклада, а именно, =БС(С2;С4;СЗ;С1;0) - это финансовая формула вычисляет будущее значение вклада в зависимости от процента ставки, количества лет, суммы ежегодных выплат и суммы вклада. Последний ноль в формуле означает, что ежегодные выплаты производятся по окончании года, (если в начале года, то установить нужно 1).

После вычислений в С5 появится результат -52000000р, отрицательный итог показывает, что 10 миллионов недостаточно для выполнения всех условий и даже 52 миллиона Вы должны отдать банку.

  • Осуществим подбор параметра для С5.

  1. активизируйте ячейку С5,

  1. по меню СЕРВИС, ПОДБОР ПАРАМЕТРА, установить в ячейке $С$5, значение 100000000, изменяя значения ячейки $С$1, ОК. После чего появится сообщение "Решение найдено"

После подбора параметра Excel выведет в ячейке С1 значение, которое нужно положить в банк на депозит, чтобы решить все условия

  • Проанализируйте результаты вычислений

  • Сохраните Ваши труды и покажите их преподавателю.



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

    1. Что означает формула =ЕСЛИ($С$5>А13;0;$С$7*$С$2)?

    2. Что означает формула = ПЛТ($C$2;C$6;$C$7;0;0))?

    3. Что означает формула =БС(С2;С4;СЗ;С1 ;0)?

    4. Как копировать формат ячейки в другие ячейки?

    5. Как подбирать параметр?


написать администратору сайта