Лабораторная работа № 7
Кредит и депозит
Цель работы:
Совершенствование практических навыков работы в Excel
Самостоятельная работа с реальными данными.
Задание 1: Кредит
Представим себе, что Вы заняли $1000 под 10% в месяц на 2 года. Предположим, что Ваш бизнес дает Вам 20% прибыли в месяц. Возможны два варианта выплаты: выплата только процентов с выплатой кредита в конце срока и погашение кредита равными долями вместе с процентами
Требуется подсчитать, какой вариант выгоден Вам.
Вариант 1: ежемесячная выплата процентов.
Подготовьте таблицу как показано на Рис.19.
Рисунок 19
Внесите в ячейку С1 цифру 1000.
Измените формат данной ячейки:
по меню ФОРМАТ, ЯЧЕЙКИ, вкладка ЧИСЛО, ВСЕ ФОРМАТЫ, выберите тип # ##0.00, в строке с наименованием ТИП исправьте содержимое в этой строке на $# ##0,00, ОК. и в ячейке С1 должно появится ($1 000,00)
введите в ячейку С2 - 0,1
задайте ячейкам С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, СЗ -финансовый с показом денежных единиц, С2 -процентный.
В ячейку С5 внесите формулу для вычисления будущего значения вклада, а именно, =БС(С2;С4;СЗ;С1;0) - это финансовая формула вычисляет будущее значение вклада в зависимости от процента ставки, количества лет, суммы ежегодных выплат и суммы вклада. Последний ноль в формуле означает, что ежегодные выплаты производятся по окончании года, (если в начале года, то установить нужно 1).
После вычислений в С5 появится результат -52000000р, отрицательный итог показывает, что 10 миллионов недостаточно для выполнения всех условий и даже 52 миллиона Вы должны отдать банку.
Осуществим подбор параметра для С5.
активизируйте ячейку С5,
по меню СЕРВИС, ПОДБОР ПАРАМЕТРА, установить в ячейке $С$5, значение 100000000, изменяя значения ячейки $С$1, ОК. После чего появится сообщение "Решение найдено"
После подбора параметра Excel выведет в ячейке С1 значение, которое нужно положить в банк на депозит, чтобы решить все условия
Проанализируйте результаты вычислений
Сохраните Ваши труды и покажите их преподавателю.
Контрольные вопросы.
Что означает формула =ЕСЛИ($С$5>А13;0;$С$7*$С$2)?
Что означает формула = ПЛТ($C$2;C$6;$C$7;0;0))?
Что означает формула =БС(С2;С4;СЗ;С1 ;0)?
Как копировать формат ячейки в другие ячейки?
Как подбирать параметр?
|
Скачать 57.33 Kb.