Як використовувати функцію PMT в Excel 2016

Функція Excel 2016 PMT у спадному меню кнопки «Фінансові» на вкладці «Формули» стрічки обчислює періодичний платіж для ануїтету, припускаючи потік рівних платежів і постійну процентну ставку. Функція PMT використовує такий синтаксис:

=PMT(ставка,nper,pv,[fv],[type])

Як і в інших загальних фінансових функціях, ставка – це процентна ставка за період, nper – кількість періодів, pv – поточна вартість або сума, на яку вартість майбутніх платежів зараз, fv – це майбутня вартість або залишок готівки, який ви хочете отримати після здійснено останній платіж (якщо ви опускаєте цей необов’язковий аргумент, Excel приймає майбутнє значення нуля, як і під час розрахунку платежів за позикою), а тип — це значення 0 для платежів, здійснених наприкінці періоду, або значення 1 для здійснених платежів на початку періоду. (Якщо ви пропустите необов’язковий аргумент типу , Excel припускає, що платіж здійснено в кінці періоду.)

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

На малюнку показано зразок робочого аркуша, який містить таблицю з використанням функції PMT для розрахунку платежів за позикою для діапазону відсоткових ставок (від 2,75 відсотка до 4,00 відсотка) та основної суми (від 150 000 до 159 000 доларів США). У таблиці використовується початковий принципал, який ви вводите в клітинку B2, копіює його в клітинку A7, а потім збільшує його на 1000 доларів США в діапазоні A8:A16. У таблиці використовується початкова процентна ставка, яку ви вводите в клітинку B3, копіюється в клітинку B6, а потім збільшується початкова ставка на 1/4 відсотка в діапазоні C6:G6. Термін у роках у клітинці B4 є постійним коефіцієнтом, який використовується у всій таблиці платежів за кредитом.

Таблиця «Платежі по кредиту» з використанням функції PMT для розрахунку різних платежів по кредиту.

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

У клітинку A1 введіть заголовки «Платежі за кредитом», у клітинку A2 – основна сума, у клітинку A3 – відсоткова ставка, а в клітинку A4 – «Термін (у роках»).

Введіть 150 000 доларів США в клітинку B2, введіть 2,75% в клітинку B3 і введіть 30 в клітинку B4.

Це початкові значення, за допомогою яких ви будуєте таблицю Платежі за позикою.

Помістіть покажчик комірки в B6, а потім побудуйте формулу =B3.

Створюючи формулу зв’язку, яка переносить початкове значення процентної ставки в B3 з формулою, ви гарантуєте, що значення процентної ставки в B6 негайно відображатиме будь-які зміни, які ви внесете в клітинку B3.

Помістіть покажчик клітинки в клітинку C6, а потім побудуйте формулу =B6+.25%.

Додаючи 1/4 відсотка до відсоткової ставки до значення в B6 за формулою =B6+0,25% в C6 замість створення ряду з маркером автозаповнення, ви гарантуєте, що значення процентної ставки в клітинці C6 завжди буде на 1/4 відсотка більше, ніж будь-яке значення процентної ставки, введене в клітинку B6.

Перетягніть маркер заповнення в клітинку C6, щоб розширити виділення праворуч до клітинки G6, а потім відпустіть кнопку миші.

Помістіть покажчик комірки в клітинку A7, а потім побудуйте формулу =B2.

Знову ж таки, використовуючи формулу =B2 для перенесення початкового принципала до клітинки A7, ви гарантуєте, що клітинка A7 завжди має те саме значення, що й клітинка B2.

Помістіть покажчик комірки в активне поле A8, а потім побудуйте формулу =A7+1000.

Тут також ви використовуєте формулу =A7+1000, а не створюєте ряд із функцією автозаповнення, щоб основне значення в A8 завжди було на 1000 доларів більше, ніж будь-яке значення, розміщене в клітинці A7.

Перетягніть маркер заповнення в клітинці A8, доки не розширите виділення на клітинку A16, а потім відпустіть кнопку миші.

У клітинці B7 натисніть кнопку Вставити функцію на панелі формул, виберіть Фінансові зі спадного списку Або Виберіть категорію, а потім двічі клацніть функцію PMT у списку Виберіть функцію.

У діалоговому вікні «Аргументи функції», що відкриється, ви можете вказати аргументи rate, nper і pv . Обов’язково перемістіть діалогове вікно «Аргументи функції» праворуч, щоб жодна його частина не закривала дані в стовпцях A і B вашого робочого аркуша, перш ніж перейти до наступних кроків для заповнення аргументів.

Клацніть клітинку B6, щоб вставити B6 у текстове поле Оцінити, а потім двічі натисніть F4, щоб перетворити відносне посилання B6 на змішане посилання B$6 (відносне стовпець, абсолютне рядок), перш ніж ввести /12.

Ви конвертуєте відносне посилання осередки B6 до змішаної контрольної B $ 6 , так що Excel це НЕ змінити кількість рядків при копіюванні формули PMT вниз кожному рядку таблиці, але це робить налаштувати букву стовпця при копіюванні формули через її стовпці . Оскільки початкова відсоткова ставка, введена в B3 (а потім перенесена в клітинку B6), є річною процентною ставкою, але ви хочете знати щомісячний платіж за позикою, вам потрібно перетворити річну ставку на місячну ставку, розділивши значення в клітинці B6 на 12.

Клацніть текстове поле Nper, клацніть клітинку B4, щоб вставити це посилання на клітинку в це текстове поле, а потім натисніть F4 один раз, щоб перетворити відносне посилання B4 на абсолютне посилання $B$4, перш ніж ввести *12.

Вам потрібно перетворити відносне посилання на комірку B4 на абсолютне посилання $B$4, щоб Excel не коригував ні номер рядка, ні літеру стовпця, коли ви копіюєте формулу PMT вниз по рядках і по стовпцях таблиці. Оскільки термін — це річний період, але ви хочете знати щомісячний платіж за позикою, вам потрібно перетворити річні періоди на місячні, помноживши значення в клітинці B4 на 12.

Клацніть текстове поле Pv, клацніть A7, щоб вставити це посилання на клітинку в це текстове поле, а потім натисніть F4 тричі, щоб перетворити відносне посилання A7 на змішане посилання $A7 (абсолютне стовпець, відносне рядок).

Вам потрібно перетворити відносне посилання на клітинку A7 у змішане посилання $A7, щоб Excel не коригував літеру стовпця під час копіювання формули PMT в кожному стовпці таблиці, але коригував номер рядка, коли ви копіюєте формулу вниз. через його ряди.

Натисніть OK, щоб вставити формулу =PMT(B$6/12,$B$4*12,$A7) у клітинку B7.

Тепер ви готові скопіювати цю оригінальну формулу PMT вниз, а потім знову, щоб заповнити всю таблицю Виплати позики.

Перетягніть маркер заповнення клітинки B7, доки не розширите діапазон заповнення до клітинки B16, а потім відпустіть кнопку миші.

Після того, як ви скопіюєте вихідну формулу PMT до клітинки B16, ви готові скопіювати її праворуч до G16.

Перетягніть маркер заповнення вправо, доки не розширите діапазон заповнення B7:B16 до клітинки G16, а потім відпустіть кнопку миші.

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

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