如果您要比较贷款报价或计算每年（和每月）需要支付多少贷款，您需要找到其年利率 ( APR )。您可以使用Microsoft Excel ，通过有用的(Microsoft Excel)Excel公式轻松计算您的年利率。
在本文中，我们将解释如何在Microsoft Excel(Microsoft Excel)中计算年百分比。
年利率是多少(Rate)？
年利率(APR)是您借出的贷款产生的年利息。年利率(APR)以百分比表示。这代表一年内收取的额外费率，包括利率和额外费用（但不包括复利）。
如果您以 5% 的年利率(APR)申请一笔 100 美元的贷款，那么您将在年底偿还 105 美元，其中 5 美元是按 5% 的年利率(APR)添加的额外金额。
这为您提供了一种比较不同贷款的简单方法，并有助于保护您免受误导性广告的影响。
在美国，所有贷方（包括信用卡公司）都必须披露他们向借款人收取的
年利率。(APR)
如何在 Excel 中计算年利率
年利率的(APR)计算方法是将贷款的定期利率乘以一年中的期数。APR方程如下所示：
其中“利息(Interest)”是贷款期限内支付的利息总额，“本金(Principal)”是贷款金额，“n”是贷款期限的天数。
因此，要在Excel中计算您的(Excel)年利率(APR)，您需要知道：
- 贷款金额。
- 付款期数。
- 每月付款金额。
然后，我们可以使用Excel使用各种内置函数
来计算年利率。(APR)
计算您的每月付款金额
第一步是计算您的每月付款。如果您已经知道这一点，则可以跳到下一节。
如果没有，我们将使用 Excel 的PMT 函数(PMT function)计算每月付款。这是财务函数之一，其公式如下：
=PMT(速率, nper, pv, [fv], [类型])
在哪里：
- 利率是贷款的利率。
- Nper是贷款还款总数。
- Pv 是“现值”，即贷款的总价值。
- Fv 是一个可选的质量，代表您在上次付款后想要获得的投资或贷款的未来价值。如果您忽略这一点，Excel将假定您希望获得的未来值为 0。
- 类型是另一种可选质量，用于解释付款何时到期。数字 0 表示周期开始，数字 1 表示周期结束。
要在 Excel 中计算 PMT：
- 打开Excel 工作表(Excel worksheet)。
- 单击(Click)单元格内部并输入“ =PMT（利率、nper、pv）”，将每个单词替换为您的贷款的具体值。确保(Make)您的利率使用小数。因此，对于 6% 的贷款，小数值为 0.06。
- 按 Enter 键(Press Enter)，您将看到每月付款金额出现。
注意：您需要保持单位一致。如果您要计算每月还款额，但只知道年利率，请务必将该利率除以 12 得出月利率。按照上面的示例，它将是 0.06/12。
Nper也是如此-(Nper —)付款次数将是三年乘以 12 个月，因此月数将为 36。如果您计算年付款，请使用年费率和 1 作为年数。
例如，想象一笔 10,000 美元的贷款，为期 3 年（因此每月还款 36 个月），利率为 6%。要计算PMT，您可以输入：“= PMT ((0.06/12), 36, 10000)。” 由此产生的贷款支付额为：每月 - 304.30 美元。
Excel在计算(Excel)PMT时将返回负数。这说明欠钱了。如果您希望它返回正数，请输入贷款金额为负数。
计算年利率
有了每月还款金额，就可以计算年利率(APR)了。您可以将其与另一个称为RATE函数的(RATE)Excel函数一起使用。
RATE函数具有以下格式：
=速率(nper, pmt, pv)*12
在哪里：
- Nper是贷款还款总数。
- Pmt是上面计算的月费率。
- Pv 是贷款的总价值。
- 最终结果乘以12就得到实际的年利率，而不是月利率。
要在 Excel 中计算年利率：
- 打开 Excel 工作簿。
- 选择一个单元格并输入：“=RATE(nper, pmt, pv)*12”，将每个值替换为您的贷款数量。答案将显示为十进制值 - 乘以 100 即可得到最终百分比。
- 按 Enter 键。
继续使用上面的示例，您可以输入：“=RATE(36,-304.30,10000)*12”。最终年利率(APR)为 6%。
注意：如果您收到#NUM！错误，请仔细检查您的语法是否正确。
精明理财
计算年利率(APR)可以帮助您选择最好的贷款并尽快还清贷款。如果您想做出最佳决策并让您的钱尽可能花得更多，这一点至关重要。
Microsoft Excel的好处是它可以兼作会计软件(accounting software)，帮助您计算贷款的年利率等内容。
How to Calculate Annual Percentage Rate (APR) In Microsoft Excel
Іf you’re comparing loan offers or working out how much you’ll need to pay on уoυr loan each year (and month), yoυ need to find its annual percentage rate (APR). You can use Microsoft Excel to easіly calсulatе your annuаl interest rate using helpful Εxcel formulas.
In this article, we’ll explain how to calculate the annual percentage rate in Microsoft Excel.
What Is the Annual Percentage Rate?
APR is the yearly interest that’s generated by a loan you’ve taken out. APR is expressed as a percentage. This represents the extra rate that’s charged over a year, including the interest rate and additional fees (but not compounding interest).
If you take out a $100 loan with a 5% APR, you’ll pay back $105 at the end of the year — the $5 is the extra amount added by a 5% APR.
This gives you an easy way to compare different loans and helps protect you against misleading advertising.
In the US, all lenders (including credit card companies) must disclose the APR they charge to borrowers.
How to Calculate APR in Excel
APR is calculated by multiplying the loan’s periodic interest rate by the number of periods in a year. Here’s what the APR equation looks like:
Where “Interest” is the total amount of interest paid over the life of the loan, “Principal amount” is the amount of the loan, and “n” is the number of days in a loan term.
So, to calculate your APR in Excel, you need to know:
- The loan amount.
- The number of payment periods.
- The monthly payment amount.
Then, we can use Excel to calculate the APR using various built-in functions.
Calculate Your Monthly Payment Amount
The first step is to calculate your monthly payment. If you already know this, you can skip to the next section.
If not, we’ll calculate the monthly payment using Excel’s PMT function. This is one of the financial functions, with the following formula:
=PMT(rate, nper, pv, [fv], [type])
Where:
- Rate is the interest rate for the loan.
- Nper is the total number of payments for the loan.
- Pv is the “present value”, or the total value of the loan.
- Fv is an optional quality that represents the future value of an investment or loan you want to attain after your last payment. If you leave this out, Excel will assume the future value you’re looking to attain is 0.
- Type is another optional quality that explains when payments are due. The number 0 means at the start of the period, while the number 1 means at the end of the period.
To calculate PMT in Excel:
- Open your Excel worksheet.
- Click inside a cell and type “=PMT(rate, nper, pv)”, replacing each word with the specific values of your loan. Make sure to use the decimal value for your interest rate. So, for a 6% loan, the decimal value will be 0.06.
- Press Enter and you’ll see the monthly payment amount appear.
Note: You need to keep your units consistent. If you’re calculating monthly payments, but only know the annual interest rate, make sure to divide this rate by 12 to find the monthly interest rate. Following the above example, it would be 0.06/12.
The same goes for Nper — the number of payments will be three years multiplied by 12 months, so the number of months will be 36. If you’re calculating annual payments, use the annual rate and 1 for the number of years.
For example, imagine a $10,000 loan, on a 3-year loan (so having 36 monthly payments), with a 6% interest rate. To calculate the PMT, you would type: “=PMT((0.06/12), 36, 10000).” The resulting loan payment is: -$304.30 per month.
Excel will return a negative number when calculating the PMT. This shows that it’s money owed. If you want it to return a positive number, input the loan amount as a negative figure, instead.
Calculate the APR
With your monthly payment amount in hand, it’s time to calculate the APR. You can use this with another Excel function known as the RATE function.
The RATE function has the following format:
=RATE(nper, pmt, pv)*12
Where:
- Nper is the total number of payments for the loan.
- Pmt is the monthly rate calculated above.
- Pv is the total value of the loan.
- The final result is multiplied by 12 to get the actual annual rate, rather than monthly.
To calculate the APR in Excel:
- Open your Excel workbook.
- Select a cell and type: “=RATE(nper, pmt, pv)*12”, replacing each value with the quantities of your loan. The answer will be shown as a decimal value – multiply this by 100 to get the final percentage.
- Press Enter.
Sticking with the example above, you’d type: “=RATE(36,-304.30,10000)*12”. The resulting APR is 6%.
Note: If you receive a #NUM! error, double-check that your syntax is correct.
Be Money Smart
Calculating the APR can help you choose the best loan and pay that loan off as quickly as possible. This is crucial if you want to make the best decisions and make your money go as far as possible.
The good thing about Microsoft Excel is that it doubles as accounting software, helping you calculate things like the annual percentage rate of your loan.
