Лабораторная работа 7-1. Лабораторная работа 7 Кредит и депозит Цель работы Совершенствование практических навыков работы в Excel
 Скачать 57.33 Kb.
|
|
Лабораторная работа № 7 Кредит и депозит Цель работы: Совершенствование практических навыков работы в Excel Самостоятельная работа с реальными данными. Задание 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 прибыли и этого можно сделать вывод, что выгоднее выплатить процент за кредит в конце срока.
Рисунок 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.
Рисунок 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)? Как копировать формат ячейки в другие ячейки? Как подбирать параметр? |