您正在(Are)考虑买房吗？您刚买车吗？(Did)你临近大学毕业了吗？(Are)房地产、车辆、大学和其他类似的主要项目通常需要从贷方借钱。使用Microsoft Excel，您可以计算和跟踪您的贷款。
从确定还款计划到查看您的负担能力，您可以利用贷款计算器模板或使用 Excel 的内置财务功能。
(Loan Calculator Templates)Excel贷款(Excel)计算器模板
掌握贷款、付款和利息的最佳方法之一是使用方便的跟踪工具和Excel贷款计算器。这些模板涵盖了最常见的贷款类型，并且全部免费提供。
简单的贷款计算器(Simple Loan Calculator)
对于个人贷款，您可以查看有用的图表和摊销时间表，直到您使用这个简单的贷款计算器模板还清贷款。
输入(Enter)贷款详细信息，包括金额、利率、期限和开始日期。然后，您将看到汇总表、摊销表和柱形图自动更新。
汇总表对于查看您的总利息和贷款总成本非常有用，可以提供很好的概览。
汽车贷款计算器(Auto Loan Calculator)
无论您已经拥有汽车贷款还是正在考虑购买汽车贷款，此模板都能满足您的需求。该工作簿包含汽车贷款详细信息、付款计算和贷款比较三个选项卡，因此您可以在还款之前和期间使用该模板。
在决定最适合您的贷款时，请从“贷款比较”(Loan Comparisons)选项卡开始。您可以查看各种利率的付款金额。您还可以根据付款总数、付款频率和不同的首付金额来比较金额。
接下来，在“汽车贷款计算器”选项(Auto Loan Calculator)卡上输入您的贷款详细信息，包括购买价格、贷款金额、首付款、以旧换新价格、转让费、销售税和杂费。然后，您将在“付款计算器”选项(Payment Calculator)卡上看到您的付款时间表，其中包含显示日期、利息、本金和余额的表格。
当您准备好购买梦想之车时，请使用此模板跟踪所有信息。
抵押贷款计算器(Mortgage Loan Calculator)
如果您想了解自己能负担多少固定抵押贷款或最近已获得批准的固定抵押贷款，此Excel模板会向您显示所需的一切。
输入(Enter)贷款金额、利率、期限、每期还款次数和开始日期。然后，您可以查看预定的付款金额、付款次数、付款总额和利息，以及上次付款的日期。
您还会看到一个有用的表格，其中包含所有日期和金额，以便您可以随着时间的推移跟踪支付的利息和剩余余额。
气球贷款计算器(Balloon Loan Calculator)
也许您的抵押贷款或车辆有气球贷款而不是固定贷款。使用此Excel计算器模板，您可以查看摘要和摊销时间表以及贷款的最终付款。
输入(Enter)贷款详细信息，包括起始金额和日期、利率、摊销期以及每期还款次数。然后，查看显示您的每月付款、大额付款、总利息和总付款的摘要。
摊销时间表可让您自始至终掌握贷款情况。
大学贷款计算器(College Loan Calculator)
当需要开始偿还大学贷款时，此Microsoft 365模板是理想的选择。您可以查看所需工资的百分比以及预计的月收入。
输入您的年薪、贷款偿还开始日期以及每笔学生贷款的一般详细信息。然后，您将看到还款信息，包括每笔贷款的年限和结束日期，以及付款详细信息以及总利息。
对于有用的大学贷款偿还计划，此模板可以让您步入正轨。
信用额度计算器(Line of Credit Calculator)
对于您的信用额度，此计算器会显示您所需的有关贷款的每一条信息。使用这款方便的Excel计算器，您永远不会想知道应计利息或付款次数。
输入(Enter)信用额度、起始利率、提款期限、计息首日、还款频率和天数。您还可以添加费率更改详细信息并添加额外付款。
要查看您的信用额度详细信息，请使用这个简单的工具进行计算和跟踪。
贷款摊销时间表(Loan Amortization Schedule)
如果您使用 Microsoft 365，则可以利用此贷款摊销计划。有了它，您可以计算您的总利息和付款，并可选择额外付款。
输入(Enter)您的基本贷款详细信息，包括贷款金额、年利率、期限、每期还款次数以及贷款开始日期。然后，输入您的付款信息以及可选的额外付款。
您将立即看到摊销表更新，其中包含跟上贷款付款、本金、余额、利息和累积利息所需的所有详细信息。
(Loan Payment)Excel中的贷款支付、利息(Interest)和期限函数(Term Functions)
如果您想计算付款金额、利率或贷款期限，可以使用一些方便的Excel函数。这些可以让您了解根据不同的金额、费率和时间范围您可以负担多少费用。
对于每个函数及其公式(function and its formula)，您输入一些信息以查看结果。然后，您可以调整这些细节以获得不同的结果并执行简单的比较。
注意：您在下面看到的公式没有考虑您的贷方收取的任何额外费用或成本。
计算贷款付款
如果您已获得特定贷款金额和利率的批准，您可以轻松计算出还款额。然后，您可以看看是否应该货比三家以获得更好的利率，是否需要减少贷款金额，或者是否应该增加还款次数。为此，您将使用 Excel 中的 PMT 函数(use the PMT function in Excel)。
该公式的语法为PMT（费率、num_pay、本金、future_value、type），其中需要前三个参数。
- Rate：年利率（公式中除以12）
- Num_pay：贷款还款次数
- 本金：贷款总额
- 对于年金，您可以使用 future_value 作为最后一次付款后的值，并输入付款到期时间。
在这里，单元格 B2 中有年利率，单元格 B3 中有还款次数，单元格 B4 中有贷款金额。您可以在单元格 B5 中输入以下公式来确定您的付款：
=PMT(B2/12,B3,B4)
公式确定后，您可以更改输入的金额，看看这对付款有何影响。您可以输入较低的利率或较高的付款次数，以获得所需的付款金额。公式结果自动更新。
计算利率
如果您有当前贷款并且不确定您的年利率(your annual interest rate)，您只需几分钟即可计算出来。您所需要的只是贷款期限、还款金额、本金和 Excel 的RATE函数。
该公式的语法为RATE（term、pmt、principal、future_value、type、guess_rate），其中需要前三个参数。
- 期限：以年为单位的期限（公式中乘以12）或付款总数
- Pmt：以负数输入的每月付款
- 本金：贷款总额
- 对于年金，您可以使用上述的 future_value 和 type 参数，并使用guess_rate 作为您的估计费率。
在这里，我们在单元格 B2 中输入贷款期限（以年为单位），在单元格 B3 中输入每月还款额（以负数输入），在单元格 B4 中输入贷款金额。您可以在单元格 B5 中输入以下公式来计算利率：
=比率(B2*12,E3,E4)*12
现在您可以通过一个简单的公式查看您支付的贷款利率。
提示：要计算月利率而不是年利率，请从公式末尾删除 *12。
计算付款期限
也许您正在尝试根据您的情况决定最佳的贷款期限。通过利率、还款金额和贷款金额，您可以看到以年为单位的期限。这样您就可以调整利率或付款以增加或减少还款年限。在这里，我们将使用Excel中的(Excel)NPER（周期数）函数。
该公式的语法为NPER（rate、pmt、principal、future_value、type），其中需要前三个参数。
- Rate：年利率（公式中除以12）或固定利率
- Pmt：每月付款额输入为负数。
- 本金：贷款总额
- 对于年金，您可以使用前面所述的 future_value 和 type 参数。
在这里，单元格 B2 中有年利率，单元格 B3 中有月还款额（输入为负数），单元格 B4 中有贷款金额。您可以在单元格 B5 中输入以下公式来计算您的贷款期限：
=NPER(B2/12,B3,B4)
同样，您可以调整基本贷款详细信息，并查看条款自动更新以适合您的时间范围和金额。
管好你的钱
如果您对贷款持观望态度或已经拥有贷款，请记住这些模板和功能或尝试一下。了解从利率到期限的贷款详细信息与付款同样重要。
有关更多信息，请查看这些在线计算器(these online calculators)，其中包括贷款计算器。
How to Calculate a Loan in Microsoft Excel (Templates and Formulas)
Are you considering buying a house? Did yoυ just purchase a car? Arе you close to college gradυation? Real estate, vehicles, college, and other maјor items like these nоrmally require borrowing money from a lender. Usіng Microsoft Exсel, yоu can сalculate and track your loan.
From figuring out a payment schedule to seeing how much you can afford, take advantage of loan calculator templates or use Excel’s built-in financial functions.
Loan Calculator Templates for Excel
One of the best ways to keep up with your loan, payments, and interest is with a handy tracking tool and loan calculator for Excel. These templates cover the most common types of loans and are all available for free.
For personal loans, you can see a helpful chart and amortization schedule until your loan is paid off with this simple loan calculator template.
Enter the loan details including amount, interest rate, term, and start date. You’ll then see the summary table, amortization schedule, and column chart update automatically.
The summary table is useful for seeing your total interest and the total cost of the loan, for a nice overview.
Whether you already have a car loan or are considering one, this template has you covered. The workbook contains three tabs for auto loan details, payment calculating, and loan comparisons, so you can use the template before and during your repayment.
Start with the Loan Comparisons tab when deciding the best loan for you. You can see the payment amounts for various interest rates. You can also compare amounts based on the total number of payments, payment frequency, and different down payment amounts.
Next, enter your loan details on the Auto Loan Calculator tab including purchase price, loan amount, down payment, trade-in price, transfer fees, sales tax, and miscellaneous fees. You’ll then see your payment schedule on the Payment Calculator tab with a table showing dates, interest, principal, and balance.
When you’re ready to purchase the car of your dreams, keep track of it all with this template.
If you’re trying to figure out how much you can afford or have recently been approved for a fixed mortgage loan, this Excel template shows you everything you need.
Enter the loan amount, interest rate, term, number of payments per term, and start date. You can then review the scheduled payment amount, number of payments, and total payments and interest, along with the date of your last payment.
You’ll also see a helpful table with all dates and amounts so you can track the interest paid and your remaining balance as time passes.
Maybe you have a balloon loan instead of a fixed loan for your mortgage or vehicle. With this Excel calculator template, you can view a summary and amortization schedule with the final payment for your loan.
Enter the loan details including starting amount and date, interest rate, amortization period, and number of payments per term. Then, review the summary showing your monthly payment, balloon payment, total interest, and total payments.
The amortization schedule keeps you on top of your loan from start to finish.
When it’s time to start paying off your college loan(s), this template for Microsoft 365 is ideal. You can see the percentage of your salary required and estimated monthly income.
Enter your annual salary, loan payback start date, and general details for each student loan. You’ll then see payback information including the length in years and ending date for each loan with payment details as well as total interest.
For a helpful college loan repayment plan, this template can keep you on track.
For your line of credit, this calculator shows you every piece of information you need about your loan. You’ll never wonder about the accrued interest or the number of payments you’ve made with this convenient calculator for Excel.
Enter the credit limit, starting interest rate, draw period, first day of interest accrual, payment frequency, and number of days. You can also include rate change details and include additional payments.
For reviewing your line of credit details, use this simple tool for calculations and tracking.
If you use Microsoft 365, you can take advantage of this loan amortization schedule. With it, you can calculate your total interest and payments with the option for extra payments.
Enter your basic loan details including the loan amount, annual interest rate, term, number of payments per term, and the start date of the loan. Then, enter your payment information and optionally extra payments.
You’ll immediately see the amortization table update with all the details you need to keep up with your loan payments, principal, balance, interest, and cumulative interest.
Loan Payment, Interest, and Term Functions in Excel
If you want to figure out the payment amounts, interest rate, or term for a loan, you can use a few handy Excel functions. These let you see how much you can afford based on different amounts, rates, and timeframes.
With each function and its formula, you enter a few pieces of information to see the results. You can then adjust those details to obtain different results and perform simple comparisons.
Note: The formulas you see below do not take into account any additional fees or costs charged by your lender.
Calculate Loan Payments
If you’ve been approved for a specific loan amount and interest rate, you can figure out your payments easily. You can then see if you should shop around for a better rate, need to reduce your loan amount, or should increase the number of payments. For this, you’ll use the PMT function in Excel.
The syntax for the formula is PMT(rate, num_pay, principal, future_value, type) with the first three arguments required.
- Rate: The annual interest rate (divided by 12 in the formula)
- Num_pay: The number of payments for the loan
- Principal: The total loan amount
- For an annuity, you can use future_value for the value after the last payment is made and type for when the payment is due.
Here, we have our annual interest rate in cell B2, number of payments in cell B3, and loan amount in cell B4. You would enter the following formula in cell B5 to determine your payments:
=PMT(B2/12,B3,B4)
With the formula in place, you can then change the amounts you’ve entered to see how this affects the payment. You can enter a lower interest rate or higher number of payments to get your payment amount where it needs to be. The formula result updates automatically.
Calculate an Interest Rate
If you have a current loan and are unsure of your annual interest rate, you can calculate it in just minutes. All you need is your loan term, payment amount, principal, and Excel’s RATE function.
The syntax for the formula is RATE(term, pmt, principal, future_value, type, guess_rate) with the first three arguments required.
- Term: The term in years (multiplied by 12 in the formula) or the total number of payments
- Pmt: The monthly payment entered as a negative number
- Principal: The total loan amount
- For an annuity, you can use the future_value and type arguments as described above and guess_rate for your estimated rate.
Here, we have our loan term in years in cell B2, monthly payment in cell B3 (entered as a negative number), and loan amount in cell B4. You would enter the following formula in cell B5 to calculate your interest rate:
=RATE(B2*12,E3,E4)*12
Now you can see the interest rate you’re paying on the loan with a simple formula.
Tip: To calculate a monthly interest rate rather than yearly, remove the *12 from the end of the formula.
Calculate a Payment Term
Maybe you’re trying to decide the best loan term for your situation. With the interest rate, payment amount, and loan amount, you can see the term in years. This lets you then adjust the rate or payment to increase or decrease the number of years for repayment. Here, we’ll use the NPER (number of periods) function in Excel.
The syntax for the formula is NPER(rate, pmt, principal, future_value, type) with the first three arguments required.
- Rate: The annual interest rate (divided by 12 in the formula) or the flat rate
- Pmt: The monthly payment entered as a negative number.
- Principal: The total loan amount
- For an annuity, you can use the future_value and type arguments as described earlier.
Here, we have the annual interest rate in cell B2, monthly payment in cell B3 (entered as a negative number), and loan amount in cell B4. You would enter the following formula in cell B5 to calculate your loan term:
=NPER(B2/12,B3,B4)
Again, you can then adjust the basic loan details and see the term update automatically for the timeframe and amounts that work for you.
Mind Your Money
If you’re on the fence about taking out a loan or already have one, keep these templates and functions in mind or give one a try. Being conscious of your loan details, from the interest rate to the term, is as important as making the payments.
For more, look at these online calculators which include those for loans.
