Как да използвате функцията PMT в Excel 2016

Функцията PMT на Excel 2016 в падащото меню на бутона Финанси в раздела Формули на лентата изчислява периодичното плащане за анюитет, като приема поток от равни плащания и постоянен лихвен процент. Функцията PMT използва следния синтаксис:

=PMT(скорост,nper,pv,[fv],[тип])

Както при другите общи финансови функции, процентът е лихвеният процент за период, 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 в списъчното поле Изберете функция.

Диалоговият прозорец Аргументи на функцията, който се отваря, ви позволява да зададете аргументите за скорост, nper и pv . Не забравяйте да преместите диалоговия прозорец Аргументи на функцията вдясно, така че никоя част от него да не скрие данните в колони A и B на вашия работен лист, преди да продължите със следните стъпки за попълване на аргументите.

Щракнете върху клетка B6, за да вмъкнете B6 в текстовото поле Rate и след това натиснете 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.)

След като създадете таблица за заем като тази, можете да промените началната главница или лихвения процент, както и срока, за да видите какви биха били плащанията при различни други сценарии. Можете също така да включите ръчното преизчисляване, за да можете да контролирате кога се преизчислява таблицата за плащания по кредита.