在 Excel 比较流行的公式中，金融专业人士经常使用EFFECT 公式(EFFECT formula)从名义利率中计算出实际(interest rate)利率(interest rate)。
Excel也称为年利率( APR ) 和年收益率(percentage yield)((percentage rate) APY )(APY)，可以轻松根据贷款机构经常引用的名义利率计算有效抵押贷款、汽车贷款(car loan)和小企业贷款利率。(business loan interest)
有效利率与名义利率
贷款机构经常引用名义利率，因为它们可以使贷款成本看起来低于贷款的实际成本。这是因为通常在一年内进行多次付款和利息计算。
假设(Suppose)您借了一笔需要每月还款的贷款。因此，利息也是按月计算的。名义利率(interest rate)，也称为年利率(percentage rate)( APR )，只是月利率(interest rate)（比如每月 1%）乘以十二（一年中的期数）。这话一出就12 % interest rate。
但是，由于利息是按月计算的，因此实际利率或实际利率(interest rate)会更高，因为当月的利息与上个月的利息相对应。
事实证明，12% APR（名义）利息贷款(interest loan)的有效（APY）利率(interest rate)约为 12.68%。
对于只有一年期限的贷款，12% 和 12.68% 之间的差异很小。对于抵押贷款等长期贷款，差异可能很大。
继续阅读以了解如何使用 Excel 的EFFECT 公式(EFFECT formula)从名义利率(interest rate)( APR )计算实际利率(interest rate)( APY )。
使用 Excel 的 EFFECT 公式
假设(Suppose)您想从每月复利的 12% 名义利率 ( APR ) 贷款中计算实际(APR)利率(interest rate)( APY )。您已将Excel 工作表(Excel worksheet)设置为如下所示。
请注意(Notice)，我们在单元格 B1 中有名义利率(B1)((interest rate) APR )(APR)，在单元格B2中有支付期数。
要计算实际利率(interest rate)( APY )，请单击B3处的单元格，单击Insert Function按钮，然后从标记为Or Select a Category的下拉菜单中选择Financial。
找到并单击(Locate and click)标题为“效果(EFFECT)”的功能，然后单击“确定(OK)”按钮。
这将打开函数参数(Functions Argument)窗口。在Nominal_rate框中，输入B1并在Npery框中，输入B2。然后，单击确定(OK)按钮。
请注意(Notice)，Excel将数字0.1268放在B3单元格中。如果您愿意，可以将B3单元格的格式更改为百分比。
请注意，现在您可以更改B1和B2中的值，Excel将计算单元格B3中的实际(B3)利率(interest rate)( APY ) 。例如，将B1中的名义(B1)利率(interest rate)( APR )更改为6%B3中的实际利率(interest rate)( APY )更改为6.17%。
使用Excel中的(Excel)EFFECT函数，您可以在给定任何名义利率和一年中的复利期数的情况下计算出任何实际利率。(interest rate)
Use Excel to Figure Out an Effective Interest Rate from a Nominal Interest Rate
Among Εxcel’ѕ more popular formulas, the EFFECT formula is often used by financial professiоnals to figure out an effective interest rate from a nominal interest rate.
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.
