If you’re an accountant or a business owner dealing with commercial loans, you know the drill. That monthly loan payment hits the Xero bank feed, and immediately, you groan.

Why? Because the split between principal and interest shifts every single month. You have to stop what you’re doing, dig up the amortisation schedule, find the exact new numbers, and manually edit the lines in Xero.

If you have five loans, that’s 60 manual entries per year. It’s boring, slow, and basically just asking for a typo. I refused to do it anymore.

The “Lazy” Accountant’s Nightmare: Messing up your cash flow

We’ve all been tempted to be “lazy” and code the whole payment to the Loan Liability account, thinking we’ll fix the interest with a journal at year-end.

But here’s the kicker. That little shortcut is actually a trap that completely breaks your Statement of Cash Flows.

Xero ignores those standard manual journals entirely, making your Operating Cash Flow look artificially healthy, and your Financing Cash Flow overstated.

And if you try to outsmart the system by ticking the “Show journal on cash basis reports” box, Xero reads the journal’s credit as a cash inflow, meaning your report suddenly shows “Proceeds from New Loans” that never actually existed.

The hard truth: the only way to get a perfectly accurate Statement of Cash Flows is to correctly code every single “Spend Money” transaction out of the bank account, month after month.

Since I refuse to do 60 manual edits a year, I built a system to handle it.

My Solution: Putting Google Sheets on autopilot

Waiting to react to the bank feed felt backward. I decided to get ahead of the payment. I built a predictive system using tools I already had: Google Workspace and the Xero API.

Here is the simple, automated workflow that turned a tedious monthly chore into a single-click task:

Step 1: The Master Planner (It’s just a Google Sheet)

Forget fancy software. The brain of the operation is a simple, secure “Loan Schedules” Google Sheet.

This sheet holds the amortisation schedule for every loan, detailing the exact date, payment amount, principal, and variable interest. Crucially, it also knows the critical Xero account codes and lender contact details.

Step 2: The Daily Proactive Push

I wrote a custom Google Apps Script that runs silently every single day.

It scans the schedules for a payment due tomorrow, and instead of just sending an alert, it acts. The script uses the Xero API to automatically push a fully detailed “Spend Money” transaction directly into the client’s Xero file, splitting the Principal and Interest amounts perfectly based on that month’s schedule.

Step 3: The Beautiful Green “OK” Button

The next morning, the actual loan payment leaves the client’s bank account and drops into the Xero bank feed.

Because the exact transaction was already pushed into Xero the day before, Xero’s matching algorithm instantly recognises it. The bookkeeper logs in, sees a massive, beautiful green “OK” button, clicks it once, and the reconciliation is done. Flawless Cash Flow Statement achieved.

Step 4: The Monthly Sanity Check

Even with automation, you need to trust the numbers. Once a month, a secondary script runs to audit the system.

It retrieves the closing balance of the Loan Liability accounts from Xero via the API and compares them to the balance expected in the Google Sheet schedule. If there’s a difference (maybe an unexpected bank fee), I get an automated email alert. We catch any mess in 30 days, not at year-end.

My Strategic Takeaway: Stop Being a Robot, Start Being an Advisor

This isn’t about being fancy; it’s about being strategic. Modern finance means building automated engines that ensure the ledger is pristine before a human even has to look at it.

By utilising the Xero API and Google Workspace, I completely eliminated a tedious manual data entry task, guaranteed 100% compliance on cash flow reporting, and built an automated audit layer, all without paying a dime for new software.

My role as a vCFO isn’t to spend my time splitting bank transactions. My value is in interpreting that data and providing advice to my clients. Get rid of the repetitive work so you can focus on the work that matters.

(Want to see more of how I’m building an automated finance tech stack? Check out my recent post on automating Xero invoicing with AI.)

What manual data entry is clogging up your finance function right now? Let’s have a conversation about automating it.

Let’s Talk