Here is the syntax:. The first step is to specify the terms of the loan and use the PMT function to calculate the monthly payment.
Notice that in the above example the parameters to the PMT function are cell references. This allows you to change a value in row 4 and quickly see how your change effects the monthly payment--especially handy when you are doing what-if analysis.
Four loan options you most likely don't need to touch. Payment Period or Frequency - how often do you want to schedule payments? The calculator supports 11 options, including biweekly, monthly, and semiannual useful for bond coupon interest schedules. The schedule calculates the payment dates from the first payment due date not the loan date.
Compounding Period or Frequency - usually, the compounding frequency should be set to the same setting as the payment frequency. Doing so results in simple, periodic interest. Points - one point is one percent of the loan amount. Points are generally applicable to U. More about loan schedules with points, fees, and APR support. Amortization Method - leave this setting set to "normal" unless you have a specific reason for setting it otherwise.
For a complete explanation of these options, see Nine Loan Amortization Methods. Five loan options you may want to tweak. Printing the Payment Schedule.
Beyond Basic Amortization Schedules. This option impacts calculations when compounding is set to "Exact" or "Daily" or when there are odd days in the cash flow. Cancel Save change. Albania Lek Lek12,, All calculators will remember your choice. You may also change it at any time. Clicking "Save changes" will cause the calculator to reload. Your edits will be lost. Cancel Save changes.
If initial cash flow period is longer than the payment frequency:. None With origination. With first Amortized. If initial cash flow period is shorter than the payment frequency:. No payment reduction Reduce first. Enter the PMT formula in B8, drag it down the column, and you will see a constant payment amount for all the periods:. To find the interest part of each periodic payment, use the IPMT rate, per, nper, pv, [fv], [type] function:.
All the arguments are the same as in the PMT formula, except the per argument that specifies the payment period. This argument is supplied as a relative cell reference A8 because it is supposed to change based on the relative position of a row to which the formula is copied.
This formula goes to C8, and then you copy it down to as many cells as needed:. This formula goes to column D, beginning in D To find the balance after the first payment in E8, add up the loan amount C5 and the principal of the first period D8 :. Because the loan amount is a positive number and principal is a negative number, the latter is actually subtracted from the former.
For the second and all succeeding periods, add up the previous balance and this period's principal:. The above formula goes to E9, and then you copy it down the column. Due to the use of relative cell references, the formula adjusts correctly for each row. That's it! Our monthly loan amortization schedule is done:. Because a loan is paid out of your bank account, Excel functions return the payment, interest and principal as negative numbers.
By default, these values are highlighted in red and enclosed in parentheses as you can see in the image above. For the Balance formulas, use subtraction instead of addition like shown in the screenshot below:. In the above example, we built a loan amortization schedule for the predefined number of payment periods. This quick one-time solution works well for a specific loan or mortgage. If you are looking to create a reusable amortization schedule with a variable number of periods, you will have to take a more comprehensive approach described below.
In the Period column, insert the maximum number of payments you are going to allow for any loan, say, from 1 to You can leverage Excel's AutoFill feature to enter a series of numbers faster.
Because you now have many excessive period numbers, you have to somehow limit the calculations to the actual number of payments for a particular loan. This can be done by wrapping each formula into an IF statement. The logical test of the IF statement checks if the period number in the current row is less than or equal to the total number of payments.
Assuming Period 1 is in row 8, enter the following formulas in the corresponding cells, and then copy them across the entire table. As the result, you have a correctly calculated amortization schedule and a bunch of empty rows with the period numbers after the loan is paid off. There are many ways that you can use the information in a loan amortization schedule.
Knowing the total amount of interest you'll pay over the lifetime of a loan is a good incentive to get you to make principal payments early. When you make extra payments that reduce outstanding principal, they also reduce the amount of future payments that have to go toward interest. That's why just a small additional amount paid can have such a huge difference.
Even when your lender gives you a loan amortization schedule, it can be easy just to ignore it in the pile of other documents you have to deal with. But the information on an amortization schedule is crucial to understanding the ins and outs of your loan. By knowing how a schedule gets calculated, you can figure out exactly how valuable it can be to get your debt paid down as quickly as possible. Our favorites offer quick approval and rock-bottom interest rates.
Check out our list to find the best loan for you. Dan is a lawyer and financial planner living in Williamstown, Massachusetts. With experience in tax and estate planning, trust administration, and wealth management, Dan led The Motley Fool's investment planning bureau when he started writing in and covers a host of topics ranging from retirement and tax planning to personal finance.
The Ascent does not cover all offers on the market. Editorial content from The Ascent is separate from The Motley Fool editorial content and is created by a different analyst team. The Motley Fool has a Disclosure Policy. The Ascent is a Motley Fool service that rates and reviews essential products for your everyday money matters. By submitting your email address, you consent to us sending you money tips along with products and services that we think might interest you.
You can unsubscribe at any time. Advertiser Disclosure We do receive compensation from some partners whose offers appear on this page. We have not reviewed all available products or offers.
0コメント