Определение параметров кредита для принятия оптимального решения с помощью стандартных функций Excel. Поиск оптимальное решение зависит от задач пользователя и достигается посредством корректировки начальных условий и сравнении полученных результатов.
Расчет платежа
ПЛТ( ставка; кпер; пс; [бс]; [тип] ) — возвращает сумму периодического платежа для аннуитета на основе постоянства сумм платежей и постоянной процентной ставки.
| ставка | процентная ставка по ссуде |
| кпер | общее число выплат по ссуде |
| пс | приведенная к текущему моменту стоимость или общая сумма, которая на текущий момент равноценна ряду будущих платежей, называемая также основной суммой. |
| бс | будущая стоимость, или величина остатка денежных средств, которая нужна после последней выплаты. Если бс опущен, предполагается значение 0 (ноль), то есть будущая стоимость для займа составляет 0. |
| тип | когда должна производиться выплата ( 0 или опущен – в конце периода, 1 – в начале) |
Замечания:
- Выплаты, возвращаемые функцией ПЛТ, включают основные платежи и платежи по процентам, но не включают налогов, резервных платежей или комиссий, иногда связываемых со ссудой.
- Убедитесь, что вы последовательны в выборе единиц измерения для задания аргументов «ставка» и «кпер». Если вы делаете ежемесячные выплаты по четырехгодичному займу из расчета 12 процентов годовых, то используйте значения 12%/12 для задания аргумента «ставка» и 4*12 для задания аргумента «кпер». Если вы делаете ежегодные платежи по тому же займу, то используйте 12 процентов для задания аргумента «ставка» и 4 для задания аргумента «кпер».
Применение:
- Расчет платежа, на основании % ставки, суммы займа и периода выплаты.
Для кредита: S, с процентной ставкой P, сроком Y лет с ежемесячными выплатами в конце месяца формула будет:
ПЛТ( P / 12; Y * 12; S; 0; 0 )
- Расчет пополнений для накопления определенной суммы в течении заданного времени.
Пусть требуется накопить сумму S, за Y лет, размещая взносы на банковском вкладе с процентной ставкой P, проценты начисляются ежемесячно в конце месяца, формула будет:
ПЛТ( P / 12; Y * 12; 0; S; 0 )
Расчет переплаты
ПРПЛТ( ставка ; период ; кпер ; пс ; [бс] ; [тип] ) — Возвращает сумму платежей по процентам для инвестиции за данный период на основе постоянства сумм периодических платежей и постоянства процентной ставки.
| ставка | процентная ставка по ссуде |
| период | период, для которого требуется найти платежи по процентам; число в интервале от 1 до «кпер» |
| кпер | общее число выплат по ссуде |
| пс | приведенная к текущему моменту стоимость или общая сумма, которая на текущий момент равноценна ряду будущих платежей, называемая также основной суммой. |
| бс | будущая стоимость, или величина остатка денежных средств, которая нужна после последней выплаты. Если бс опущен, предполагается значение 0 (ноль), то есть будущая стоимость для займа составляет 0. |
| тип | когда должна производиться выплата ( 0 или опущен – в конце периода, 1 – в начале) |
Применение:
- Расчет переплаты за конкретный период
Для кредита: S, с процентной ставкой P, сроком Y лет с ежемесячными выплатами в конце месяца формула будет сумма переплаты за X месяц будет:
ПРПЛТ( P / 12; X; Y * 12; S; 0; 0 )
- Расчет полная переплаты по кредиту
Для кредита: S, с процентной ставкой P, сроком Y лет с ежемесячными выплатами в конце месяца рассчитать сумму переплаты за весь срок:
Возможные способы расчета в зависимости от подготовки пользователя
- Определить сумму переплаты для каждого месяца и просуммировать полученные значения
- задать «числовую последовательность» на месте «периода» в виде {1:2: … : ( до Y * 12 ) } и воспользоваться в формулой (подходит для расчета краткосрочных кредитов):
СУММ( ПРПЛТ( P / 12; {1:2: … : ( до Y * 12 ) }; Y *12; S; 0; 0 ) )
- задать «числовую последовательность» с помощью функции VBA
Function seq( count ) As Variant Dim r() As Integer ReDim r(1 To count ) For i = 1 To count r(i) = i Next i seq = r End Function
Воспользоваться формулой:
СУММ( ПРПЛТ( P / 12; seq( Y * 12 ); Y * 12; S; 0; 0 ) )
Ввода формулы закончить нажатием «Ctrl» + «Shift» + «Enter», а не «Enter», это связано с особенностью обработки функций, возвращающих массивы значений.