Розрахунок ефективної процентної ставки в Excel

В Excel ефективні щорічні процентні ставки, якщо задані номінальні процентні ставки та кількість періодів, які складають рік, розраховує функція ЭФФЕКТ:

Синтаксис ЭФФЕКТ(номинальная_ставка; колпер). (2.17)

Аргументи функції означають:

Номинальная_ставка - це номінальна річна процентна ставка; Кол_пер — це кількість періодів у році, за які нараховуються складні проценти.

Приклад 2.32. Позика 1 млн. грн. з номінальною нормою процента 12% буде сплачена в кінці 3 років єдиною сумою з нарахованими процентами. Яка сума буде сплачена, якщо проценти нараховуються кожні півроку, щоквартально, щомісяця, щоденно.

Розв'язання: Задачу можна розв'язати кількома способами:

1) за формулою (1.30):

• 2) використовуючи функцію Excel БС: БС(12% / 2; 2-3; ; - 1000) = 1418,52; БС(12% / 4; 4-3; ; - 1000) = 1425,76; БС(12% / 12; 12-3; ; -1000) = 1430,77; БС(12% / 365; 365-3; ; -1000) = 1433,24;
• 3) використовуючи ефективну процентну ставку - формула (2.17). Майбутню вартість позики можна розрахувати, використовуючи ефективну процентну ставку. Обчислимо ефективні ставки в комірках А1:А4:

А1 = ЗФФЕКТ(12%; 2) = 0,1236; А2 = ЗФФЕКТ(12%; 4) = 0,1255; A3 = ЗФФЕКТ(12%; 12) = 0,1268; А4 = ЗФФЕКТ(12%; 365) = 0,1275.

У комірку В1 введемо формулу для обчислення майбутньої вартості позики

В1 = БС(А1;3; ;-1000) та скопіюємо її в В2:В4. Результати розрахунків знаходятьс я в комірках В1:В4 відповідно: 1418,52; 1425,76; 1430,77 та 1433,24.

4) введенням математичних формул в Excel у рядок формул: = (1+0,12/2)л2 - 1;

= (1+0,12/4)л4 - 1; = (1+0,12/12)л12 - 1;

= (1+0,12/365)л365 - 1.

Розрахунок номінальної процентної ставки в Excel

В Excel номінальну річну процентну ставку, якщо відома ефективна процентна ставка та кількість періодів у році розраховує функція НОМИНАЛ:

Синтаксис НОМИНАЛ(эффективная ставка; кпер). (2.18)

Аргументи функції означають:

эффективная ставка - це ефективна процентна ставка; кол пер — це кількість періодів у році, за які нараховуються складні проценти.

Значення функції НОМІНАЛ — аргумент i формули (1.4).

Приклад 2.33. Ефективна ставка складає 28%, нарахування процентів щомісячне. Розрахувати номінальну процентну ставку. Розв'язання: Задачу можна розв'язати кількома способами: 1) за формулою (1.31)

2) за допомогою функції НОМИНАЛ(28%; 12) = 0,2494 або 24,94 %.

Розрахунки в Excel за цінними паперами

Особливістю розрахунків за цінними паперами в Excel із використанням вбудованих функцій Excel є можливість урахувати тривалість фінансових операцій з точністю до днів.

Операції з векселями в Excel (облікові процентні ставки)

Функція ПОЛУЧЕНО розраховує суму, отриману в строк вступу в силу цінних паперів при використанні облікової (дисконтної) ставки (розраховує суму, отриману в строк погашення повністю забезпечених цінних паперів):

Синтаксис ПОЛУЧЕНО(дата_согл; датавступлвсилу;

инвестиция; скидка; базис). (2.19)

Аргументи функції означають:

дата_согл - дата розрахунку за цінні папери (пізніша, ніж дата випуску, коли цінні папери були продані покупцеві);

дата_вступл_в_силу — строк погашення цінних паперів. Ця дата визначає момент строку дії цінних паперів;

инвестиция — це обсяг інвестиції в цінні папери;

скидка — скидка на цінний папір;

базис — це використовуваний спосіб обчислення строку

Приклад 2.34. Облікова ставка 12% річних. Векселедавець отримав 1200 тис. грн., вексель був виданий на 3 календарні місяці. Визначте номінал векселя. Спосіб обчислення строку: фактичний / фактичний. Вважати дату розрахунку за вексель 1 січня 2005 р.

Розв'язання: Задачу можна розв'язати кількома способами:

1) За формулою (1.33):

2) За допомогою функції (2.19):

ПОЛУЧЕНО(05,01,01; 05,04,01; 1 200 000; 12 %; 1) = 1 236 589,5 грн.

3) Введенням математичної функції в Excel:

= 1200 / (1 — (90/365)*0,12). Функція СКИДКА обчислює норму скидки — облікову ставку для цінних паперів, за якими не передбачені періодичні виплати (розраховує ставку дисконтування цінних паперів):

Синтаксис СКИДКА(дата_согл; дата_ вступлвсилу; цена; погашение; базис). (2.20)

Аргументи функції означають:

дата_согл — дата розрахунку за цінні папери (пізніша, ніж дата випуску, коли цінні папери були продані покупцеві);

дата_вступл_в_силу — строк погашення цінних паперів. Ця дата визначає момент строку дії цінних паперів;

цена — це ціна цінних паперів;

погашение — це викупна вартість цінних паперів;

базис — це використовуваний спосіб обчислення строку.

Приклад 2.35. Визначте величину облікової ставки, якщо вексель видано 1 січня 2005 р. на суму 870 тис. грн. з погашенням суми боргу 1 млн. грн. через три місяці (2005 рік — не високосний). Спосіб обчислення строку: фактичний / фактичний.

Розв'язання: Задачу можна розв'язати кількома способами:

1) за формулою d = S—— = 1000—870 = 0,5272222 або 52,72%,

S-n 1000 .2°.

• 365
• 31 + 28 + 31 90

де n =-=- років.

• 365 365
• 2) за допомогою функції (2.20):

СКИДКА(05,01,01; 05,04,01; 870 000; 1 000 000; 1) = 0,52722222 або

• 52,72 %.
• 3) введенням математичної функції в Excel:

= (1000 - 870) / (1000*90/365).

Щоб проглянути числа у вигляді процентних співвідношень, виділіть комірку і потім на вкладці Число виберіть у списку Числовые форматы варіант Процентный.

Функція ЦЕНАСКИДКА визначає ціну за 100 грн. номінальної вартості цінних паперів, на які зроблена скидка з ціни погашення:

Синтаксис ЦЕНАСКИДКА(дата_согл; датавступлвсилу; скидка; погашение; базис). (2.21)

Аргументи функції означають:

дата_согл - дата розрахунку за цінні папери (пізніша, ніж дата випуску, коли цінні папери були продані покупцеві);

дата_вступл_в_силу — строк погашення цінних паперів. Ця дата визначає момент строку дії цінних паперів;

скидка — скидка на цінний папір;

погашение — це викупна вартість цінних паперів;

базис — це використовуваний спосіб обчислення строку.

Приклад 2.36. Вексель номіналом 1 млн. грн. видано 1 січня 2005 р. строком на 3 місяці під облікову ставку 20% річних. Визначте суму, отриману векселедавцем. Спосіб обчислення строку: фактичний / фактичний.

Розв'язання: Задачу можна розв'язати кількома способами:

1) за формулою P = S-(1 -n-d) = 1-Й --90--0,21 = 0,950685 млн. грн.;

^ 365 )

2) За допомогою функції (2.21):

ЦЕНАСКИДКА(05,01,01; 05,04,01; 20 %; 1 000 000; 1) = = 950 684,9315 грн.;

3) введенням математичної функції в Excel:

= 1000000*(1-(90/365)*0,2).

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

Функція ИНОРМА розраховує річну ставку додаткового доходу (нарощення) для цінних паперів без періодичної виплати процентів згідно з формулою розрахунку простої процентної ставки (1.2) (розраховує процентну ставку для повністю інвестованих цінних паперів):

Синтаксис ИНОРМА(дата_соглашения; датавступлвсилу; инвестиция; погашение; базис). (2.22)

Аргументи функції означають:

дата_соглашения - дата розрахунку за цінні папери (пізніша, ніж дата випуску, коли цінні папери були продані покупцеві);

дата_вступл_в_силу — строк погашення цінних паперів. Ця дата визначає момент строку дії цінних паперів;

инвестиция — це обсяг інвестиції в цінні папери;

погашение — це викупна вартість цінних паперів;

базис — це використовуваний спосіб обчислення строку.

Приклад 2.37. Визначте річний рівень доходу за векселем, який було випущено 1 січня 2005 р. на три місяці, якщо векселедавець отримав 0,85 номіналу. Спосіб обчислення строку: фактичний / фактичний.

Розв'язання: Задачу можна розв'язати кількома способами: s — р і — 085

1) за формулою j =-=-,— = 0,7156864 або 71,57%;

P n 0,85 - —

• 365
• 2) за допомогою функції (2.22)

ИНОРМА(05,01,01; 05,04,01; 85; 100; 1) = 0,715686275;

3) введенням математичної функції в Excel:

= (1 - 0,85)/(0,85*90/365). Функція ДОХОДСКИДКА розраховує річну ставку додаткового доходу для цінних паперів, періодичні виплати процентів за якими не передбачені і на які зроблена скидка, за формулою розрахунку простої процентної ставки (1.2):

Синтаксис ДОХОДСКИДКА(дата_согл; датавступлвсилу; цена; погашение; базис). Аргументи функції означають:

дата_согл - дата розрахунку за цінні папери (дата продажу цінних паперів покупцю, пізніша, ніж дата випуску);

дата_вступл_в_силу — строк погашення цінних паперів. Ця дата визначає момент, коли закінчується строк дії цінних паперів;

цена — ціна цінних паперів на 100 грн. номінальної вартості;

погашение — викупна вартість цінних паперів на 100 грн. номінальної вартості;

базис — це використовуваний спосіб обчислення строку.

Приклад 2.38. Ціна облігації на дату придбання 1.07.2011 р. дорівнює 77,345, дата погашення 31.12.2011 р. за курсом - 100. Знайдіть річну ставку доходу. Спосіб обчислення строку: фактичний / фактичний.

Розв'язання: Задачу можна розв'язати кількома способами:

• 5 _ р 100 - 77 345
• 3) за формулою і =-=-'—- = 0,5842 або 58,42%;

р-п 77,345-і83-365

4) за допомогою функції ДОХОДСКИДКА

ДОХОДСКИДКА(1.07,2011; 31.12.201; 77,345; 100; 1) = 0,5842162.