Qaababka caanka ah ee Excel waxaa ka mid ah, qaacidada EFFECT waxaa inta badan adeegsada xirfadlayaasha maaliyadeed si ay u ogaadaan dulsaarka waxtarka leh ee laga soo bilaabo heerka dulsaarka magacaaban.

Waxa kale oo loo yaqaan heerka boqolkiiba sannadlaha ah ( APR ) iyo wax-soo-saarka boqolkiiba sannadlaha ah ( APY ), Excel wuxuu fududeeyaa xisaabinta amaahda wax ku oolka ah, amaahda baabuurta, iyo dulsaarka amaahda ganacsiga yaryar ee heerarka magac-u-yaalka ah ee inta badan laga soo xigtay hay'adaha amaahda.

Wax ku ool ah vs. Heerarka Dulsaarka Magaca

Heerarka dulsaarka magaca ah waxaa inta badan soo xigtay hay'adaha deymaha sababtoo ah waxay ka dhigi karaan kharashka amaahda mid ka hooseeya haddii qiimaha dhabta ah ee deynta la soo xigtay. Tani waa sababta oo ah sida caadiga ah waxaa jira lacag-bixinno badan iyo xisaabin dulsaar ah oo la sameeyo sannadkii.

Ka soo qaad^(Suppose) inaad qaadatay dayn u baahan lacag bixin bishii. Natiijo ahaan, dulsaarka ayaa la xisaabiyaa bishiiba sidoo kale. Heerka dulsaarka magacaaban, oo sidoo kale loo yaqaan heerka boqolkiiba sannadlaha ah ( APR ), ayaa si fudud dulsaarka bilaha ah (dheh 1% bishii) oo lagu dhufto laba iyo toban (tirada xilliyada sanadka). Tani waxay ka dhigan tahay 12% dulsaarka.

Si kastaba ha ahaatee, mar haddii dulsaarka la kordhiyey bishiiba, heerka ribada dhabta ah ama waxtarka leh ayaa sarreeya sababtoo ah dulsaarka bisha hadda la joogo wuxuu ka soo horjeedaa ribada bishii hore.

Sida ay soo baxday, amaahda dulsaarka 12% APR (magac ahaan) waxay leedahay dulsaar wax ku ool ah ( APY ) oo ah qiyaastii 12.68%.

Amaahda nolosha hal sano kaliya, farqiga u dhexeeya 12% iyo 12.68% waa mid aad u yar. Amaahda muddada dheer sida amaahda guryaha, farqigu wuxuu noqon karaa mid weyn.

Akhri si aad u barato sida loo isticmaalo qaacidada EFFECT ee Excel si loo xisaabiyo dulsaarka wax ku ool ah ( APY ) laga bilaabo dulsaarka magacaaban ( APR ).

Isticmaal foomamka SAAMAYNTA ee Excel

Ka soo qaad^(Suppose) inaad rabto inaad ogaato heerka dulsaarka waxtarka leh ( APY ) laga bilaabo 12% amaahda heerka magacaaban ( APR ) kaas oo leh isku-dar bishiiba mar. Waxaad u dejisay xaashida shaqada ee Excel si aad ugu ekaato midda hoose.

Ogaysii^(Notice) inaan ku hayno heerka dulsaarka magacaaban ( APR ) unugga B1 iyo tirada xilliyada lacag bixinta unugga B2 .

Si aad u ogaato heerka dulsaarka waxtarka leh ( APY ), dhagsii unugga B3 , dhagsii badhanka Insert Function , oo ka dooro Maaliyadeed^(Financial) liiska hoos u dhaca ee ku calaamadsan ama dooro qayb^{(Or Select a Category)} .

Hel^(Locate) oo guji shaqada cinwaankeedu yahay EFFECT ka dibna dhagsii badhanka OK .

Tani waxay furi doontaa daaqada doodda Functions . ^{(Functions Argument)}Sanduuqa Nominal_rate , ku qor B1 iyo sanduuqa Npery , ku qor B2 . Kadib, dhagsii badhanka OK .

Ogsoonow^(Notice) in Excel uu dhigayo jaantuska 0.1268 unugga B3 . Haddii aad doorbidayso, waxaad bedeli kartaa qaabka unugga B3 boqolkiiba.^(B3)

Ogsoonow in hadda aad bedeli karto qiyamka B1 iyo B2 labadaba Excel waxay xisaabin doontaa dulsaarka waxtarka leh ( APY ) ee unugga B3 . Tusaale ahaan, u beddel dulsaarka magacaaban ( APR ) ee B1 ilaa 6% iyo heerka dulsaarka waxtarka leh ( APY ) ee B3 wuxuu isu beddelaa 6.17% .

Isticmaalka shaqada EFFECT ee Excel , waxaad ogaan kartaa dulsaar kasta oo wax ku ool ah oo la siinayo heer kasta oo magac-u-yaal ah iyo tirada xilliyada isku-darka ah ee sanadka.

Use Excel to Figure Out an Effective Interest Rate from a Nominal Interest Rate

Among Excel’s more popular formulas, the EFFECT formula is often used by financial professionals to figure out an effective interest rate from a nomіnal interest rаte.

Also called annual percentage rate (APR) and annual percentage yield (APY), Excel makes it easy to calculate effective mortgage, car loan, and small business loan interest rates from the nominal rates often quoted by lending institutions.

Effective vs. Nominal Interest Rates

Nominal interest rates are often quoted by lending institutions because they can make the cost of a loan appear lower than if the actual cost of the loan were quoted. This is because there are normally multiple payments and interest calculations made in a year.

Suppose you take out a loan that requires monthly payments. As a result, interest is calculated monthly as well. The nominal interest rate, also called annual percentage rate (APR), is simply the monthly interest rate (say 1% per month) multiplied by twelve (the number of periods in a year). This words out to a 12% interest rate.

However, since interest is compounded monthly, the actual or effective interest rate is higher because interest in the current month compounds against interest in the previous month.

As it turns out, a 12% APR (nominal) interest loan has an effective (APY) interest rate of about 12.68%.

On a loan with a life of only one year, the difference between 12% and 12.68% is minimal. On a long-term loan such as a mortgage, the difference can be significant.

Read on to learn how to use Excel’s EFFECT formula to calculate an effective interest rate (APY) from a nominal interest rate (APR).

Use Excel’s EFFECT Formula

Suppose you want to figure out the effective interest rate (APY) from a 12% nominal rate (APR) loan that has monthly compounding. You have set up your Excel worksheet to look like the one below.

Notice that we have the nominal interest rate (APR) in cell B1 and the number of payment periods in cell B2.

To figure out the effective interest rate (APY), click on the cell at B3, click on the Insert Function button, and choose Financial from the drop down menu labeled Or Select a Category.

Locate and click on the function titled EFFECT and then click the OK button.

This will open up the Functions Argument window. In the Nominal_rate box, type in B1 and in the Npery box, type in B2. Then, click the OK button.

Notice that Excel places the figure 0.1268 in the B3 cell. If you prefer, you can change the format of the B3 cell to a percent.

Note that now you can change the values in both B1 and B2 and Excel will calculate the effective interest rate (APY) in cell B3. For example, change the nominal interest rate (APR) in B1 to 6% and the effective interest rate (APY) in B3 changes to 6.17%.

Using the EFFECT function in Excel, you can figure out any effective interest rate given any nominal rate and the number of compounding periods in a year.

Related posts

• U isticmaal Excel Qalab ahaan si aad xogta uga koobbiso Shabakadda

• Adeegso Excel Mobile's cusub ee shaqada "Ka Geli Xogta Sawirka".

• Isticmaal Hawlaha Kooban si aad u soo koobto Xogta ku jirta Excel

• Kudar Khadka Dib-u-Celinta Toosan ee Qorshaha Kala firdhisan ee Excel

• Sida loo sameeyo Histogram gudaha Excel

• Sida loo saaro, loo jebiyo, ama loo jebiyo erayga sirta ah ee Excel XLS ee la iloobay

• U qaabeeya Unugyada iyadoo la isticmaalayo Habaynta Shuruudaha ah ee Excel

• Sida loo barbar dhigo labada faylal ee Excel oo loo muujiyo faraqa u dhexeeya

• Sida Loo Sameeyo Calaamadaha Erayga laga bilaabo xaashida faafinta ee Excel

• Sida loo sameeyo oo loo isticmaalo qaabka MLA ee Microsoft Word

• Sida loola socdo isbedelada Excel

• Sida Loo Sameeyo loona Isticmaalo Foomamka Shaxanka ee Word

• Casharrada Aasaasiga ah ee Microsoft Excel - Barashada Sida loo Isticmaalo Excel

• Sida Loogu Beddelayo Inta u dhaxaysa Waraaqaha Shaqada ee Excel

• Sida loo isticmaalo Sparklines gudaha Excel

• Jadwalka xogtaada Excel

• Sida loo isticmaalo COUNTIFS, SUMIFS, AVERAGEIFS gudaha Excel

• Sida loo kala saaro Magacyada Hore iyo Dambe ee Excel

• Sida loo Helo loona Xisaabiyo Range gudaha Excel

• Isticmaal daaqada daawashada Excel si aad ula socoto unugyada muhiimka ah ee buug-shaqeedka