Финансовые функции для вычисления текущих значений
Теперь рассмотрим, как по будущему значению определить текущее (современное) значение. В этом случае используется финансовая функция ПС (приведенная сумма).
Задание 10
Вкладчик собирается положить деньги в банк на четыре года под 25 % и накопить 15 000 руб. Какую сумму он должен вложить?
Решение
S = 15 000 руб, n=4, r=25 %.
Найдем
Р = S/(1+r)n =15 000(1+0,25)4 = - 6 144 (руб).
(1 + г)п (1 + 0,25)4
Теперь воспользуемся функцией ПC. Она примет вид =ПC(25%;4;;15000). Эта функция вернет значение – 6 144 руб (знак «минус» означает, что данная сумма отдается вкладчиком в банк).
Задание 11
Вексель на 5 млн долларов с процентной ставкой 10 % и начислением процентов дважды в год выдан на два года. Какую сумму можно получить под этот вексель?
Процентная ставка дана годовая, а проценты начисляются дважды в год. Значит, ставка за период равна 10 %/2=5 %. Число периодов 2*2=4. Рассчитаем, какую сумму можно получить сегодня, взяв на себя обязательство вернуть через два года 5 млн долларов.
S 5000000 лл„е„,
Р = S/(1+r)n = 5 000 000(1+0,05)4 = 4 113 512 (долларов).
Теперь проведем эти расчеты в Excel, введя в любую ячейку функцию ПЗ. Функция имеет вид =ПC(10%/2;2*2;;-5000000). Эта функция возвращает значение 4 113 512,37 руб.
Вычисление продолжительности ссуды
Обратимся к задаче определения продолжительности ссуды при заданных современном и будущем значениях и процентной ставке.
Для решения этой задачи удобно воспользоваться функцией КПЕР (количество периодов). Она имеет синтаксис
КПЕР(ставка;плт;пс;бс;тип)
Ставка — процентная ставка за период.
Плт — выплата, производимая в каждый период; это значение не может меняться в течение всего периода выплат. Обычно платеж состоит из основного платежа и платежа по процентам и не включает налогов и сборов.
Пс — приведенная к текущему моменту стоимость или общая сумма, которая на текущий момент равноценна ряду будущих платежей.
Бс — требуемое значение будущей стоимости или остатка средств после последней выплаты. Если аргумент Бс опущен, то он полагается равным 0.
Тип — число 0 или 1, обозначающее, когда должна производиться выплата: 0 – в конце периода, 1 – в начале периода. Если этот аргумент опущен, то он полагается равным 0.
Задание 12
За какой срок сумма, равная 30 000 рублей, достигнет 100 000 рублей при начислении процентов по сложной ставке 18 %?
Запишем функцию
=КПЕР(18%;;-30000;100000). Она возвращает значение 7,27 лет.
Вычисление процентной ставки
Если известны современное и будущее значения сумм, а также число периодов выплат, можно вычислить процентную ставку с использованием функции СТАВКА. Ее синтаксис:
СТАВКА(кпер;плт;пс;бс;тип;предположение)
Кпер — общее число периодов платежей по аннуитету.
Плт — регулярный платеж (один раз в период), величина которого остается постоянной в течение всего срока аннуитета. Обычно Плт состоит из платежа основной суммы и платежа процентов, но не включает других сборов или налогов. Если аргумент опущен, должно быть указано значение аргумента Бс
Пс — приведенная к текущему моменту стоимость или общая сумма, которая на текущий момент равноценна ряду будущих платежей.
Бс — требуемое значение будущей стоимости или остатка средств после последней выплаты. Если аргумент Бс опущен, то он полагается равным 0.
Тип — число 0 или 1, обозначающее, когда должна производиться выплата: 0 – в конце периода, 1 – в начале периода. Если этот аргумент опущен, то он полагается равным 0.
Предположение — предполагаемая величина ставки. Если значение предположения опущено, то оно полагается равным 10 процентам.
Задание 13
В долг на полтора года дана сумма 5 000 долларов с условием возврата 8 000 долларов. Вычислить годовую процентную ставку.
Функция имеет вид =СТАВКА(1,5;;5000;-8000) и возвращает значение 37 %.
Расчет размера платежей ренты
Для расчета определенного члена ренты R в Excel используется функция ПЛТ. Ее синтаксис
ПЛТ(ставка;кпер;пс;бс;тип)
Ставка — процентная ставка по ссуде.
Кпер — общее число выплат по ссуде.
Пс — приведенная к текущему моменту стоимость, или общая сумма, которая на текущий момент равноценна ряду будущих платежей, называемая также основной суммой.
Бс — требуемое значение будущей стоимости, или остатка средств после последней выплаты. Если аргумент Бс опущен, то он полагается равным 0 (нулю), т. е. для займа, например, значение Бс равно 0.
Тип — число 0 или 1, обозначающее, когда должна производиться выплата.
Задание 14
Семья планирует взять в банке 6-месячную ссуду в размере $20 000. Какова будет величина месячных выплат при процентной ставке, равной 6 %?
Решение
1. Если в условии не указан тип процентной ставки, подразумевается, что это сложная процентная ставка. Итак, n=6 месяцев, P=$20 000, S=0, r=6 % годовых.
2. Ставка дана годовая, значит ее величина за период составит
6 % / 2 = 3 % = 0,03.
А за каждый период выплат (месяц) 3 % = 0,5 % = 0,05.
3. Если в условии задачи не оговаривается, в начале или в конце периода производятся выплаты, подразумевается, что они осуществляются в конце периода (т.е. аргумент тип равен 0 или опускается). Число периодов 6. В любую ячейку Excel вводим формулу
=ПЛТ(6%/12;6;20000;0). В результате получаем -3 391,91.
Если поток платежей представляет собой постоянные выплаты R за взятый кредит, то каждый член ренты R (платежи) включает в себя две составляющие: платежи по процентам за заданный период (для расчета этого параметра в Excel используется функция ПРПЛТ) и основные платежи - без процентов (функция ОСПЛТ). Синтаксис этих функций:
ПРПЛТ(ставка;период;кпер;пс;бс;тип)
Ставка — процентная ставка за период.
Период — это период, для которого требуется найти платежи по процентам; должен находиться в интервале от 1 до «Кпер».
Кпер — общее число периодов выплат годовой ренты.
Пс — приведенная к текущему моменту стоимость или общая сумма, которая на текущий момент равноценна ряду будущих платежей.
Бс — требуемое значение будущей стоимости или остатка средств после последней выплаты. Если аргумент Бс опущен, то он полагается равным 0 (например, Бс для займа равно 0).
Тип — число 0 или 1, обозначающее, когда должна производиться выплата. Если аргумент «тип» опущен, то он полагается равным 0.
ОСПЛТ(ставка;период;кпер;пс;бс;тип)
Ставка — процентная ставка за период.
Период — задает период, значение должно быть в интервале от 1 до «Кпер».
Кпер — общее число периодов выплат годовой ренты.
Пс — приведенная стоимость, т. е. общая сумма, которая равноценна ряду будущих платежей.
Бс — требуемое значение будущей стоимости, или остатка средств после последней выплаты. Если аргумент Бс опущен, то он полагается равным 0 (нулю), т. е. для займа, например, значение Бс равно 0.
Тип — число 0 или 1, обозначающее, когда должна производиться выплата.
Задание 15
Рассчитать плату за проценты и основные платежи для каждого месяца в задаче примера 14.
Решение
Так как плата по процентам и основная плата будут производиться по месяцам, результаты вычислений следует свести в табл. 2.2.3.
В ячейке В7 вычисляется плата по процентам за первый месяц возврата ссуды, в ячейке С7 - основная плата, в D7 - остаток долга. Для расчета этих сумм на следующие 5 месяцев следует провести копирование формул: из ячейки В7 - в ячейки В8:В12, из ячейки С7 - в ячейки С8:С12 и из ячейки D7 -в ячейки D8:D12. Результат вычислений представлен в табл. 2.2.4.
Таблица 2.2.3
| A | B | C | D | |
| Процент | =6%/12 | |||
| Число выплат | лет | |||
| Ежемесячная плата | =ПЛТ(C1;C2;C4) | |||
| Размер ссуды | ||||
| Месяц | Плата по процентам | Основная плата | Остаток долга | |
| =ПРПЛТ($C$1;A7;$C$2;$C$4) | =ОСПЛТ($C$1;A7;$C$2;$C$4) | =D6+C7 | ||
| =ПРПЛТ($C$1;A8;$C$2;$C$4) | =ОСПЛТ($C$1;A8;$C$2;$C$4) | =D7+C8 | ||
| =ПРПЛТ($C$1;A9;$C$2;$C$4) | =ОСПЛТ($C$1;A9;$C$2;$C$4) | =D8+C9 | ||
| =ПРПЛТ($C$1;A10;$C$2;$C$4) | =ОСПЛТ($C$1;A10;$C$2;$C$4) | =D9+C10 | ||
| =ПРПЛТ($C$1;A11;$C$2;$C$4) | =ОСПЛТ($C$1;A11;$C$2;$C$4) | =D10+C11 | ||
| =ПРПЛТ($C$1;A12;$C$2;$C$4) | =ОСПЛТ($C$1;A12;$C$2;$C$4) | =D11+C12 |
Таблица 2.2.4
| A | B | C | D | |
| Процент | 0,005 | |||
| Число выплат | лет | |||
| Ежемесячная плата | -3 391,91р. | |||
| Размер ссуды | 20 000 | |||
| Месяц | Плата по процентам | Основная плата | Остаток долга | |
| 20 000,00р. | ||||
| -100,00р. | -3 291,91р. | 16 708,09р. | ||
| -83,54р. | -3 308,37р. | 13 399,72р. | ||
| -67,00р. | -3 324,91р. | 10 074,81р. | ||
| -50,37р. | -3 341,54р. | 6 733,28р. | ||
| -33,67р. | -3 358,24р. | 3 375,03р. | ||
| -16,88р. | -3 375,03р. | -0,00р. |
КОНТРОЛЬНЫЕ ЗАДАНИЯ
1. Ссуда в размере P млн руб. выдана х1 марта 2009 г. до х2 июля 2009 г. включительно под rt % годовых. Какую сумму нужно заплатить в конце срока?
2. Кредит в P млн руб. предоставили на три года под rt % годовых с начислением по схеме сложных процентов. Какую сумму придется возвращать?
3. Ссуда в P тыс. долларов дана в долг на n года под rt % годовых с ежеквартальным начислением. Какова будет сумма накопленного долга?
4. Банк принимает вклады на срок 3 месяца с объявлением годовой ставки rt % или на шесть месяцев под rt1 %. Как выгоднее вкладывать деньги на полгода: дважды на три месяца или один раз на шесть месяцев?
5. На счет в банке вносится сумма P тыс. долларов в течение n лет равными долями в конце каждого года. Годовая ставка rt %. Какая сумма будет на счету через n лет?
6. Вкладчик собирается положить деньги в банк на n лет под rt % и накопить P тыс. долларов. Какую сумму он должен вложить?
7. За какой срок сумма, равная P тыс. долларов, достигнет P1 тыс. долларов при начислении процентов по сложной ставке rt %?
8. В долг на полтора года дана сумма P долларов с условием возврата P1 долларов. Вычислить годовую процентную ставку.
9. Семья планирует взять в банке 6-месячную ссуду в размере P1 тыс. долларов. Какова будет величина месячных выплат при процентной ставке, равной rt %? Рассчитайте плату за проценты и основные платежи для каждого месяца.
Исходные данные к заданиям 1-9 для каждого варианта представлены в таблице:
| Вариант | ||||||||||
| P | ||||||||||
| х1 | ||||||||||
| х2 | ||||||||||
| rt | ||||||||||
| n | ||||||||||
| rt1 | ||||||||||
| P1 |