Формула расчета линейной амортизации и регрессивной в Excel

Программа Excel предлагает сразу несколько функций для расчета амортизации: ФУО, ДДОБ, АПЛ, АСЧ и ПУО. Рассмотрим функцию АПЛ для линейного метода вычисления амортизации и функцию ПУО с регрессивным методом.

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

Формула расчета линейной амортизации по функции АПЛ в Excel

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

Столбцы A:D содержат следующие данные заполненные пользователем графика амортизации:

1. Номер основных средств – уникальный идентификатор для каждого основного средства. Он не является обязательным в этом графике, но будет удобным при отметке средств.
2. Суммарная стоимость – сумма начальной стоимости ввода в эксплуатацию основных средств. Содержит цену приобретения средства, а также налоги, заплаченные при приобретении, расходы на доставку к месту эксплуатации и все другие сопутствующие расходы, связанные с вводом в эксплуатацию каждого основного средства. Данная суммарная стоимость так же еще часто называется «базой» или «базовой стоимостью».
3. Год приобретения – год, в котором основное средство было введено в эксплуатацию. Может отличаться от года, в котором была проведена оплата за средство. Данный год определяет начало всего амортизационного периода эксплуатации.
4. Период лет использования – оцененное число лет, на протяжении которых средство будет использоваться.

Ячейки диапазона E2:M6 содержат следующую формулу:

Самая главная часть выше приведенной формулы – это функция =АПЛ($B2;0;$D2), которая рассчитывает амортизацию линейным методом за один период. Функция имеет 3 аргумента:

1. Начальная стоимость.
2. Остаточная стоимость.
3. Время эксплуатации.

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

Сама функция АПЛ является очень простой. Но так как вы имеете дело с графиком амортизации, придется приложить немного усилий для его подготовки. Первая в формуле функция ЕСЛИ проверяет находиться ли дата, узнанная в заголовке текущего столбца в периоде эксплуатации основного средства. Если дата в ячейке E$1 является ранней чем дата приобретения основного средства $C2 – это значит, что средство еще не введено в эксплуатацию и амортизационные отчисления равны 0. Если же вместо этого значение даты в ячейке E$1 является позднейшей от даты приобретения $C2 плюс период лет использования $D2, тогда это значит, что основное средство полностью изношено и выведено из эксплуатации. А его амортизационные отчисления также равны 0. Оба условия помещены внутри функции ИЛИ, благодаря чему если одно из условий будет выполнено, то целое выражение будет возвращать значение ИСТИНА, а значит значение во втором аргументе функции ЕСЛИ, которое =0. Если же ни одно условие не будет выполнено формула возвратит результат вычислении функции АПЛ.

Вторая часть формулы также содержит слияние функций ИЛИ и ЕСЛИ. Использование в них логическое выражение определяет, будет ли год, записанный в ячейке E$1 первым =$C2 или последним =$C2+$D2 годом периода амортизации. Если значение одного с выражений является ИСТИНА, размер амортизационных отчислений вычисленный линейным методом умножается на 0,5 (то есть разделяется по полам), согласно с принятой полугодовой конвенцией амортизации в данном примере.

Все ссылки, использованные в выше приведенной формуле, являются смешанными (частично абсолютными), благодаря чему формулу можно копировать во все нижние и правые ячейки. Ссылки в формулах будут соответственно изменятся автоматически. Ссылка на целую первую строку позволяет выражениям вычислять результаты с учетом дат записанных в заголовках столбцах первой строки листа Excel. Аналогично ссылки на целые столбцы B:D позволяют копированным формулам использовать одни и те же значения со столбцов «Суммарная стоимость», «Год приобретения» и «Период лет использования».



Функция ДДОБ для расчета ускоренной амортизации основных средств в Excel

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

Программа Excel предлагает функцию ДДОБ для расчета ускоренной амортизации. Данная функция вычисляет амортизационные отчисления для остаточной стоимости основного средства по линейному методу двойного уменьшения остатка с увеличенным коэффициентом:

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

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

Формула ПУО для расчета амортизации регрессивным методом в Excel

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

Ниже на рисунке представлен график амортизации с использование следующей формулы:

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

Первая часть формулы является идентичной части формулы с функцией АПЛ, описанной выше в данной статье. Если дата в строке 1 не включена в срок эксплуатации основных средств, тогда амортизационные отчисления =0. В противном случае вычисляется результат, возвращаемый функцией ПУО и используется как содержимое третьего аргумента для функции ЕСЛИ. Функция же ПУО содержит в свой аргумент начало и конец периода амортизации вычисление которых будут описаны ниже:

Первые три аргумента функции ПУО такие же, как и в функции АПЛ: начальная стоимость, остаточная стоимость и время эксплуатации. Функция АПЛ возвращает одно и тоже значение для каждого периода амортизации, поэтому в ней нет необходимости указывать расчетные периоды. Вместо этого функция ПУО возвращает разные значения в зависимости от номера периода расчета амортизации. Последние 2 аргумента функции ПУО (начальный и конечный период) определяют только те номера периодов, которых будет касается вычисление функции. Период эксплуатации, указанный в ячейке $D2 удвоенный, о чем речь пойдет далее.

Ни одна функция Excel не учитывает принятой конвекции амортизации. Это значит, что амортизация рассчитывается так, как бы все основные средства были приобретены первого числа первого года. На практике – это далеко не так. В данном примере принята полугодовая конвенция, согласно которой в первом и последнем полугодии эксплуатации основного средства начисляется только половина вычисленного амортизационного отчисления. Чтобы с помощью функции ПУО рассчитать амортизацию согласно с полугодовой конвенцией, необходимо немного «обмануть» Excel так, чтобы он посчитал срок эксплуатации основного средства в два раза большим.

Если период эксплуатации средства составляет 5 лет, в тоже время начало и конец первого года определены номерами 0 и 1. Во втором году это будут уже номера 1 и 3, в третьем 3 и 5. Данная схема повторяется аж до последнего года, определенного номерами 9 и 10 (число десять значит двойной пятилетний период эксплуатации средства). Начальный период вычисляется выше указанной формулой следующим способом:

1. Если год учитывается в вычислениях, и он является годом приобретения средства, начальный период ему присваивается под номером 0.
2. Если год учитывается в вычислениях, и он является последним годом периода, тогда начальный период равен двойному сроку эксплуатации средства минус 1.
3. В случаи всех остальных лет от года приобретения вычитается текущий год, а результат умножается на число 2 и вычитается от полученного результата число 1.

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

Скачать графики линейной и регрессивной амортизации в Excel

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