Как работает функция ставка в excel

Обновлено: 06.01.2025

Excel умеет многое, в том числе и эффективно планировать финансы.

Существуют сотни онлайновых финансовых планировщиков. Все они просты в использовании, но ограничены по функциональности. MS Excel на их фоне — настоящий комбайн. В нём есть 53 финансовые формулы на все случаи жизни, а для контроля и планирования бюджета полезно знать три из них.

Функция ПЛТ

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

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

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

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

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

Функция ЭФФЕКТ

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

Нс — номинальная процентная ставка.
Кпер — количество периодов в году, за которые начисляются сложные проценты.

Существует множество способов упростить и ускорить работу в Excel, и мы с радостью расширим эти списки вашими советами.

Microsoft Excel функция СТАВКА возвращает процентную ставку за период выплаты для аннуитета. СТАВКА - это встроенная в Excel функция, относящаяся к категории финансовых функций.
Её можно использовать как функцию рабочего листа (WS) и функцию VBA в Excel.
Как функцию рабочего листа, функцию СТАВКА можно ввести как часть формулы в ячейку рабочего листа.
В качестве функции VBA вы можете использовать функцию RATE в коде макроса, который вводится через редактор Microsoft Visual Basic Editor.

Синтаксис

Синтаксис функции СТАВКА в Microsoft Excel:

Аргументы или параметры

кпер Количество периодов выплаты аннуитета. плт Сумма платежа, произведенного в каждый платежный период. пс Это текущая стоимость. бс Необязательно. Это будущая ценность.
Если этот параметр опущен, предполагается, что значение бс равно 0. тип Необязательно. Он указывает, когда должны быть произведены платежи.
Если аргумент тип опущен, предполагается, что значение тип равно 0. тип может быть одним из следующих значений:

Значение	Пояснение
0	Платежи подлежат оплате в конце периода. (по умолчанию)
1	Платежи подлежат оплате в начале периода.

прогноз Необязательно. Какая будет ставка - это ваше предположение.
Если этот параметр опущен, предполагается, что прогноз составляет 10%.

Возвращаемое значение

Функция СТАВКА возвращает числовое значение.

Применение

Excel для Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 для Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Тип функции

Функция рабочего листа (WS)
Функция VBA

Пример (как функция рабочего листа)

Давайте рассмотрим несколько примеров СТАВКА, чтобы понять, как использовать Excel функцию СТАВКА как функцию рабочего листа в Microsoft Excel:

В первом примере возвращается процентная ставка за период платежа по ссуде на $5000, где ежемесячные выплаты в размере $250 производятся на 2 года. Все выплаты производятся в конце периода.

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

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

Примеры финансовых расчетов по функции СТАВКА в Excel

Пример 1. В МФО был взят кредит сроком на 16 дней, сумма которого составляет 1000 долларов. Сумма возврата составляет 1250 долларов. Определить годовую процентную ставку по указанным условиям займа.

Для расчета в ячейку B7 введем следующую формулу:

B4 – число периодов выплат (в данном случае – 1);
0 – размер фиксированной выплаты (поскольку в данном примере только один период выплат, указано значение 0);
B5 – тело кредита;
B6 – сумма на момент погашения долга;
0 – характеризует тип выплат, при котором выплата производится в конце периода;
0,1 – предполагаемое значение процентной ставки (любое число из диапазона от 0 до 1);
B3/2 – коэффициент для пересчета полученного значения ставки к годовой процентной ставке.

Пи данных условиях микрокредитования сроком займа на 16 дней процентная ставка составляет 570,31% годовых! Несмотря на это, услуги по микрокредитованию сегодня продолжают набирать популярность.

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

Пример 2. Определить темпы роста пенсионных отчислений (процентную ставку), если баланс средств на конец года составляет 12000 долларов, а в начале года – 2400 долларов. Еженедельные платежи на протяжении года составляли 150 долларов (то есть, количество периодов – 52).

Формула для расчета:

B2 – количество периодов выплат;
B3 – сумма платежа (расходная операция, поэтому отрицательное значение);
B4 – сумма средств до наступления первого периода выплат;
B5 – сумма по окончанию последнего периода выплат;
0 – выплаты в конце периода;
0,1 – произвольное значение из интервала от 0 до 1;
52 – количество периода выплат для пересчета размера ставки в годовых.

То есть, пенсионные отчисления выполняются под 7% годовых.

Определение реальной процентной ставки по кредиту

Пример 3. Ноутбук одной и той же модели можно приобрести за 1200 долларов в рассрочку (беспроцентную, судя по рекламе в первом магазине) или за 1050 долларов в другом магазине. Рассрочка выдается на 1 год с 12 периодами выплат. Определить реальный процент «беспроцентной» рассрочки платежей по кредиту.

Формула для расчета:

B2 – число периодов выплат;
-B3/B2 – выражение для расчета размера ежемесячного платежа;
B4 – реальная стоимость ноутбука (используется как начальная стоимость финансового инструмента, цена которого повысится до 1200 к окончанию последнего периода выплат);
0 – остаток по окончанию последнего периода выплат;
0 - выплаты в конце периода;
0,01 - произвольное значение предполагаемой ставки.

То есть, фактически в первом магазине клиенту предложили кредит на ноутбук под 25,4% годовых.

Давайте возьмем пример. Рам хочет взять кредит / занять немного денег или хочет вложить деньги в финансовую компанию XYZ. Компания должна сделать некоторые финансовые расчеты, например, клиент должен заплатить что-то финансовому предприятию в счет суммы кредита или того, сколько клиенту нужно инвестировать, чтобы через некоторое время он / она мог получить такую большую сумму денег.

В этих сценариях Excel имеет наиболее важную функцию «СКОРОСТЬ», которая является частью финансовой функции.

Что такое функция RATE?

Функция, которая используется для расчета процентной ставки для оплаты указанной суммы кредита или для получения указанной суммы инвестиций через некоторый период времени, называется функцией RATE.

RATE Formula

Ниже приведена формула RATE:

Функция RATE использует ниже аргументы

Nper: Всего нет. периодов для займа или инвестиций.

Pmt: платеж производится каждый период, и это фиксированная сумма во время кредита или инвестиций.

Pv: текущая (текущая) стоимость кредита / инвестиции.

(Fv): это необязательный аргумент. Это указывает будущую стоимость кредита / инвестиций в конце общего нет. платежей (nper) платежей.

Если не указать какое-либо значение, оно автоматически считает fv = 0.

(тип): это также необязательный аргумент. Он принимает логические значения 0 или 1.

1 = если оплата произведена в начале периода.

0 = если оплата произведена в конце периода.

Если не указать какое-либо значение, оно автоматически считает его равным 0.

(предположение): первоначальное предположение о том, какой будет ставка. Если не указать какое-либо значение, оно автоматически считает это 0, 1 (10%).

Объяснение функции RATE:

Функция RATE используется в разных сценариях.

PMT (оплата)
PV (текущая стоимость)
FV (будущая стоимость)
NPER (количество периодов)
IPMT (выплата процентов)

Как использовать функцию RATE в Excel?

Функция RATE очень проста в использовании. Давайте теперь посмотрим, как использовать функцию RATE в Excel с помощью нескольких примеров.

Вы можете скачать этот шаблон функции RATE здесь - шаблон функции RATE

Пример № 1

Вы хотите купить машину. Для этого вы подаете заявку в банк на 5000 долларов. Банк предоставляет этот кредит сроком на 5 лет и фиксирует сумму ежемесячного платежа в размере 150, 60 долларов США. Теперь вам нужно знать годовую процентную ставку.

Здесь у нас есть следующая информация:

= СКОРОСТЬ (B4, B3, -B2)

Здесь результат функции умножается на 12, дает годовую процентную ставку. B2 - отрицательное значение, потому что это исходящий платеж.

= СКОРОСТЬ (B4, B3, -B2) * 12

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

PMT (оплата)

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

Пример № 2

Вы хотите купить дом, который стоит 350 000 долларов. Чтобы купить это, вы хотите подать заявку на банковский кредит. Банк предлагает вам кредит под 18% годовых на 10 лет. Теперь вам нужно рассчитать ежемесячный взнос или оплату этого кредита.

Здесь у нас есть следующая информация:

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

= PMT (B14 / 12, B13, -B12)

PV (текущая стоимость)

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

Пример № 3

Вы сделали инвестицию, которая выплачивает вам 200 000 долларов через 18 лет под 9% годовых. Теперь вам нужно узнать, сколько нужно инвестировать сегодня, чтобы вы могли получить будущую стоимость в 200 000 долларов.

Здесь у нас есть следующая информация:

= PV (B23, B22, 0, B21)

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

Давайте возьмем еще один пример функции PV.

Пример № 4

Вы взяли кредит на 5 лет с фиксированной суммой ежемесячного платежа в размере 150, 60 долларов США. Годовая процентная ставка составляет 5%. Теперь вам нужно рассчитать первоначальную сумму кредита.

Здесь у нас есть следующая информация:

= PV (5 / 12, 5 * 12, -B17, 0)

Этот ежемесячный платеж был округлен до ближайшей копейки.

FV (будущая стоимость)

Эта функция используется для определения будущей стоимости инвестиций.

Пример № 5

Вы вкладываете определенную сумму в размере 35 000 долларов США под 6% годовых в течение 20 лет. Теперь вопрос: с этими инвестициями, сколько мы получим через 20 лет?

Здесь у нас есть следующая информация:

NPER (количество периодов)

Эта функция возвращает общее число. периодов для инвестиций или для кредита.

Пример № 6

Вы берете кредит на сумму 5000 долларов США с ежемесячной суммой выплат в размере 115, 43 доллара США. Кредит имеет 5% годовых. Для расчета нет. периодов, нам нужно использовать функцию NPER.

Здесь у нас есть следующая информация:

= NPER (B3 / 12, B4, -B2, 0)

Эта функция возвращает 47, 869, т.е. 48 месяцев.

IPMT (выплата процентов)

Эта функция возвращает выплату процентов по кредиту или инвестициям за определенный период времени.

Пример № 7

Вы взяли кредит в размере 30 000 долларов США на один год с годовой процентной ставкой 5%. Теперь для расчета процентной ставки за первый месяц мы будем использовать функцию IPMT.

Здесь у нас есть следующая информация:

= ПРПЛТ (B3 / 12, 1, В4,

Что нужно помнить о функции RATE

В индустрии финансов обычно используются два фактора: отток денежных средств и приток денежных средств.

Отток денежных средств: исходящие платежи обозначаются отрицательными числами.

Приток денежных средств: входящие платежи обозначаются положительными числами.