Содержание

Образец составления графика платежей по кредиту в Excel

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

Преимущества и недостатки использования Excel

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

Способность выполнения дополнительных функций. Помимо платёжного календаря и собственного реестра программа может составлять бюджеты и лимитировать платежи относительно них.
Возможность организовать обмен информацией с платёжной подсистемой 1С. Благодаря этому преимуществу можно компенсировать необходимость повторного ввода некоторых данных.
Использование в расчётах простых и понятных формул. Это помогает новичкам быстрее разобраться в процессе составления графика платежей и устранить некоторые ошибки.

Несмотря на эти важные достоинства, у использования Excel есть и несколько недостатков. Их нужно учитывать перед началом проведения расчётов и составлением платёжного графика, в противном случае можно столкнуться с различными трудностями, которые осложнят процесс и увеличат вероятность получения недостоверных данных. Отрицательные характеристики проведения расчётов Excel:

Невозможность контроля ссылочной ценности. Программа не способна противостоять удалению каких-либо данных из ячеек, даже если на них установлены макросы или специальная защита.
Трудности с многопользовательским режимом работы. В Excel довольно трудно организовать одновременную работу большого количества людей, так как программа способна функционировать только в единичном режиме. Выходом из такой ситуации будет создание специальной базы данных.
Конфиденциальность информации и ограничение доступа. Для специалиста не составит труда взломать установленный пароль, поэтому Excel редко используется в тех случаях, когда нужно составить платёжный график для большого количества людей. Из-за отсутствия необходимой защиты доступ к файлу с информацией должен быть строго ограничен.
Повторный ввод данных. Excel, в отличие от 1С, не способен обмениваться информацией с клиентом банка. Из-за этого необходимо будет дорабатывать используемую базу данных и составлять платёжный календарь в ней.
Ограничение размера файла. Для ведения некоторых расчётов Excel подойдёт идеально, но для большого количества данных возможностей программы будет недостаточно.

Образцы расчётов по кредиту

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

Аннуитетные платежи

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

Размер ежемесячного аннуитетного платежа определяется по стандартной формуле А=K*S, где S — сумма кредита, а K — коэффициент аннуитетного платежа. Последний параметр вычисляется с учетом продолжительности срока взятого кредита и месячной процентной ставки (1/12 от годовой). Порядок введения данных в Excel:

В программе создаётся отдельный лист, ему присваивается соответствующее название.
Создаётся таблица из трех строк и двух столбцов.
В неё вводятся входные данные (размер кредита, процентная ставка и срок в месяцах).
После этого формируется пустой платёжный график. Он должен содержать 2 столбца и столько строк, на сколько месяцев взят заём.
Выделяется первая пустая ячейка в правом столбце.
В командной строке выбирается раздел «Формулы» и подраздел «Вставить функцию».
В открывшемся окне выбирается ПЛТ (специальная функция, которая позволяет рассчитать аннуитетные платежи).
При помощи мышки выделяются числовые значения. Их следует брать из первой таблицы.
После этого нажимается Enter, и выводится расчётное значение.

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

Дифференцированные выплаты

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

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

Расчёт размера дифференцированной выплаты осуществляется по общей формуле ДП=ОСЗ/(ПС*ОСЗ/ПП). В ней присутствуют такие условные обозначения:

ДП — величина платежа;
ПС — месячная ставка в процентах, которая равна двенадцатой части годовой;
ПП — количество месяцев, оставшихся до полного погашения кредита;
ОСЗ — остаток ссудной задолженности.

ДП — величина платежа;
ПС — месячная ставка в процентах, которая равна двенадцатой части годовой;
ПП — количество месяцев, оставшихся до полного погашения кредита;
ОСЗ — остаток ссудной задолженности.

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

Первым делом создаётся отдельный лист, название которого выбирает сам заёмщик или кредитор.
Создаётся небольшая таблица из двух столбиков и трех строчек.
В неё вводятся 3 числовых значения (срок в месяцах, размер ставки в процентах, величина кредита).
На следующем этапе составляется незаполненный график погашения задолженности. Для этого формируется таблица, состоящая из 5 столбиков и нескольких строчек (количество должно соответствовать числу месяцев).
Крайний левый столбец отводится для номера месяца.
Во втором слева определяется остаток задолженности по кредиту. Для этого выделяется верхняя пустая ячейка, и в неё вводится значение, равное сумме взятого кредита.
Во всех остальных строках ставится формула =ЕСЛИ (D10>$B$4;0;E9-G9). В ней D10 — это номер месяца, B4 — общее количество периодов, на которые выдан заём, G9 — размер основного долга в предыдущем месяце, E9 — остаток по кредиту на прошедший период.
Средний столбец отведён для выплаты процентов. В верхнюю ячейку вводится формула, которая предусматривает умножение двенадцатой части годовой процентной ставки ($B$3/12) на остаток по кредиту (E9).
Предпоследний столбик отображает размер выплат основного долга. Во все его ячейки поочерёдно вводится формула =ЕСЛИ (D9.

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

Полученные результаты распространяются на всю таблицу.

Процентная ставка

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

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

Все эти сведения оформляются в виде таблицы, которая содержит 5 строк и 2 столбика.

После этого выполняются те же действия, что и для аннуитетных платежей. Итогом вычислений становится определение ежемесячной процентной ставки и ПЛТ.

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

Первые 4 столбца заполняются одинаково для всех месяцев (номер периода, сумма аннуитетного платежа, комиссия, размер выплаты по кредиту).

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

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

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

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

Полная стоимость

Полная стоимость кредита должна рассчитываться в процентах по формуле ПСК=ЧБП*i*100, где ЧБП — число базовых периодов за один календарный год, i — ставка в процентах. Этот показатель можно также рассчитать при помощи программы MS Excel. Для этого нужно выполнить следующие действия:

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

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

После этого составляется платёжный график по одной из схем (аннуитетной или дифференцированной).

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

Для определения БП (базового периода) необходимо взять стандартный интервал. В большинстве случаев он составляет 28 дней.

Затем определяется ЧБП (число базовых периодов за год). Для этого количество дней в году делится на БП.

Затем определяется i (ставка в процентах за базовый период) путём деления исходной процентной ставки на ЧБП. Для этого в нужной ячейке пишется формула =B3/B8.

На основании полученного результата вычисляется ПСК (=B9*B8).

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

[expert_bq id=»1570″]Совместное использование задач расчетов платежей и поступлений дает возможность спланировать денежные потоки и остатки денежных средств в каждом плановом периоде. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] 4. Отчет Платежный календарь в «1С:Бухгалтерии 8» редакции 3.0 может включать в себя следующие разделы (при наличии соответствующих ситуаций): оплата от покупателей, прочие поступления, налоги и взносы, платежи поставщикам, зарплата, периодические платежи.

Планирование платежей

ДП — величина платежа;

ПС — месячная ставка в процентах, которая равна двенадцатой части годовой;

ПП — количество месяцев, оставшихся до полного погашения кредита;

ОСЗ — остаток ссудной задолженности.

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

Как Построить Платежный Календарь в Excel

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

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

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

Задачи, которые позволит решить платежный календарь

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

Управлять ликвидностью клиники. Одним из основных критериев правильности управленческих решений, принимаемых в финансовой сфере, является положительность совокупного потока ДС в любой момент времени;

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

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

Максимально исключить человеческий фактор;

Планирование потоков денежного оборота

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

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

Основные составляющие текущих активов — запасы, дебиторская задолженность и ДС, текущих обязательств — кредиторская задолженность.

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

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

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

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

Принципы, правила, последовательность формирования

Основные этапы разработки системы платежного календаря:

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

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

Построение механизма формирования реестров платежей, заявок.

Описание бизнес-процесса «Платежный календарь», определение в его рамках ответственных лиц по процедурам, задачам.

Регламентация и документальное оформление бизнес-процесса «Платежный календарь».

Автоматизация бизнес-процесса «Платежный календарь».

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

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

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

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

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

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

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

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

Заполнение плана входящих платежей

Перед тем как запланировать расходы необходимо хоть примерно спрогнозировать будущие доходы.

Заполняем план входящих платежей для того чтобы отразить планируемые поступления денег. В форме платежного календаря “Енот” это можно сделать при помощи мастера заполнения двумя способами:

На основании реальных данных о поступлениях денежных средств за последнее время (от месяца до года). Можно указать процент увеличения реальных сумм поступления ДС за прошлый период с учетом % переоценки. Это позволяет учесть ваши взгляды на сезонность. Рост или понижение потока клиентов и т.п. Осторожное планирование рекомендует считать небольшой % понижения от предыдущего периода. Т.е. планирование по программе “минимум” (Рис.3).

Рис.3 Заполняем план входящих платежей на основании реальных данных поступлениях денежных средств за последнее время (от месяца до года)

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

Рис.4 Заполняем план входящих платежей автоматически по Х денег в сутки

Оформление предстоящих выплат кредиторам

Оформление предстоящих выплат кредиторам по закупкам выполняется на основании заявок на расход ДС. Существует два варианта запланировать выплаты:

Это могут быть заявки на любые выплаты (кроме инкассации). Все они создаются с указанием вида движения (суть выплаты), объекта (получателя), суммы и запланированной даты выплаты. Такие заявки нуждаются в подтверждении руководителя (Рис.6).

Устранение кассовых разрывов. Утверждение заявок

Когда мы составили план доходов и затрат денежных средств, может сложиться ситуация при которой в один из дней плановый остаток на конец дня окажется меньше НУЛЯ. Т.е. произойдет так называемый “кассовый разрыв” (Рис.7).

Денежная логистика

Платежный календарь это инструмент планирования. Ежедневно в нем меняется остаток денежных средств на начало дня в соответствии с их фактическими поступлениями за прошлый день. Суммы которые доступны к выплате могут находиться в разных кассах клиники или на Расчетном счете.

Задача руководителя предусмотреть из какой кассы будут производиться выплаты по заявкам и обеспечить в этой кассе достаточное количество ДС на момент выплаты. Также нужно учитывать и планировать конвертацию безналичных в наличные и наоборот (Рис.9).

Выплаты по заявкам

Для того что бы выплатить деньги и закрыть сделанную заявку — достаточно при оплате выбрать ее в поле “Заявка” документа платежа.

В денежном ордере можно воспользоваться мастером — Заполнить по заявкам. При этом все заявки, на дату ордера, автоматом подтянутся в ордер с суммами. Останется только откорректировать и провести (Рис.10).

Корректировка платежей. Работа с журналом заявок

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

Жесткий контроль расхода денег

Для того чтобы исключить непродуманную/несогласованную выплату денежных средств нужно включить “жесткий режим” расхода денег — строго по заявкам.

В настройках программы включаем режим учетной политики «Использовать заявки на расход денежных средств». Это означает, что провести документ на расход денег на который нет утвержденной заявки программа не даст (Рис.11).

Рис.11 Включаем режим учетной политики «Использовать заявки на расход денежных средств»

Все суммы заявок отражаются в календаре в блоке Расход. Соответственно указанным в заявке наличном или безналичном разделе. Не подтвержденные заявки отображаются в календаре розовой клеткой с суммой. Подтвержденные — голубой клеткой с суммой. Отклоненные заявки отображаются перечеркиванием суммы на белом фоне (Рис.12).

При возникновении кассовых разрывов любую заявку можно открыть двойным кликом прямо из календаря для редактирования. Спорные заявки можно аннулировать или перенести на другие даты. Поменять способ оплаты (наличный/безналичный).
[expert_bq id=»1570″]В целом работа над созданием вечного календаря завершена, хотя вы можете ещё долго проводить над ним различные косметические работы, редактируя внешний вид на свой вкус. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Для тех, кто просто хочет скачать календарь в Excel на текущий год, на картинке ниже показан как он выглядит. Красным отмечены праздничные дни, розовым – выходные. Текущий день, также автоматически выделяется заливкой.
Про платежный календарь — Shevchenko

На основании реальных данных о поступлениях денежных средств за последнее время (от месяца до года). Можно указать процент увеличения реальных сумм поступления ДС за прошлый период с учетом % переоценки. Это позволяет учесть ваши взгляды на сезонность. Рост или понижение потока клиентов и т.п. Осторожное планирование рекомендует считать небольшой % понижения от предыдущего периода. Т.е. планирование по программе “минимум” (Рис.3).

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

Дополнительные данные → Похожие темы → Все про Exel → Как вставить значения → Как объединить ячейки → Как вставить форматы → Дополнительные данные → Вставить формулы→ Аргументы функции