10 лучших бухгалтерских формул и функций Excel (с примерами)

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

Подробнее: 25 лучших советов по Excel для оптимизации вашего использования

Формулы против функций

И формулы, и функции позволяют выполнять вычисления в Microsoft Excel. Формулы — это определяемые пользователем вычисления. Например, если пользователь хочет сложить значения в ячейках B2, B3 и B4 вместе, формула в Excel может выглядеть так: =B2+B3+B4. Функции — это встроенные формулы Excel. Примерами функций являются СУММ и СРЗНАЧ.

Лучшие бухгалтерские формулы Excel

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

1. Амортизация активов

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

• DB (стоимость, утилизация, срок службы, период, месяц): Метод снижения остатка амортизации может использоваться для уменьшения стоимости актива на фиксированный процент. Значение месяца является необязательным.

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

• DDB (стоимость, ликвидация, срок службы, период, коэффициент): Амортизация остатка с двойным снижением — это еще один метод Excel, который может удвоить норму линейной амортизации. Значение фактора является необязательным.

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

• SYD (стоимость, утилизация, срок службы, за): Сумма амортизации в годах также может показывать снижение стоимости предмета.

Пример: Хулио — бухгалтер, чей клиент хочет купить лодку за 5000 долларов. Клиент хочет знать, насколько лодка обесценится за первые пять лет своей жизни. Хулио решает рассчитать амортизацию при снижении баланса лодки. Ликвидационная стоимость (или остаточная стоимость) лодки составляет 350 долларов США, стоимость после амортизации лодки. Расчетный срок службы лодки, которую хочет купить клиент, составляет 15 лет. Хулио использует формулу =DB(5000, 350, 15, 5), чтобы найти, что амортизация остатка снижения стоимости лодки составляет 399,45 долларов США.

2. Годовая процентная ставка

Вы можете использовать функцию СТАВКА в Excel для расчета годовой процентной ставки по кредиту.

Формула: СТАВКА(nper, pmt, pv, fv, тип, предположение)

• Кпер: это количество периодов оплаты.

• Pmt: это означает сумму, выплачиваемую по кредиту каждый год.

• Pv: Представляет текущую стоимость кредита.

• Fv: Вы можете дополнительно включить остаток кредита после последнего платежа.

• Тип: этот необязательный аргумент означает, когда должен быть погашен кредит. Единицу можно использовать для начала периода оплаты, а ноль — для конца.

• Guess: это необязательное значение представляет собой предположение пользователя Excel о скорости. Это значение по умолчанию равно 10%.

Пример: клиентка Миши хочет знать, какая у нее годовая процентная ставка по кредиту в размере 7000 долларов, который она выплачивает из расчета 350 долларов в месяц в течение двух лет. Миша использует функцию =СТАВКА(24, 350, -7,000)*12, чтобы определить, что годовая процентная ставка по кредиту составляет 18,16%.

3. Сложные проценты

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

Формула: p * (1+r)^n

• P: это означает основную сумму кредита

• Р: Это процентная ставка по кредиту.

• N: Это инвестиционный период для кредита.

Пример: если Джеки возьмет личный кредит в размере 500 долларов США с выплатой 8% в течение двух лет, она может использовать Excel для расчета сложных процентов. Используя формулу = 500 * (1 + 8%) ^ 2, она могла найти, что ее сложные проценты по кредиту составят 583,20 доллара.

4. Платежи по ипотеке

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

Формула: ПЛТ (коэффициент, кпер, pv, fv, тип)

• Ставка: это процентная ставка по кредиту.

• Кпер: Кпер означает количество платежей по кредиту.

• PV: Это основная сумма кредита.

• FV: это значение представляет остаток денежных средств после последнего платежа. Это значение является необязательным. Если вы не включаете значение fv, по умолчанию оно равно нулю.

• Тип: указывает дату платежа по кредиту. Это значение также является необязательным. Значение единицы представляет начало периода оплаты, а значение нуля представляет конец периода оплаты.

Пример: клиент Марджори хочет купить дом на 30-летний ипотечный кредит в размере 100 000 долларов, который нужно погашать ежемесячно. Процентная ставка по кредиту составляет 7%. Марджори использует формулу =PMT(7%/12, 360, -100000), чтобы найти, что ее клиент должен ежемесячно платить 665,30 долларов.

5. Будущая стоимость инвестиций

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

Формула: БС(коэффициент, кпер, плт, пс, тип)

• Ставка: представляет собой процентную ставку за период.

• Кпер: Кпер — это количество периодов оплаты.

• Pmt: это стоимость платежа, сделанного за каждый период.

• Pv: Это необязательное значение представляет собой стоимость будущих платежей в настоящее время.

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

Пример: клиент Нины хочет накопить деньги на первый взнос за дом через три года. У ее клиента есть 7000 долларов на сберегательном счете, на который начисляется 3% годовых. Клиент планирует добавить на счет 3000 долларов в течение следующего года. Нина использует формулу =FV(3%/12, 36, -3000, -7000) в Excel, чтобы показать своему клиенту, что он может сэкономить 120 520,04 доллара на первоначальном взносе.

Подробнее: Как включить навыки работы с Excel в свое резюме

6. Эффективная годовая процентная ставка

Бухгалтеры могут использовать формулу ЭФФЕКТ для определения эффективной годовой процентной ставки по кредиту.

Формула: ЭФФЕКТ(номинальная_скорость, количество_периодов)

• Номинальная_ставка: это номинальная процентная ставка или процентная ставка до инфляции.

• Npery: это количество годовых периодов начисления сложных процентов или количество раз за период выплаты, когда проценты добавляются к кредиту.

Пример: клиент бухгалтера хочет знать реальную стоимость ежемесячного сложного кредита на 30 лет после вычета комиссий и процентов. Номинальная процентная ставка по кредиту составляет 8%. Бухгалтер использует функцию =ЭФФЕКТ(8%, 30*12), чтобы найти, что эффективная годовая процентная ставка по кредиту составляет 8,33%.

7. Фиксированная процентная ставка по кредиту

Вы можете рассчитать фиксированную часть процентов по кредитному платежу с помощью функции IPMT в Excel.

Формула: IPMT(скорость, за, кпер, pv, fv, тип)

• Ставка: это процентная ставка по кредиту.

• Per: Это период для процентов.

• Кпер: Кпер означает количество платежей по кредиту.

• PV: Это основная сумма кредита.

• FV: это значение представляет остаток денежных средств после последнего платежа. Это значение является необязательным.

• Тип: указывает дату платежа по кредиту. Это значение также является необязательным.

Пример: Компания просит бухгалтера найти долю процентов по пятилетнему платежу по кредиту в размере 15 000 долларов США с процентной ставкой 5%, которую компания выплачивает ежемесячно. Бухгалтер может использовать функцию =IPMT(5%/12, 1, 5*12, -15 000), чтобы найти, что 6% каждого ежемесячного платежа составляют проценты.

8. Денежные потоки

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

Функция: MIRR(денежные_потоки, ставка_финансирования, ставка_реинвестирования)

• Cash_flows: первый параметр относится к ячейкам, содержащим денежные потоки.

• Finance_rate: это норма прибыли (или ставка дисконтирования), показанная в процентах.

• Reinvest_rate: это процент, который представляет собой процентную ставку, полученную от реинвестированных денежных потоков.

Пример: ежеквартальные значения денежного потока компании в таблице Excel представляют собой инвестиции в размере 5000 долларов США и доходы в размере 6700 долларов США, 8000 долларов США и 7000 долларов США за каждый месяц квартала. Требуемая норма прибыли составляет 15%. Он получает 10% процентную ставку на реинвестированные денежные потоки. Бухгалтер компании вводит =MIRR(A13:A15, 15%, 10%), чтобы получить модифицированную внутреннюю норму доходности 70%.

9. Внутренняя норма доходности

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

Функция: IRR(значения, предположение)

• Cash_flows: это ссылка на ячейки, содержащие денежные потоки компании. Например, (D6:D11).

• Предположение. Этот необязательный параметр представляет собой предположение пользователя о внутренней норме доходности.

Пример. Годовые денежные потоки компании указаны в столбце F электронной таблицы Excel. Бухгалтер может использовать формулу IRR(F5:F17), чтобы найти годовую внутреннюю норму прибыли компании.

10. Начисленные проценты

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

Функция: ACCRINT(id, fd, sd, rate, par, freq, base, calc)

• id: это дата выпуска ценной бумаги.

• fd: представляет дату первого процентного платежа по ценной бумаге.

• sd: это дата расчета ценной бумаги.

• ставка: представляет процентную ставку ценной бумаги.

• номинал: это номинальная стоимость ценной бумаги или ее номинальная стоимость.

• freq: это количество платежей в год.

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

• calc: этот необязательный параметр определяет, как Excel вычисляет метод.

Пример: Компания хочет рассчитать проценты, начисленные ей по облигации номинальной стоимостью 6000 долларов. Дата выдачи 01.05.17. Это была также дата начала интереса. Дата его расчетов 01.05.19. Процентная ставка по облигации составляет 6%. Проценты по облигации выплачиваются ежеквартально. Бухгалтер использует функцию =НАЧИСЛ(01.05.17, 01.05.17, 01.05.19, 6%, 6000, 4), чтобы определить, что компания начислила проценты по облигации.