лаба 3

Цель работы: Освоить приемы расчетов по сложным процентам с использованием функций MS Excel.

Основные функции Excel:

БС (Ставка; Кпер; Плт; Пс; Тип) - рассчитывает будущую стоимость вклада, учитывая процентную ставку, количество периодов, платеж, текущую стоимость и тип (начало/конец периода).

Основные параметры функций:

Ставка: Процентная ставка за период.
Кпер: Общее число периодов платежей.
Плт: Выплата, производимая в каждый период.
Пс: Текущая стоимость.
Тип: 0 - выплата в конце периода, 1 - выплата в начале периода.

Применение для расчетов по сложным процентам:

Ставка: i/n, где i - годовая ставка, n - количество периодов в году.
Кпер: 1 (для расчета наращенной суммы)

Важно:

Исходящие денежные потоки (инвестиции, платежи) - отрицательные числа.
Входящие денежные потоки (получение прибыли, выплата кредита) - положительные числа.

Пример:

Рассчитать будущую стоимость вклада 10 000 руб. через 5 лет при ставке 10% годовых, если проценты начисляются ежегодно:В Excel: =БС(0,10;5;0;-10000;0)

Ставка = 0,10
Кпер = 5
Пс = -10000 (отрицательное число, т.к. это инвестиция)
Тип = 0 (выплата в конце периода)

Вывод:

зАДАНИЕ 1

Расчет суммы вклада:

Простые проценты:

Функция: ПС
Формула: ПС(Ставка; Кп; Пс)

Ставка: 0,065 (6,5% годовых)
Кп: 1,5 (18 месяцев = 1,5 года)
Пс: -250000 (отрицательное число, т.к. это инвестиция)

Сложные проценты:

Функция: БС
Формула: БС(Ставка; Кп; 0; Пс; 0)

Ставка: 0,065 (6,5% годовых)
Кп: 1,5 (18 месяцев = 1,5 года)
Пс: -250000 (отрицательное число, т.к. это инвестиция)

Результаты расчетов:

Тип процентов

Сумма вклада

Простые

266 250 руб.

Сложные

268 327,69 руб.

Графическое представление:

Вывод:

Как видно из результатов расчетов, при начислении сложных процентов сумма вклада больше, чем при начислении простых процентов. Это связано с тем, что при сложных процентах проценты начисляются не только на первоначальную сумму вклада, но и на ранее начисленные проценты.

2 задание

1. Данные в Excel:

Создайте таблицу в Excel с заголовками столбцов: “Период начисления”, “Сумма кредита”, “Ставка”, “Срок”, “Накопленная сумма”.
Введите следующие данные:

В ячейку A2 введите “1 раз в год”.
В ячейку A3 введите “Поквартально”.
В ячейку A4 введите “Ежемесячно”.
В ячейку A5 введите “Ежедневно”.
В ячейку B2 введите 1000000.
В ячейку C2 введите 0,12 (12%).
В ячейку D2 введите 3 (3 года).

2. Расчет накопленной суммы:

1 раз в год: В ячейку E2 введите формулу =БС(C2, D2, 0, -B2, 0)
Поквартально: В ячейку E3 введите формулу =БС(C2/4, D2*4, 0, -B2, 0)
Ежемесячно: В ячейку E4 введите формулу =БС(C2/12, D2*12, 0, -B2, 0)
Ежедневно: В ячейку E5 введите формулу =БС(C2/365, D2*365, 0, -B2, 0)

Результаты:

Период начисления

Накопленная сумма

1 раз в год

1 404 928

Поквартально

1 425 761

Ежемесячно

1 430 769

Ежедневно

1 433 248

3. График:

Выделите столбцы “Период начисления” и “Накопленная сумма” (A2:E5).
Перейдите на вкладку “Вставка” и выберите тип графика “Гистограмма”.
Вы получите график, показывающий накопленную сумму для каждого периода начисления процентов.

4. Объяснение:

Функция БС (будущая стоимость) в Excel рассчитывает будущую стоимость вложения, учитывая процентную ставку, количество периодов, платежи и начальную сумму.
Чем чаще начисляются проценты, тем выше накопленная сумма.

Задача 3

1. Данные в Excel:

Создайте таблицу в Excel с заголовками столбцов: “Банк”, “Сумма кредита”, “Ставка”, “Срок”, “Период начисления”, “Накопленная сумма”.
Введите следующие данные:

В ячейку A2 введите “Банк А”.
В ячейку A3 введите “Банк Б”.
В ячейку B2 введите 500000.
В ячейку B3 введите 500000.
В ячейку C2 введите 0,1 (10%).
В ячейку C3 введите 0,11 (11%).
В ячейку D2 введите 2 (2 года).
В ячейку D3 введите 2 (2 года).
В ячейку E2 введите “Ежегодно”.
В ячейку E3 введите “Ежеквартально”.

2. Расчет накопленной суммы:

Банк А: В ячейку F2 введите формулу =БС(C2, D2, 0, -B2, 0)
Банк Б: В ячейку F3 введите формулу =БС(C3/4, D2*4, 0, -B2, 0)

Результаты:

Банк

Накопленная сумма

Банк А

599 500

Банк Б

615 126

3. Анализ результатов:

Накопленная сумма долга в банке А меньше, чем в банке Б.

4. Вывод:

Для П.П. Петрова наиболее оптимальным вариантом кредитования является Банк А, так как накопленная сумма долга в этом банке меньше, чем в банке Б.

Задача 4

1. Данные в Excel:

Создайте таблицу в Excel с заголовками столбцов: “Сумма вклада”, “Срок вклада”, “Годовая ставка”, “Начисление процентов”, “Наращенная сумма (функция БС)”, “Наращенная сумма (формула)”.
Введите следующие данные:

В ячейку A2 введите 20000.
В ячейку A3 введите 20000.
В ячейку A4 введите 20000.
В ячейку A5 введите 20000.
В ячейку B2 введите 7.
В ячейку B3 введите 7.
В ячейку B4 введите 7.
В ячейку B5 введите 7.
В ячейку C2 введите 5.
В ячейку C3 введите 5.
В ячейку C4 введите 5.
В ячейку C5 введите 5.
В ячейку D2 введите “Ежедневно”.
В ячейку D3 введите “Месячное”.
В ячейку D4 введите “Квартальное”.
В ячейку D5 введите “Полугодовое”.

2. Расчет наращенной суммы:

Ежедневно:

В ячейку E2 введите формулу =БС(C2/365, B2*365, 0, -A2, 0)
В ячейку F2 введите формулу =A2*(1+C2/365)^(B2*365)

Месячное:

В ячейку E3 введите формулу =БС(C2/12, B2*12, 0, -A2, 0)
В ячейку F3 введите формулу =A2*(1+C2/12)^(B2*12)

Квартальное:

В ячейку E4 введите формулу =БС(C2/4, B2*4, 0, -A2, 0)
В ячейку F4 введите формулу =A2*(1+C2/4)^(B2*4)

Полугодовое:

В ячейку E5 введите формулу =БС(C2/2, B2*2, 0, -A2, 0)
В ячейку F5 введите формулу =A2*(1+C2/2)^(B2*2)

Результаты:

Сумма вклада

Срок вклада

Годовая ставка

Начисление процентов

Наращенная сумма (функция БС)

Наращенная сумма (формула)

20 000

Ежедневно

28 939

20 000

Месячное

28 899

20 000

Квартальное

28 878

20 000

Полугодовое

28 857

3. Анализ результатов:

Видно, что чем чаще начисляются проценты, тем больше накопленная сумма.

Задача 5

1. Данные в Excel:

Создайте таблицу в Excel с заголовками столбцов: “Вариант”, “Первоначальный взнос”, “Годовая ставка”, “Срок”, “Период платежа”, “Сумма платежа”, “Наращенная сумма”.
Введите следующие данные:

В ячейку A2 введите “а”.
В ячейку A3 введите “б”.
В ячейку B2 введите 200000.
В ячейку B3 введите 200000.
В ячейку C2 введите 0,09 (9%).
В ячейку C3 введите 0,09 (9%).
В ячейку D2 введите 3 (3 года).
В ячейку D3 введите 3 (3 года).
В ячейку E2 введите “Квартально”.
В ячейку E3 введите “Ежемесячно”.
В ячейку F2 введите 50000.
В ячейку F3 введите 20000.

2. Расчет наращенной суммы:

Вариант а (квартальные платежи): В ячейку G2 введите формулу =БС(C2/4, D2*4, -F2, -B2, 0)
Вариант б (ежемесячные платежи): В ячейку G3 введите формулу =БС(C3/12, D3*12, -F3, -B2, 0)

Результаты:

Вариант

Первоначальный взнос

Годовая ставка

Срок

Период платежа

Сумма платежа

Наращенная сумма

200 000

Квартально

50 000

735 157

200 000

Ежемесячно

20 000

750 835

3. График:

Выделите столбцы “Вариант” и “Наращенная сумма” (A2:G3).
Перейдите на вкладку “Вставка” и выберите тип графика “Гистограмма”.
Вы получите график, показывающий наращенную сумму для каждого варианта.

4. Анализ результатов:

Наращенная сумма при ежемесячных платежах выше, чем при квартальных платежах.

5. Вывод:

Для А.А. Иванова более выгодным вариантом является ежемесячное пополнение счета.

Задача 6

1. Данные в Excel:

Создайте таблицу в Excel с заголовками столбцов: “Платежи”, “Период платежей”, “Годовая процентная ставка”.
Введите следующие данные:

В ячейку A2 введите 25000.
В ячейку A3 введите 75000.
В ячейку A4 введите 150000.
В ячейку A5 введите 300000.
В ячейку B2 введите “Месяц”.
В ячейку B3 введите “Квартал”.
В ячейку B4 введите “Полугодие”.
В ячейку B5 введите “Год”.

2. Расчет годовой процентной ставки:

Месяц: В ячейку C2 введите формулу =СТАВКА(4*12, A2, -700000, 0, 0)
Квартал: В ячейку C3 введите формулу =СТАВКА(4*4, A3, -700000, 0, 0)
Полугодие: В ячейку C4 введите формулу =СТАВКА(4*2, A4, -700000, 0, 0)
Год: В ячейку C5 введите формулу =СТАВКА(4, A5, -700000, 0, 0)

Результаты:

Платежи

Период платежей

Годовая процентная ставка

25 000

Месяц

13,81%

75 000

Квартал

13,99%

150 000

Полугодие

14,14%

300 000

Год

14,29%

3. Анализ результатов:

Видно, что чем реже выплачиваются платежи, тем выше годовая процентная ставка.

4. Вывод:

Это объясняется тем, что при менее частых платежах кредитор не получает деньги от заемщика так часто, и поэтому компенсирует это более высокой процентной ставкой.

Важно:

В формуле СТАВКА (процентная ставка) используются следующие параметры:

Количество_периодов: Общее количество периодов (4 года * количество периодов в году).
Платеж: Сумма платежа (с отрицательным знаком, так как это расход для заемщика).
Начальная_сумма: Сумма займа (с отрицательным знаком, так как это расход для заемщика).
Будущая_сумма: Обычно 0, так как предполагается, что заем погашается полностью.
Тип: 0, если платеж в конце периода.

Задача 7

1. Данные в Excel:

Создайте таблицу в Excel с заголовками столбцов: “Дата”, “Ставка”, “Количество дней”, “Проценты”.
Введите следующие данные:

В ячейку A2 введите “16.03.20XX”.
В ячейку A3 введите “14.06.20XX”.
В ячейку A4 введите “18.09.20XX”.
В ячейку A5 введите “16.03.20XX”.
В ячейку B2 введите 0,14 (14%).
В ячейку B3 введите 0,13 (13%).
В ячейку B4 введите 0,115 (11,5%).
В ячейку C2 введите 90.
В ячейку C3 введите 95.
В ячейку C4 введите 275 (365 - 90 - 95).

2. Расчет процентов:

В ячейку D2 введите формулу =B2*2000000*C2/365
В ячейку D3 введите формулу =B3*2000000*C3/365
В ячейку D4 введите формулу =B4*2000000*C4/365

3. Суммирование процентов:

В ячейку D5 введите формулу =SUM(D2:D4)

Результаты:

Дата

Ставка

Количество дней

Проценты

16.03.20XX

14%

86,30

14.06.20XX

13%

85,48

18.09.20XX

11,5%

275

172,60

344,38

4. Вывод:

Доход банка от совершенной операции по истечению срока кредитования составит 344 38 рубле

задача 9

1. Данные в Excel:

Создайте таблицу в Excel с заголовками столбцов: “Первоначальная сумма”, “Наращенная сумма”, “Сумма процентов”, “Ставка”, “Срок (дни)”.
Введите следующие данные:

В ячейку A2 введите 200000.
В ячейку B2 введите 0,125.
В ячейку E2 введите 45.
В ячейку A3 введите 360500.
В ячейку B3 введите 0,07.
В ячейку E3 введите 150.
В ячейку A4 введите 202065.
В ячейку B4 введите 0,095.
В ячейку E4 введите 365.
В ячейку A5 введите 800500.
В ячейку C5 введите 1000000.
В ячейку B5 введите 0,1.
В ячейку A6 введите 1000000.
В ячейку C6 введите 2000000.
В ячейку B6 введите 0,085.
В ячейку A7 введите 595000.
В ячейку C7 введите 750000.
В ячейку E7 введите 255.
В ячейку A8 введите 700653.
В ячейку C8 введите 900000.
В ячейку E8 введите 400.

2. Расчет недостающих данных:

Строка 2:

В ячейку C2 введите формулу =A2*B2*E2/365
В ячейку D2 введите формулу =C2/(A2*E2/365)

Строка 3:

В ячейку A3 введите формулу =C3/(B3*E3/365)
В ячейку D3 введите формулу =C3/(A3*E3/365)

Строка 4:

В ячейку C4 введите формулу =A4*B4*E4/365
В ячейку D4 введите формулу =C4/(A4*E4/365)

Строка 5:

В ячейку A5 введите формулу =C5/(1+B5*E5/365)
В ячейку D5 введите формулу =C5/(A5*E5/365)

Строка 6:

В ячейку C6 введите формулу =A6*B6*E6/365
В ячейку D6 введите формулу =C6/(A6*E6/365)

Строка 7:

В ячейку B7 введите формулу =C7/(A7*E7/365)
В ячейку D7 введите формулу =C7/(A7*E7/365)

Строка 8:

В ячейку A8 введите формулу =C8/(1+B8*E8/365)
В ячейку B8 введите формулу =C8/(A8*E8/365)

Результаты:

Первоначальная сумма

Наращенная сумма

Сумма процентов

Ставка

Срок (дни)

200 000

227 397

27 397

12,5%

360 500

388 500

28 000

150

202 065

222 065

20 000

9,5%

365

800 500

1 000 000

199 500

10%

365

1 000 000

2 000 000

1 000 000

8,5%

365

595 000

750 000

155 000

10,4%

255

700 653

900 000

199 347

14,2%

400

Важно:

В формулах используется формула для расчета процентов: Сумма процентов = Первоначальная сумма * Ставка * Срок / 365.
При расчете процентной ставки используется формула: Ставка = Сумма процентов / (Первоначальная сумма * Срок / 365).

срс

. Данные в Excel:

Создайте ячейку, где введите будущую сумму: 2 500 000 (в ячейку A1).
Введите годовую процентную ставку: 8% (в ячейку A2).
Введите срок: 5 лет (в ячейку A3).

2. Расчет современной стоимости:

В ячейку A4 введите формулу: =ПС(A2, A3, 0, -A1, 0)

Результат:

В ячейке A4 будет показана современная стоимость (текущая стоимость будущей суммы) - 1 684 944,13 рублей.

Объяснение:

Функция ПС (Present Value - текущая стоимость) в Excel рассчитывает текущую стоимость будущей суммы, учитывая процентную ставку, количество периодов, платежи и будущую сумму.
В нашем случае, мы используем функцию ПС, чтобы рассчитать, сколько денег нужно вложить сегодня, чтобы через 5 лет получить 2 500 000 рублей, если годовая ставка составляет 8%.
В формуле ПС мы указываем процентную ставку (A2), количество периодов (A3), платеж (0, так как в задаче платежей нет), будущую сумму (A1, с отрицательным знаком, так как это расход) и тип (0, так как платеж осуществляется в конце периода).

Вывод:

Современная стоимость суммы 2 500 000 рублей, которая будет выплачена через 5 лет при ставке 8% годовых, составляет 1 684 944,13 рубля.

Задание 3

1. Данные в Excel:

Создайте ячейку, где введите сумму вклада: 100 000 (в ячейку A1).
Введите годовую процентную ставку: 11% (в ячейку A2).
Введите будущую сумму: 125 000 (в ячейку A3).
Введите ежемесячный платеж: 1000 (в ячейку A4).

2. Расчет срока:

В ячейку A5 введите формулу: =ЧПС(A2/12, 0, -A4, -A1, A3, 0)

Результат:

В ячейке A5 будет показан срок вклада в месяцах - 19,46 месяцев.

Объяснение:

Функция ЧПС (Численность периодов) в Excel рассчитывает количество периодов (месяцев в нашем случае), за которые вклад достигнет определенной суммы, учитывая процентную ставку, платежи, начальную сумму и будущую сумму.
В формуле ЧПС мы указываем процентную ставку за период (A2/12, так как проценты начисляются ежемесячно), платеж (A4, с отрицательным знаком, так как это расход), начальную сумму (A1, с отрицательным знаком, так как это расход), будущую сумму (A3) и тип (0, так как платеж осуществляется в конце периода).

Вывод:

Клиент может разместить 100 000 рублей на срок 19,46 месяцев, чтобы получить 125 000 рублей, при условии ежемесячного пополнения счета на 1000 рублей, при годовой процентной ставке 11% и ежемесячном начислении процентов.