Prepaid Expense Amortization Schedule Excel TemplateDaily-Rate Method, Contract Terminations & Power Query Ready (2025)

What a Prepaid Amortisation Schedule Needs to Handle

A prepaid amortisation schedule sounds simple. You pay upfront, then expense a bit each month. But month-end reality is messier. Every tracker must solve three problems.

Partial months and the daily-rate method

Take a $12,000 insurance policy running from 15 March 2025 to 14 March 2026. March has 31 days, so the contract covers 17 days in March. February has 28 days, so it covers all 28.

Straight-line amortisation divides $12,000 by 12 months and calls it $1,000 per month. That is wrong for March. It is wrong for February. It is wrong for any partial month or termination.

The daily-rate method calculates cost per day. Here is the formula:

February amortisation is 28 days times $32.88 = $920.64. This is how accounting systems calculate it. This is what auditors expect to see.

For authoritative guidance on prepaid expense recognition under IFRS, see the IAS 1 Presentation of Financial Statements standard.

Contract terminations and refund logic

A contract terminates on 20 June. The tracker must stop amortising from that date, calculate the unamortised balance as a refund, and not continue into July.

Running balances and GL mapping

Every row in the output shows opening balance, amortisation for the period, and closing balance. It also carries the GL code and GL name. This is how you reconcile to the general ledger and produce the journal entry without manual mapping.

Why Most Free Templates Fall Short

I have tested dozens of free prepaid expense trackers. They fall into the same traps every time.

• Fixed structure. Most use a 12-month grid. What if your contract is 15 months? What if it spans three calendar years? You hack extra columns or start a new sheet. The structure fights you.
• No termination logic. I have yet to find a free template that handles mid-contract terminations. You manually delete rows or overwrite formulas. Your audit trail disappears.
• No refund calculation. When a contract ends early, there is almost always a refund or credit note. Free templates do not calculate this. You work it out on the side and hope you remember to book it.
• No running balance. Most templates show only the monthly expense. The balance sheet impact is invisible. You cannot reconcile without rebuilding the schedule manually.
• Not Power Query compatible. The output is usually a matrix with months as columns. That structure is useless for Power Query. You cannot pivot it. You cannot feed it into a dashboard.

How the FinDataPro Prepaid Amortisation Engine Works

The FinDataPro Prepaid Amortisation Engine V3 is built to solve these gaps. It is a single Excel file with a VBA engine that generates the full amortisation schedule from a simple input table.

The Input Table

You fill in one row per contract. The columns are:

• Contract ID
• Vendor Name
• Description
• Prepaid Amount
• Start Date
• End Date
• GL Code
• GL Name
• Status (Active or Terminated)
• Termination Date (if applicable)
• Refund Mode (Automatic or Manual)

That is it. No formulas to write. No months to map out. The engine does the rest.

Running the Engine

Press Alt plus F8 and run the macro, or click the button on the sheet. The engine clears the Output table and rebuilds it from scratch.

This flat structure is exactly what Power Query expects. You load it straight into a pivot table or dashboard.

Termination and Refund Logic

When a contract status is set to Terminated, the engine reads the Termination Date. It calculates the days from Start Date to Termination Date, amortises up to that date, and flags the remaining unamortised balance as a refund.

GL Codes in the Output

Every output row carries the GL Code and GL Name from the input. This means you can pivot by GL Code to get the exact journal entry. No manual mapping. No VLOOKUPs. The output feeds directly into your journal upload template.

Setting Up the Template (Step-by-Step)

Step 1: Download and enable macros

Download the file from findatapro.app. When you open it, Excel will prompt you to enable macros. Click Enable Content. The file is an .xlsm workbook and will not run without macros enabled.

Step 2: Enter contract data in the Input table

Go to the Input sheet. Enter your contracts one per row. Dates must be proper Excel dates, not text. Amounts must be numbers. The GL Code and GL Name should match your chart of accounts.

Step 3: Run amortisation

Press Alt plus F8, select RunAmortisation, and click Run. Or click the Run Amortisation button on the sheet. The Output table will populate automatically.

Step 4: Clear and re-run when data changes

When you add new contracts, change dates, or terminate existing contracts, update the Input table and run the macro again. The engine always clears the Output table first and rebuilds it from the current input.

Step 5: Connect Power Query and build the Report table

Select the Output table. Go to Data, then Get Data, then From Table/Range. Power Query opens with your data loaded. From there, you can group by GL Code, summarise amortisation by month, or build a pivot table.

The Pro version includes a pre-built Report sheet and Dashboard sheet with the Power Query connections already configured. If you are new to Power Query, read my guide on how to install and use Power Query in Excel.

Free Version vs Pro Version

The free version is fully functional. Use it to test the engine with your first ten contracts. When you hit the limit, upgrade to Pro for $29.99. No subscription. One payment, unlimited use, free future updates.

Common Mistakes That Break Prepaid Schedules

Even with a solid engine, four mistakes will break your schedule.

Dates stored as text

The engine skips rows where dates are stored as text. It does not throw an error. It silently ignores the row, so your output will be missing contracts and your reconciliation will not balance. Always format dates as proper Excel date values before running the macro.

Status is Terminated but no Termination Date entered

The engine needs the Termination Date to know when to stop amortising. If you mark a contract as Terminated but leave the date blank, the engine may amortise to the original end date or skip the row entirely. Always enter the Termination Date when you change the status.

Mixed currencies without normalising first

The engine does not convert currencies. If you have contracts in USD, AED, and EUR in the same table, the output will sum them as if they were the same currency. Normalise to your functional currency before entering amounts in the Input table.

Re-running without clearing the Output table first

The engine clears the Output table automatically before each run. But if you copy and paste the output elsewhere, or if you modify the output manually between runs, you can end up with duplicate or orphaned rows. Always work from a fresh run. If in doubt, run the macro again.

Frequently Asked Questions

Conclusion

Most prepaid expense trackers fail because they use straight-line math on a daily-rate problem. The result is inaccurate figures, broken reconciliations, and extra work every close cycle.

The daily-rate method is not complicated. It is just more precise. That precision saves hours at month-end and keeps auditors satisfied.

If you want to see how Power Query transforms this output into automated reporting, read my guide on Excel Power Query for accounting processes. And if you are still managing prepaid expenses in a static table, my Ultimate Excel Prepaid Expenditure Template offers an alternative approach with multi-year forecasting and dynamic dashboards.