Лабораторна робота № 9

Автозавершення, авто заповнення комірок, використання стандартних функцій у Excel 2007. Використовування ітогових функцій, розрахунок іпотечного займу

Завдання № 1

Ціль: Освоїти засоби автоматизації уведення даних у послідовні осередки: автозавершення, автозаповнениеня числами, автозаповнення формулами. Використання стандартних функцій.

Для освоєння засобів автозавершення введіть в осередок А1 слово Інститут, в осередок А2 Бухгалтерія, в осередок А3 Фінанси. Після цього в осередок А4уведіть букву І та зверніть увагу, що в осередку А4 з'явилося слово Інститут. Для завершення уведення натисніть клавішу Enter. Аналогічно в осередок А5 уведіть букву Б, в осередку А5 з'явиться слово Бухгалтерія, натисніть клавішу Enter. В осередок А6 уведіть букву Ф й натисніть клавішу Enter.Проаналізуйте результат.

1. В осередок А7 уведіть букву Б,зверніть увагу, що в осередку А7 з'явилося слово Бухгалтерія, не звертаючи увагу на це, продовжуйте набір символів для слова Баланс, після набору натисніть клавішу Enter.В осередокА8уведіть букву І, зверніть увагу, що в осередку з'явиться слово Інститут, не звертаючи на це увагу, продовжуйте набір символів для слова Інтернет, після набору натисніть клавішу Enter. Аналогічно в осередок А9 уведіть слово Факультет. Проаналізуйте, що буде в осередку після набору букви Ф.

2. Для того, щоб перервати режим автозавершення пропустіть один осередок (А10). Далі введіть у послідовні осередки стовпця А слова Інститут, Бухгалтерія, Фінанси. Зверніть увагу, що режим автозавершення не працює. Якщо продовжувати після цього набір слів, які будуть починатися з перших букв уведених слів, то режим знову відновиться. Перевірте це самостійно.

3. Розглянемо Автозаповнення числами послідовних осередків. Автозаполвнення осередків числами може здійснюватися шляхом простого копіювання того самого числа (розглядається як операція «розмноження») або з виконанням операцій арифметичної або геометричної прогресії. Для заповнення осередків шляхом простого копіювання введіть в осередок В1 дату 01.01.2000, в осередок С1 число 9, в осередок D1 грошову суму 200 грн. Виділіть ці осередки. Установіть курсор мишки на чорний хрестик прямокутної рамки, що розташований у нижньому правому куті. Натисніть ліву клавішу мишки й простягніть мишку так, щоб виділити три стовпці (B, C, D) до 34 рядка, а потім відпустіть ліву клавішу мишки. Проаналізуйте отриманий результат.

4. Автозаповнення осередків з виконанням операцій арифметичної або геометричної прогресії може виконуватися трьома способами. Перший спосіб використовують для заповнення осередків арифметичною прогресією. Для цього введіть в осередок Е1 цифру 1 а в осередок Е2 цифру 2. Виділіть ці два осередки, а потім установіть покажчик миші на чорний хрестик виділеної прямокутної рамки. Натисніть ліву клавішу мишки й простягніть мишку так, щоб виділити стовпець до 10 рядка. Проаналізуйте результат.

5. Другий спосіб. Установіть курсор в осередок F1 і введіть у нього цифру 1. Установіть курсор на осередок F1, а потім виконайте команду Прогресія (вкладка Головна, група Редагування, кнопка Заповнити). У діалоговому вікні, що відкрилося, установіть перемикачі Розташування: по стовпцях і Тип:арифметична, Крок прогресії встановіть1, Граничне значення:20. Натисніть кнопку ОК і проаналізуйте результат.

6. Установите курсор в осередок G1 і проробіть аналогічні операції як у пункті 6 тільки встановите перемикач прогресії в положення геометрична. Крок установите 2, граничне значення 20 і натисніть кнопку ОК. Проаналізуйте отриманий результат.

7. Третій спосіб заснований на спеціальному перетаскуванні з використанням правої кнопки миші. Установіть курсор в осередок Н1 і введіть у нього цифру 1. Знову встановіть курсор на осередок Н1, а потім установіть покажчик мишки на чорний хрестик прямокутної рамки й нажавши праву кнопку миші простягніть мишку так, щоб виділити стовпець до 20 рядка. Відпустіть кнопку миші, і в меню, що відкрилося, виберіть командуПрогресія. У діалоговому вікні, що відкрилося, установіть перемикачі: Розташування: постовпцях, Тип:арифметична,Крок: 1, Граничне значення: 18, натисніть кнопкуОК. Проаналізуйте отриманий результат.

8. Установіть курсор в осередок I1 і введіть у нього цифру 1. Проробіть аналогічні дії, як у пункті 8. Тільки встановіть геометричну прогресію із кроком 2. Проаналізуйте результат.

9. Для аналізу автозаповнення формулами введіть в осередок J1 формулу =H1*I1. Далі методом перетаскування осередку J1 спочатку за допомогою лівої клавіші миші, а потім правої перетягніть його в цьому стовпці до 20 рядка. Проаналізуйте результат.

10. Розглянемо використання стандартних функцій. Для цього введіть в осередок К1 значення рівне 0. Установіть знову курсор на осередок К1, а покажчик мишки на маркер цього осередку. Використовуючи праву клавішу миші, простягніть його до 21 рядка. У діалоговому вікні, що відкрилося, установіть параметри арифметичної прогресії по стовпцях, Крок: 0,1, Граничне значення: 2. Натисніть клавішу ОК.

11. Установіть курсор на осередок L1. Натисніть на кнопку зі знаком = у рядку формул, ліворуч у рядку формул відкрийте список з функціями й відшукайте функцію Cos, якщо її немає, виберіть зі списку Інші функції категорію математичні, потім праворуч виберіть необхідну функцію й натисніть кнопку ОК.

12. У палітрі формул, що з'явилася, у полі число введіть аргумент функції: К1*ПІ(). Буква К повинна бути латинською, а ПІ() набрати за допомогою кирилиці й натисніть кнопку ОК.

13. Простягніть осередок з формулою до рядка 21, використовуючи ліву клавішу миші. Проаналізуйте отриманий результат.

Завдання № 2

Ціль: Освоїти засоби форматування документа в Excel: зміна ширини стовпців, об'єднання осередків, керування вирівнюванням тексту, створення рамок осередків. Ознайомитися з використанням засобів попереднього перегляду документа.

1. Двічі клацніть на ярличку другого аркуша й перейменуйте його як Прейскурант.

2. В осередок А1 уведіть текст Прейскурант і натисніть клавішу Enter.

3. В осередок А2 уведіть текст Курс перерахунку й натисніть клавішу Enter. В осередок В2 уведіть текст 1 у.е. = і натисніть клавішу Enter. В осередок С2 уведіть поточний курс перерахунку й натисніть клавішу Enter.

4. В осередок А3уведіть текст Найменування товару й натисніть клавішу Enter. В осередок В3 уведіть текст Ціна (у.е.) і натисніть клавішу Enter. В осередок С3уведіть текст Ціна (грн.) і натисніть Enter.

5. У наступні осередки стовпця А введіть наступні назви товарів, що включені у прейскурант: Комп'ютер Соларис Virtuoso; Комп'ютер Соларис CОЛО; Комп'ютер Соларис СОЛО М; Комп'ютер Соларис PROTEGE, Комп'ютер Соларис WIZARD - A, Комп'ютер Соларис ELITE; Комп'ютер Соларис ELITE - W; Комп'ютер BRANDNAME IBM Aptiva E84; Комп'ютер BRANDNAME HP Vectra VE6/350; Комп'ютер BRANDNAME HP Pavilion 8370; Материнська плата P-II ASUS P2L97A, ATX; Материнська плата P-II ASUS P 2-99; Процесор MMX AMD (2 Ггц); Процесор MMX AMD (3 Ггц); Процесор Pentium III 500 BOX; Вінчестер 100 GB Maxtor 91000D8; Вінчестер 130.0 GB WD AC313000; Пам'ять DIMM 64 Mб; Пам'ять DIMM 128 Мб; Монітор Samsung 510B; Монітор Philips 107S; Принтер Epson Stylus Color 670; Принтер Lexmark 5700; Клавіатура Turbo +; Клавіатура Turbo +PS/2.

6. У відповідні осередки стовпця В уведіть ціни товарів в умовних одиницях (350, 380, 400, 420, 360, 395, 399, 800, 570, 600, 90, 95, 250, 450, 220, 200, 50, 150, 190, 350, 160, 190,12, 13,14).

7. В осередок С4 уведіть формулу: = В4*$С$2, що використовується для перерахування ціни з умовних одиниць у гривні.

8. Методом автозаповнення скопіюйте формули в усі осередки стовпця С, яким відповідають заполонені осередки стовпцівА и В. Чому при такому копіюванні вийдуть вірні формули?

9. Змініть курс перерахування в осередку С2. Зверніть увагу, що всі ціни в гривнях при цьому обновляються автоматично.

10. Виділите методом протягання діапазон А1:С1 і дайте команду Формат осередків (вкладкаГоловна,групаОсередки,кнопкаФормат). На вкладці Вирівнювання задайте вирівнювання по горизонталі: По центру й установите прапорець Об'єднання осередків.

11. На вкладці Шрифт задайте розмір шрифту в 14 пунктів і в списку Накреслення виберіть варіант Напівжирний. Клацніть на кнопці ОК.

12. Клацніть правою кнопкою миші на осередку В2 і виберіть у контекстному меню команду Формат осередків. Задайте вирівнювання по горизонталі: По правому краю й клацніть на кнопці ОК.

13. Клацніть правою кнопкою миші на осередку С2 і виберіть у контекстному меню команду Формат осередків. Задайте вирівнювання по горизонталі: По лівому краюй клацніть на кнопці ОК.

14. Виділите методом протягання діапазон В2:С2. Відкрийте список Границі в групі Шрифт на вкладці Головна й задайте для цих осередків широку зовнішню рамку.

15. Двічі клацніть на границі між заголовками стовпців А і В, В і С, С і D. Зверніть увагу, як при цьому змінюється ширина стовпців А, В, С.

16. Виділите методом протягання весь діапазон осередків, що містять дані. Відкрийте список Границі й задайте для цих осередків широку зовнішню рамку. Аналогічні операції виконаєте для стовпців В і С починаючи відповідно з осередків В3 і С3.

17. Подивитесь, чи влаштовує вас отриманий формат таблиці. Клацніть на кнопці Розмітка сторінки на вкладці Вид, щоб побачити, як документ буде виглядати при печаті.

18. Збережіть робочу книгу в папці з вашим прізвищем за назвою ЛАБ2.xlsx.

Завдання № 3

Ціль: Навчитися використовувати підсумкові функції для обчислення значень, що характеризують набір даних.

1. Двічі клацніть на ярличку нового робочого аркуша й дайте цьому робочому аркушу ім'я Підсумкові функції.

2. Уведіть будь-які значення в осередки діапазону А1:А21.

3. Зробіть поточний осередок А22.

4. Клацніть на кнопці Сума(вкладка Головна, група Редагування).

5. Переконайтеся в тому, що програма автоматично підставила у формулу функцію СУМ і правильно вибрала діапазон осередків для підсумовування. Натисніть клавішу Enter.

6. Зробіть поточним наступний вільний осередок у стовпці А (після натискання клавіші Enter у попередньому пункті цей пункт виконується автоматично).

7. Відкрийте меню кнопки Сума й виберіть функцію Середнє.

8. Зверніть увагу, що автоматично обраний діапазон включає всі осередки із числовим умістом, включаючи й той, котрий містить суму. Виділите правильний діапазон (без суми) методом протягання й натисніть клавішу Enter.

9. Використовуйте порядок дій, описаний у п. 7, обчислите мінімальне число в заданому наборі (функція Мінімум), максимальне число (Максимум), кількість елементів у наборі (Число). Обчислені значення функцій повинні розташовуватися після значення функції Середнє.

10.Збережіть робочу книгу.

Завдання № 4

Ціль: Розрахунок іпотечної позички з використанням фінансової функції ПЛТ.

1. Двічі клацніть на ярличку нового робочого аркуша й дайте цьому робочому аркушу ім'я Іпотека.

2. Розглянемо приклад розрахунку 30-літньої іпотечної позички зі ставкою 8% річних при початковому внеску 20% і щомісячної (щорічної) виплаті за допомогою функції ПЛТ, якщо позика склала 201900 грн. Для розрахунку іпотечної позички введіть текстові пояснення й вихідні дані як на рисунку 1. (Іпотечна позичка - це довгострокові грошові позички під заставу нерухомого майна).

Рис. 1

3. Уведіть формули у відповідні осередки як показано на рисунку 2.

Рис. 2.

Розмір позички - це не що інше, як різниця між позикою й першим внеском, тому в осередок В7 необхідно ввести формулу =В4*(1-В5).В осередок В9 уводиться формула яка обчислює скільки буде місяців за 30 років. В осередок В11 необхідно ввести формулу ПЛТ яка обчислює величину постійної періодичної виплати (ренти) при постійній процентній ставці. Синтаксис функції ПЛТ: ПЛТ (ставка; кпер; пс; бс; тип), де ставка - це процентна ставка за період позики, кпер - загальне число періодів виплат по позиці; пс – приведена (нинішня) вартість, тобто загальна сума на справжній момент, що складуть майбутні платежі (розмір позички). У функціях, пов'язаних з інтервалом виплат, виплачувані вами гроші, такі як депозит, на нагромадження, представляється від'ємним числом; бс – майбутня вартість або баланс готівки, якого потрібно досягти після останньої виплати, якщо аргумент бс опущений, тоді він приймається рівним нулю; тип - число 0 або 1, що позначає, коли повинна проводитися виплата, якщо він 0 то оплата проводиться наприкінці періоду, а якщо 1 то на початку періоду виплати. Якщо бс = 0 і тип = 0, то функція ПЛТ в Excel обчислюється по формулі:

де P - пс, i - ставка (процентна ставка), n - кпер.

Загальна сума виплат визначається в осередкуВ12 по формулі=В11*В9, тобто множиться кількість періодів, у цьому випадку місяців, на місячну виплату. Загальна сума комісійних в осередку В13 визначається як різниця між загальною сумою виплат і розміром позички по формулі = В12-B7. Аналогічні обчислення проводяться для щорічних виплат (розглянути самостійно).

4. Після введення формул ви повинні одержати результат, що показаний на рисунку 3.

Рис. 3.

5. Змініть розмір позики й проаналізуйте отримані результати розрахунку.

6. Збережіть книгу з колишнім ім'ям і доповідять викладачеві.

Лабораторна робота № 10
Фінансові функції MS Office Excel 2007. Підбір параметру..

Завдання № 1

Ціль: Розрахунок ефективності нерівномірних капіталовкладень за допомогою функцій ЧПС для Excel 2007/2010, ВНДОХ і Підбор параметра.

1. Запустити програму MS Excel 2007.

2.
Розглянемо завдання на використання функції ЧПС, що дозволяє визначити чистий поточний обсяг внеску, що обчислюється на основі ряду надходжень наявних сум і норми амортизації. Чистий поточний обсяг внеску - це той внесок, що дорівнює розміру позички при певній дисконтній ставці. І так вас просять позичити 10000 грн. і обіцяють повернути через рік 2000 грн., через два роки - 4000 грн., через три - 7000 грн. При якій процентній ставці ця угода вигідна. Для рішення завдання введіть у таблицю пояснювальний текст і вихідні дані до завдання як показано на малюнку.

3. Уведіть формули у відповідні осередки таблиці, як показано на малюнку нижче. В осередок З6 логічну функцію «ЯКЩО» уведіть від руки за зразком, а в осередок В7 функцію ЧПС уводити через команду Вставка функції(вкладка Функції або нажавши Shift+F3).

Синтаксис функціїЧПС: ЧПС (ставка; 1-е значення: 2-е значення .....29-е значення).Аргументи: ставка - процентна ставка за період, на початку може бути довільної; 1-е значення....29 -е значення - видатки й доходи, повинні бути рівномірно розподілені за часом.

4. Для того щоб довідатися оптимальну річну дисконтну ставку, при якій розмір позички дорівнює розміру внеску, скористаємося методом підбора параметрів. Для цього виконаєте команду Підбор параметра (вкладка Дані, група Робота з Даними, кнопка Аналіз « що-якщо», у меню, що відкрився,
виберітьПідборпараметра) і заповните діалогове вікно, що відкрилося, як показано на рис. вище.

5. Натисніть кнопку ОК. Ви повинні одержати результат як на малюнку.

6. Збільште значення річної дисконтної ставки, наприклад до 15% або більше. Ви побачите, що чистий обсяг внеску зменшується (чистий поточний обсяг внеску - це та сума грошей, що вам необхідно вкласти в початковий рік, щоб дістати прибуток залежно від річних відсотків, у нашім випадку чистий поточний обсяг внеску дорівнює розміру позички). Це значить, що якщо банки пропонують більшу річну процентну ставку, те пропонована угода не вигідна.

В Excelчистий поточний обсяг внеску обчислюється по формулі:

,

де n-це кількість грошових сум, P_j - це j - е значення сум грошей, що повертаються, i - це процентна річна ставка.

6. Річну дисконтну ставку можна одержати й за допомогою функції ВСД. Ця функція повертає внутреннею ставку прибутковості для ряду потоків коштів, представлених їхніми числовими значеннями.

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

Функція ВСД пов'язана зі ЧПС - це швидкість обороту, для якої ЧПС рівняється нулю. Синтаксис функції ВСД: ВСД(значення; прогноз),де значення - це масив або посилання на осередки, що містять числа, для яких обчислюється внутрішня ставка прибутковості, прогноз - це точність обчислень (якщо параметр опущений те він дорівнює 0.1). На аркуші 2 уведіть пояснювальний текст, дані й формули, як показано на малюнку.

8. Після введення формул ви повинні одержати результат як на малюнку. Зверніть увагу, що при використанні функції ВСД розмір позички треба вводити зі знаком -.

9. Перейменуйте Аркуш2 на ЧПСВСД. Збережете книгу під ім'ям ЧПСВСД як шаблон у папці з вашим прізвищем.

Завдання № 2

Ціль: Розрахунок ефективності капіталовкладень за допомогою функції ПС вExcel 2007/2010).

1. Запустити програму Excel.

Розглянемо завдання на використання функції ПС, що дозволяє визначити поточну вартість внеску. Функція ПС аналогічна функції ЧПС. Основне розходження між ними укладається в тім, що функція ПС допускає, щоб грошові внески відбувалися або наприкінці, або на початку періоду (функція ЧПС вимагає уведення наприкінці кожного періоду). Крім того, на відміну від функції ЧПС, грошові внески у функції ПС повинні бути постійними на весь період інвестиції.

Синтаксис функції ПС:

ПС (ставка; кпер; плт; бс; тип)

Аргументи:

ставка-Процентна ставка за період

кпер- Загальне число періодів виплат

плт - Величина постійних періодичних платежів

бс- необхідне значення майбутньої вартості або залишку коштів після останньої виплати. Якщо аргумент бс опущений, він покладається рівним 0 (наприклад, майбутня вартість позики дорівнює 0).

тип - Число 0 або 1, що позначає, коли повинна провадитися виплата. Якщо тип дорівнює 0 або опущений, то оплата провадиться наприкінці періоду, якщо 1 - те на початку періоду.

Якщо тип = 0 і бс = 0, то функція ПЗ обчислюється по наступній формулі

де A - виплата,i - ставка, n - кпер.

Розглянемо таке завдання. Допустимо, що у вас просять у борг 10000 грн. і обіцяють повертати по 2000 грн. протягом 6 років. Чи буде вигідна ця угода при річній ставці 7 %.

2. Уведіть дані в осередки й пояснювальний текст до них, як показано на малюнку 1. В осередок В5 уведіть формулу =ПС(В4;В2;-В3).Крім того, для автоматизації складання таблиці в осередки З2 і В6 уведіть відповідно формули, які являють собою логічні функції:

=ЯКЩО (В2=1;”рік”; ЯКЩО(И(В2>=2;B2<=4);”року”; ”років”))=ЯКЩО (В1<B5;”Вигідно дати гроші в борг”; ЯКЩО(В5=В1;”Варіанти рівнозначні”; ”Вигідніше гроші покласти під відсотки”))

Рисунок 1-1- Розрахунок ефективності капіталовкладень

3. У даному завданні було розглянуте завдання із двома результуючими функціями: числовий - чистим поточним обсягом внеску і якісної, оцінюючої, чи вигідна угода. Ці функції залежать від декількох параметрів. Деякими з них ви можете управляти, наприклад, строком і сумою грошей, що повертаються щорічно. Часто буває зручно проаналізувати ситуація для декількох можливих варіантів параметрів. Команда Диспетчер сценаріїв (вкладка Дані, група Робота з Даними, кнопка Аналіз « що-якщо», у меню, що відкрився, виберіть Диспетчер сценаріїв) надає таку можливість із одночасним автоматизованим складанням звіту. Розглянемо спосіб застосування цієї команди для наступних трьох комбінацій строку й суми грошей, що повертаються щорічно: 6, 2000; 12,1500і 7,1500.

Для цього виберіть команду Диспетчер сценаріїв навкладці Дані-Робота з дан-аналіз « Що-Якщо»- Диспетчер сценаріїв. У діалоговому вікні, що відкрилося, Диспетчер сценаріїв (див. рис.) для створення першого сценарію натисніть кнопку Додати.

4. У діалоговому вікні, що з'явилося, Додавання сценарію (див.мал.) у поле Назви сценарію введіть ПС 1, а в поле Змінювані осередки посилання на осередок В2 і В3, у які вводяться значення параметрів завдання.

5. Натисніть кнопку ОК, на екрані з'явиться діалогове вікно Значення осередків сценарію, у поле якого введіть значення параметрів (6, 2000) для першого сценарію (див.рис.).

6. За допомогою кнопки Додати послідовно створіть потрібне число сценаріїв ПС 2для параметрів12, 1500 і ПС 3 для 7, 1500.

7. Після уведення останнього сценарію натисніть кнопку ОК і на екрані ви побачите діалогове вікно Диспетчер сценаріїв як на малюнку.

8. У вікні Диспетчер сценаріїв клацніть мишкою по ПС1 а потім по кнопці Вивести й переглянете зміни які будуть в осередках В2, В3, В5 і В6, у створеній вами таблиці. Проробіть аналогічні операції для ПС2 і ПС3.

9. Після перегляду таблиці виведіть Звіт. Для цього в діалоговому вікні Диспетчер сценаріїв клацніть на кнопці Звіт. Натискання кнопки Звіт відкриває діалогове вікно Звіт по сценарії. У цьому вікні в групі Тип звіту (мал.) необхідно встановити перемикач у положення Структура,

а в поле Осередку результату дати посилання на осередки (В5:В6), де обчислюються значення результуючих функцій. Після натискання ОК створюється звіт як на малюнку нижче

10. Відформатуйте осередки D10, E10, F10, G10 як на малюнку вище.

Завдання № 3

Ціль: Освоїти розрахунок плати по відсотках, основної плати з використанням функцій ПРПЛТ, ОСПЛТ,а також залишку боргу, при виплаті позички.

1. Запустити програму Excel.

Розглянемо завдання обчислення основних платежів, плати по відсотках, загальної щорічної плати й залишку боргуна прикладі позички 100000 грн. на строк 5 років при річній ставці 2%. Уведіть вихідні дані, і текст як показано на рис.

Привласніть осередкам В1, В2, В3, В4 імена: відсоток, строк, щорічна_плата, розмір_позички. Присвоєння ім'я кожному осередку здійснюється за допомогою команди Ім'я Діапазонув контексном меню (на осередку права кнопка миші).

2.
В осередок В3 уведіть формулу обчислення щорічної плати. Формула являє собою функцію ПЛТ:=ПЛТ (відсоток; строк; -розмір_позички). Після введення формули ви повинні одержати результат як на малюнку.

3. Додайте таблицю даними як показано на малюнку.

4. В осередок В7 уведіть формулу =D6*відсоток, що дозволяє обчислити плату по відсотках.

5. В осередок З7 уведіть формулу обчислення основної плати =щорічна плата - В7.

6. В осередок D7 уведіть формулу =D6 - C7, що обчислює залишок боргу.

7. В осередки B7, C7, D7 уведені формули для обчислення плати по відсотках, основної плати, залишку боргу за один рік. Для лет, що залишилися, ці плати визначаються протаскивания маркера заповнення діапазону B7:D7 долілиць по стовпцях. У результаті ви повинні одержати розрахунок плат за роки, що залишилися (мал.).

Відзначимо, що основну плату й плату по відсотках можна було безпосередньо знайти за допомогою функцій ОСПЛТ і ПРПЛТ відповідно. Функція ПРПЛТ повертає платежі по відсотках за даний період на основі періодичних постійних виплат і постійної процентної ставки.

Синтаксис: ПРПЛТ (ставка; період; кпер; нз; бз; тип)

Функція ОСПЛТ повертає величину основної виплати за даний період на основі періодичних постійних платежів і постійної процентної савке. Синтаксис: ОСПЛТ (ставка; період; кпер; нз; бз; тип)

Аргументи функцій ПРПЛТ і ОСПЛТ:

ставка -Процентна ставка за період

період - Період за який потрібно знайти прибуток, за рік, два й т.д. (перебуває в інтервалі від 1 до кпер)

кпер - Загальне число періодів

нз- Загальна сума, що складуть майбутні платежі

бз- Майбутня вартість або баланс готівки, якому потрібно досягти після останньої виплати. Якщо аргумент бз опущений, він покладається рівним 0 (наприклад, майбутня вартість позики дорівнює 0).

тип - Число 0 або 1, що позначає, коли повинна провадитися виплата. Якщо тип дорівнює 0 або опущений, то оплата провадиться наприкінці періоду, якщо 1, то на початку періоду.

8. Тепер уведіть в осередок F5 текст: Плата по відсотках по формулі ПРПЛТ, в осередок G5 текст: Основна плата по формулі ОСПЛТ.

Уведіть функцію =ПРПЛТ (Відсоток;A7;Строк;-$D$6) в осередок F7.

В осередок G7 уведіть функцію =ОСПЛТ (Відсоток;A7;Строк;-$D$6).

9. Виділите осередку F7, G7і простягніть їх долілиць до 11 рядка.У результаті ви повинні побачити розрахунки, як показано на малюнку 5.Відформатуйте таблицю відповідно до малюнка.

Завдання № 4

Ціль: Вивчити фінансові функції БС, КПЕР, СТАВКА), що обчислюють майбутнє значення внеску, загальне кількість періодів виплати й процентну ставку за один період.

1. Функція БСповертає (обчислює) майбутнє значення внеску на основі постійних платежів і постійній процентній ставці.

Синтаксис функції: БС(Ставка; Кпер; Плата; Нз; Тип)

Ставка - це процентна ставка за період (місяць, рік).

Кпер - це загальне число періодів платежів.

Плата - це виплата, вироблена в кожний період. Плата складається з основного платежу й платежу по відсотках.

Нз - це загальна сума всіх майбутніх платежів, якщо його ні, те він дорівнює нулю.

Тип - це число 0 або 1 що позначає, коли повинна провадиться виплата для нагромадження. Якщо число 0, наприкінці періоду, 1 на початку наступного періоду.

Виплати, що означають гроші, які ви платите (наприклад, депозитні внески), представляються негативними числами. Гроші, які Ви одержуєте (наприклад, дивіденди), представляються позитивними числами.

Якщо Тип = 0 і Нз = 0, то функція БС обчислюється по формулі:

,

де A - плата, i - ставка, n - кпер. Якщо Тип =1 тоді

Припустимо, ви збираєтеся вкласти 1000 грн. під 6% річних, щоб зарезервувати гроші для спеціального проекту. Ви також збираєтеся вкладати по 100 грн. на початку кожного наступного місяця протягом наступних 12 місяців. Визначити, скільки грошей буде на рахунку на початку наступного року.

Уведіть у будь-який осередок функцію БС, наприклад в осередок В2, =БС(0.06/12;12;-100;0;1),що враховує тільки внески по 100 грн. щомісяця. Після натискання клавіші Enter ви одержите, скільки грошей буде на рахунку, коли ви будете вкладати гроші по 100 грн. щомісяця. Ця сума дорівнює 1 239.7240 . Доведіть, що функція обчислює суму грошей за 12 місяців вірно.

Якщо враховувати й 1000 грн. тоді функцію БС уведіть в осередок В3 у такому виді: =БС(0.06/12;12;-100;-1000;1).Після натискання клавіші Enter відповідь буде таким: 2301.40.

2. Розглянемо функцію КПЕР, що обчислює загальну кількість періодів виплати для даного внеску на основі періодичних постійних виплат і постійної процентної ставки.

Синтаксис: КПЕР(Ставка; Виплата; Нс; Бс; Тип)

Ставка -це процентна ставка за період.

Виплата - це величина постійних періодичних платежів. Звичайно платіж складається з основного платежу й платежу по відсотках, ніякі інші збори й податки не враховуються.

Нс - це поточна вартість позики, тобто це загальна сума, що складуть майбутні платежі зі справжнього моменту.

Бс - це майбутня вартість позики або баланс готівки, якому потрібно досягти після останньої виплати. Якщо цей параметр опущений, тоді покладається, що він дорівнює нулю.

Тип - це число 0 або 1. Нуль указує на те,. що оплата провадиться наприкінці періоду, 1 на початку періоду.

Якщо Тип = 0 і Бз = 0 тоді функція КПЕР обчислюється по наступній формулі

,

де P -нз, i - ставка, A - виплата.

Наприклад, якщо ви позичаєте 1000 грн. при річній ставці 1% і збираєтеся виплачувати по 100 грн. у рік, то число виплат обчислюється в такий спосіб:

=КПЕР(1%; -100; 1000)

У результаті одержуємо відповідь: 11.

Уведіть цю функцію в осередок В4 і перевірте результат.

У наступні осередки введіть функції й перевірте результат.

=КПЕР(1%;-100; - 1000; 10000), результат 60.

=КПЕР(1%;-100; - 1000; 10000;1), результат 60.

3. Розглянемо функцію СТАВКА. Функція СТАВКА обчислює процентну ставку за один період, необхідну для одержання певної суми протягом заданого строку шляхом постійних внесків. Слід зазначити, що функція СТАВКА обчислює процентну ставку методом ітерацій, тому рішення може бути й не знайдено. Якщо посол 20 ітераці погрішніст визначенн процентн ставк перевищува 0,0000001, то функці СТАВК поверта значенн помилк #ЧИСЛО!

Синтаксис: СТАВКА (Кпер; Виплата; Нс; Бс; Тип; Нач. прибл)

Кпер -загальне число періодів позики (ренти).

Виплата -величина постійних періодичних платежів по позиці.

Нс -загальна сума, що складуть майбутні платежі.

Бс -баланс готівки (майбутня вартість позики), якому потрібно досягти після останньої виплати. Якщо його ні, тоді він дорівнює нулю.

Тип -число 0 або 1 указывающее кінець періоду або початок періоду виплат.

Нач. прибл -це передбачувана величина норми наближення. Якщо цей параметр опущений, то покладається, що він дорівнює 10%. Якщо функція СТАВКА не сходиться, варто спробувати використовувати різні значення Нач. приб. Звичайно функція СТАВКА сходиться, якщо Нач. прибл має значення між 0 і 1.

Якщо Бс = 0 і Тип = 0, функція СТАВКА є коренем наступного рівняння ,

де A - виплата, i - норма (процентна ставка), n - Кпер, P-P- Нз.

Розглянемо приклад використання функції СТАВКА.

Наприклад, щоб визначити процентну ставку для чотирирічної позики розміром в 8000 грн. із щомісячною виплатою 200 грн., можна використовувати функцію =СТАВКА(48; -200; 8000).У результаті одержуємо: місячна процентна ставка 0,77%. Річна процентна ставка складе 0,77%*12=9,24 %.

Уведіть у наступний осередок функцію =СТАВКА(48; -200; 8000), і переконаєтеся в цьому самі.

Лабораторна робота №11
Надбудови у MS Office Excel 2007. Використання команди Поиск решения для рішення задач оптимізації. Складання звітних відомостей

Завдання № 1

Ціль: Вивчити функції для розрахунку амортизації АПЛ, АСЧ, ФУО, ДДОБ.

1. Функція АПЛ повертає величину амортизації майна за кожний період часу, використовуючи метод рівномірної амортизації.

Під амортизацією мається на увазі зменшення (звичайно - на одиницю часу) вартості майна в процесі експлуатації.

Синтаксис функції: АПЛ (нач. вартість; ост. вартість; час експлуатації)

Нач. вартість -це початкова вартість майна.

Ост. вартість -це залишкова вартість наприкінці амортизації (ліквідна вартість майна).

Час експлуатації- це кількість періодів, за яке власність амортизується.

Розглянемо приклад. Припустимо, ви купили за 6000 грн. комп'ютер, що має строк експлуатації 5 років, після чого він оцінюється в 1000 грн.

Зниження вартості комп'ютера для кожного року експлуатації обчислюється по функції: =АПЛ(6000; 1000; 5).

Уведіть цю функцію в осередок З1, у результаті ви одержите 1000 грн. Поясните чому.

2. Функція АСЧ повертає амортизацію майна за кожний рік, для зазначеного періоду, використовуючи метод нерівномірної амортизації (метод суми річних чисел).

Синтаксис: АСЧ (нач. вартість; ост. вартість; час експлуатації; період)

Нач. вартість - це початкова вартість майна.

Ост. вартість- це залишкова вартість наприкінці періоду експлуатації.

Час експлуатації - це кількість періодів експлуатації.

Період - це номер періоду експлуатації (має тугіше одиницю виміру що й час експлуатації).

АСЧможна обчислювати по формулі:

Розглянемо той же приклад, що й у першому питанні. Визначимо амортизацію за перший і останній рік.