Prepaid expense amortization schedule with AI: build and roll-forward prompt

When to use it

Use it when prepaids live in a hand-maintained spreadsheet that drifts from the GL, when a batch of annual renewals lands, or when you want to rebuild the schedule from source documents at year-end. It works best inside Excel (Claude for Excel or Copilot), so the output is formulas rather than pasted numbers.

Input data

• Invoices or contracts for new prepaid items: vendor, invoice number, amount, service start and end dates.
• The existing prepaid schedule (if any) and the GL prepaid account balance(s) by sub-account.
• Policy: prepaid threshold, amortization method (straight-line by month is typical), the convention for partial months (e.g. full month if the service starts by the 15th), and the expense account mapping.

The prompt

Replace the [bracketed] items. Paste it with your files attached. It works in any capable assistant; tool-specific notes are in the steps.

Build (or update) our prepaid expense schedule in this workbook.

Inputs:
- New items: invoices in [sheet/file] (vendor, invoice #, amount, service start, service end).
- Existing schedule: [sheet], GL prepaid balance at [prior period-end]: $[amount].
- Policy: straight-line monthly; partial month convention: [describe]; threshold $[X]; expense account per item in column [ ].

Requirements:
1. One row per item: vendor, invoice #, GL expense account, total amount, service start, service end, months in term, monthly amortization, amortization to date, remaining balance.
2. Use Excel formulas, not hard-coded numbers, for months in term, monthly amount, to-date and remaining. Handle the final month so the total amortization equals the invoice amount exactly (no rounding residue).
3. Add a monthly columns section for [current fiscal year] showing amortization by month.
4. Build a roll-forward: beginning balance + additions − amortization = ending balance, and compare the ending balance to the GL balance I provide. Show the difference. Don't plug it.
5. Produce the summary for this month's journal entry: debit expense by account, credit prepaid. Label it "DRAFT JE".
6. List exceptions: items under the threshold (which should probably be expensed), service dates missing or ambiguous, terms over 12 months (consider a long-term prepaid classification), and items fully amortized but still on the schedule.

Explain any assumption you make about dates.

Steps

1. Gather invoices and confirm service periods from the invoice or contract, not the invoice date.
2. Run the prompt in the workbook, then inspect the formulas in two or three rows (especially the final-month true-up logic).
3. Check the roll-forward: the ending balance should tie to the GL after this month's entry. Investigate any difference before booking.
4. Review the exceptions list, and decide reclassifications for long-term portions and immaterial items per policy.
5. Book the amortization through your JE process with the schedule attached.

Control map

What the AI may do, step by step. How ratings work.

Audit trail

• Keep the invoice or contract for every addition linked or referenced on the schedule row.
• Save the workbook version used for the entry, with a reviewed tie-out to the GL.
• If the AI built the formulas, note it in the workbook's notes tab. The reviewer should check formula consistency (no hard-codes in formula columns).

Failure modes

• Off-by-one month counts (start and end inclusive vs exclusive), leaving a small residue at the end of the term.
• Using the invoice date as the service start, which accelerates or delays expense.
• Hard-coded values instead of formulas, so next month's update silently breaks. Check a few cells.
• Multi-currency invoices amortized in the wrong currency. Specify the functional-currency amount.

Data handling

• Use a company-approved AI workspace whose terms say your inputs are not used for model training. Check your plan; don't assume it.
• Strip or mask what the task doesn't need: employee names, bank account numbers, customer PII, SSNs or TINs.
• If you are a public company, treat pre-release results as material non-public information and keep them inside approved systems.