Дебет-Кредит
Украинский бухгалтерский еженедельник
#44'2001: Практичная бухгалтерия - Уроки по 1c: Бухгалтерии

Экспресс-анализ финансовой аренды с помощью Excel

Владимир ЛАВРЕНОВ


Предыдущие уроки "Автоматизации бухучёта: Excel"

Как рассчитать экономические показатели при аренде

Расчет показателей арендной платы уже рассматривался в "Дебете-Кредите" (см. статьи Леонида Гуйды в "ДК" №39 и №43). Использование для этого формулы, приведенной в П(С)БУ 14, не позволяет быстро подобрать необходимые параметры арендных платежей и проанализировать рентабельность финансовой аренды или выгодность кредита.

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

Сумму минимального арендного платежа (аннуитета) можно рассчитать с помощью формулы:

ППЛАТ(Ставка;Кпер;Нз;Бз;Тип),

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

ОСНПЛАТ(Ставка;Период;Кпер;Нз;Бз;Тип).

В приведенных формулах аргументы означают:

Ставка - арендная ставка процента или процентная ставка по кредиту (ссуде);

Период - период платежа (значение от 1 до Кпер);

Кпер - общее число выплат по аренде или кредиту (ссуде);

Нз - величина справедливой стоимости объекта аренды или дебиторская задолженность арендатора на начало срока аренды, или настоящая стоимость минимальных арендных платежей;

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

Тип - число 0 или 1, обозначающее, когда должна производиться выплата (0 или опущен - в конце периода, 1 - в начале периода).

Эти формулы можно использовать также и для других финансовых расчетов. Например, если вы задались целью накопить 10 000 грн за 2 года, предполагая, что удастся обеспечить на вкладе 18% годовых, с помощью функции ППЛАТ можно определить, какую сумму нужно откладывать ежемесячно:

ППЛАТ(18%/12; 2*12; 0; 10000) равняется 349,24 грн.

При ежемесячном, в размере 349,24 грн, пополнении вклада в течение 2 лет на 18%-ном депозите вы получите 10000 грн.

Рисунок 1

Как заполнить расчетную таблицу

Расчетная ведомость, показанная на рисунке 1, состоит из 4 частей:

- таблицы исходных данных;

- таблицы выбора условий выплаты;

- таблицы подбора параметров;

- расчетной таблицы.

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

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

Сначала введите в ячейку F19 формулу "=F3" (так как это вспомогательное значение, оно выделено бледным цветом), которая даст возможность заполнить все строки расчетной таблицы однотипными формулами, скопировав их из строки 20 (рис. 1).

Заполните ячейки этой строки такими формулами:

А20 =ЕСЛИ(И(ЕПУСТО(A19);A$10=1);0;
ЕСЛИ(A19<F$5*F$6;A19+1;””));
В20 =ЕСЛИ(ЕТЕКСТ(A20)=ИСТИНА;””;
ЕСЛИ(A$10=1;F$7+365*A20/F$5;F$7+365*(A20-1)/F$5));
С20 =ЕСЛИ(ИЛИ(ЕТЕКСТ(A20)=ИСТИНА;A20=0);
””;ЕСЛИ(A$10=1;ППЛАТ(F$4/F$5;F$5*F$6;-F$3);
ППЛАТ(F$4/F$5;F$5*F$6;-F$3;;1)));
D20 =ЕСЛИ(ЕТЕКСТ(C20)=ИСТИНА;””;C20-E20);
Е20 =ЕСЛИ(ИЛИ(ЕТЕКСТ(A20)=ИСТИНА;A20=0);””;
ЕСЛИ(A$10=1;ОСНПЛАТ(F$4/F$5;A20;F$5*F$6;-F$3);ОСНПЛАТ(F$4/F$5;A20;F$5*F$6;-F$3;;1)))
F20 =ЕСЛИ(ЕТЕКСТ(A20)=ИСТИНА;””;
ЕСЛИ (A20=0;F19;F19-E20));

Внимание! Наличие двойного символа ;; в функциях ППЛАТ и ОСНПЛАТ обязательно.

А теперь скопируйте эти формулы на достаточное число ячеек (это зависит от количества выплат по договору, но можете не ограничивать себя и 100 строками). Все расчетные данные по выбранному варианту арендного договора автоматически появятся в нужных ячейках таблицы, как только вы введете исходные значения в ячейки F3:F7. Если они не будут введены, таблица останется пустой. Например, если арендные платежи или платежи по кредиту производятся ежемесячно в течении 3-х лет, автоматически заполнятся только 36 строк, остальные ячейки таблицы останутся пустыми. Именно с этой целью в формулах используется функция ЕТЕКСТ( )*

В приведенных формулах на ячейки таблицы исходных данных и ячейку А10 используются смешанные ссылки (с абсолютной ссылкой на адрес строки, например A$10)

Аргумент F$3 должен быть со знаком "минус", так как предполагается, что расчет осуществляет арендодатель.

Надеюсь, вы обратили внимание, что в расчетной таблице итоговая строка занимает непривычное место (А18:F18)? Во-первых, это позволит при увеличении количества периодов уплаты просто копировать последнюю строку с формулами ниже, во-вторых, это удобнее.

Выбор условий выплаты арендных платежей

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

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

Внимание! Первому введенному на лист переключателю присвойте название "в кінці періоду", а второму - "на початку періоду". Эта последовательность будет использоваться в формулах расчета.

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

Рисунок 2

Для автоматизации выбора расчетных параметров можно воспользоваться таким управляющим элементом, как "счетчик". С его помощью можно быстро установить нужный параметр, удерживая ЛКМ стрелку на "счетчике". Его расположение на листе не имеет значения, единственное, что необходимо сделать, - ввести в поле "Связать с ячейкой" окна "Формат элемента управления" ссылку на ячейку, в которой вы будете менять данные (рис. 2).

Подбор параметров договора финансовой аренды

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

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

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

Итак, введите формулы в ячейки:

D14 (финансовый доход за указанный в ячейке F10 период) =C14-E14;

Е14 (компенсация в указанном периоде справедливой стоимости объекта аренды) =ЕСЛИ(A10=1;ОСНПЛАТ(F4/F5;F10;F5*F6;-E15);ОСНПЛАТ(F4/F5;F10;F5*F6;E15;;1));

С15 (сумма выплат по договору) =C14*F5*F6;

D15 (суммарный финансовый доход) =C15-E15;

Е15 (справедливая стоимость объекта аренды на дату договора) =ЕСЛИ(A10=1;ПЗ(F4/F5;F5*F6;-C14);ПЗ(F4/F5;F5*F6;-C14;;1)).

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

Для возврата даты внесения арендного платежа, относящегося к периоду, указанному в ячейке F10, введите в ячейку F11 формулу: =ЕСЛИ(A10=2;F7+365/F5*(F10-1);F7+365/F5*F10).

Для расчета длительности периода в формуле использован 365-дневный год. Конечно, дату периода можно ввести и вручную, однако формула даст возможность выбирать период и дату выплаты, переключая счетчик, расположенный в ячейках D10:D11 (не забудьте при установке счетчика связать его с ячейкой F10, как показано выше).

Теперь можно, варьируя значениями в ячейках F5:F7, F10, С14 и изменяя переключателями период внесения платежей (В10, В11), выбрать оптимальные параметры кредитно-финансового договора. В результате этой процедуры в ячейке Е15 должно быть подобрано значение, близкое к справедливой стоимости объекта аренды (сравните значения в ячейках F3, Е15 на рис. 1).

Рисунок 3


Примечания:

* Функция ЕТЕКСТ(ячейка-источник) дает возможность оставить целевую ячейку пустой в случае, если в ячейке-источнике записана формула, не возвращающая значение. Например, по договору аренды осуществляется 20 платежей, а расчетные формулы скопированы в 100 строк. Начиная с 21-й строки в ячейке-источнике (например А41) значения периода (21) не будет. Так как фактически ячейка А41 пуста, хотя в ней записан текст (формула), остальные ячейки этой строки, которые ссылаются на А41, будут пустыми.

© 2001
"Дебет-Кредит"
Редакция: debet-kredit@gc.kiev.ua