Ставка кпер плт пс бс тип предположение это финансовая функция в excel для вычисления

Обновлено: 07.07.2024

В этой статье описаны синтаксис формулы и использование функции СТАВКА в Microsoft Excel.

Описание

Синтаксис

СТАВКА(кпер; плт; пс; [бс]; [тип]; [прогноз])

Примечание: Полное описание аргументов "кпер", "плт", "пс", "бс" и "тип" см. в разделе, посвященном функции ПС.

Аргументы функции СТАВКА описаны ниже.

Кпер — обязательный аргумент. Общее количество периодов платежей по аннуитету.

Плт Обязательный. Выплата, производимая в каждый период и не меняющаяся на протяжении всего периода ежегодного платежа. Обычно аргумент "плт" состоит из основного платежа и платежа по процентам, но не включает других налогов и сборов. Если он опущен, аргумент "пс" является обязательным.

Пс — обязательный аргумент. К настоящему моменту — общая сумма, на которую сейчас стоит ряд будущих платежей.

Fv Необязательный. Будущая стоимость или баланс, который вы хотите достичь после последнего платежа. Если значение "ок" опущено, предполагается значение 0 (например, будущая стоимость займа — 0). Если аргумент "пс" опущен, необходимо включить аргумент "pmt".

Тип Необязательный. Число 0 или 1, обозначающее, когда должна производиться выплата.

Когда нужно платить

В конце периода

В начале периода

Прогноз Необязательный. Предполагаемая величина ставки.

Если аргумент "прогноз" опущен, предполагается, что его значение равно 10 %.

Если функция СТАВКА не сходится, попробуйте изменить значение аргумента "прогноз". Функция СТАВКА обычно сходится, если значение этого аргумента находится между 0 и 1.

Замечания

Убедитесь, что единицы измерения, выбранные для аргументов "прогноз" и "кол_пер" соответствуют друг другу. При ежемесячных выплатах по четырехгодичному займу под 12 процентов годовых используйте значение 12%/12 для аргумента "прогноз" и 4*12 — для аргумента "кол_пер". При ежегодных платежах по тому же займу используйте значение 12% для аргумента "прогноз" и 4 —для аргумента "кол_пер".

Пример

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

В статье рассмотрены финансовые функции ПЛТ() , ОСПЛТ() , ПРПЛТ() , КПЕР() , СТАВКА() , ПС() , БС() , а также ОБЩДОХОД() и ОБЩПЛАТ() , которые используются для расчетов параметров аннуитетной схемы.

Данная статья входит в цикл статей о расчете параметров аннуитета. Перечень всех статей на нашем сайте об аннуитете размещен здесь .

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

Немного теории

Аннуитет (иногда используются термины «рента», «финансовая рента») представляет собой однонаправленный денежный поток, элементы которого одинаковы по величине и производятся через равные периоды времени (например, когда платежи производятся ежегодно равными суммами).

Каждый элемент такого денежного потока называется членом аннуитета , а величина постоянного временного интервала между двумя его последовательными элементами называется периодом аннуитета . В широком смысле, аннуитетом может называться как сам финансовый инструмент, так и сумма периодического платежа. Исторически вначале рассматривались равные ежегодные денежные поступления (период между платежами принимался равным одному году), что и послужило основой для именования денежного потока аннуитетом («год» на латинском языке — anno). В дальнейшем, в качестве периода стал выступать любой промежуток времени, но прежнее название сохранилось. Сейчас период аннуитета чаще всего равен одному месяцу.

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

На картинке ниже приведен пример погашения кредита (100 000 руб.) ежемесячными платежами в течение 5 лет при ставке 15%. Для погашения тела кредита и начисленных процентов потребуется произвести 60 платежей (5 лет*12мес в году). Сумма ежемесячного платежа = 2378,99руб. См. файл примера Лист Аннуитет (ПЛТ) . Как видно из графика платежей, банк в первые периоды получает платежи, идущие на погашение %, а тело кредита сокращается медленно (см. статью Сравнение графиков погашения кредита дифференцированными и аннуитетными платежами в MS EXCEL ).

Если каждый элемент аннуитета имеет место в конце соответствующего периода, аннуитет называется аннуитетом постнумерандо (Ordinary Annuity); если в начале периода — аннуитетом пренумерандо (Annuity Due). Обычно используется аннуитет постнумерандо.

Примечание . В функциях MS EXCEL для указания типа аннуитета предусмотрен специальный необязательный параметр [тип] . По умолчанию тип =0 (выплаты в конце периода), что соответствует аннуитету постнумерандо. Если тип =1, то предполагается аннуитет пренумерандо (выплаты в начале периода).

Часто в расчетах используют понятие аннуитетный коэффициент (А):

A = -Ставка * (1+ Ставка)^Кпер / (1-(1+ Ставка)^ Кпер ) / (1+ Ставка*Тип)

где: Ставка — процентная ставка за период; Кпер — общее количество периодов выплаты; Тип – для аннуитета постнумерандо Тип=0, для пренумерандо Тип=1.

Чтобы вычислить член аннуитета (величину регулярного платежа) нужно использовать формулу =А*ПС, где ПС – это начальная сумма кредита. Специфика аннуитета (равенство денежных поступлений) позволяет вывести стандартизованные формулы, существенно упрощающие счетные процедуры. Об этих формулах и об их использовании в MS EXCEL и пойдет речь ниже.

Параметры функций аннуитета

Финансовые функции ПЛТ() , ОСПЛТ() , ПРПЛТ() , КПЕР() , СТАВКА() , БС() , ПС() , а также ОБЩДОХОД() и ОБЩПЛАТ() тесно связаны между собой, т.к. все они вычисляют параметры аннуитета и, соответственно, используют один и тот же набор аргументов. В этом можно убедиться, перечислив все функции вместе с аргументами:

ПЛТ(ставка; кпер; пс; [бс]; [тип]) ОСПЛТ(ставка; период; кпер; пс; [бс]; [тип]) ПРПЛТ(ставка; период; кпер; пс; [бс]; [тип]) КПЕР(ставка; плт; пс; [бс]; [тип]) СТАВКА(кпер; плт; пс; [бс]; [тип]; [предположение]) БС(ставка; кпер; плт; [пс]; [тип]) ПС(ставка; кпер; плт; [бс]; [тип])

ПЛТ (английское название функции: PMT, от слова payment ). Регулярный платеж, осуществляемый каждый период. Платеж – постоянная величина, она не меняется в течение всего срока аннуитета. Ставка (англ.: RATE, interest). Процентная ставка за период , чаще всего за год или за месяц. Обычно задается через годовую ставку, деленную на количество периодов в году. При годовой ставке 10% месячная ставка составит 10%/12. Ставка не изменяется в течение всего срока аннуитета. Кпер (англ.: NPER). Общее число периодов платежей по аннуитету . Если кредит взят на 5 лет, а выплаты производятся ежемесячно, то всего 60 периодов (12 мес. в году * 5 лет) Бс (англ.: FV, future value). Будущая стоимость в конце срока аннуитета (по истечении числа периодов Кпер). Бс - требуемое значение будущей стоимости или остатка средств после последней выплаты. Например, в случае расчета аннуитетного платежа для полной выплаты ссуды к концу срока Бс = 0, т.к. ссуда в конце срока должна быть полностью погашена. Пс (англ.: PV, present value). Приведенная стоимость , т.е. стоимость приведенная к определенному моменту (часто к текущему, т.е. настоящему времени). Если взят кредит и производятся регулярные выплаты по аннуитетной схеме, то Приведенная стоимость – это сумма кредита. Если планируется регулярно вносить равновеликие платежи на счет в банке (и период начисления % совпадает с периодом платежей), то Приведенную стоимость также нужно указывать = 0. Тип (англ.: type). Число 0 или 1, обозначающее, когда должна производиться выплата (и соответственно начисление процентов). 0 – в конце периода, 1 – в начале. Подробнее см. раздел Немного теории в начале статьи о постнумерандо и пренумерандо или статьи с примерами, указанные выше.

Все 6 аргументов (параметров аннуитета) связаны между собой выражением:

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

Примечание . Формула 1 работает, если Ставка не равна 0. Если ставка равна 0, то вместо Формулы 1 действует гораздо более простое выражение: ПЛТ * Кпер + ПС + БС = 0 (в этом случае схема платежей перестает быть аннуитетом и превращается в беспроцентную ссуду).

О направлениях денежных потоков и знаках ПС, БС и ПЛТ

Вышеуказанная Формула 1 предполагает, что знаки денежных потоков (+/-) указываются с учетом их направления. Например, банк выдал кредит (ПС>0), клиент банка ежемесячно вносит одинаковый платеж (ПЛТ ПЛТ() возвращает отрицательные значения, если ПС>0.

Тождество аннуитета

Если Тип=0, то для функций MS EXCEL справедливо тождество: ОБЩДОХОД(за все периоды) + ПС + БС = 0

Это тождество можно переписать в другом виде: СУММ(ОСПЛТ()) + ПС + БС = 0. В случае использования аннуитетной схемы погашения кредита (сумма кредита =ПС), выражение СУММ(ОСПЛТ()) вычисляет общую сумму платежей, идущих на оплату основной суммы долга (тело кредита). В случае полного погашения кредита БС=0, а тождество превращается в ПС=-СУММ(ОСПЛТ()).

Функции MS EXCEL для расчета параметров аннуитета

Теперь кратко рассмотрим функции MS EXCEL. Для того, чтобы нижесказанное было понятным, необходимо предварительно ознакомиться с теорией аннуитета, понятиями Будущая и Приведенная стоимость.

Функция ПЛТ(ставка; кпер; пс; [бс]; [тип]) рассчитывает величину регулярного платежа на основе заданных 5 аргументов.

Примечание . Английский вариант функции: PMT(rate, nper, pv, [fv], [type]), т.е. PayMenT – платеж.

Для понимания работы формулы приведем эквивалентное ей выражение для расчета платежа:

Формула 2 есть не что иное, как решение Формулы 1 относительно параметра ПЛТ.

Примечание. В файле примера на листе Аннуитет (без ПЛТ) приведен расчет ежемесячных платежей без использования финансовых функций EXCEL.

Если процентная ставка = 0, то Формула 2 упростится до =(ПС + БС)/Кпер

Если Тип=0 (выплата в конце периода) и БС =0, то Формула 2 заметно упрощается:

В случае применения схемы аннуитета для выплаты ссуды платеж включает денежную сумму в счет погашения части ссуды и сумму для оплаты начисленных за прошедший период процентов, поэтому функция ПЛТ() связана с ОСПЛТ() и ПРПЛТ() соотношением ПЛТ = ОСПЛТ + ПРПЛТ (для каждого периода).

Примечание . В файле примера на листе Зависимости ПЛТ() приведены графики: Зависимость суммы платежа от размера ссуды, Зависимость суммы платежа от ставки, Зависимость суммы платежа от срока ссуды. Также в файле примера приведены некоторые задачи.

Функция ОСПЛТ(ставка; период; кпер; пс; [бс]; [тип]) используется для вычисления регулярных сумм идущих на погашение основной суммы долга практически с теми же аргументами, что и ПЛТ() . Т.к. сумма идущая на погашение основной суммы долга изменяется от периода к периоду, то необходим еще один аргумент период , который определяет к какому периоду относится сумма.

Примечание . Английский вариант функции: PPMT(rate, per, nper, pv, [fv], [type]), т.е. Principal Payment – платеж основной части долга.

В случае применения схемы аннуитета для выплаты ссуды для каждого периода действует равенство: ОСПЛТ =ПЛТ – ПРПЛТ, т.к. платеж включает сумму в счет погашения части ссуды (ОСПЛТ) и сумму для оплаты начисленных за прошедший период процентов (ПРПЛТ). Сумму, идущую на погашение основной суммы долга также можно вычислить, зная величину платежа (ПЛТ), период (Период), общее количество периодов (Кпер) и ставку (СТАВКА):

Вышеуказанная формула работает при БС=0. При ТИП=1 (платеж в начале периода) и n=1 (первый платеж), ПРПЛТ=ПЛТ Если БС<>0, то формула усложнится:

Функцию ОСПЛТ() часто применяют при составлении графика платежей по аннуитетной схеме (см. Выплата основной суммы долга в аннуитетной схеме. Расчет в MS EXCEL )

Примечание . В файле примера на листе Аннуитет (без ПЛТ) определена аналитическая зависимость суммы идущей на погашение долга от номера периода.

Функция ПРПЛТ (ставка; период; кпер; пс; [бс]; [тип]) используется для вычисления регулярных сумм идущих на погашение процентов за ссуду используется с теми же аргументами, что и ОСПЛТ() .

Примечание. Английский вариант функции: IPMT(rate, per, nper, pv, [fv], [type]), т.е. Interest Payment – выплата процентов.

В случае применения схемы аннуитета для выплаты ссуды для каждого периода действует равенство: ПРПЛТ =ПЛТ – ОСПЛТ

Сумму, идущую на погашение процентов за ссуду, можно вычислить зная: величину платежа (ПЛТ), период (Период), общее количество периодов (Кпер) и ставку (СТАВКА):

Вышеуказанная формула работает при БС=0. При ТИП=1 (платеж в начале периода) и n=1 (первый платеж), ПРПЛТ=0 Если БС<>0, то формула усложнится:

Соотношение выплат основной суммы долга и на погашение начисленных процентов за период хорошо демонстрирует график, приведенный в файле примера .

Функцию ПРПЛТ() часто применяют при составлении графика платежей по аннуитетной схеме (см. Аннуитет. Расчет в MS EXCEL выплаченных процентов за период ).

Функция КПЕР(ставка; плт; пс; [бс]; [тип]) позволяет вычислить количество периодов, через которое текущая сумма вклада (пс) станет равной заданной сумме (бс) при известной процентной ставке за период (ставка) и известной величине пополнения вклада (плт). При этом предполагается, сумма пополнения вклада вносится регулярно в каждый период, тогда же происходит и начисление процентов. Сумма пополнения вклада может быть равна 0 (вклад не пополняется, рост вклада осуществляет только за счет капитализации процентов). Бс (будущая стоимость) может быть =0 или опущена. Также функцию КПЕР() можно использовать для определения количества периодов, необходимых для погашения долга по ссуде (погашение осуществляется регулярно равными платежами, ставка не изменяется весь срок, на который выдана ссуда, процент начисляется каждый период на остаток ссуды).

Примечание . Английский вариант функции: NPER(rate, pmt, pv, [fv], [type]), т.е. Number of Periods – число периодов.

Эквивалентная формула для расчета платежа:

Если ставка равна 0, то: Кпер = (Пс + Бс) /ПЛТ

Подробнее про функцию можно прочитать в статье Аннуитет. Расчет в MS EXCEL количества периодов .

Функция СТАВКА(кпер; плт; пс; [бс]; [тип]; [предположение]) возвращает процентную ставку по аннуитету.

Примечание . Английский вариант функции: RATE(nper, pmt, pv, [fv], [type], [guess]), т.е. Number of Periods – число периодов.

Подробнее про функцию можно прочитать в статье Аннуитет. Определяем процентную ставку в MS EXCEL .

Функция БС(ставка; кпер; плт; [пс]; [тип]) возвращает будущую стоимость инвестиции на основе периодических постоянных (равных по величине сумм) платежей и постоянной процентной ставки. Например, если у Вас сейчас на банковском счете сумма ПС (ПС м.б. =0) и вы ежемесячно вносите одну и туже сумму ПЛТ, то функция вычислит остаток на Вашем банковском счете через Кпер месяцев (предполагается, что капитализация процентов происходит также ежемесячно с процентной ставкой равной величине СТАВКА).

Примечание . Английский вариант функции: FV(rate, nper, pmt, [pv], [type]), т.е. Future Value – будущая стоимость.

Вычисления в функции БС() производятся по этой формуле:

Если СТАВКА =0, то Будущую стоимость можно определить по формуле БС= - ПЛТ * Кпер + ПС

Подробнее про функцию можно прочитать в статье Аннуитет. Определяем в MS EXCEL Будущую Стоимость .

Функция ПС(ставка; кпер; плт; [бс]; [тип]) возвращает приведенную (к текущему моменту) стоимость инвестиций . Приведенная (нынешняя) стоимость представляет собой общую сумму, которая на настоящий момент равноценна ряду будущих регулярных выплат ПЛТ за количество периодов Кпер. Также предполагается, что капитализация процентов происходит также регулярно с процентной ставкой равной величине СТАВКА.

Примечание . Английский вариант функции: PV(rate, nper, pmt, [fv], [type]), т.е. Present Value – будущая стоимость.

Вычисления в функции ПС() производятся по этой формуле:

Если СТАВКА =0, то Приведенную стоимость можно определить по формуле ПС=-БС-ПЛТ*Кпер

Функции ОБЩДОХОД() и ОБЩПЛАТ() Аргументы функций ОБЩДОХОД() и ОБЩПЛАТ() несколько отличаются от рассмотренных выше. Но на самом деле разница только в их названии: кол_пер – это кпер; нз – это пс. Нач_период и кон_период – это «начальный период» и «конечный период».

Функция ОБЩДОХОД(ставка; кол_пер; нз; нач_период; кон_период; тип) возвращает кумулятивную (нарастающим итогом) сумму, выплачиваемую в погашение основной суммы займа в промежутке между двумя периодами ( нач_период и кон_период ).

Примечание . Английский вариант функции: CUMPRINC(rate, nper, pv, start_period, end_period, type) returns the CUMulative PRincipal paid for an investment period with a Constant interest rate.

Функция ОБЩПЛАТ(ставка; кол_пер; нз; нач_период; кон_период; тип) возвращает кумулятивную (нарастающим итогом) величину процентов, выплачиваемых по займу в промежутке между двумя периодами выплат ( нач_период и кон_период ).

Примечание . Английский вариант функции: CUMIPMT(rate, nper, pv, start_period, end_period, type) returns the CUMulative Interest paid on a loan between start_period and end_period.

Общую сумму выплат по займу между двумя периодами (Нач_период и кон_период) можно найти сложив результаты возвращаемые ОБЩПЛАТ() и ОБЩДОХОД() с одинаковыми аргументами, что эквивалентно ПЛТ*(кон_период - Нач_период+1).

Функция ПЛТ (PMT) – возвращает сумму периодического платежа на основе постоянства сумм платежей и постоянства процентной ставки.

СТАВКА – Удельная ставка за период займа.
КПЕР– общее число периодов выплат.
ПС– текущая стоимость: общая сумма всех будущих платежей с настоящего момента.
БС – будущая стоимость или баланс наличности, которую нужно достичь после последней выплаты.Если аргумент БС опущен, то он полагается равным 0 (нулю), т. е. для займа, например, значение БС равно 0.
ТИП– логическое значение (0 или 1), обозначающее, должна ли производиться выплата в конце периода (0) или в начале периода (1).

Функция ПЛТ может быть использована для анализа всевозможных ссуд. Необходимым условием является непротиворечивость аргументов функции.

Пример 1. Предположим, что нужно воспользоваться 9-процентной 15-летней ссудой. Объем ссуды составляет 150 000 000 рублей. C помощью Мастера функций можно определить величины ежемесячных выплат. Предварительно следует привести все другие значения к месячной норме.

Ввести таблицу (рис. 9.1 рис. 9.1 ), начиная с ячейки А1:

Рис. 9.1. Определение величины ежемесячных выплат

В ячейки В 3 и В 4 ввести соответствующие формулы.

Процентная ставка (СТАВКА) – годовая, поэтому для получения месячной ставки (Удельная ставка) соответствующее значение делится на 12 (0,09/12).

Срок действия ссуды – 15 лет, поэтому с учетом 12 платежей год общее количество месячных выплат (КПЕР) составит 12х15.

Для ячейки В6 пошаговыми действиями Мастера функций выполните настройку функции ПЛТ. Для вызова Мастера функций необходимо выбрать команду Вставить функцию (значок f_x ) в меню Формулы.

После этого в поле Значение диалогового окна Мастера функций вы увидите сумму ежемесячного взноса. А после нажатия на кнопку Готово результат отобразится в ячейке.

Примечание. Необходимо исходные данные заносить в ячейки на рабочий лист Excel , давая им в левом столбце соответствующие названия параметров, а для рассчитываемых параметров использовать формулы. Тогда при изменении исходных данных будет автоматически выполнен перерасчет по формулам.

Функция БС

Функция БС(FV) предназначена для расчета будущей стоимости периодических постоянных платежей и единой суммы вклада или займа на основе постоянной процентной ставки.

БС – будущее значение, возвращает будущее значение вклада на основе периодических постоянных платежей и постоянной процентной ставки.

СТАВКА – это процентная ставка за период.
КПЕР– это общее число периодов платежей.
ПЛТ– это выплата, производимая в каждый период; это значение не может меняться в течение всего периода выплат. Обычно ПЛТ состоит из основного платежа и платежа по процентам, но не включает других налогов и сборов.
ПС – это текущая стоимость, или общая сумма всех будущих платежей с настоящего момента. Если аргумент ПС опущен, то он полагается равным 0. В этом случае должно быть указано значение аргумента ПЛТ.
ТИП– это число 0 или 1, обозначающее, когда должна производиться выплата: 0 – в конце периода, 1 – в начале периода. Если аргумент опущен, то он полагается равным 0.

Для аргументов СТАВКА и КПЕР используются согласованные единицы измерения. Если производятся ежемесячные платежи по четырехгодичному займу из расчета 12% годовых, то СТАВКА должна быть 12%/12, а КПЕР должно быть 4*12. Если производятся ежегодные платежи по тому же займу, то СТАВКА должна быть 12%, а КПЕР должно быть 4.

Все аргументы, означающие деньги, которые вы платите (например, депозитные вклады), представляются отрицательными числами; деньги, которые вы получаете (например, дивиденды), представляются положительными числами.

Например, вы собираетесь вложить под 12% годовых (что составит в месяц 12%/12 или 1%). Вы собираетесь вкладывать по 1000 руб. в конце каждого следующего месяца в течении следующих 12 месяцев. Сколько денег будет на счету в конце12 месяцев?

Результат 12682,50 руб.

Для выполнения расчета вызывается Мастер функций, в поле Категории выбираются финансовые функции и в поле Функция выбирается функция БС. В появившемся окне заполняются соответствующие поля путем подстановки значений аргументов, а если данная функция вычисляется в расчете, то вместо этого указываются адреса исходных данных из таблицы расчета.

Функция ПС

Функция ПС ( PV ) предназначена для расчета текущей стоимости как единой суммы вклада (займа), так и будущих фиксированных периодических платежей. Этот расчет является обратным по отношению к будущей стоимости (БС).

ПС ( PV )– возвращает текущий объем вклада. Текущий объем -это общая сумма, которую составят будущие платежи. Например, когда вы берете взаймы деньги, заимствованная сумма и есть текущий объем для заимодавца.

Например, определите необходимую сумму текущего вклада в банк, чтобы через пять лет он достиг 5000 руб. при 20% годовых и ежегодном начислении процентов в конце года. Синтаксис: ПС (20%, 5, 5000). Результат 2009,39.

Функция КПЕР

Для определения срока платежа и процентной ставки используются функции КПЕР ( NPER ) и СТАВКА ( RATE ).

Функция КПЕР вычисляет общее число периодов выплат как для единой суммы вклада (займа), так и для периодических постоянных выплат на основе постоянной процентной ставки. Если платежи производятся несколько раз в год, то для того, чтобы найти число лет выплат, общее число периодов надо разделить на число периодов в году.

СТАВКА– это процентная ставка за период.
ПЛТ– это выплата, производимая в каждый период; он не может меняться в течение всего периода выплат. Обычно платеж состоит из основного платежа и платежа по процентам, никакие другие сборы или налоги не учитываются.
ПС– это текущая стоимость, или общая сумма всех будущих платежей с настоящего момента.
БС– это будущая стоимость, или баланс наличности, который должен быть достигнут после последней выплаты. Если аргумент БС опущен, то предполагается, что он равен 0 (будущая стоимость займа, например, равна 0).
ТИП– это число 0 или 1, обозначающее, когда должна производиться выплата.

Например, рассчитаем срок погашения ссуды размером 5000 руб., выданной под 20% годовых при погашении ежемесячными платежами по 200 руб.

Результат 32,6 месяца или 2,7 года.

Функция СТАВКА

Функция СТАВКА ( RATE ) определяет значение процентной ставки за один расчетный период. Для нахождения годовой процентной ставки полученное значение необходимо умножить на число расчетных периодов в году.

Кпер– общее число периодов платежей по аннуитету.
Плт– регулярный платеж (один раз в период), величина которого остается постоянной в течение всего срока аннуитета. Обычно плт состоит из платежа основной суммы и платежа процентов, но не включает других сборов или налогов. Если аргумент опущен, должно быть указано значение аргумента БС.
Пс– приведенная к текущему моменту стоимость или общая сумма, которая на текущий момент равноценна ряду будущих платежей.
Бс– требуемое значение будущей стоимости или остатка средств после последней выплаты. Если аргумент бс опущен, то он полагается равным 0 (например, бс для займа равно 0).
Тип– число 0 или 1, обозначающее, когда должна производиться выплата.
Предположение – предполагаемая величина ставки. Если значение предположения опущено, то оно полагается равным 10 процентам.

Если функция СТАВКА не сходится, попробуйте подставить различные значения для предположения. СТАВКА обычно сходится, если величина предположения находится между числами 0 и 1.

Например, надо определить процентную ставку для четырёхлетнего займа в 8000 руб. с ежемесячной выплатой в 200 руб.

Результат 0,008, или 0,8 в месяц или 9,6% годовых.

Функции по расчету амортизации: AПЛ, АСЧ и ДДОБ

Под амортизацией подразумевается уменьшение стоимости имущества в процессе эксплуатации. Обычно оценивают величину этого уменьшения на единицу времени.

Функция АПЛ (SLN) возвращает величину амортизации имущества за один период времени, используя метод равномерной амортизации.

нач_стоимость – начальная стоимость имущества;
ост_стоимость –остаточная стоимость в конце периода амортизации;
время_эксплуатации – количество периодов, за которые собственность амортизируется (иногда называется временем полной амортизации).

Предположим, вы купили за 6000 руб. компьютер, который имеет срок эксплуатации 5 лет, после чего оценивается в 1000 руб. Снижение стоимости для каждого года эксплуатации вычисляется формулой

которая возвращает значение 1000 р.

Функция АСЧ(SYD) возвращает годовую амортизацию имущества для указанного периода.

По-настоящему мощным инструментом Excel является благодаря своей уникальной многофункциональности и умению решать задачи людей из разных профессиональных областей. Excel незаменим для менеджеров и экономистов, предпринимателей и финансистов, бухгалтеров и аналитиков, математиков и инженеров. Универсальность ему придают специфические встроенные функции, которые те или иные специалисты используют в своих расчётах.

Одна из самых больших и популярных категорий функций - финансовые. В последней версии Excel есть 55 функций, относящихся к этой группе. Многие из них специфические и узконаправленные, но некоторые могут пригодиться практически каждому. Одна из таких базовых функций - ПЛТ (PMT).

Как гласит официальная справка, функция ПЛТ возвращает сумму периодического платежа для аннуитета на основе постоянства сумм платежей и постоянной процентной ставки. Если Вас смущает специфический термин "аннуитет" - не пугайтесь. Иными словами, с помощью функции ПЛТ можно рассчитать сумму, которую нужно будет выплачивать каждый месяц при условии, что процент по кредиту не изменится и платежи вносятся регулярно равными суммами.

Синтаксис функции

Функция имеет следующий синтаксис:

ПЛТ(ставка; кпер; пс; [бс]; [тип])

Разберем по очереди все аргументы:

Ставка. Обязательный аргумент. Представляет процентную ставку за период. Самое главное здесь - не ошибиться в пересчете размера ставки на нужный период. Если предполагается погашать кредит ежемесячными платежами, а ставка годовая - то ее нужно перевести в месячную, разделив на 12. Если же, например, кредит гасится 1 раз в квартал, то годовую ставку нужно поделить на 4 (и получить таким образом ставку за 1 квартал). Ставку можно указать в процентах или в сотых долях.
Кпер. Обязательный. Этот аргумент представляет собой число расчетных периодов (сколько раз будет вноситься платёж в счёт погашения кредита). Как и ставка, этот аргумент зависит от того, какой расчетный период принят для вычислений. Если кредит получен на 5 лет с платежами 1 раз в месяц, то Кпер = 5*12 = 60 периодов . Если же на 3 года, с платежами 1 раз в квартал - то Кпер = 3*4 = 12 периодов .
Пс . Обязательный. Сумма кредита, то есть объем долга, который нужно будет погасить будущими платежами.
[бс]. Необязательный. Сумма долга, которая должна остаться неоплаченной после истечения всех расчетных периодов. Обычно этот аргумент равен 0 (кредит должен быть погашен полностью). Так как аргумент необязательный, то его можно не указывать (в таком случае он будет принят равным нулю).
[тип]. Необязательный. Обозначает момент произведения выплаты - в начале или в конце периода. Для первого случая нужно указать единицу, а для второго ноль (или вообще пропустить этот аргумент). В большинстве случаев используется второй вариант - выплаты в конце периода, а значит чаще всего этот аргумент можно опустить.

Особенностью синтаксиса функции является указание направления денежного потока. Если денежный поток входящий (например, сумма полученного кредита, указанная в аргументе Пс), то необходимо указывать его как положительное число. Исходящие потоки наоборот, указываются как отрицательные числа (например, после вычисления функция ПЛТ вернет отрицательный результат, так как размер платежа по кредиту - это исходящий денежный поток).

Примеры использования

Задача 1. Расчет суммы выплат по кредиту

Предположим, что в банке получен кредит на сумму 1 000 000 руб. под 17,5% годовых на срок 6 лет. Кредит будет погашаться равными платежами ежемесячно на протяжении всего срока займа. К концу срока будет выплачена вся сумма долга. Первый платеж будет внесен в конце первого периода. Необходимо найти величину ежемесячного платежа.

Итак, нам известна годовая ставка, а кредит будет погашаться ежемесячно. Значит для расчета нам потребуется перевести годовую ставку в месячную, разделив 17,5% на 12 месяцев. В первый аргумент записываем 17,5%/12 .

Кредит получен на 6 лет. Выплачивается ежемесячно. Значит, количество периодов выплат = 6*12. Во второй аргумент записываем 72 .

В третий аргумент пишем сумму кредита. Она равна 1 000 000 руб. (для займополучателя это входящий денежный поток, указываем его как положительное число).

Четвертый аргумент опустим, так как сумма будет полностью погашена к концу срока. Пятый аргумент также опустим, так как выплаты производятся в конце периода.

Формула примет вид:

Результат вычисления равен -22526,05 руб . Число отрицательное, так как платеж по кредиту для займополучателя является исходящим денежным потоком. Именно такую сумму нужно будет вносить каждый месяц для погашения кредита, описанного в условии.

В Microsoft Excel предусмотрено огромное количество разнообразных функций, позволяющих справляться с математическими, экономическими, финансовыми и другими задачами. Программа является одним из основных инструментов, использующихся в малых, средних и больших организациях для ведения различных видов учета, выполнения расчетов и т.д. Ниже мы рассмотрим финансовые функции, которые наиболее востребованы в Экселе.

Вставка функции

Для начала вспомним, как вставить функцию в ячейку таблицы. Сделать это можно по-разному:

Независимо от выбранного варианта, откроется окно вставки функции, в котором требуется выбрать категорию “Финансовые”, определиться с нужным оператором (например, ДОХОД), после чего нажать кнопку OK.

На экране отобразится окно с аргументами функции, которые требуется заполнить, после чего нажать кнопку OK, чтобы добавить ее в выбранную ячейку и получить результат.

Указывать данные можно вручную, используя клавиши клавиатуры (конкретные значения или ссылки на ячейки), либо встав в поле напротив нужного аргумента, выбирать соответствующие элементы в самой таблице (ячейки, диапазон ячеек) с помощью левой кнопки мыши (если это допустимо).

Обратите внимание, что некоторые аргументы могут не показываться и необходимо пролистать область вниз для получения доступа к ним (с помощью вертикального ползункам справа).

Альтернативный способ

Находясь во вкладке “Формулы” можно нажать кнопку “Финансовые” в группе “Библиотека функций”. Раскроется список доступных вариантов, среди которых просто кликаем по нужному.