To import loan payments into QuickBooks Online, first create a liability account for the loan. Then upload the bank statement as usual. On the review screen, split each loan payment line in two: the principal goes to the loan account, which lowers what you owe, and the interest goes to an interest expense account.
Setting up the loan account before importing payments
A loan you owe needs its own liability account in QuickBooks Online before any payment is imported. Intuit's article on setting up a loan says to use a Long Term Liabilities account with the Notes Payable detail type for a loan you won't repay this fiscal year, or Other Current Liabilities with Loan Payable if you will.
Most business loans, such as a five-year equipment loan, are long term. A short bridge loan you expect to clear before year end is current. The choice only affects where the balance appears on the balance sheet, so pick the one that fits and move on.
The opening balance depends on when the loan started:
- A new loan whose money was deposited into your bank. Intuit's article says to leave the opening balance at zero. The deposit itself will put the balance in place, as the next section shows.
- A loan you were already repaying before your QuickBooks start date. Enter the balance you owed on the start date, from the lender's statement. The article says this beginning balance posts to Opening Balance Equity.
Name the account after the lender and the loan, such as "Equipment loan, Valley Bank". A business with two loans needs two accounts, so that each balance can be checked against its own lender statement.
Interest expense
You also need an expense account for the interest. Many charts of accounts already have one called interest expense or interest paid. If yours doesn't, create it now. Intuit's article says to record each interest payment to your expense account.
Recording the loan money when it first lands in the bank
When the lender pays a new loan into your bank account, the deposit shows up on the statement like any other money in. It is not income. On the review screen, record that deposit against the loan liability account, so the loan balance rises to the amount borrowed and your income stays untouched.
Suppose the lender deposits 24,000.00. Categorize that line to the loan account. The loan account now shows 24,000.00 owed, and the bank account shows the cash. Profit is unchanged, which is right, because borrowing money doesn't make any.
Sometimes the deposit is smaller than the loan, because the lender kept an origination fee. A 24,000.00 loan with a 300.00 fee might arrive as 23,700.00. In that case the loan account still needs to show 24,000.00 owed. Split the deposit line: 24,000.00 to the loan account and minus 300.00 to a loan fees expense account. Ask your accountant whether the fee should be expensed at once or spread over the loan's life.
If the loan money paid a seller directly and never touched your bank, there is no deposit to import. Intuit's article suggests talking to an accountant in that case.
Where do the loan's principal and interest figures come from?
The principal and interest split comes from the lender, not from the bank statement. The bank sees one payment leaving your account. The lender's statement, online loan account or amortization schedule shows how that payment was divided between interest for the period and principal repaid.
Get the schedule before you start. Most lenders give one at signing and show each payment's split in their online portal or on a monthly or annual loan statement. A schedule lists every payment date, the interest portion, the principal portion and the balance after each payment.
If you can't get the lender's figures, you can estimate the interest for a fixed-rate loan. For a monthly payment, multiply the balance by the yearly rate and divide by twelve. Treat that as an estimate only. Lenders often calculate interest by the day, so their figures will differ slightly, and theirs are the ones that count.
A worked example
The figures below are made up for illustration: a 24,000.00 loan at 6% a year, with a fixed monthly payment of 464.00. Interest is estimated as one twelfth of the yearly rate on the balance.
| Payment | Bank line | Interest (to expense) | Principal (to loan account) | Loan balance after |
|---|---|---|---|---|
| Start | 24,000.00 | |||
| 1 | -464.00 | 120.00 | 344.00 | 23,656.00 |
| 2 | -464.00 | 118.28 | 345.72 | 23,310.28 |
| 3 | -464.00 | 116.55 | 347.45 | 22,962.83 |
The bank line is the same every month. The split shifts a little each time, with interest falling and principal rising as the balance shrinks. That is why you can't set the split once and copy it. Each payment needs its own figures.
Splitting each imported loan payment on the review screen
Each loan payment arrives from the bank upload as a single line. On the review screen, open the line and use the split option to give it two categories: the interest amount to interest expense and the principal amount to the loan liability account. The two parts must add up to the bank amount.
For payment 1 in the example, the 464.00 line becomes 120.00 to interest expense and 344.00 to the loan account. After you save, the loan account shows 23,656.00 owed, and interest expense for the month shows 120.00.
A bank rule can recognize the loan payment by its description and amount, and Intuit's guide to bank rules says a rule can even split a transaction by amount. Because the split changes every month, though, a fixed rule will be slightly wrong after the first payment. Let the rule set the payee and leave the line for review, then type the month's split by hand. It takes less than a minute per payment.
The upload steps for the statement file itself are in the guide to importing bank transactions into QuickBooks Online. The bank upload is available on every plan, including the entry plan, as the post on importing bank transactions into QuickBooks Online Simple Start explains.
Catching up on a year of payments
If you are entering twelve months at once, build a small spreadsheet first with the date, the bank amount, the interest and the principal for each payment, copied from the lender's schedule. Then work down the review screen with the sheet beside you. The guide to importing Excel into QuickBooks covers tidying a sheet like that if you also want to upload it. Keep the bank files themselves within the size and row caps described in the post on the QuickBooks Online bank upload limit.
Does the QuickBooks loan balance match the lender's?
The loan balance in QuickBooks should equal the balance on the lender's statement for the same date. Check it at least once a year, and ideally each quarter. If the two agree, every principal amount was split correctly. If not, the difference points to a payment that was split wrongly or never recorded.
Common reasons for drift:
- Estimated interest. If you estimated interest rather than using the lender's figures, small differences add up. Compare a few months with the lender's portal and correct them.
- A payment categorized whole. One payment recorded entirely as interest, or entirely to the loan, throws the balance off by that month's principal or interest.
- Extra principal payments. A one-off extra payment goes straight to principal. If it was split like a normal payment, the balance will be off.
- A missing month. If a statement is missing, so is a payment. The guide to downloading bank statements from online banking covers getting older statements from your bank.
When the lender's balance and yours differ by a small amount after a year of estimated interest, adjust interest expense and the loan account by the difference with your accountant's agreement. Don't edit old payments one by one.
Loan payments that need more than a two-way split
Some loan payments carry more than principal and interest. A mortgage payment may include escrow for property tax and insurance. A late payment may add a fee. A line of credit may charge interest only. Each extra part needs its own line in the split, so that every amount lands in the right account.
- Escrow. The lender holds part of each payment to pay tax and insurance later. Split that portion to an escrow asset account or straight to the tax and insurance expense accounts, as your accountant prefers.
- Late fees and other charges. Split them to a bank or loan fees expense, not to interest and not to principal.
- Interest-only months. Some loans begin with months where the whole payment is interest. Categorize the full line to interest expense, and the loan balance stays put.
- Lines of credit. Draws arrive as deposits and go to the liability account. Repayments are split between principal and interest in the same way as a term loan.
Your loan payments may only appear on PDF bank statements. SimpleBankConvert is being built to turn each PDF into a CSV or QBO upload file, so the payment lines arrive ready for splitting. It isn't open to the public yet, and the get started page lets you request access.