Розрахунок ПДВ в Microsoft Excel

Одним з безлічі показників, з якими доводиться мати справу бухгалтерам, працівникам податкової служби і приватним підприємцям, є податок на додану вартість. Тому для них актуальним стає питання його розрахунку, а також обчислення інших показників з ним пов'язаних. Провести даний розрахунок для одиничної суми можна і за допомогою звичайного калькулятора. Але, якщо потрібно обчислити ПДВ у безлічі грошових значень, то з одним калькулятором зробити це буде досить проблематично. До того ж, лічильної машинкою не завжди зручно користуватися.

На щастя, в Excel можна значно прискорити обчислення необхідних результатів для вихідних даних, які занесені в таблицю. Давайте розберемося, як це зробити.

процедура обчислення

Перед тим, як перейти безпосередньо до обчислення, давайте з'ясуємо, що ж собою являє зазначений податок. Податок на додану вартість є непрямим податком, який сплачують продавці товарів і послуг від суми реалізованої продукції. Але реальними платниками є покупці, так як величина податкового платежу вже включена у вартість придбаної продукції або послуг.

У Російській Федерації в даний момент встановлена ​​податкова ставка в розмірі 18%, але в інших країна світу вона може відрізнятися. Наприклад, в Австрії, Великобританії, України і Білорусі вона дорівнює 20%, в Німеччині - 19%, в Угорщині - 27%, в Казахстані - 12%. Але ми при розрахунках будемо використовувати податкову ставку актуальну для Росії. Втім, просто змінивши процентну ставку, ті алгоритми розрахунків, які будуть приведені нижче, можна використовувати і для будь-якої іншої країни світу, де застосовується даний вид оподаткування.

У зв'язку з цим перед бухгалтерами, працівниками податкових служб і підприємцями в різних випадках ставиться такі основні завдання:

• Розрахунок власне ПДВ від вартості без податку;
• Обчислення ПДВ від вартості, в яку податок вже включений;
• Розрахунок суми без ПДВ від вартості, в яку податок вже включений;
• Обчислення суми з ПДВ від вартості без податку.

Виконанням даних обчислень в Ексель ми далі і займемося.

Спосіб 1: обчислення ПДВ від податкової бази

Перш за все, давайте з'ясуємо, як розрахувати ПДВ від податкової бази. Це досить просто. Для виконання даного завдання потрібно оподатковуваний базу помножити на податкову ставку, яка в Росії становить 18%, або на число 0,18. Таким чином, у нас є формула:

«НДС» = «База налогообложения» x 18%

Для Excel формула розрахунку прийме наступний вигляд

=число*0,18

Природно, множник «Число» є числовим виразом цієї самої податкової бази або посиланням на осередок, в якій цей показник знаходиться. Спробуємо застосувати ці знання на практиці для конкретної таблиці. Вона складається з трьох стовпців. У першому розташовані відомі значення бази оподаткування. У другому будуть розташовуватися шукані значення, які нам і слід розрахувати. У третьому стовпці буде знаходитися сума товару разом з податковою величиною. Як не важко здогадатися, її можна обчислити шляхом додавання даних першого і другого шпальти.

1. Виділяємо першу осередок колонки з шуканими даними. Ставимо в ній знак «=», а після цього натискаємо по осередку в тому самому рядку з шпальти «Податкова база». Як бачимо, її адреса тут же заноситься в той елемент, де ми виробляємо розрахунок. Після цього в розрахунковій осередку встановлюємо знак множення Excel (*). Далі вбиваємо з клавіатури величину «18%» або «0,18». У кінченому підсумку формула з цього прикладу прийняла такий вигляд:

   =A3*18%

   У вашому випадку вона буде точно така ж за винятком першого множника. Замість «A3» можуть бути інші координати, в залежності від того, де користувач розмістив дані, які містять базу оподаткування.

2. Після цього, щоб вивести готовий результат в клітинку, клацаємо по клавіші Enter на клавіатурі. Необхідні обчислення будуть тут же зроблені програмою.
3. Як бачимо, результат виведений з чотирма десятковими знаками. Але, як відомо, грошова одиниця рубль може мати тільки два десяткових знака (копійки). Таким чином, щоб наш результат був коректний, потрібно значення округлити до двох десяткових знаків. Зробимо це за допомогою форматування осередків. Щоб не повертатися до цього питання пізніше, відформатуємо відразу все осередки, призначені для розміщення грошових значень.

   Виділяємо діапазон таблиці, призначений для розміщення числових значень. Натискаємо правою кнопкою миші. Запускається контекстне меню. Вибираємо в ньому пункт «Формат ячеек».

4. Після цього проводиться запуск вікна форматування. Переміщаємося у вкладку «Число», якщо воно було відкрито в будь-який інший вкладці. У блоці параметрів «Числові формати» встановлюємо перемикач в позицію «Числовий». Далі перевіряємо, щоб в правій частині вікна в полі «Число десяткових знаків» стояла цифра «2». Дане значення має стояти за замовчуванням, але про всяк випадок варто перевірити і змінити його, якщо там відображається будь-яке інше число, а не 2. Далі тиснемо на кнопку «OK» в нижній частині вікна.

   Можна також замість числового формату включити грошовий. У цьому випадку числа також будуть відображатися з двома десятковими знаками. Для цього переставляємо перемикач в блоці параметрів «Числові формати» в позицію «Грошовий». Як і в попередньому випадку, дивимося, щоб в поле «Кількість десяткових знаків» стояла цифра «2». Також звертаємо увагу на те, щоб в поле «Позначення» був встановлений символ рубля, якщо, звичайно, ви цілеспрямовано не збираєтеся працювати з іншою валютою. Після цього тиснемо на кнопку «OK».

5. Якщо ви застосуєте варіант з використанням числового формату, то все числа перетворюються на значення з двома десятковими знаками.

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

6. Але, поки ми розрахували значення податку на додану вартість тільки для одного значення бази оподаткування. Тепер нам потрібно це зробити і для всіх інших сум. Звичайно, можна вводити формулу по тій же аналогії, як ми це робили в перший раз, але обчислення в Excel відрізняються від обчислень на звичайному калькуляторі тим, що в програмі можна значно прискорити виконання однотипних дій. Для цього слід застосувати копіювання за допомогою маркера заповнення.

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

7. Як бачимо, після додавання коментарів необхідна величина буде розрахована для всіх значень бази оподаткування, які є в нашій таблиці. Таким чином, ми розрахували показник для семи грошових величин значно швидше, ніж це було б зроблено на калькуляторі або, тим більше, вручну на листку паперу.
8. Тепер нам потрібно буде провести підрахунок загальної суми вартості разом з податковою величиною. Для цього виділяємо перший порожній елемент в стовпці «Сума з ПДВ». Ставимо знак «=», натискаємо по першій клітинці стовпця «База оподаткування», встановлюємо знак «+», а потім виробляємо клацання по першій клітинці колонки «ПДВ». У нашому випадку в елементі для виведення результату відобразилося такий вираз:

   =A3+B3

   Але, звичайно, в кожному конкретному випадку адреси осередків можуть відрізнятися. Тому вам при виконанні аналогічної задачі потрібно підставити власні координати відповідних елементів листа.

9. Далі клацаємо по кнопці Enter на клавіатурі, щоб отримати готовий результат обчислень. Таким чином, величина вартості разом з податком для першого значення розрахована.
10. Для того, щоб розрахувати суму з податком на додану вартість і для інших значень, застосовуємо маркер заповнення, як це ми вже робили для попереднього розрахунку.

Таким чином, ми розрахували необхідні величини для семи значень бази оподаткування. На калькуляторі це зайняло б набагато більше часу.

урок: Як змінити формат комірки в Excel

Спосіб 2: розрахунок податку від суми з ПДВ

Але існують випадки, коли для податкової звітності слід вирахувати розмір ПДВ від суми, в яку цей податок вже включений. Тоді формула розрахунку буде виглядати наступним чином:

«НДС» = «Сумма с НДС» / 118% x 18%

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

=число/118%*18%

Як аргумент «Число» виступає відоме значення вартості товару разом з податком.

Для прикладу розрахунку візьмемо все ту ж таблицю. Тільки тепер в ній буде заповнений стовпець «Сума з ПДВ», а значення стовпців «ПДВ» і «База оподаткування» нам належить розрахувати. Будемо вважати, що елементи таблиці вже відформатовані в грошовий або числовий формат з двома десятковими знаками, так що повторно дану процедуру проводити не будемо.

1. Встановлюємо курсор в перший осередок шпальти з шуканими даними. Вводимо туди формулу (= число / 118% * 18%) тим же чином, що застосовували в попередньому способі. Тобто, після знака ставимо посилання на осередок, в якому розташоване відповідне значення вартості товару з податком, а потім з клавіатури додаємо вираз «/ 118% * 18%» без лапок. У нашому випадку вийшла наступна запис:

   =C3/118%*18%

   У вказаному записі, в залежності від конкретного випадку і місця розташування вступних даних на аркуші Ексель, може змінюватися тільки посилання на осередок.

2. Після цього клацаємо по кнопці Enter. Результат підрахований. Далі, як і в попередньому способі, за допомогою застосування маркера заповнення копіюємо формулу в інші комірки стовпчика. Як бачимо, всі необхідні значення розраховані.
3. Тепер нам потрібно розрахувати суму без податкового платежу, тобто, базу оподаткування. На відміну від попереднього способу, даний показник обчислюється не за допомогою додавання, а при використанні вирахування. Для цього потрібно від загальної суми відняти величину самого податку.

   Отже, встановлюємо курсор в першій клітинці стовпця «База оподаткування». Після знака «=» виробляємо віднімання даних з першої комірки стовпчика «Сума з ПДВ» величини, яка знаходиться в першому елементі колонки «ПДВ». У нашому конкретному прикладі вийде ось такий вислів:

   =C3-B3

   Для виведення результату не забуваємо тиснути на клавішу Enter.

4. Після цього звичайним способом за допомогою маркера заповнення копіюємо посилання в інші елементи колонки.

Завдання можна вважати вирішеною.

Спосіб 3: обчислення податкової величини від бази оподаткування

Досить часто потрібно підрахувати суму разом з податковою величиною, маючи значення бази оподаткування. При цьому обчислювати сам розмір податкового платежу не потрібно. Формулу обчислення можна уявити в такому вигляді:

«Сумма с НДС» = «База налогообложения» + «База налогообложения» x 18%

Можна формулу спростити:

«Сумма с НДС» = «База налогообложения» x 118%

У Ексель це буде виглядати наступним чином:

=число*118%

Аргумент «Число» являє собою оподатковуваний базу.

Для прикладу візьмемо все ту ж таблицю, тільки без стовпчика «ПДВ», так як при цьому обчисленні він не знадобиться. Відомі значення будуть розташовуватися в стовпці «База оподаткування», а шукані - в колонці «Сума з ПДВ».

1. Виділяємо перший осередок шпальти з шуканими даними. Ставимо туди знак «=» і посилання на перший осередок шпальти «База оподаткування». Після цього вводимо вираз без лапок «* 118%». У нашому конкретному випадку було отримано вираз:

   =A3*118%

   Для виведення результату на лист клацаємо по кнопці Enter.

2. Після цього застосовуємо маркер заповнення і виробляємо копіювання раніше введеної формули на весь діапазон стовпця з розрахунковими показниками.

Таким чином, сума вартості товару, включаючи податок, була розрахована для всіх значень.

Спосіб 4: розрахунок бази оподаткування від суми з податком

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

Формула обчислення бази оподаткування від вартості, куди вже включено податок, виглядає наступним чином:

«База налогообложения» = «Сумма с НДС» / 118%

У Ексель дана формула прийме такий вигляд:

=число/118%

Як діленого «Число» виступає величина вартості товару з урахуванням податку.

Для обчислень можна застосувати таку саму таблицю, як в попередньому способі, тільки на цей раз відомі дані будуть розташовані в колонці «Сума з ПДВ», а обчислювані - в стовпці «База оподаткування».

1. Виробляємо виділення першого елемента стовпця «База оподаткування». Після знака «=» вписуємо туди координати першого осередку іншої колонки. Після цього вводимо вираз «/ 118%». Для проведення розрахунку і виведення результату на монітор виконуємо клацання по клавіші Enter. Після цього перше значення вартості без податку буде розраховано.
2. Для того, щоб зробити обчислення в інших елементах стовпця, як і в попередніх випадках, скористаємося маркером заповнення.

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

урок: Робота з формулами в Excel

Як бачимо, знаючи ази розрахунку податку на додану вартість і пов'язаних з ним показників, впоратися із завданням їх обчислення в Excel досить просто. Власне, сам алгоритм розрахунку, по суті, мало чим відрізняється від розрахунку на звичайному калькуляторі. Але, у виконання операції в зазначеному табличного процесора є одна незаперечна перевага перед калькулятором. Воно полягає в тому, що розрахунок сотень значень займе не набагато більше часу, ніж обчислення одного показника. У Ексель буквально протягом хвилини користувач зможе зробити розрахунок податку по сотнях позицій, вдавшись до такого корисного інструменту, як маркер заповнення, в той час як розрахунок подібного обсягу даних на простому калькуляторі може зайняти годинник часу. До того ж, в Excel можна зафіксувати розрахунок, зберігши його окремим файлом.

• ← Використання «розумних» таблиць в Microsoft Excel
• Створення прайс-листа в Microsoft Excel →