Расчеты в excel

Регулировка напора воды в квартире

Расчет полной стоимости кредита в Excel

Согласно Закону о потребительском кредите для расчета полной стоимости кредита (ПСК) теперь применяется новая формула. ПСК определяется в процентах с точностью до третьего знака после запятой по следующей формуле:

• ПСК = i * ЧБП * 100;
• где i – процентная ставка базового периода;
• ЧБП – число базовых периодов в календарном году.

Возьмем для примера следующие данные по кредиту:

Для расчета полной стоимости кредита нужно составить график платежей (порядок см. выше).

Нужно определить базовый период (БП). В законе сказано, что это стандартный временной интервал, который встречается в графике погашения чаще всего. В примере БП = 28 дней.

Далее находим ЧБП: 365 / 28 = 13.

Теперь можно найти процентную ставку базового периода:

У нас имеются все необходимые данные – подставляем их в формулу ПСК: =B9*B8

Примечание. Чтобы получить проценты в Excel, не нужно умножать на 100. Достаточно выставить для ячейки с результатом процентный формат.

ПСК по новой формуле совпала с годовой процентной ставкой по кредиту.

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

Расчет дифференцированных платежей в программе MO Excel

В основу снова ляжет пример. Клиент взял в банке 180 тысяч рублей на 3 года. Ставка – 13% годовых. Погашение предполагается каждый месяц, в конце периода.

Для расчетов необходимо узнать ежемесячную базовую сумму, подлежащую выплате. Каждый месяц клиент обязан возвращать банку равную сумму – часть долга. В рассматриваемом случае это 180000 / 3 / 12 = 5000 рублей. Каждый месяц на остаток начисляются прописанные в договоре проценты. Соответственно, уменьшается остаток – меньше становится и сумма, начисляемая банком.

Расчет основывается на функции ПРОЦПЛАТ. Через точку с запятой в ней обозначаются четыре показателя:

Расчет процентов по кредиту

• ставка за период (13%/12)
• номер периода, за который будет считаться величина
• число периодов начисления суммы долга к уплате
• приведенная стоимость (сумма кредита)

Функция ПРОЦПЛАТ совпадает по аргументам с предыдущей формулой, однако не имеет с ней ничего схожего, подменять их друг другом нельзя. В англоязычной версии наименование функции – ISPMT, аргументы в ней такие же.

В ПРОЦПЛАТ предполагается начисление суммы процентов в начале периода. Сдвинуть эту функцию на конец месяца можно, если сместить вычисления на период раньше (не «период», а «период-1»). Итоги будут отображены с противоположным знаком, то есть минусом. Таким образом отличаются расчеты при начислении процентов по кредиту и вкладу.

Расчет полной стоимости кредита в Excel по новой формуле

​ 30 лет.​: возвращает приведенную (к​ решить, прошу не​ желтой ячейке -​ знаете, как посчитать?​ базового периода.​ со времени завершения​Для нового расчета ПСК​ суммы​ при удобной возможности.​Чтобы сделать наш калькулятор​ воспользоваться еще двумя​ аргументы для расчета:​Так что очень надеюсь,​ депозит по 200​Аргумент «Ставка» составляет 3%/12​Теперь допустим, вы хотите​С помощью функции ПЛТ(ставка;КПЕР;ПС)​ текущему моменту) стоимость​ направлять — повторно​ переплата заемщика (или​Guest​Расчет по новой формуле​ qk-ого периода до​

​ законодатели установили формулу,​отрицательные суммы — наши​ Для реализации этого​

Формула расчета ПСК

​ более универсальным и​ полезными финансовыми функциями​Ставка​ что изложенный ниже​ долларов США в​ ежемесячных платежей за​ собрать 8500 долларов​=ПЛТ(5%/12;30*12;180000)​ инвестиции. Приведенная (нынешняя)​ их решать не​ доход банка), в​

​: Сумма значений функции​ показал ПСК, равный​ даты k-ого денежного​ которая в ряде​ выплаты банку, положительные​ можно добавить в​ способным автоматически подстраиваться​ Excel -​

​- процентная ставка​

​ материал вам не​ месяц под 1,5%​

• ​ год.​ США на отпуск​получена сумма ежемесячного платежа​ стоимость представляет собой​ интересно). При направлении​ столбцах — проверка.​ процплат для всех​ договорной процентной ставке.​ платежа;​ зарубежных стран используется​ — берем дополнительный​ нашу модель столбец​ под любой срок​
• ​ОСПЛТ (PPMT)​ по кредиту в​ пригодится.​ годовых?​Аргумент ПЛТ составляет -150.​ за три года,​

​ (без учета страховки​

• ​ общую сумму, которая​_{​ задания сообщайте свою​}​По функциям читайте​ периодов (если кредит​ Впрочем, в данном​q​ для нахождения эффективной​ кредит к уже​ с дополнительными выплатами,​ кредита, имеет смысл​и​ пересчете на период​
• ​Но если уж случится​С помощью функции БС(ставка;КПЕР;ПЛТ;ПС)​Аргумент ПС (приведенная стоимость)​
• ​ и вам интересно,​_{​ и налогов) в​}​ на данный момент​ специальность.​ справку.​ по аннуитету)​ примере заемщик не​k​ годовой процентной ставки​
• ​ имеющемуся​_{​ которые будут уменьшать​}​ немного подправить формулы.​ПРПЛТ (IPMT)​ выплаты, т.е. на​ так, что вам​
• ​=БС(1,5%/12;10;-200;-500)​ составляет 2500.​ какую сумму необходимо​

​ размере 966,28 долларов​

Пример расчета ПСК в Excel

​Рассчитать ежемесячные выплаты по​При ежедневной капитализации​Михаил С.​ уплачивает кредитору дополнительные​– число базовых​ (APR, или Annual​подсчитать точное количество дней​ остаток. Однако, большинство​

​ В ячейке А18​. Первая из них​

​ месяцы. Если годовая​ или вашим близким​получаем, что за 10​Расчет суммы первого взноса​ положить на счет,​ США.​ выплат.​ кредиту в  600​ процент ежедневно прибавляется​

​: Мало данных. Нужно​

​ суммы (комиссии, сборы).​ периодов с даты​ Percentage Rate).​ между двумя датами​ банков в подобных​ лучше использовать формулу​ вычисляет ту часть​

​ ставка 12%, то​ придется влезть в​

​ месяцев выйдет сумма​Допустим, вы хотите приобрести​ чтобы ежемесячный взнос​Аргумент «ставка» составляет 5%,​БС​ 000 р., 800 000 р.,​ к сумме долга​ еще знать срок​

​ Только проценты.​ выдачи займа до​Сама формула:​ (и процентов, которые​ случаях предлагают на​ вида:​ очередного платежа, которая​ на один месяц​

​ это дело, то​ 2517,57 долларов США.​

​ автомобиль стоимостью 19​ составлял 175,00 долларов​ разделенных на 12​: возвращает будущую стоимость​ 1 000 000 р., 1​ и процент на​ капитализации процентов и​

​Рассмотрим другой пример, с​ k-ого денежного платежа;​ПСК = i *​

​ на них приходятся)​ выбор: сокращать либо​=ЕСЛИ(A17>=$C$7;»»;A17+1)​ приходится на выплату​ должно приходиться по​ неплохо бы перед​Аргумент «Ставка» составляет 1,5%/12.​ 000 долларов США​ США. Функция ПС​

exceltable.com>

Кредитный калькулятор с плавающей процентной ставкой в Excel

Ниже на рисунке изображен график платежей по кредиту с плавающей процентной ставкой. Он содержит столбец «Изменение процентов» благодаря которому наглядно видно изменение процентной ставки. В отдельной таблице записаны изменяемые значения процентной ставки.

В столбце «Изменение процентов» записанная формула, которая подставляет соответствующие процентные ставки из дополнительной таблицы по условию:

Формула в столбце «Сумма процентов» использует данные из ячеек в столбце «Изменение процентов»:

Примечание: Все формулы в каждом столбце изображены выше заголовков столбцов за исключением столбца G, там формула: =ВПР(D11;$K$11:$L$23;2;ИСТИНА).

В столбце «Изменение процентов» используется функция ВПР, в которой четвертый аргумент имеет значение ИСТИНА. Чтобы можно было использовать такой аргумент, данные первого столбца в дополнительной таблице с плавающими процентными ставками должны быть отсортированы по возрастанию. Функция ВПР находит ближайшее большее значение в первом столбце дополнительной таблице для каждого текущего номера платежа в исходной таблице. Функция при поиске не требует точного совпадения, а только ближайшее большее значение соответственное числу из первого столбца дополнительной таблицы. При нахождении ближайшего большего значения функция ВПР возвращает процентную ставку с ячейки на против найденного числа. Например, когда функция ищет значения процентной ставки для номера 16, она возвращает значение со второй строки, так как ближайшее большее значение чем 16 в очередной строке равно 98. То есть 98 – это самое ближайшее большее число от искомого номера 16.

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

Редактор Microsoft Excel позволяет обрабатывать информацию любым удобным для вас способом. Для этого есть все необходимые условия и возможности. Рассмотрим несколько примеров формул по категориям. Так вам будет проще разобраться.

Арифметика

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

1. Создайте таблицу с какими-нибудь условными данными.

1. Для того чтобы высчитать сумму, введите следующую формулу. Если хотите прибавить только одно значение, можно использовать оператор сложения («+»).

=СУММ(B3:C3)

1. Как ни странно, в редакторе Excel нельзя отнять при помощи функций. Для вычета используется обычный оператор «-». В этом случае код получится следующий.

=B3-C3

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

=B3/C3%

1. Для определения среднего значения используйте следующую формулу.

=СРЗНАЧ(B3:C3)

1. В результате описанных выше выражений, вы увидите следующий итог.

Условия

Считать ячейки можно с учетом определенных условий.

1. Для этого увеличим нашу таблицу.

1. Например, сложим те ячейки, у которых значение больше трёх.

=СУММЕСЛИ(B3;»>3″;B3:C3)

1. Excel может складывать с учетом сразу нескольких условий. Можно посчитать сумму клеток первого столбца, значение которых больше 2 и меньше 6. И ту же самую формулу можно установить для второй колонки.

=СУММЕСЛИМН(B3:B9;B3:B9;»>2″;B3:B9;»<6″)=СУММЕСЛИМН(C3:C9;C3:C9;»>2″;C3:C9;»<6″)

1. Также можно посчитать количество элементов, которые удовлетворяют какому-то условию. Например, пусть Эксель посчитает, сколько у нас чисел больше 3.

=СЧЁТЕСЛИ(B3:B9;»>3″)=СЧЁТЕСЛИ(C3:C9;»>3″)

1. Результат всех формул получится следующим.

Математические функции и графики

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

В качестве примера попробуем построить графики для экспоненты и какого-нибудь уравнения. Инструкция будет следующей:

1. Создадим таблицу. В первой графе у нас будет исходное число «X», во второй – функция «EXP», в третьей – указанное соотношение. Можно было бы сделать квадратичное выражение, но тогда бы результирующее значение на фоне экспоненты на графике практически пропало бы.

1. Для того чтобы преобразовать значение «X», нужно указать следующие формулы.

=EXP(B4)=B4+5*B4^3/2

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

1. Выделяем всю таблицу. Переходим на вкладку «Вставка». Кликаем на инструмент «Рекомендуемые диаграммы».

1. Выбираем тип «Линия». Для продолжения кликаем на «OK».

1. Результат получился довольно-таки красивый и аккуратный.

Как мы и говорили ранее, прирост экспоненты происходит намного быстрее, чем у обычного кубического уравнения.

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

Обучение основам Excel: ячейки и числа

Многие начинающие пользователи, после запуска Excel — задают один странный вопрос: «ну и где тут таблица?». Между тем, все клеточки, что вы видите после запуска программы — это и есть одна большая таблица!

Теперь к главному: в любой клетке может быть текст, какое-нибудь число, или формула. Например, ниже на скриншоте показан один показательный пример:

слева: в ячейке (A1) написано простое число «6»

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

справа: в ячейке (C1) с виду тоже простое число «6», но если выбрать эту ячейку, то вы увидите формулу «=3+3» — это и есть важная фишка в Excel!

Просто число (слева) и посчитанная формула (справа)

Суть в том, что Excel может считать как калькулятор, если выбрать какую нибудь ячейку, а потом написать формулу, например «=3+5+8» (без кавычек). Результат вам писать не нужно — Excel посчитает его сам и отобразит в ячейке (как в ячейке C1 в примере выше)!

Но писать в формулы и складывать можно не просто числа, но и числа, уже посчитанные в других ячейках. На скриншоте ниже в ячейке A1 и B1 числа 5 и 6 соответственно. В ячейке D1 я хочу получить их сумму — можно написать формулу двумя способами:

• первый: «=5+6» (не совсем удобно, представьте, что в ячейке A1 — у нас число тоже считается по какой-нибудь другой формуле и оно меняется. Не будете же вы подставлять вместо 5 каждый раз заново число?!);
• второй: «=A1+B1» — а вот это идеальный вариант, просто складываем значение ячеек A1 и B1 (несмотря даже какие числа в них!).

Сложение ячеек, в которых уже есть числа

Распространение формулы на другие ячейки

В примере выше мы сложили два числа в столбце A и B в первой строке. Но строк то у нас 6, и чаще всего в реальных задачах сложить числа нужно в каждой строке! Чтобы это сделать, можно:

1. в строке 2 написать формулу «=A2+B2», в строке 3 — «=A3+B3» и т.д. (это долго и утомительно, этот вариант никогда не используют);
2. выбрать ячейку D1 (в которой уже есть формула), затем подвести указатель мышки к правому уголку ячейки, чтобы появился черный крестик (см. скрин ниже). Затем зажать левую кнопку и растянуть формулу на весь столбец. Удобно и быстро! (Примечание: так же можно использовать для формул комбинации Ctrl+C и Ctrl+V (скопировать и вставить соответственно)).

Растягивание формулы

Кстати, обратите внимание на то, что Excel сам подставил формулы в каждую строку. То есть, если сейчас вы выберите ячейку, скажем, D2 — то увидите формулу «=A2+B2» (т.е

Excel автоматически подставляет формулы и сразу же выдает результат).

Как задать константу (ячейку, которая не будет меняться при копировании формулы)

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

Далее в ячейке E2 пишется формула «=D2*G2» и получаем результат. Только вот если растянуть формулу, как мы это делали до этого,  в других строках результата мы не увидим, т.к. Excel в строку 3 поставит формулу «D3*G3», в 4-ю строку: «D4*G4» и т.д. Надо же, чтобы G2 везде оставалась G2…

Чтобы это сделать — просто измените ячейку E2 — формула будет иметь вид  «=D2*$G$2». Т.е. значок доллара $ — позволяет задавать ячейку, которая не будет меняться, когда вы будете копировать формулу (т.е. получаем константу, пример ниже).

Константа / в формуле ячейка не изменяется

Расчет кредита за месячную арендную плату

Во-первых, давайте посмотрим, как реализовать расчет ежемесячного платежа за ипотеку. Другими словами, используя годовую ставку Iinterest, основную сумму и продолжительность, мы можем определить сумму, подлежащую ежемесячной выплате.

Формула, как показано на скриншоте выше, написана следующим образом:

= — PMT (rate; length; present_value; ; )

Знак минус перед PMT необходим, так как формула возвращает отрицательное число. Первыми тремя аргументами являются ставка займа, продолжительность кредита (количество периодов) и главный заем. Последние два аргумента являются необязательными, остаточное значение по умолчанию равно 0, оплачивается заранее (для 1) или в конце (для 0), также является необязательным.

Формула Excel, используемая для расчета ежемесячного платежа по кредиту:

= — PMT ((1 + B2) ^ (1/12) -1; B4 * 12; B3) = PMT ((1 + 3, 10%) ^ (1/12) -1; 10 * 12; 120000)

Объяснение: Для ставки мы используем период ставки, который является месячной ставкой, затем мы вычисляем количество периодов (месяцев здесь 120 за 10 лет, умноженное на 12 месяцев), и, наконец, мы указываем основной заем. Наш ежемесячный платеж составит $ 1, 161. 88 за 10 лет.

Формула 2: Если

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

Синтаксис

В формулу этой функции входит три основных аргумента, и она выглядит так:

=ЕСЛИ(логическое_выражение;«значение_если_истина»;«значение_если_ложь»).

Здесь под логическим выражением подразумевается непосредственно описывающая критерий формула. С ее помощью будут проверяться данные на предмет соответствия определенному условию. Соответственно, аргумент «значение если ложь» предназначен для той же задачи, с тем лишь отличием, что оно зеркально противоположное по смыслу. Простыми словами, если условие не подтвердилось, то программа осуществляет определенные действия.

Есть еще один вариант, как можно применять функцию ЕСЛИ – вложенные функции. Здесь может быть намного больше условий, вплоть до 64. Пример рассуждения, соответствующего формуле, приведенной на скриншоте, такой. Если ячейка А2 равна двум, то нужно вывести значение «Да». Если же она имеет другое значение, то необходимо проверить, равна ли ячейка D2 двум. Если да, то нужно выдать значение «нет», если и здесь условие оказывается ложным, то формула должна вернуть значение «возможно».

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

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

Тут синтаксис следующий:

=ЕСЛИ(ЕПУСТО(номер ячейки);«Пустая»;«Не пустая»).

Кроме этого, есть возможность применять вместо функции ЕПУСТО применять стандартную формулу, но указать, что при условии отсутствия никаких значений в ячейке.

ЕСЛИ – это одна из наиболее часто встречаемых функций, которая очень легко используется и она дает возможность понять, насколько истинны те или иные значения, получить результаты по разным критериям, а также определить, является ли определенная ячейка пустой.

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

График платежей по кредиту с плавающей процентной ставкой в Excel

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

1. В ячейке D4 введите дату первого платежа (пусть будет для примера: 10.01.2020).
2. В ячейке D4 введите формулу, которую следует скопировать в остальные нижние ячейки столбца D (теперь уже «Дата платежа», а не «№»):
3. В столбце K «Дата платежа» в дополнительной таблице с процентами (диапазон: K2:L15) введите даты, от которых будет изменяться процентная ставка в графике платежей по кредиту.

Следует также создать условное форматирование для автоматического скрытия ячеек с отрицательными значениями долга по кредиту при уменьшении срока кредитования. Например: не 30, а 20 лет. Изменив цвет шрифта на белый мы скроем лишние значения графика по условию. Для этого выделите диапазон табличной части графика D4:I363 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило».

В появившемся окне «Создание правила форматирования» выберите опцию «Использовать формулу для определения форматируемых ячеек», а в поле «Изменение описания правила» введите формулу:

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

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

Что можно продавать в группе (сообществе)

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

Действия со столбцами и строками

Вариант А – просто растянем по ширине. Наводим курсор мышки на границу столбцов А и В до появления специфического курсора и тянем вправо.

Вариант Б – воспользоваться специальной функцией в меню: один раз кликнуть по ячейке – нажать кнопку «Перенос текста».

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

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

Автоподбор

Выделить столбец – в меню найти «Формат» – Автоподбор.

Массовое изменение ширины ячеек

Выделяем область: навести курсор мышки на столбец – зажать ЛКМ – провести по всем столбцам, которые нужно выделить. Если столбцы стоят не подряд, кликаем на первый, зажимаем Ctrl и кликаем по одному разу на необходимые.

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

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

Вставить строку/столбец

Выделить столбик, рядом с которым необходимо вставить новый – в меню отыскать «Вставить» – выбрать «Вставить столбец на лист», либо выбрать «Вставить ячейки» и в появившемся меню поставить галочку для определения, с какой стороны вставится столбик.

Аналогичные действия для вставки строки.

Чтобы удалить строку или ячейку, нужно: выделить столбец – ПКМ по выделенной области – Удалить.

Как задать формат ячеек

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

Выделяем область, которая будет содержать цифры и участвовать в формуле: наводим курсор на верхнюю левую ячейку – зажимаем ЛКМ и тянем область. Можно воспользоваться клавишей Ctrl, если между ячейками есть промежутки.

Затем ПКМ по выделенной области – «Формат ячеек». В закладках находим «Число», выбираем Числовой формат, определяем количество символов после запятой, жмем Ок.

Задачу упрощает меню: выделить область, открыть меню и выбрать нужный формат.

Как убрать числа после запятой

Не всегда наличие знаков после запятой уместно. Выделяем область – заходим в контекстное меню – выбираем формат, изменяем количество знаков после запятой на 0.

Как создать формулу

В Эксель вписано множество формул, для совершения математических действий с данными. Для примера рассмотрим умножение.

Кликаем по ячейке, в которую вписывается формула, ставим курсор в строку для формулы.

В строке формулы ставим знак «=», кликаем по ячейке с данными, ставим знак «*», кликаем по ячейке со вторыми данными, нажимаем Enter.

В результате произошло математическое действие умножения, при котором перемножились данные из В2 и С2, а результат записался в D2.

Копирование формулы

Выделить ячейку с формулой – навести курсор на черный квадратик в правом нижнем углу границы до появления черного крестика – зажать ЛКМ – растянуть область на весь столбец.

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

Объединение ячеек

Выделить ячейки, которые нужно объединить – найти в меню кнопку «Объединить и поместить в центре» – нажать на нее.

Для снятия объединения: выделить объединенную область – нажать на кнопку «Объединить и поместить в центре».

Метод работает только для объединенных ячеек.

The Unarchiver — бесплатный архиватор для Mac

Количество выплат за кредит

Формула, которую мы будем использовать, это NPER, как показано на скриншоте выше, и она написана следующим образом:

= NPER (rate; pmt; present_value; ; )

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

= NPER ((1 + B2) ^ (1/12) -1; -B4; B3) = NPER ((1 + 3, 10%) ^ (1/12) -1; -1100, 120000)

Примечание: соответствующим данным в ежемесячном платеже должен быть указан отрицательный знак. Вот почему у нас есть знак минус перед формулой. Продолжительность возмещения составляет 127. 97 периодов (месяцев в нашем случае).

Мы будем использовать формулу = B5 / 12 = 127. 97/12 для количества лет, чтобы завершить погашение кредита. Другими словами, чтобы занять 120 000 долларов США с годовой ставкой 3,10% и ежемесячно выплачивать 1 100 долларов США, мы должны погасить сроки погашения в течение 128 месяцев или 10 лет и 8 месяцев.

Популярные вопросы по калькулятору

Можно ли в калькуляторе менять ставку по кредиту?

Да, изменение ставки возможно. Для этого добавьте досрочное погашение с типом «Изменение ставки». Новая ставка будет действовать обычно с ближайшей даты очередного платежа

Можно ли внести ежемесячные досрочные погашения?

Да, такое возможно. Нужно добавить досрочное погашение с типом «Ежемесячное уменьшение срока» или суммы. Каждый месяц в дату, указанную при добавлении гашения будет добавляться досрочка на заданную сумму.

Возможен ли расчет досрочки с материнским капиталом?

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

Плавающая и фиксированная процентная ставка

Если в депозитном договоре установлено, что в течении всего срока проценты будут начисляться по фиксированной ставке, или отсутствует упоминание об условиях изменения величины ставки, то в графе калькулятора «Процентная ставка» следует указать, что ставка является фиксированной и ввести ее значение. Этот вариант является наиболее распространенным (более 95% всех вкладов, рассчитанных на Fin-Plus.ru).

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

Еще один возможный вариант, предлагаемый некоторыми банками, определяет, что ставка начисления процентов будет плавающей в зависимости от срока, в течение которого ваши деньги находятся в банке. В этом случае в калькуляторе, выбрав соответствующий вид ставки, нужно ввести несколько строк, по одной для каждого значения ставки. Каждая строка определяет временной интервал действия ставки. День (от начала срока вклада) окончания такого интервала (более поздний срок) вводится явно, а в качестве дня начала интервала (более ранняя дата) калькулятор использует или день вклада или день, следующий за указанным в качестве дня окончания предыдущего интервала. Например, если в договоре вклада продолжительностью 1 год прописано, что с первого по сотый день вклада действует ставка 10%, а со сто первого дня и до конца срока действует ставка 12%, то в калькулятор следует ввести две строки со следующими значениями: 1) Номер дня = 100, Ставка = 10%; 2) Номер дня = 366, Ставка = 12%

Особенности использования функции КПЕР в Excel

Функция КПЕР используется для решения финансовых задач совместно с функциями ПЛТ, БС, СТАВКА, ПС и имеет следующую синтаксическую запись:

=КПЕР(ставка;плт;пс;;)

Описание аргументов (первые три аргумента – обязательные для заполнения):

• ставка – числовое значение, характеризующее ставку за 1 период выплат (для ссуд) или капитализации (для депозитных вкладов). Аргумент может быть указан в виде дробного числа или в качестве значения в процентном формате (например, 14,5% или 0,145 – эквивалентные варианты записи). Если в условии задачи указана годовая ставка, необходимо выполнить пересчет по формуле Rп=Rг/12, где Rп – ставка за период, Rg – годовая ставка, 12 – число месяцев в году.
• плт – числовое значение, соответствующее сумме выплаты за период, которая является фиксированной величиной (простые проценты).
• пс – числовое значение, характеризующее текущую стоимость инвестиции (например, сумма, выданная кредитной организацией в долг клиенту, или сумма средств, положенных на депозитный счет в банк).
• – числовое значение, соответствующее будущей стоимости инвестиции. Например, данный аргумент может характеризовать сумму, которую получит вкладчик по окончанию действия договора по депозитному вкладу. Если аргумент явно не указан или принимает значение 0 (нуль), функция КПЕР вернет количество периодов выплат до полного погашения задолженности. Аргумент необязателен для заполнения, по умолчанию принимается значение 0.
• – необязательный аргумент, характеризующий способ выплат (0 – выплата на конец периода, 1 – выплата на начало периода).

Примечания 1:

1. Функция КПЕР возвращает код ошибки #ЧИСЛО! В случае, если сумма платежа за каждый период меньше, чем произведение начальной суммы инвестиции и ставки за период, при этом будущая стоимость инвестиции равна 0 (ситуация при расчете количества периодов для полного возврата задолженности), а выплата производится в конце периода (то есть, аргумент или явно указан как 0 (нуль).
2. Указанная выше особенность работы функции КПЕР вытекает из алгоритма, который она использует для расчета:
3. Все аргументы функции КПЕР должны указываться в виде числовых значений или конвертируемых в числа текстовых срок. Иначе рассматриваемая функция будет возвращать код ошибки #ЗНАЧ!.

Примечания 2:

1. Фактически, функция КПЕР позволяет определить количество периодов, по окончанию последнего из которых будущая стоимость инвестиции примет указанное значение.
2. В случае с кредитом, считается, что задолженность погашена полностью, если будущая стоимость инвестиции равна 0 (нулю).
3. Также функция КПЕР позволяет вычислить количество периодов капитализации депозитного вклада, необходимых для достижения требуемой суммы накоплений.
4. Для расчета количества периодов выплаты задолженности с нулевой процентной ставкой можно использовать формулу =A1/A2, где A1 – будущая стоимость, A2 – фиксированная сумма выплат за период.