To get started , I would recommend downloading the Simple Amortization Chart template. If you are wanting to create your own amortization table, or even if you just want to understand how amortization works, I'd recommend you also read about Negative Amortization.
In that article, I explain what happens when a payment is missed or the payment is not enough to cover the interest due. Listed below are other spreadsheets by Vertex This template is unique in that the amortization table ends after a specified number of payments.
The final payment, or balloon payment, is the amount required to pay off in full. For a revolving line of credit such as a credit card or HELOC , interest normally accrues daily, so this spreadsheet is like the "simple interest loan" calculator except that it allows you to include additional draws besides the initial loan amount.
You enter your current mortgage loan terms, and it gives you both a schedule and totals for the principal and interest paid on the loan. You can also figure out what will happen by making extra payments and how long it would take you to pay off both a mortgage and home equity loan. You simply put in your credit limit and other terms and there are multiple repayment options as well as a schedule that shows you how much you are paying over time. The right loan amortization schedule will give you the full picture that includes loan interest and possible fees.
Smartsheets has a free auto loan amortization schedule template that is incredibly detailed. You can enter information about the purchase price of the vehicle, fees title, registration, taxes, etc. There is a bi-weekly auto loan amortization schedule for people that make auto loan payments every two weeks. The simple auto loan amortization schedule assumes a single monthly payment over the course of the loan. The auto loan amortization schedule with extra payments lets you enter data about optional extra monthly payments for a faster payoff.
While many Excel loan amortization schedules are simple enough to use, one of these files could give you some difficulty or may not quite suit your needs. Whether you need help with a formula, updating some formatting, or fixing an error, Excelchat provides expert-level assistance around the clock. Our team of Excel experts is standing by 24 hours a day, 7 days a week to give you the help you need when you need it. Just open a chat window to get started.
Your first session is always free. By continuing to use this website, you agree to their use. Connect anytime to free, instant, live Expert help by installing the Chrome extension Add Excelchat to Chrome.
Quick Excel Help. Things You'll Need. Related Articles. Article Summary. Method 1. Create labels in column A. Create labels for your data in the first column to keep things organized. Here's what you should put in each cell:. Enter the information pertaining to your loan in column B. Fill out cells B1-B3 with information about your loan. Leave B4 the cell next to the Payments label blank.
The "Months" value should be the total number of months in the loan term. For example, if you have a 2-year loan, enter The "Interest Rate" value should be a percentage e. Calculate your payment in cell B4. The dollar signs in the formula are absolute references to make sure the formula will always look to those specific cells, even if it is copied elsewhere into the worksheet.
The loan interest rate must be divided by 12, since it is an annual rate that is calculated monthly. Create column headers in row 7. You'll be adding some additional data to the sheet, which requires a second chart area. Populate the Period column. This column will contain your payment dates. Here's what to do: Type the month and year of the first loan payment in cell A8.
You may need to format the column to show the month and year correctly. Click the cell once to select it. Drag down from the center of the selected cell downward to cover all cells through A If this doesn't make all of the cells reflect the correct monthly payment dates, click the small icon with a lightning bolt on it at the bottom-right corner of the bottommost cell and make sure the Last Month option is selected. Fill out the other entries in cells B8 through H8.
The beginning balance of your loan into cell B8. In cell E8, create a formula to calculate the loan interest amount on the beginning balance for that period. The single dollar sign creates a relative reference. The formula will look for the appropriate cell in the B column.
In cell D8, subtract the loan interest amount in cell E8 from the total payment in C8. Use relative references so this cell will copy correctly.
In cell H8, create a formula to subtract the principal portion of the payment from the beginning balance for that period. Continue the schedule by creating the entries in B9 through H9. Cell B9 should include a relative reference to the ending balance of the prior period. This is where the relative reference becomes helpful. In cell F9, create a formula to tabulate cumulative principal paid. Highlight cells B9 through H9. When you rest the mouse cursor over the bottom-right part of the highlighted area, the cursor will turn to a crosshair.
Drag the crosshair all the way down to row This populates all the cells through row with the amortization schedule. If this looks funny, click the small spreadsheet-looking icon at the bottom-right corner of the final cell and select Copy Cells. Photo albums. Planners and trackers.
Profit and loss. Resumes and cover letters. Social media. Download with Microsoft Bring your ideas to life with Microsoft Subscribe today. If you made your purchase, refresh to get started. Transaction must be complete to see changes. Share Facebook LinkedIn Email.
0コメント