Финансовые функции для вычисления текущих значений
Теперь рассмотрим, как по будущему значению определить текущее (современное) значение. В этом случае используется финансовая функция ПС (приведенная сумма).
Задание 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 руб (знак «минус» означает, что данная сумма отдается вкладчиком в банк).
| Задание 10 | |||
| S | r | n | P |
| 25% | =ПС(B3;C23;;A3) |
| Задание 10 | |||
| S | r | n | P |
| 25% | -6 144,00 |
Задание 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 лет.
| Задание 12 | |||
| P | P1 | r1 | |
| 18% | =КПЕР(C3;;-A3;B3) |
| Задание 12 | |||
| P | P1 | r1 | |
| 18% | 7,27 |
Вычисление процентной ставки
Если известны современное и будущее значения сумм, а также число периодов выплат, можно вычислить процентную ставку с использованием функции СТАВКА. Ее синтаксис:
СТАВКА(кпер;плт;пс;бс;тип;предположение)
Кпер — общее число периодов платежей по аннуитету.
Плт — регулярный платеж (один раз в период), величина которого остается постоянной в течение всего срока аннуитета. Обычно Плт состоит из платежа основной суммы и платежа процентов, но не включает других сборов или налогов. Если аргумент опущен, должно быть указано значение аргумента Бс
Пс — приведенная к текущему моменту стоимость или общая сумма, которая на текущий момент равноценна ряду будущих платежей.
Бс — требуемое значение будущей стоимости или остатка средств после последней выплаты. Если аргумент Бс опущен, то он полагается равным 0.
Тип — число 0 или 1, обозначающее, когда должна производиться выплата: 0 – в конце периода, 1 – в начале периода. Если этот аргумент опущен, то он полагается равным 0.
Предположение — предполагаемая величина ставки. Если значение предположения опущено, то оно полагается равным 10 процентам.
Задание 13
В долг на полтора года дана сумма 5 000 долларов с условием возврата 8 000 долларов. Вычислить годовую процентную ставку.
Функция имеет вид =СТАВКА(1,6;;5000;-8000) и возвращает значение 34 %.
| задание13 | |||
| Р | Р1 | n | rt |
| 1,6 | =СТАВКА(C3;;A3;-B3) |
| задание13 | |||
| Р | Р1 | n | rt |
| 1,6 | 34% |
Расчет размера платежей ренты
Для расчета определенного члена ренты 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р. |