|
Муратбай Молдыр, УиА-22, в.о., (2 года) (4). Использование встроенных функций ms excel в финансовых вычислениях Функции ms excel
Функции MS Excel
Функция
|
Формат функции
|
Назначение функции
|
БС
|
БС (ставка; кпер; плт; пс; тип)
|
Рассчитывает будущую стоимость периодических постоянных платежей и будущее значение вклада (или займа) на основе постоянной процентной ставки
|
ПС
|
ПС (ставка; кпер; плт; бс; тип)
|
Рассчитывает текущую стоимость как единой суммы вклада (займа), так и будущих фиксированных периодических платежей
|
КПЕР
|
КПЕР (ставка; плт; пс; бс; тип)
|
Вычисляет количество периодов начисления процентов, исходя из известных величин процентной ставки, будущей и настоящей стоимости
|
СТАВКА (НОРМА)
|
СТАВКА (кпер; плт; пс; бс; тип; предположение)
|
Вычисляет процентную ставку, которая в зависимости от условий операции может выступать либо в качестве цены, либо в качестве нормы рентабельности
|
ПЛТ
|
ПЛТ (ставка; кпер; пс; бс; тип)
|
Рассчитывает сумму постоянных периодических платежей, необходимых для равномерного погашения займа при известной сумме займа, ставки процентов и срока , на который выдан займ
| Функции MS Excel
Функция
|
Формат функции
|
Назначение функции
|
БЗРАСПИС
|
БЗРАСПИС (первичное; план)
|
Используется для расчета будущей величины разовых инвестиционных вложений, если начисление процентов осуществляется по плавающей процентной ставке
|
НОМИНАЛ
|
НОМИНАЛ (факт_ставка; кол_пер)
|
Рассчитывает номинальную годовую процентную ставку, если известна фактическая ставка и количество периодов, составляющих год
|
ЭФФЕКТ
|
ЭФФЕКТ (номинальная_ставка; кол_пер)
|
Рассчитывает фактическую годовую процентную ставку, если задана номинальная ставка и количество периодов, составляющих год
|
ПРПЛТ
|
ПРПЛТ (ставка; период; кпер; пс; бс; тип)
|
Рассчитывает сумму платежей процентов по инвестициям за данный период на основе постоянства сумм периодических платежей и постоянства процентной ставки
| Функции MS Excel
Функция
|
Формат функции
|
Назначение функции
|
ОСПЛТ
|
ОСПЛТ (ставка; период; кпер; пс; бс; тип)
|
Рассчитывает величину платежа в погашение основной суммы по инвестициям за данный период на основе постоянства сумм периодических платежей и постоянства процентной ставки
|
ОБЩДОХОД
|
ОБЩДОХОД (ставка; кол_пер; нз; нач_период; кон_период)
|
Рассчитывает нарастающим итогом сумму, выплачиваемую в погашение основной суммы займа в промежутке между двумя периодами
|
ОБЩПЛАТ
|
ОБЩПЛАТ (ставка; кол_пер; нз; нач_период; кон_период)
|
Рассчитывает нарастающим итогом величину процентов, выплачиваемых по займу в промежутке между двумя периодами
| Простые проценты Пример. Определить наращенную сумму по вкладу в размере 10000 ден. ед., размещенного под 15% годовых на один год
Ставка
|
15%
|
= 0,15
|
Кпер
|
1
|
= 1
|
Плт
| |
= число
|
Пс
|
-10000
|
= -10000
|
Тип
| |
= число
| | | |
= 11500
| Простые проценты Пример. Вклад в размере 2000 ден. ед. открыт с 06.06.14 по 17.09.14 под 15% годовых. Определить наращенную сумму к 17.09 по различной практике начисления процентов
Английский способ
| | |
Ставка
|
(17.09.2014-06.06.2014-1)/365*15%
|
= 0,041917808
|
Кпер
|
1
|
= 1
|
Плт
| |
= число
|
Пс
|
-2000
|
= -2000
|
Тип
| |
= число
| | | |
= 2083,84
| Простые проценты
Французский способ
| | |
Ставка
|
(17.09.2014-06.06.2014-1)/360*15%
|
= 0,0425
|
Кпер
|
1
|
= 1
|
Плт
| |
= число
|
Пс
|
-2000
|
= -2000
|
Тип
| |
= число
| | | |
= 2085
| Простые проценты
Немецкий способ
| | |
Ставка
|
(17.09.2014-06.06.2014-3)/360*15%
|
= 0,04166667
|
Кпер
|
1
|
= 1
|
Плт
| |
= число
|
Пс
|
-2000
|
= -2000
|
Тип
| |
= число
| | | |
= 2083,33
| Сложные проценты
Ежегодно
| | |
Ставка
|
15%
|
= 0,15
|
Кпер
|
5
|
= 5
|
Плт
| |
= число
|
Пс
|
-10000
|
= -10000
|
Тип
| |
= число
| | | |
= 20113,57
| Сложные проценты
Ежемесячно
| | |
Ставка
|
15%/12
|
= 0,0125
|
Кпер
|
5*12
|
= 60
|
Плт
| |
= число
|
Пс
|
-10000
|
= -10000
|
Тип
| |
= число
| | | |
= 21071,81
| Пример. Организации потребуется 5000000 ден. ед. через 10 лет. Рассчитать, какую сумму необходимо инвестировать сейчас под 12% годовых, чтобы через 10 лет получить планируемую сумму?
Ставка
|
12%
|
= 0,12
|
Кпер
|
10
|
= 10
|
Плт
| |
= число
|
Бс
|
-5000000
|
= -5000000
|
Тип
| |
= число
| | | |
= 1609866,18
| Пример. Организации потребуется 5000000 ден. ед. через 10 лет. Рассчитать, какую сумму необходимо инвестировать сейчас под 12% годовых с ежемесячным начислением процентов, чтобы через 10 лет получить планируемую сумму?
Ставка
|
12%/12
|
= 0,01
|
Кпер
|
10*12
|
= 120
|
Плт
| |
= число
|
Бс
|
-5000000
|
= -5000000
|
Тип
| |
= число
| | | |
= 1514973,9
| Пример. По вкладу в размере 10000 ден. ед., помещенному в банк под 15% годовых, начисляемых ежегодно, была выплачена сумма 18000 ден. ед. Рассчитать срок проведения операции или количество периодов начисления процентов
Ставка
|
15%
|
= 0,15
|
Плт
| |
= число
|
Пс
|
-10000
|
= -10000
|
Бс
|
18000
|
= 18000
|
Тип
| |
= число
| | | |
= 4,205627
| Пример. Через сколько лет вклад размером 10000 ден. ед., помещенный в банк под 15% годовых, начисляемых ежемесячно, достигнет величины 18000 ден. ед.?
Ставка
|
15%/12
|
= 0,125
|
Плт
| |
= число
|
Пс
|
-10000
|
= -10000
|
Бс
|
18000
|
= 18000
|
Тип
| |
= число
| | | |
= 47,316
|
47,316/12 = 3,94 лет
| | | Пример. Как только вам исполнилось 20 лет, вы решили ежемесячно вносить в банк по 500 ден. ед. К какому возрасту вы сможете накопить 1000000 ден. ед., если банк предлагает 10% годовых, начисляемых ежемесячно?
Ставка
|
10%/12
|
= 0,00833333
|
Плт
|
-500
|
= -500
|
Пс
| |
= число
|
Бс
|
1000000
|
= 1000000
|
Тип
| |
= число
| | | |
= 346,04
|
346,04/12 = 28,84 лет
| | | Пример. Через 2 года организации потребуется 100000 ден. ед. Планируется инвестировать 25000 ден. ед. Под какой уровень доходности следует инвестировать финансовые ресурсы, чтобы достичь запланированной величины?
Кпер
|
2
|
= 2
|
Плт
| |
= число
|
Пс
|
-25000
|
=-25000
|
Бс
|
100000
|
= 100000
|
Тип
| |
= число
| | | |
= 1
|
1 х 100% = 100%
| | | Пример. Через 2 года организации потребуется 100000 ден. ед. Планируется инвестировать 25000 ден. ед. Под какой уровень доходности следует инвестировать финансовые ресурсы, чтобы достичь запланированной величины, если проценты начисляются ежемесячно?
Кпер
|
2*12
|
= 24
|
Плт
| |
= число
|
Пс
|
-25000
|
=-25000
|
Бс
|
100000
|
= 100000
|
Тип
| |
= число
| | | |
= 0,0594630
|
5,95% х 12 = 71,36%
| | | Определение процентной ставки. Функция НОМИНАЛ Пример. Эффективная процентная ставка составляет 28% годовых. Начисление процентов – ежемесячное. Рассчитать номинальную процентную ставку
Факт_ставка
|
28%
|
= 0,28
|
Кол_пер
|
12
|
= 12
| | | |
= 0,249417
|
0,249417 х 100% = 24,94%
| | | Определение будущей суммы при плавающей процентной ставке. Функция БЗРАСПИС Функция БЗРАСПИС используется в случае, когда начисление процентов по инвестициям осуществляется по плавающей процентной ставке Пример. Ставка процента по срочным депозитам на начало года составляет 20% годовых, начисляемых ежеквартально. Первоначальная сумма вклада – 100000 ден. ед. В течение года ожидается снижение ставок раз в квартал на 2, 3 и 5 пунктов соответственно. Рассчитать величину вклада к концу года | |
А
|
В
|
1
|
Сумма вклада
|
100000
|
2
|
Ставка процента
|
20%
|
3
|
Ставка процента 1 квартал
|
5%
|
4
|
Ставка процента 2 квартал
|
4,5%
|
5
|
Ставка процента 3 квартал
|
3,75%
|
6
|
Ставка процента 4 квартал
|
2,5%
| | | |
БЗРАСПИС(B1;B3:B6)
| | |
Сумма вклада к концу года
|
116685,68
| Аннуитет Пример. На счет в банке в течение 5 лет в конце каждого года будет вноситься сумма в размере 50000 ден. ед. Процентная ставка – 12% годовых. Определить размер вклада к концу пятого года
Ставка
|
12%
|
= 0,12
|
Кпер
|
5
|
= 5
|
Плт
|
-50000
|
= -50000
|
Пс
| |
= число
|
Тип
| |
= число
| | | |
= 317642,37
| Аннуитет Пример. На счет в банке в течение 5 лет в конце каждого месяца будет вноситься сумма в размере 5000 ден. ед. Процентная ставка – 12% годовых. Начисление процентов – ежемесячное. Определить размер вклада к концу пятого года
Ставка
|
12%/12
|
= 0,01
|
Кпер
|
5*12
|
= 60
|
Плт
|
-5000
|
= -5000
|
Пс
| |
= число
|
Тип
| |
= число
| | | |
= 408348,35
| Пример. Ежегодно на протяжении 5 лет в банк в конце каждого года будут вноситься денежные суммы в размере 100000 ден. ед., 200000 ден. ед., 200000 ден. ед., 300000 ден. ед., 300000 ден. ед. соответственно. Ставка процента – 12% годовых. Проценты начисляются в конце года. Определить наращенную сумму к концу 5 года | |
А
|
В
|
1
|
Период
|
Сумма
|
2
|
1
|
-100000
|
3
|
2
|
-200000
|
4
|
3
|
-200000
|
5
|
4
|
-300000
|
6
|
5
|
-300000
| | |
Сумма вклада к концу периода
|
1325217,54
| Пример. Ежегодно на протяжении 5 лет в банк в конце каждого года вносились денежные суммы в размере 100000 ден. ед., 200000 ден. ед., 200000 ден. ед., 300000 ден. ед., 300000 ден. ед. соответственно. Ставка процента – 12% годовых. Проценты начисляются в конце года. Определить наращенную сумму к концу 5 года
Ставка
|
12%
|
= 0,12
|
Кпер
|
5 – А2:А6
|
=
|
Плт
| |
= число
|
Пс
|
В2:В6
|
= В2:В6
|
Тип
| |
= число
|
СУММ(БС(0,12;5-А2:А6;;В2:В6))
| | |
Нажать клавиши Ctrl+Shift+Enter
| | | Аннуитет Пример. Планируется получать ежегодный доход в размере 100000 ден. ед. на протяжении 4 лет. Какая сумма обеспечит получение такого дохода, если ставка процента по депозиту составляет 10% годовых?
Ставка
|
10%
|
= 0,10
|
Кпер
|
4
|
= 4
|
Плт
|
-100000
|
= -100000
|
Бс
| |
= число
|
Тип
| |
= число
| | | |
= 316986,54
| Аннуитет Пример. Планируется получать ежемесячный доход в размере 10000 ден. ед. на протяжении 4 лет. Какая сумма обеспечит получение такого дохода, если ставка процента по депозиту составляет 10% годовых, начисление процентов – ежемесячное?
Ставка
|
10%/12
|
= 0,0083333
|
Кпер
|
4*12
|
= 48
|
Плт
|
-10000
|
= -10000
|
Бс
| |
= число
|
Тип
| |
= число
| | | |
= 394281,6
| Аннуитет Пример. Рассматривается 2 варианта приобретения недвижимости стоимостью 100000 ден. ед.: единовременный платеж или ежемесячно в течение 15 лет вносить платеж в размере 1000 ден. ед. Определить, какой из вариантов предпочтительнее, если ставка процента по банковскому депозиту – 8% годовых, начисляемых ежемесячно
Ставка
|
8%/12
|
= 0,00666666667
|
Кпер
|
15*12
|
= 180
|
Плт
|
-1000
|
= -1000
|
Бс
| |
= число
|
Тип
| |
= число
| | | |
= 104640,59
|
104640,59 100000
| | | Пример. Планируется накопить за 6 лет 40000 ден. ед., делая ежегодные равные вклады в банк, который выплачивает проценты по ставке 10% годовых. Какую сумму следует ежегодно вкладывать в банк?
Ставка
|
10%
|
= 0,1
|
Кпер
|
6
|
= 6
|
Плт
| |
= число
|
Бс
|
-40000
|
= -40000
|
Тип
| |
= число
| | | |
= 5184,3
| Пример. Планируется накопить за 6 лет 40000 ден. ед., делая ежеквартальные равные вклады в банк, который выплачивает проценты по ставке 10% годовых. Какую сумму следует ежеквартально вкладывать в банк?
Ставка
|
10%/4
|
= 0,025
|
Кпер
|
6*4
|
= 24
|
Плт
| |
= число
|
Бс
|
-40000
|
= -40000
|
Тип
| |
= число
| | | |
= 1236,51
| Пример. Необходимо рассчитать величину платежей по процентам за первый месяц по кредиту в сумме 800000 ден. ед., выданному на 3 года под 15% годовых
Ставка
|
15%/12
|
= 0,0125
|
Период
|
1
|
= 1
|
Кпер
|
3*12
|
= 36
|
Пс
|
-800000
|
= -800000
|
Бс
| |
= число
| | | |
= 10000
| Пример. За счет ежегодных отчислений в течение 6 лет было накоплено 5000000 ден. ед. Необходимо определить, какой доход принесли вложения за последний год, если рыночная доходность составляет 12% годовых
Ставка
|
12%
|
= 0,12
|
Период
|
6
|
= 6
|
Кпер
|
6
|
= 6
|
Пс
| |
= число
|
Бс
|
5000000
|
= 5000000
| | | |
= 469700,51
| Пример. В банке был получен кредит в сумме 12 млн. ден. ед. под 12% годовых на срок 15 лет. Проценты по кредиту начисляются ежемесячно. Необходимо рассчитать сумму процентных выплат за 2-й год и за 1-ый месяц займа
Ставка
|
12%/12
|
= 0,01
|
Кол_пер
|
15*12
|
= 180
|
Нз
|
12000000
|
= 12000000
|
Нач_период
|
13
|
= 13
|
Кон_период
|
24
|
= 24
|
Тип
|
0
|
= 0
| | | |
= -1384970,71
| Пример. В банке был получен кредит в сумме 12 млн. ден. ед. под 12% годовых на срок 15 лет. Проценты по кредиту начисляются ежемесячно. Необходимо рассчитать сумму процентных выплат за 2-й год и за 1-ый месяц займа
Ставка
|
12%/12
|
= 0,01
|
Кол_пер
|
15*12
|
= 180
|
Нз
|
12000000
|
= 12000000
|
Нач_период
|
1
|
= 1
|
Кон_период
|
1
|
= 1
|
Тип
|
0
|
= 0
| | | |
= -120000
|
1 год
| | |
Ставка
|
17%
|
= 0,17
|
Период
|
1
|
= 1
|
Кпер
|
3
|
= 3
|
Пс
|
-700000
|
= -700000
|
Бс
| |
= число
| | | |
= 197801,6
|
2 год
| | |
Ставка
|
17%
|
= 0,17
|
Период
|
2
|
= 2
|
Кпер
|
3
|
= 3
|
Пс
|
-700000
|
= -700000
|
Бс
| |
= число
| | | |
= 231427,8
|
3 год
| | |
Ставка
|
17%
|
= 0,17
|
Период
|
3
|
= 3
|
Кпер
|
3
|
= 3
|
Пс
|
-700000
|
= -700000
|
Бс
| |
= число
| | | |
= 270770,6
|
197801,6 + 231427,8 + 270770,6 = 700000
| | |
ПЛТ = ПРПЛТ + ОСПЛТ
| | | Расчет основных платежей по кредиту. Функция ОБЩДОХОД Пример. Кредит выдан на сумму 1000000 ден. ед. на 6 лет под 15% годовых, начисляемых ежеквартально. Необходимо определить сумму основных выплат по кредиту за пятый год
Ставка
|
15%/4
|
= 0,0375
|
Кол_пер
|
6*4
|
= 24
|
Нз
|
1000000
|
= 1000000
|
Нач_период
|
17
|
= 17
|
Кон_период
|
20
|
= 20
|
Тип
|
0
|
= 0
| | | |
= -201434,76
| Подбор параметра используется в том случае, когда существует ситуация неполного набора показателей, необходимых для проведения оценки эффективности финансовых операций. В этом случае необходимо рассчитать недостающий показатель, преобразуя соответствующим образом формулы АНАЛИЗ «ЧТО ЕСЛИ» ПОДБОР ПАРАМЕТРА Пример. Через 5 лет потребуется 250000 ден. ед. Какую денежную сумму следует вносить ежемесячно в банк, чтобы накопить 250000 ден. ед. за 5 лет, если банк предлагает 12% годовых | |
А
|
В
|
1
|
Ставка
|
12%
|
2
|
Число периодов (Кпер)
|
5
|
3
|
Платеж (Плт)
| |
4
|
Наращенная сумма
|
БС
|
Ставка
|
В1/12
|
Кпер
|
В2*12
|
Плт
|
В3
|
Пс
| |
Тип
|
0
| | |
А
|
В
|
1
|
Ставка
|
12%
|
2
|
Число периодов (Кпер)
|
5
|
3
|
Платеж (Плт)
|
-3061,11 р.
|
4
|
Наращенная сумма
|
0,00 р.
|
Подбор параметра
| |
Установить в ячейке
|
В4
|
Значение
|
250000
|
Изменяя значение в ячейке
|
В3
|
ОК
|
Отмена
| |
|
|
Скачать 290.6 Kb.