Haddii aad waligaa ku fikirtay inaad qaadato deyn ama deyn kale, ogaanshaha sida loo isticmaalo shaqada PMT ee Excel waxay ku siin kartaa xoogaa aragti ah sida lacagahaagu u ekaan doonaan.

PMT waxay u taagan tahay " Bixinta^(Payment) ". Tani waa marka aad bixiso dhammaan agabyada loo baahan yahay ee shaqada, waxay soo celin doontaa lacag-bixinta xilliyeed ee aad u baahan doonto inaad sameyso.

Fahamka sida shaqada PMT ugu shaqeyso Excel waxay kaa caawin kartaa inaad cabbirto inta ay qaadanayso bixinta deynta haddii aad bixiso qaddar go'an, ama waxay kuxirantahay sida uu isu beddelo heerka dulsaarka.

Sida Shaqada PMT ee Excel u shaqeyso

Shaqada PMT aad ayey uga fudud tahay shaqooyinka kale ee Excel , sida Index ama Vlookup . Laakiin taasi kama dhigayso faa'iido yar.

Si aad u hesho lacag-bixinta xilliyeed ee amaahda, waxaad u baahan tahay inaad siiso shaqada PMT waxyaabaha soo socda.

• heerka^(rate) : Dulsaarka amaahda.
• nper : Wadarta tirada lacag-bixinta ee la bixiyay muddada deynta oo dhan.
• pv : Hadhaaga bilawga amaahda (qiimaha hadda).
• fv : Lacagta caddaanka ah ee aad ka tagtay ka dib marka deynta la bixiyo (qiimaha mustaqbalka). Tani waa ikhtiyaari waxayna ku dhici kartaa 0.
• nooca^(type) : Haddii lacag-bixinuhu ay dhacayaan bilowga xilliga lacag-bixinta kasta (1) ama dhammaadka (0). Tani sidoo kale waa ikhtiyaari.

Habka ugu fudud ee lagu fahmi karo sida aad u isticmaali karto shaqadan waa in lagu bilaabo tusaale fudud.

Aynu nidhaahno waxaad ku fikiraysaa inaad bangigaaga ka qaadato dayn gaar ah oo dhan $10,000. Waad ogtahay inaad rabto inaad ku bixiso 4 sano (48 bilood), laakiin ma hubin heerka dulsaarka aad heli doonto marka bangigu bixiyo deyntaada.

Si loo qiyaaso waxa lacag bixintaadu noqon doonto dulsaarka kala duwan, waxaad isticmaali kartaa shaqada PMT ee Excel .

Deji^(Set) xaashida faaqidaadda leh ee la yaqaan, qiimayaasha go'an ee sare. Xaaladdan oo kale waa lacagta deynta iyo tirada lacag bixinta. Samee hal^{(Create one)} tiir oo ka mid ah dhammaan heerarka ribada ee suurtogalka ah, iyo tiir madhan oo ah qaddarka lacag bixinta.

Hadda, unugga ugu horreeya ee Lacag-bixinta^(Payment) , ku qor shaqada PMT .

=PMT(B5,B2,B1)

Halka B5 uu yahay unugga dulsaarka leh, B2 waa unugga leh tirada lacag bixinta, B1 waa unugga leh qaddarka deynta (qiimaha hadda). Isticmaal calaamadda "$" ee B1 iyo B2 sida hoos ku cad si aad u ilaaliso unugyadaas joogto ah marka aad buuxiso tiirka tallaabada xigta.

Riix Enter oo waxaad arki doontaa lacagta lagu bixiyay heerka dulsaarka.

Qabo furaha Shiftka^(Shift) oo dhig calaamadeeyaha dhanka midig ee hoose ee unugga koowaad oo leh qaddarka lacag bixinta ilaa uu calaamaduhu isu beddelo laba xariiq oo toosan. Laba jeer guji^{(Double-click)} inta ka hartay tiirka ayaa buuxin doona lacagaha dulsaarka kale.

Natiijooyinkani waxay ku tusayaan dhab ahaan waxa aad ka filan karto deyntan, iyadoo ku xidhan heerka dulsaarka uu bangigu bixiyo.

Maxaa faa'iido leh oo ku saabsan isticmaalka Excel tan waa inaad sidoo kale bedeli kartaa unugyada wadarta qaddarka deynta ama tirada lacag bixinta oo aad u fiirsato sida tani u beddesho qaddarka lacag bixinta xilliyeed ee deynta. 

Tusaalooyinka kale ee Shaqada PMT ee ^{(PMT Function Examples)}Excel

Aynu eegno lammaane, tusaaleyaal xoogaa ka sii adag.

Bal qiyaas^(Imagine) inaad ku guulaysatay abaal-marin ballaadhan, oo ururka ku siin doona abaal-marinta ayaa ku siiyay doorashada inaad ku aqbasho abaal-marin lacageed oo dhan ama magdhow. Waxaad heli kartaa $1,000,000 oo ah 5% annuity in ka badan 10 sano, ama $750,000 oo wadar ah maanta. Waa kee doorashada ugu wanaagsan mustaqbalka fog? 

Shaqada PMT ee Excel ayaa ku caawin karta. Marka laga hadlayo gunnada, waxaad u baahan doontaa inaad ogaato waxa ku imanayo lacag-bixinta sannadlaha ah.

Si tan loo sameeyo, isticmaal hab la mid ah tusaalihii ugu dambeeyay, laakiin markan qiyamka la yaqaan waa:

• qiimaha mustaqbalka: $1,000,000
• heerka: 5%
• tirada lacag bixinta: 10 (hal lacag bixin sanadeed toban sano ka badan)

Ku qor shaqada:

=PMT(B2,B3,0,B1,0)

Eberku dhamaadka waxay ka dhigan tahay in lacag bixinta la bixin doono dhamaadka xilli kasta (sanadka).

Riix Gelida^(Enter) , oo waxaad arki doontaa in lacag bixinta sanadlaha ah ay timaado $79,504.57.

Marka xigta, aan eegno inta lacag ah ee aad heli lahayd 10 sano gudahood haddii aad qaadato $750,000 maanta oo aad geliso maalgashi kaliya oo kasbanaya 3% dulsaar yar.

Go'aaminta qiimaha mustaqbalka ee wadarta hal mar waxay u baahan tahay qaacido Excel ka duwan oo loo yaqaan FV (qiimaha mustaqbalka).

Habkani waxa uu u baahan yahay:

• Qiimaha dulsaarka: 3%
• Tirada lacag bixinta: 10 (sano)
• Lacagaha la bixiyay: 0 (wax lacag ah lagama bixin)
• Qiimaha hadda: - $750,000 (lacag la dhigay)

Habkani waa: =FV(B2,B3,B4,B1)

Riix Enter oo waxaad arki doontaa in haddii aad maanta gelisay dhammaan $750,000 oo aad kasbato 3% kaliya, waxaad ku dambayn doontaa $7,937.28 dheeraad ah 10 sano gudahood.

Tani waxay la macno tahay in aad qaadato lacagta hal mar ah maanta oo aad naftaada maalgeliso way ka caqli badan tahay, sababtoo ah waxaad u badan tahay inaad kasbato wax ka badan 3% kaliya haddii aad si caqli gal ah u maalgashato.

Shaqada PMT ee Excel waa mid faa'iido leh

Haddii aad codsanayso deyn guri, deyn baabuur, ama aad ka fekereyso inaad lacag amaahiso xubin qoyskaaga ah, shaqada PMT ee Excel waxay kaa caawin kartaa inaad ogaato shuruudaha deynta saxda ah ee xaaladaada.

Markaa marka xigta ee aad ka fikirayso inaad gasho bangi ama goob baabuur, marka hore la fadhiiso Excel oo samee shaqada gurigaaga. Fahamka saamaynta dulsaarka dulsaarka iyo lacag-bixinta waxay ku siin doontaa faa'iido aad uga fiican intii aad ku socon lahayd qabow oo aad u qaadato qof kale eraygiisa.

Haddii aad wax badan isticmaasho Excel, waxaad u baahan doontaa inaad eegto tabahayada iyo tabaha Excel . Oo haddii aad ogtahay isticmaalka kale ee wanaagsan ee shaqada PMT , la wadaag qaybta faallooyinka ee hoose.

How To Use The PMT Function In Excel

If yоu’ve ever сonsidered tаkіng out a mortgage or any other loan, knowing how to use the PMT function in Excel can give you some insight intо what your payments are gоing to look like.

PMT stands for “Payment”. This is once you provide all of the required inputs into the function, it will return the periodic payment you’ll need to make.

Understanding how the PMT function works in Excel can help you measure how long it’ll take to pay off a loan if you pay a certain amount, or depending how the interest rate changes.

How The PMT Function in Excel Works

The PMT function is much simpler than other Excel functions, like Index or Vlookup. But that doesn’t make it any less useful.

To get the periodic payment on a loan, you need to provide the PMT function with the following inputs.

• rate: The loan interest rate.
• nper: Total number of payments made over the entire period of the loan.
• pv: The starting balance of the loan (present value).
• fv: The cash you have left after the loan is paid off (future value). This is optional and defaults to 0.
• type: Whether payments are due at the beginning of each pay period (1) or the end (0). This is also optional.

An easy way to understand how you can use this function is to start with a simple example.

Let’s say you’re thinking of taking out a personal loan of $10,000 from your bank. You know you want to pay it off in 4 years (48 months), but you aren’t sure what interest rate you’ll get when the bank runs your credit.

To estimate what your payment will be for different interest rates, you can use the PMT function in Excel.

Set up the spreadsheet with the known, fixed values at the top. In this case that’s the loan amount and the number of payments. Create one column of all possible interest rates, and an empty column for the payment amounts.

Now, in the first cell for Payment, type the PMT function.

=PMT(B5,B2,B1)

Where B5 is the cell with interest rate, B2 is the cell with the number of payments, and B1 is the cell with the loan amount (present value). Use the “$” symbol for B1 and B2 as shown below to keep those cells constant when you fill the column in the next step.

Press Enter and you’ll see the payment amount for that interest rate.

Hold down the Shift key and place the cursor over the lower right corner of the first cell with the payment amount until the cursor changes to two horizontal lines. Double-click and the rest of the column will fill with payments for the other interest rates.

What these results show you is exactly what payment you can expect for this loan, depending what interest rate the bank offers.

What’s useful about using Excel for this is you can also change the cells with the total loan amount or the number of payments and observe how this changes the periodic payment amount of the loan. 

Other PMT Function Examples In Excel

Let’s take a look at a couple, slightly more complicated examples.

Imagine you’ve won a large award, and the organization that will give you the award has given you the choice to accept the award in a lump sum or an annuity. You can receive $1,000,000 as a 5% annuity over 10 years, or $750,000 in a lump sum today. Which is the better option in the long run? 

The PMT function in Excel can help. In the case of the annuity, you’ll want to know what the annual payment comes to.

To do this, use the same approach as the last example, but this time the known values are:

• future value: $1,000,000
• rate: 5%
• number of payments: 10 (one annual payment over ten years)

Type the function:

=PMT(B2,B3,0,B1,0)

The zero at the end means the payments will be made at the end of each period (year).

Press Enter, and you’ll see that the annual payment comes to $79,504.57.

Next, let’s look at how much money you would have in 10 years if you take the $750,000 today and put it into an investment that only earns a modest 3% interest rate.

Determining the future value of a lump sum requires a different Excel formula called FV (future value).

This formula requires:

• Interest rate: 3%
• Number of payments: 10 (years)
• Payments made: 0 (no amount withdrawn)
• Present value: -$750,000 (amount deposited)

This formula is: =FV(B2,B3,B4,B1)

Press Enter and you’ll see that if you invested the entire $750,000 today and only earn 3%, you’d end up with $7,937.28 more in 10 years.

This means taking the lump sum today and investing it yourself is smarter, because you can likely earn much more than just 3% if you invest wisely.

The PMT Function In Excel Is Useful

Whether you’re applying for a home loan, a car loan, or considering lending money out to a family member, the PMT function in Excel can help you figure out the right loan terms for your situation.

So the next time you’re considering walking into a bank or a car dealership, first sit down with Excel and do your homework. Understanding the impact of interest rates and payments will put you at a much better advantage than walking in cold and having to take someone else’s word for it.

If you use Excel a lot, you’ll want to take a look at our tips and tricks for Excel. And if you know of any other cool uses for the PMT function, share them in the comments section below.

Related posts

• 2 siyaabood oo loo Isticmaalo Shaqada Transpose ee Excel

• Sida loo tirtiro khadadka banaan ee Excel

• Sida loo Isticmaalo Tilmaamaha Unugyada Hadalka ee Excel

• Sida loo Geliyo Xaashida Shaqada ee Excel Word Doc

• Sida loo isticmaalo Excel's What-If Analysis

• Sida loo Isticmaalo If iyo Nsted Haddii Odhaahyada Excel

• Sida loo qoro IF Formula/Bayaanka gudaha Excel

• Xarunta Xogta Xaashida Shaqada ee Excel si loo daabaco

• Adeegsiga Qalab Raadinta Hadafka ee Excel Maxaa-Haddii Falanqaynta

• Sida Loo Hagaajiyo Safka gudaha Excel

• Sida loo isticmaalo Flash Fill ee Excel

• Sida Loo Abuuro VBA Macro Ama Script gudaha Excel

• Sida Loo Raadiyo Excel

• Ku xidhida Excel iyo MySQL

• Sida loo Isticmaalo Tixraacyada Absolute ee Excel

• Ilaali Tixraaca Unugyada marka aad nuqul ka samaynayso foomamka gudaha Excel

• Sida looga saaro Gridlines gudaha Excel

• Hagaha VBA Sare ee MS Excel

• 4 siyaabood oo loo isticmaalo calaamadda jeeg ee Excel

• 3 Siyood Oo Unugyada Excel Loogu Kala Qaybiyo