Кто как, а я считаю кредиты злом. Особенно потребительские. Кредиты для бизнеса — другое дело, а для обычных людей мышеловка»деньги за 15 минут, нужен только паспорт» срабатывает безотказно, предлагая удовольствие здесь и сейчас, а расплату за него когда-нибудь потом. И главная проблема, по-моему, даже не в грабительских процентах или в том, что это «потом» все равно когда-нибудь наступит. Кредит убивает мотивацию к росту. Зачем напрягаться, учиться, развиваться, искать дополнительные источники дохода, если можно тупо зайти в ближайший банк и там тебе за полчаса оформят кредит на кабальных условиях, попутно грамотно разведя на страхование и прочие допы?
Так что очень надеюсь, что изложенный ниже материал вам не пригодится.
Но если уж случится так, что вам или вашим близким придется влезть в это дело, то неплохо бы перед походом в банк хотя бы ориентировочно прикинуть суммы выплат по кредиту, переплату, сроки и т.д. «Помассажировать числа» заранее, как я это называю 🙂 Microsoft Excel может сильно помочь в этом вопросе.
Вариант 1. Простой кредитный калькулятор в Excel
Для быстрой прикидки кредитный калькулятор в Excel можно сделать за пару минут с помощью всего одной функции и пары простых формул. Для расчета ежемесячной выплаты по аннуитетному кредиту (т.е. кредиту, где выплаты производятся равными суммами — таких сейчас большинство) в Excel есть специальная функция ПЛТ (PMT) из категории Финансовые (Financial). Выделяем ячейку, где хотим получить результат, жмем на кнопку fx в строке формул, находим функцию ПЛТ в списке и жмем ОК. В следующем окне нужно будет ввести аргументы для расчета:
- Ставка — процентная ставка по кредиту в пересчете на период выплаты, т.е. на месяцы. Если годовая ставка 12%, то на один месяц должно приходиться по 1% соответственно.
- Кпер — количество периодов, т.е. срок кредита в месяцах.
- Пс — начальный баланс, т.е. сумма кредита.
- Бс — конечный баланс, т.е. баланс с которым мы должны по идее прийти к концу срока. Очевидно =0, т.е. никто никому ничего не должен.
- Тип — способ учета ежемесячных выплат. Если равен 1, то выплаты учитываются на начало месяца, если равен 0, то на конец. У нас в России абсолютное большинство банков работает по второму варианту, поэтому вводим 0.
Также полезно будет прикинуть общий объем выплат и переплату, т.е. ту сумму, которую мы отдаем банку за временно использование его денег. Это можно сделать с помощью простых формул:
Вариант 2. Добавляем детализацию
Если хочется более детализированного расчета, то можно воспользоваться еще двумя полезными финансовыми функциями Excel — ОСПЛТ (PPMT) и ПРПЛТ (IPMT). Первая из них вычисляет ту часть очередного платежа, которая приходится на выплату самого кредита (тела кредита), а вторая может посчитать ту часть, которая придется на проценты банку. Добавим к нашему предыдущему примеру небольшую шапку таблицы с подробным расчетом и номера периодов (месяцев):
Функция ОСПЛТ (PPMT) в ячейке B17 вводится по аналогии с ПЛТ в предыдущем примере:
Добавился только параметр Период с номером текущего месяца (выплаты) и закрепление знаком $ некоторых ссылок, т.к. впоследствии мы эту формулу будем копировать вниз. Функция ПРПЛТ (IPMT) для вычисления процентной части вводится аналогично. Осталось скопировать введенные формулы вниз до последнего периода кредита и добавить столбцы с простыми формулами для вычисления общей суммы ежемесячных выплат (она постоянна и равна вычисленной выше в ячейке C7) и, ради интереса, оставшейся сумме долга:
Чтобы сделать наш калькулятор более универсальным и способным автоматически подстраиваться под любой срок кредита, имеет смысл немного подправить формулы. В ячейке А18 лучше использовать формулу вида:
=ЕСЛИ(A17>=$C$7;»»;A17+1)
Эта формула проверяет с помощью функции ЕСЛИ (IF) достигли мы последнего периода или нет, и выводит пустую текстовую строку («») в том случае, если достигли, либо номер следующего периода. При копировании такой формулы вниз на большое количество строк мы получим номера периодов как раз до нужного предела (срока кредита). В остальных ячейках этой строки можно использовать похожую конструкцию с проверкой на присутствие номера периода:
=ЕСЛИ(A18<>»»; текущая формула; «»)
Т.е. если номер периода не пустой, то мы вычисляем сумму выплат с помощью наших формул с ПРПЛТ и ОСПЛТ. Если же номера нет, то выводим пустую текстовую строку:
Вариант 3. Досрочное погашение с уменьшением срока или выплаты
Реализованный в предыдущем варианте калькулятор неплох, но не учитывает один важный момент: в реальной жизни вы, скорее всего, будете вносить дополнительные платежи для досрочного погашения при удобной возможности. Для реализации этого можно добавить в нашу модель столбец с дополнительными выплатами, которые будут уменьшать остаток. Однако, большинство банков в подобных случаях предлагают на выбор: сокращать либо сумму ежемесячной выплаты, либо срок. Каждый такой сценарий для наглядности лучше посчитать отдельно.
В случае уменьшения срока придется дополнительно с помощью функции ЕСЛИ (IF) проверять — не достигли мы нулевого баланса раньше срока:
А в случае уменьшения выплаты — заново пересчитывать ежемесячный взнос начиная со следующего после досрочной выплаты периода:
Вариант 4. Кредитный калькулятор с нерегулярными выплатами
Существуют варианты кредитов, где клиент может платить нерегулярно, в любые произвольные даты внося любые имеющиеся суммы. Процентная ставка по таким кредитам обычно выше, но свободы выходит больше. Можно даже взять в банке еще денег в дополнение к имеющемуся кредиту. Для расчета по такой модели придется рассчитывать проценты и остаток с точностью не до месяца, а до дня:
Предполагается что:
- в зеленые ячейки пользователь вводит произвольные даты платежей и их суммы
- отрицательные суммы — наши выплаты банку, положительные — берем дополнительный кредит к уже имеющемуся
- подсчитать точное количество дней между двумя датами (и процентов, которые на них приходятся) лучше с помощью функции ДОЛЯГОДА (YEARFRAC)
Источник
В
пакете Excel
существует группа функций, предназначенных
для расчета финансовых операций по
кредитам, ссудам, займам. Эти расчеты
основаны на концепции временной стоимости
денег и предполагают неравноценность
денег, относящихся к разным моментам
времени. Эта группа функций охватывает
следующие расчеты:
определение
наращенной суммы (будущей стоимости),определение
начального значения (текущей стоимости),определение
срока платежа и процентной ставки,расчет
периодических платежей, связанных с
погашением займов.
Общая
формула расчета, которуюExcel
использует при вычислении финансовых
аргументов, связанных с денежными
потоками, имеет вид:
где
pmt
– фиксированная
(неизменная) периодическая сумма платежа;
n
– общее
число периодов выплат,
r
– процентная
ставка за один период,
type
– число 0
или 1, обозначающее, когда производится
выплата (1 – в начале периода, 0 – в конце
периода),
—
текущая стоимость вклада (займа), по
которому начисляются проценты по ставке
r%
n—ное
число периодов или текущая стоимость
серии фиксированных платежей,
-будущая стоимость вклада (займа) или
будущая стоимость серии фиксированных
периодических платежей.
Если
процентная ставка за период начисления
r=0, то используется следующая формула:
Эти
формулы используют встроенные функцииБC,
КПЕР, ПC,
ПЛТ, ЭФФЕКТ и
другие.
ПС
— текущий объём вклада
Позволяет
рассчитать текущий объем вклада.
Синтаксис
ПС
(норма;кпер;выплата;бс;тип)
норма
— процентная
ставка за период. Так, если Вы получили
ссуду под станок под 15% годовых и делаете
ежемесячные выплаты, то ставка
процента за месяц составит 15%/12, или
1,25%. Аргумент норма
в данном
случае может принимать значения 15%/12
или 1,25% или 0,0125.
кпер
— общее число
периодов выплат годовых процентов. Так,
если Вы получили ссуду на 3 года под
станок и делаете ежемесячные платежи,
то Ваша ссуда имеет 3*12 (или 36) периодов.
Аргумент кпер
в данном случае принимает значение 36.
выплата
—
размер выплаты, производимой в каждом
периоде и не меняющейся в течение всего
времени выплаты процентов. Обычно,
выплата включает основные платежи и
платежи по процентам без учета других
сборов или налогов. Например,
ежемесячная выплата по четырехгодичному
займу в 10 000 руб. под 12% годовых составит
308,3 руб. Аргумент выплата
в данном случае принимает значение
-308,3.
бс
— будущая
стоимость или баланс наличности, который
нужно достичь после последней выплаты.
Если бс
опущено, оно полагается равным 0 (будущая
стоимость займа, например, равна 0).
Например, если Вы хотите накопить 50000
руб. в течение18 лет, то 50 000 руб. это и
есть будущая стоимость. Вы можете сделать
предположение о сохранении заданной
процентной ставки и определить, сколько
нужно откладывать каждый месяц.
тип
— число,
определяющее, когда должна производиться
выплата. Может принимать значения 0 или
1: 0 означает — выплата в конце периода,
1 — выплата в начале периода.
Выбранные
единицы измерения для аргументов норма
и кпер
должны соответствовать друг другу.
Если Вы делаете ежемесячные выплаты
по четырехгодичному займу под 12% годовых,
то используйте 12%/12 для задания аргумента
норма,
4*12 для задания аргумента кпер.
Если Вы делаете ежегодные платежи по
тому же займу, то используйте 12% для
задания аргумента норма
и 4 для задания аргумента кпер.В
функциях, связанных с интервалами
выплат, выплачиваемые деньги
представляются отрицательным числом,
а получаемые деньги представляются
положительным числом. Например, депозит
в банк на сумму 1 000 руб. представляется
для вкладчика аргументом -1000, а для
банка аргументом 1000.
Пример
Фирме
потребуется 5000 руб. через 12 лет. В
настоящее время фирма располагает
деньгами и готова положить их на депозит
единым вкладом, чтобы через 12 лет он
достиг 5000 руб. Определим необходимую
сумму текущего вклада, если ставка
процента по нему составляет 12% годовых.
Необходимая
сумма текущего вклада составит:
ПС
(12%;12;;5000)=-1283.38 руб.
БС
— стоимость постоянных платежей в
определенные периоды на основе постоянной
процентной ставки
Позволяет
рассчитать объем вклада через определенный
промежуток времени на основе периодических
постоянных платежей и постоянной
процентной ставки.
Синтаксис
БС(норма;кпер;выплата;пс;тип)
ставка
— процентная
ставка за период.
кпер
— количество
периодов, в которые производится выплата
годовых процентов.
выплата—
размер выплаты, производимой в каждом
периоде; это значение постоянно в течение
всего времени выплат. Обычно плата
состоит из основного платежа и платежа
по процентам без учета других налогов
и сборов.
пс
— общая сумма
всех будущих платежей с настоящего
момента. Если аргумент пс
опущен,
то он полагается равным 0.
тип
— число,
определяющее когда должна производиться
выплата. Может принимать значения 0 или
1: 0 — выплата в конце периода, 1 — выплата
в начале периода.
Более
подробная информация об аргументах
функции БЗ и о других функциях выплат
по процентным вкладам приведена в
описании функции ПЗ.
Единицы
измерения для аргументов норма
и кпер
должны быть согласованы. Если производятся
ежемесячные платежи по четырехгодичному
займу из расчета 12% годовых, то норма
должна быть 12%/12 , а кпер
должно быть 4*12. Если производятся
ежегодные платежи по тому же займу, то
нормадолжна быть
12%, а кпер
должно быть 4.Ваш
вклад представляется отрицательным
числом, а деньги, которые вы получите,
представляются положительным числом.
Пример
Определим,
сколько денег будет на счету через год,
если вы собираетесь вложить 1000 рублей
под 6% годовых (что составит в месяц
6%/12 или 0,5%). Причем вы собираетесь
вкладывать по 100 рублей в начале каждого
следующего месяца в течение года.
Через
год на счете будет:
БС
(0,5%;12;-100;-1000;1)=2301,40 р.
ПРПЛТ
— платежи по процентам за период на
основе постоянных периодических выплат
и постоянной процентной ставки
С
помощью данной функции можно рассчитать
процентные платежи за текущий период
при периодических постоянных выплатах
и постоянной процентной ставке. Полное
описание аргументов функции ПРПЛТ
и более подробная информация о функциях,
связанных с ежегодными выплатами,
приведены в описании функции ПС.
Синтаксис
ПРПЛТ(норма;период;кпер;тс;бс;тип)
норма
—
процентная ставка за период.
период
— текущий
период, в котором требуется определить
прибыль (может принимать значения от 1
до кпер).
кпер
— общее число
периодов выплат.
тс
— сумма,
которую составят будущие платежи с
настоящего момента.
бс
— баланс
платежей, который нужно достичь после
последней выплаты.
тип
— число,
определяющее, когда должна производиться
выплата. Может принимать значения 0 или
1: 0 — выплаты в конце периода, 1 — выплаты
в начале периода.
Единицы
измерения для аргументов норма
и кпер
должны быть согласованы. Если производятся
ежемесячные платежи по трехгодичному
займу из расчета 10% годовых, то норма
должна быть 10%/12 , а кпер
должно быть 3*12. Если производятся
ежегодные платежи по тому же займу, то
норма
должна быть 10%, а кпер
должно быть 3.Выплачиваемые
денежные средства представляются
отрицательным числом, а получаемые
денежные средства представляются
положительным числом.
Пример
Требуется
определить доход за первый месяц от
четырехгодичного займа в 1000000 рублей
из расчета 15% годовых:
ПРПЛТ(0,15/12;1;48;1000000)
равняется -12500 рублей.
ПЛТ
— расчет постоянных периодических
выплат
Функция
вычисляет величину выплаты за один
период на основе фиксированных
периодических выплат и постоянной
процентной ставки. Выплаты, рассчитанные
функцией ПЛТ, включают основные платежи
и платежи по процентам.
Синтаксис
ПЛТ(норма;кпер;нз;бс;тип)
норма
– норма
прибыли за период займа.
кпер
– общее
число периодов выплат годовой ренты
нз
– текущая стоимость: общая сумма всех
будущих платежей с настоящего момента.
бс
– будущая стоимость или баланс наличности,
который нужно достичь после последней
выплаты.
тип
—число,
определяющее, когда должна производиться
выплата.
Пример
Предположим,
что необходимо накопить 4000 руб. за 3
года, откладывая постоянную сумму в
конце каждого месяца. Какой должна быть
эта сумма, если норма процента по вкладу
составляет 12% годовых?
Величина
ежемесячных выплат составит:
ПЛТ(12%/12;12*3;;4000)=-92.86
руб.
ОСПЛТ
— расчет основных платежей по займу
Функция
вычисляет величину основного платежа
(выплаты задолженности) по займу, который
погашается равными платежами в конце
или начале каждого расчетного периода,
на указанный период.
Синтаксис
ОСПЛТ(норма;период;кпер;тс;бс;тип)
норма
– норма
прибыли за период.
период
– период,
который должен быть в интервале от 1 до
кпер.
кпер
– общее
число периодов выплат годовой ренты.
тс
– текущая
стоимость: общая сумма всех будущих
платежей с настоящего периода.
бс
– будущая
стоимость или баланс наличности, который
нужно достичь после последней выплаты.
тип
—число,
определяющее, когда должна производиться
выплата.
Пример
Банком
выдан кредит в 7000 руб. на 3 года под 17%
годовых, начисляемых один раз в конце
каждого периода.
Определите
размер ежегодных основных выплат по
займу.
Основная
часть платежа на каждый из трех периодов
составит соответственно:
ОСПЛТ(17%;1;3;-70000)=19780.16
руб.
ОСПЛТ(17%;2;3;-70000)=23142.78
руб.
ОСПЛТ(17%;3;3;-70000)=27077.06
руб.
ОБЩПЛТ
— расчет суммы платежей по процентам
по займу между двумя периодами выплат
Функция
вычисляет накопленный доход (сумму
платежей по процентам) по займу, который
погашается равными платежами в конце
или начале каждого расчетного периода,
между двумя периодами выплат.
Синтаксис
ОБЩПЛТ(ставка;кол_пер;нз;нач_период;кон_период;тип)
ставка
– процентная ставка.
кол_пер
– общее
количество периодов выплат.
нз
– текущее
значение.
нач_период
– первый
период.
кон_период
— последний период.
Пример
Пусть
заем под недвижимость сделан на следующих
условиях: процентная ставка –9% годовых;
срок-30 лет, размер ссуды – 125000 руб.,
проценты начисляются ежемесячно.
Найти
сумму выплат по процентам за 2-й год и
за 1-й месяц займа.
Общая
выплата по процентам за второй год
составит:
ОБЩПЛТ(9%/12;30*12;125000;13;24)=-11135.23
руб.
Одна
выплата за первый месяц составит:
ОБЩПЛТ(9%/12;30*12;125000;1;1)=-937.50
руб.
ОБЩДОХОД
— расчет суммы основных выплат по займу
между двумя периодами
Функция
вычисляет сумму основных выплат по
займу, который погашается равными
платежами в конце или начале каждого
расчетного периода, между двумя периодами.
Синтаксис
ОБЩДОХОД
(ставка;кол_пер;нз;нач_период; нач_период;)
ставка
– процентная ставка.
кол_пер
– общее
количество периодов выплат.
нз
– текущее
значение.
нач_период
– первый
период.
кон_период
— последний период.
Пример
Выдана
ссуда размером 1000$ сроком на 6 лет под
15% годовых; проценты начисляются
ежеквартально.
Определите
величину основных выплат за 5 год.
Если
ссуда погашается равными платежами в
конце каждого расчетного периода, то
размер выплаты задолженности за пятый
год составит:
ОБЩДОХОД(15%/4;6*4;1000;17;20)=-201.43$.
Комплексный
пример
Банком
выдан кредит в 10000$ на 5 лет под 12% годовых,
начисляемых один раз в конце каждого
периода. По условиям договора кредит
должен быть погашен равными долями в
течение указанного срока, выплачиваемыми
в конце каждого периода.
Разработать
план погашения кредита.
Периодический
платеж по данной операции составит:
ПЛТ(12%;5;-10000)=2774.10$.
Процентная
часть платежа на первый период составит:
ПРПЛТ(12%;1;5;-10000)=1200,00$.
Основная
часть платежа, направленная на погашение
долга за первый период, составит:
ОСПЛТ(12%;1;5;-10000)=1574.10$.
Как
видно выполняется тождество:
ПРПЛТ()+ОСПЛТ()=ПЛТ()=2774.10$.
Будущее
значение суммы, которую банк получит в
результате проведения операции:
ПЛТ(12%;5;-10000)*5=13870.50$.
Накопленная
сумма процентов за весь период составит:
ОБЩПЛТ(12%;5;10000;1;5;0)=-3870,49$.
Накопленная
сумма в счет погашения по займу за 5 лет
составит:
ОБЩДОХОД(12%;5;10;1;5;0)=-10000.00$.
Как
видно, сумма полученных величин равна
общей сумме, выплаченной по данному
займу:
ОБЩДОХОД()+ОБЩПЛАТ()=13870.49$.
(В
силу заложенного алгоритма расчета
функции ОБЩДОХОД() и ОБЩПЛАТ возвращают
отрицательные значения. Для получения
положительных значений можно задать
их со знаком минус).
На
рис. 1 приведена таблица с планом погашения
кредита.
ЭФФЕКТ
— эффективная годовая процентная ставка
Позволяет
рассчитать фактическую годовую процентную
ставку, если известны годовая процентная
ставка и количество периодов в году.
Синтаксис
ЭФФЕКТ(нoмuнaльнaя_cmaвкa;кол_пep)
номинальная_ставка
—
номинальная годовая процентная ставка.
кол_пер
— количество
периодов в году.
Рис.
1. План погашения кредита
Число
периодов в году округляется до целого.Функция
ЭФФЕКТ
выдает значение ошибки #ЗНАЧ! в случае,
если один из аргументов — не число либо,
если номинальная_ставкаменьше 0 или
кол_пер
меньше 1.
Пример
Рассчитаем
эффективную годовую процентную ставку
при ставке 5,25% и четырех периодах в
году.
ЭФФЕКТ(5,25%;4)
равняется 0,053543 или 5,35%.
КПЕР
— определение срока платежа
Эта
функция вычисляет общее число периодов
выплат как для единой суммы вклада
(займа), так и для периодических постоянных
выплат на основе постоянной процентной
ставки. Если платежи производятся
несколько раз в год, найденное значение
необходимо разделить на число расчетных
периодов в году, чтобы найти число лет
выплат.
Синтаксис
КПЕР(норма;выплата;нз;бс;тип)
норма
– норма
прибыли за период.
выплата—
размер выплаты, производимой в каждом
периоде.
нз
— общая сумма
всех будущих платежей с настоящего
момента. Если аргумент нз опущен, то он
полагается равным 0.
бс
— будущая
стоимость или баланс наличности, который
нужно достичь после последней выплаты.
тип
— число,
определяющее, когда должна производиться
выплата.
Пример
Ожидается,
что ежегодные доходы от реализации
проекта составят 33 млн. руб. Необходимо
рассчитать срок окупаемости проекта,
если инвестиции к началу поступления
доходов составят 100 млн. руб., норма
дисконтирования 12.11%.
Срок
окупаемости проекта составит:
КПЕР(12.11%;33;-100)=4года.
Определение
скорости оборота инвестиций
ВНДОХ
— процентная ставка дохода для ряда
последовательных периодических
выплат или поступлений
Позволяет
рассчитать процентную ставку дохода
от инвестиций для ряда последовательных
операций с денежными средствами,
представленными числовыми значениями.
Объемы операций могут быть различны.
Однако они должны происходить через
равные промежутки времени, например,
ежемесячно или ежегодно. Процентная
ставка дохода, полученного от инвестиции,
состоит из выплат (отрицательные
значения) и поступлений (положительные
значения), которые происходят в регулярные
периоды времени.
Синтаксис
ВНДОХ
(значения;предположение)
значения
— массив или
ссылка на ячейки, содержащие числа, для
которых вычисляется процентная ставка
инвестиций.
предположение
—
прогнозируемая величина.
Аргумент
значения
должен включать в себя по крайней мере
одно положительное значение и одно
отрицательное значение, для того, чтобы
можно было вычислить процентную ставку.Начиная
со значения прогноз, функция ВНДОХ
выполняет
циклические вычисления, пока не получит
результат с точностью 0,00001 процента
(такой метод вычисления называется
методом итераций).Функция
ВНДОХ выдает значение ошибки #ЧИСЛО! в
том случае, если не может получить
результат после 20 попыток.Обычно
нет необходимости задавать аргумент
прогноз
для вычислений с помощью функции ВНДОХ.
Если прогнозопущен, то
он полагается равным 0,1 (10 процентов).
Если ВНДОХ
выдает значение ошибки#ЧИСЛО!,
можно попытаться выполнить вычисления
еще раз с другим значением аргумента
прогноз.
Пример
Предположим,
что затраты по проекту составят 500 тыс.
руб. Ожидаемые доходы составят 50 , 100 ,
300 и 200 тыс. руб. в течение последующих
4 лет. Оценим
экономическую целесообразность проекта
по скорости оборота инвестиций, если
рыночная норма дохода 12%.
Ячейки
А1:А5 содержат следующие значения: -500,
50, 100, 300 и 200 соответственно.
Внутренняя
скорость оборота инвестиций составит:
ВНДОХ(А1:А5)=9.25%.
Это
меньше, чем рыночная норма, поэтому
проект должен быть отвергнут.
В
том случае, если эта какая-либо из
финансовых функций недоступна из Excel,
следует установить надстройку Пакет
Анализа (AnalysisToolPak),
а затем подключить его с помощью команды
Сервис
— Надстройки…
ЗАДАЧИ
Соседние файлы в предмете [НЕСОРТИРОВАННОЕ]
- #
- #
- #
- #
- #
- #
- #
- #
- #
- #
- #
Источник
