10) Определить будущую стоимость капитала 15000 рублей, помещенных в банк под 18% годовых, сроком на 5 лет. Проценты начисляются раз в квартал. (Использование функции БС()).
11) Взята сумма в размере 90000 рублей сроком на 2 года под 15% годовых. Рассчитать сумму остаточных платежей для каждого года займа. (Использование функции ОСПЛТ()).
12) С кредитно-дебетовой карты взята сумма в размере 70000 рублей сроком на 3 года под 17% годовых. Рассчитать сумму остаточных платежей для каждого квартала займа, при условии, что конец периода на счету должна быть накоплена сумма 8000 рублей. (Использование функции ОСПЛТ()).
13) Рассчитать через сколько лет сумма вклада в размере 15 000 рублей достигнет 50000 рублей, при процентной ставке 15% годовых. (Использование функции КПЕР()).
14) Начиная с 30 лет каждый год на счет в банк вносится 1000 рублей. К какому возрасту человек станет миллионером, при условии, что процентная ставка равна 18% годовых. (Использование функции КПЕР()).
15) Рассчитать через сколько лет произойдет полное погашение займа размером 2500000 рублей, если выплаты 50000 рублей производятся в конце каждого квартала, а процентная ставка равна 17% годовых. (Использование функции КПЕР()).
Для решение поставленных задач используются функции ПС(), БС(), ПЛТ(), ПРПЛТ(), ОСПЛТ(), КПЕР.
Алгоритм решения задач:
1) Внесение исходных данных;
2) Ввод функции с аргументными значениями;
3) Получение результата.
Задача 1.
Исходные данные:
| Сумма кредита | 250000 |
| Срок кредита, лет | 1 |
| Процент | 17% |
Для решения поставленной задачи использовались функции ЕСЛИ(), ПС(), ПЛТ(), ПРПЛТ(), ОСПЛТ(), СУММ().
Ежемесячный платеж рассчитывается с помощью функции =ПЛТ(Процент/12;Срок кредита*12;Сумма кредита;;)
Аннуитет, платежи по кредиту, по процентам и остаток суммы задолженности рассчитывается по одинаковой формуле, с изменением № месяца, для которого производится расчет.
Формула расчета Аннуитета =ЕСЛИ(№ месяца>Срок кредита*12;0;Ежемесячный платеж)
Платежи по кредиту рассчитываются по формуле =ЕСЛИ(№ месяца>Срок кредита *12;0;ОСПЛТ(Процент/12;№месяца;Срок кредита*12;Сумма кредита)).
Процентные платежи рассчитываются по формуле ЕСЛИ(№месяца>Срок кредита*12;0;ПРПЛТ(Процент/12;№месяца;срок кредита*12;сумма кредита)).
Остаток суммы задолженности рассчитывается по формуле =ЕСЛИ(№месяца>Срок кредита*12;0;ПС(Процент/12;(Срок кредита *12)-№месяца; Ежемесячный платеж)).
Общая сумма процентов рассчитывается путем суммирований данных из столбца Проценты.
Мес. – рассчитывается путем умножения срока кредита (лет) на 12 месяцев.
Сумма аннуитета рассчитывается путем умножения суммы ежемесячного платежа на количество месяцев.
Результатом проведения вышеуказанных расчетом получаем график платежей.
Таблица 2.1 Решение задачи 1
| Сумма кредита | 250000 | ||||
| Срок кредита, лет | 1 | мес | Сумм. Аннуитет | Кредит | Проценты |
| Процент | 17% | 12 | -273 614,26р. | -250 000,00р. | -23 614,26р. |
| Ежемесячный платеж | -22 801,19р. | ||||
| № месяца | Аннуитет | Кредит | Проценты | Остаток СЗ | |
| 1 | -22 801,19р. | -19 259,52р. | -3 541,67р. | 230 740,48р. | |
| 2 | -22 801,19р. | -19 532,36р. | -3 268,82р. | 211 208,11р. | |
| 3 | -22 801,19р. | -19 809,07р. | -2 992,11р. | 191 399,04р. | |
| 4 | -22 801,19р. | -20 089,70р. | -2 711,49р. | 171 309,34р. | |
| 5 | -22 801,19р. | -20 374,31р. | -2 426,88р. | 150 935,03р. | |
| 6 | -22 801,19р. | -20 662,94р. | -2 138,25р. | 130 272,09р. | |
| 7 | -22 801,19р. | -20 955,67р. | -1 845,52р. | 109 316,43р. | |
| 8 | -22 801,19р. | -21 252,54р. | -1 548,65р. | 88 063,89р. | |
| 9 | -22 801,19р. | -21 553,62р. | -1 247,57р. | 66 510,27р. | |
| 10 | -22 801,19р. | -21 858,96р. | -942,23р. | 44 651,31р. | |
| 11 | -22 801,19р. | -22 168,63р. | -632,56р. | 22 482,68р. | |
| 12 | -22 801,19р. | -22 482,68р. | -318,50р. | 0,00р. |
Задача 2.
Исходные данные:
Годовая процентная ставка – 10%
Число лет хранения – 15
Необходимая величина сбережений – 50 000 руб.
Сумма ежемесячного платежа рассчитывается по формуле =ПЛТ(Процентная ставка/12;Число лет*12;;Необходимая сумма сбережений; момент выплаты) = ПЛТ(10%/12;15*12;;50000;1) = -119,64 руб.
Отрицательная сумма получилась, потому, что данную сумму необходимо платить.
При изменении момента выплаты на конец периода сумма несколько измениться = ПЛТ(10%/12;15*12;;50000;0) = -120,64 руб.
Задача 3.
Исходные данные:
Годовая процентная ставка – 10%
Число лет хранения – 15 лет
Необходимая величина сбережений – 50 000 руб.
Начальный взнос – 10 000 руб.
Сумма ежемесячного платежа рассчитывается по формуле =ПЛТ(Процент/12;Число лет*12;начальный взнос;необходимая величина сбережений)=ПЛТ(10%/12;15*12;10000;50000)=-228,10 руб.
Отрицательная сумма получилась, потому, что данную сумму необходимо платить.
Задача 4.
Исходные данные:
Ежегодный доход – 20 000 руб.
Процентная ставка – 18%
Число лет – 5
Величина вложений рассчитывается с помощью формулы =ПС(Ставка;Число лет;Ежегодный доход)=ПС(18%;5;20000)=-62 543,42 руб.
Отрицательная сумма получилась, потому, что данную сумму необходимо платить.
Задача 5.
Исходные данные:
Процентная ставка – 15%
Число лет – 10
Итоговый доход - 100 000 руб.
Ежегодный взнос – 2000 руб
Величина первоначальных вложений рассчитывается по формуле =ПС(Процент;Число лет;Ежегодный взнос;Итоговый доход)=ПС(15%;10;-2000;100000)=-14 680,933 руб.
Отрицательная сумма получилась, потому, что данную сумму необходимо платить.
Задача 6.
Исходные данные:
Процентная ставка – 10%
Месяц – 1
Срок кредита – 3 года
Сумма кредита – 100 000 руб.
Выплаты по процентам рассчитываются по формуле =ПРПЛТ(процентная ставка/12;Период (Месяц);Срок кредита*12;сумма кредита)= ПРПЛТ(10%/12;1;3*12;100000)= -833,33 руб.
Отрицательная сумма получилась, потому, что данную сумму необходимо платить.
Задача 7.
Исходные данные:
Процентная ставка – 10%
Срок кредита – 3 года
Момент дохода – 3-й год
Сумма кредита – 100000 руб.
Выплаты по процентам рассчитываются по формуле =ПРПЛТ(Ставка;Момент дохода;Срок кредита; Сумма кредита)=ПРПЛТ(10%;3;3;100000) = -3 655,59 руб.
Отрицательная сумма получилась, потому, что данную сумму необходимо платить.
В случае если не указан, хотя бы один из обязательных аргументов расчет будет невозможен.
Задача 8.
Исходные данные:
Процентная ставка – 30%
Вклад – 5000 рублей
Дата открытия вклада – 10.01.2010
Дата закрытия – 04.03.2010
Количество расчетных периодов – 1
Расчет суммы капитала производится через формулу =БС((Дата закрытия-Дата открытия)/360*Процент;Количество периодов;;вклад) = =БС((53)/360*30%;1;;-5000)= 5220,833.
В данном случае заполнение аргумента Плт не обязательно, т.к. заполняется необязательный аргумент Пс.
Задача 9.
Исходные данные:
Процентная ставка – 15%
Вклад – 10 000 руб.
Количество периодов – 3 года
Ежемесячные вклады – 1000 рублей.
Расчет накопленного капитала производится через формулу =БС(Процентная ставка/12;Количество периодов*12;Ежемесячный вклад; Первоначальный вклад; момент выплаты)=БС(15%/12;3*12;-1000;-10000;1)= 61318,89
Задача 10.
Исходные данные:
Процентная ставка – 18%
Вклад – 15 000 руб.
Количество периодов – 5 лет.
Расчет накопленного капитала производится через формулу =БС(Процентная ставка/4;Количество периодов*4;;вклад)=БС(18%/4;5*4;;-15000)=36 175,71 руб.
Задача 11.
Исходные данные:
Ссуда – 90 000 руб.
Процентная ставка – 15% годовых
Срок 2 года.
Расчет суммы остаточных платежей для каждого года производится по формуле =ОСПЛТ(Процентная ставка;Период;Ссуда;Срок кредита).
Для каждого периода в расчетной формуле меняется номер периода.
Таким образом для 1-го года остаточная сумма платежа равна =ОСПЛТ(15%;1;90000;2)=-41 860,47 руб.
Для 2-го года:
=ОСПЛТ(15;2;90000;2)=-48 139,53 руб.
Суммы отрицательны, т.к. их необходимо платить.
Задача 12.
Исходные данные:
Ссуда – 70 000 рублей
Процентная ставка – 17%
Срок кредита – 3 года
Накопления на счету – 8000 рублей.
Расчет платежей производится по формуле = ОСПЛТпроцентная ставка/4;период;срок кредита *4;ссуда;накопления).
Для каждого квартала изменяется только № периода. Результатом вычислений является таблица 2.2
Таблица 2.2 – Результаты расчетов по задаче 12
| Ссуда | 70 000,00р. |
| Процентная ставка | 17% |
| Срок кредита | 3 |
| Период | Платеж |
| 1 | -5 117,07р. |
| 2 | -5 334,55р. |
| 3 | -5 561,27р. |
| 4 | -5 797,62р. |
| 5 | -6 044,02р. |
| 6 | -6 300,89р. |
| 7 | -6 568,68р. |
| 8 | -6 847,85р. |
| 9 | -7 138,88р. |
| 10 | -7 442,28р. |
| 11 | -7 758,58р. |
| 12 | -8 088,32р. |
| Итого | -78 000,00р. |
| Накопления | 8 000,00р. |
Задача 13.
Исходные данные:
Начальный платеж – 15 000 руб.
Необходимая сумма накоплений – 50 000 руб.
Процентная ставка – 15:
Срок накопления необходимых денежных средств рассчитывается по формуле =КПЕР(Процентная ставка;; Первоначальный платеж; Необходимая сумма)=КПЕР(15;;-15000;50000)=8,61 лет.
Не обязательно заполнение аргумента Плт при заполненном аргументе Бс.
Задача 14.
Исходные данные:
Ежемесячный платеж – 1000 рублей
Процентная ставка – 18% годовых
Необходимая сумма на счету – 1 000 000 руб.
Срок накопления необходимых денежных средств рассчитывается через формулу =КПЕР(процентная ставка/12;-ежемесячный платеж;; необходимая сумма на счету; момент выплаты)/12=КПЕР(18%/12;-1000;;1000000;0)/12=15,52 лет.
Таким образом возраст человека будет составлять = 30+15,52 = 45,52 лет.
Не обязательно заполнение аргумента Пс при заполненном аргументе Бс.