How to make a loan amortization schedule
How to Use QuickBooks Loan Manager
Let QuickBooks Loan Manager Calculate Interest and Principal for You!
In all of my years as a Certified Public Accountant, whenever clients bring in the QuickBooks files of their companies, I have yet to find any with correct principal balances on all of their outstanding loans. This is indeed puzzling to me since QuickBooks has a nifty feature called “Loan Manager” allowing you to easily decompose each payment into the correct amounts of principal and interest payable. It's easy to set up; even easier to process monthly installment payments; and easily adjustable in the event that you miss a payment.
As you know with any loan requiring a fixed monthly payment, including monthly compounded interest at a fixed rate and monthly principal installments, the interest portion decreases and the principal portion increases in each installment payment over the term of the loan. Since the loan is issued at a fixed interest rate and each payment reduces the principal amount of the loan outstanding, the interest portion of the fixed amount of the installment payment is successively lower, leaving a progressively greater portion of the fixed payment to be applied against the outstanding loan amount. Because the Loan Manager calculates the amortization schedule and automatically keeps track of the current installment due as well as the outstanding balance of your loan, anyone utilizing it in QuickBooks is spared the cumbersome task of either calculating the correct allocation of interest and principal reduction in each monthly payment or the tracking of each payment to an amortization schedule.
To utilize this time-saving feature, you must first set up a loan account in your chart of accounts. Select the “Company” drop-down menu, scroll down to “Chart of Accounts” and select it. Once your chart of accounts appears, simultaneously press the two keys, “Ctrl N”, for “New” account to be added to your chart of accounts, or—if you are keyboard averse and a lover of your mouse—at the base of the window select “Account” and then “New.” If executed correctly, the following window should appear:
Select "Loan" as the type of account that you are adding and press continue. On the next appearing window, simply type in the name of the account and account number, if you wish to assign one:
Click "Save & Close".
Next record the loan in QuickBooks, crediting the total dollar amount to the Note Payable account that you just added to your chart of accounts, and debiting cash or the appropriate asset acquired by the note. You will need to add a vendor for the bank or financial institution issuing the loan if it does not already exist as a vendor in QuickBooks.
Now you are now ready to add the loan in the "Loan Manager". To access this feature in QuickBooks, simply go to the “Banking” drop-down menu located on the top bar and scroll down to “Loan Manager”, which will then load once selected. Simply select "Add a Loan"; and in the "Add Loan" information box which appears, fill in the "Account Name", "Lender", "Origination Date", "Original Amount", and "Term" of the loan; and then select "Next".
In the next screen enter the "Due Date of Next Payment", "Payment Amount", the "Next Payment Number" defaults to "1", enter "Payment Period", and select "Next".
And then enter the "Interest Rate", "Compounding Period", "Payment Account", "Interest Expense Account", and "Fees/Charges Expense Account": the payment account is typically your checking account; and the fees/charges expense account is often the Bank Service Charges account often included in the chart of
accounts for QuickBooks. If for some reason an interest expense and bank service charges or similar account does not exist in your QuickBooks file, simply add these accounts to your chart of accounts, following the same steps previously illustrated for adding the Note Payable account.
Click "Finish". If you followed all of the above-mentioned instructions, your loan account should now be set up in the Loan Manager module, as shown below:
Click the "Payment Schedule" tab and your amortization schedule appears, calculated for the entire term of the loan. Print a copy of the amortization schedule for your records.
When the first payment is due, never enter the payment outside of the Loan Manager, as this will create a discrepancy between the loan balance shown in Note Payable on your books in QuickBooks and that in the Loan Manager module. To make an installment payment on the loan, always enter Loan Manager, and in the open window, select "Set Up Payment".
In the window that appears, notice that principal and interest are decomposed and will be applied correctly to Note Payable and Interest Expense in your general ledger. Press "OK" and the Loan Manager automatically processes the check for your current loan installment payment, with the correct check number, date, payee, amount, and amounts posted to Note Payable and Interest Expense.
Now simply print the check and you are done. If set up correctly in the Loan Manager, and if all installment payments are processed through the Loan Manager, preparing and posting installment payments in QuickBooks is a breeze!
QuickBooks Loan Manager is a great tool that is hardly used because most users have never been introduced to the steps involved in setting up a loan in it. The most common error that occurs from using Loan Manager is that which results from processing any payments of the loan outside of the Loan Manager; however, this is easily correctible once discovered by using Loan Manager to correct the discrepancy and adjust the next scheduled payment. But this is a subject for another article, which is soon to follow. Stay tuned.
This article is provided for informational purposes and is not intended to be construed as legal, accounting, or other professional advice. For further information, please consult appropriate professional advice from your attorney and certified public accountant.
Have a tax or an accounting question? Please feel free to submit it to William Brighenti, Certified Public Accountant, Hartford CPA Accountants. For information and assistance on any tax and accounting issue, please visit our website: Accountants CPA Hartford.
If and only to the extent that this publication contains contributions from tax professionals who are subject to the rules of professional conduct set forth in Circular 230, as promulgated by the United States Department of the Treasury, the publisher, on behalf of those contributors, hereby states that any U.S. federal tax advice that is contained in such contributions was not intended or written to be used by any taxpayer for the purpose of avoiding penalties that may be imposed on the taxpayer by the Internal Revenue Service, and it cannot be used by any taxpayer for such purpose. The above tax advice was written to support the promotion or marketing of the accounting practice of the publisher and any transaction described herein. The taxpayer recipients of this offering memorandum should seek tax advice based on their particular circumstances from an independent tax advisor.