如果您曾经考虑过抵押或任何其他贷款，那么了解如何在Excel中使用(Excel)PMT功能可以让您深入了解您的付款方式。
PMT代表“付款(Payment)”。这是在您向函数提供所有必需的输入后，它将返回您需要支付的定期付款。
了解PMT函数在Excel中的工作原理可以帮助您衡量在支付一定金额或取决于利率如何变化的情况下还清贷款需要多长时间。
Excel 中的 PMT 函数如何工作
PMT函数比其他Excel函数（如Index或Vlookup）简单得多。但这并没有降低它的用处。
要获得贷款的定期付款，您需要为PMT函数提供以下输入。
- rate：贷款利率。
- nper：整个贷款期间的付款总数。
- pv：贷款的起始余额（现值）。
- fv : 还清贷款后剩余的现金（未来价值）。这是可选的，默认为 0。
- type：付款是在每个支付期的开始（1）还是结束（0）到期。这也是可选的。
了解如何使用此功能的一种简单方法是从一个简单的示例开始。
假设您正在考虑从银行获得 10,000 美元的个人贷款。您知道您想在 4 年（48 个月）内还清，但您不确定当银行运行您的信用时您会获得多少利率。
要估算不同利率的付款金额，您可以使用Excel中的(Excel)PMT功能。
(Set)使用顶部的已知固定值设置电子表格。在这种情况下，这是贷款金额和付款次数。为所有可能的利率创建一列，并为付款金额创建一个空列。(Create one)
现在，在Payment的第一个单元格中，键入PMT函数。
=PMT(B5,B2,B1)
其中 B5 是利率的单元格，B2 是付款次数的单元格，B1 是贷款金额（现值）的单元格。在下一步填写该列时，如下所示使用 B1 和 B2 的“$”符号来保持这些单元格不变。
按Enter 键(Enter)，您将看到该利率的付款金额。
按住Shift键并将光标放在第一个包含付款金额的单元格的右下角，直到光标变为两条水平线。双击(Double-click)，该列的其余部分将填充其他利率的付款。
这些结果向您显示的正是您可以预期这笔贷款的付款，具体取决于银行提供的利率。
使用Excel(Excel)的有用之处在于，您还可以更改包含总贷款金额或付款次数的单元格，并观察这如何更改贷款的定期付款金额。
Excel 中(Excel)的其他PMT 函数示例(PMT Function Examples)
让我们看几个稍微复杂一点的例子。
想象一下(Imagine)，您赢得了一项大奖，而授予您该奖项的组织让您可以选择一次性接受该奖项还是以年金形式接受该奖项。您可以在 10 年内以 5% 的年金形式获得 1,000,000 美元，或者今天一次性获得 750,000 美元。从长远来看，哪个是更好的选择？
Excel中的PMT功能可以提供帮助。在年金的情况下，你会想知道每年的付款是多少。
为此，请使用与上一个示例相同的方法，但这次已知值为：
- 未来价值：1,000,000 美元
- 率：5%
- 支付次数：10（十年内每年支付一次）
键入函数：
=PMT(B2,B3,0,B1,0)
最后的零表示付款将在每个期间（年）结束时进行。
按Enter，您会看到年付款为 79,504.57 美元。
接下来，让我们看看如果你把今天的 750,000 美元投入到只有 3% 的适度利率的投资中，你在 10 年内会有多少钱。
确定一次性付款的未来价值需要一个不同的Excel公式，称为 FV（未来价值）。
这个公式需要：
- 利率：3%
- 支付次数：10（年）
- 已付款：0（未提取金额）
- 现值：-$750,000（存入金额）
这个公式是：=FV(B2,B3,B4,B1)
按Enter 键(Enter)，您会看到，如果您今天投资了全部 750,000 美元并且只赚了 3%，那么您最终将在 10 年内多获得 7,937.28 美元。
这意味着今天一次性付清并自己投资会更明智，因为如果您明智地投资，您的收益可能会远远超过 3%。
Excel中的PMT函数很有用
无论您是申请住房贷款、汽车贷款，还是考虑将钱借给家庭成员，Excel中的(Excel)PMT功能都可以帮助您确定适合您情况的贷款条款。
因此，下次您考虑走进银行或汽车经销商时，请先使用Excel(Excel)坐下来做功课。了解利率和付款的影响将使您处于比在寒冷中行走并不得不接受别人的话更好的优势。
如果您经常使用 Excel，您会想看看我们的 Excel 提示和技巧。如果您知道PMT功能的任何其他很酷的用途，请在下面的评论部分分享。
How To Use The PMT Function In Excel
If you’ve ever considered taking out а mortgage or any other loan, knowing how to use the PMT functіon іn Excel can giνе уou somе insight іnto what your payments are going 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.
