# Prepaid Expenses A 12-month prepaid-asset roll-forward: prepaid panel with amortisation period (months) and a monthly prepayment plan per category, weighted-average period, a 12x12 amortisation matrix routing each month's prepayments to the months they are recognised as expense, a 12-month roll-forward (opening / payments / amortisation / closing) with implied months-of-cover each month, and a dashboard with peak-prepaid and period-distribution status. - Canonical: https://finamodel.com/templates/prepaid-expenses - Excel download: https://finamodel.com/templates/prepaid-expenses.xlsx - Category: Corporate Finance - Model type: Operating model - Difficulty: Intermediate - Audiences: CFOs & FP&A, Founders & operators, CFOs, FP&A teams, Controllers, Treasury - Tags: prepaid expenses, prepaid assets, working capital, amortisation schedule, month-end close ## Overview A prepaid-expenses model translates a monthly prepayment plan into a 12-month projection of the prepaid-asset balance and the expense recognition as each prepayment amortises. This template lays the full mechanic on six sheets: an Assumptions sheet with a panel of eight prepaid categories (rent, utilities, travel, marketing, professional fees, maintenance, insurance, software licenses), each with an amortisation period in months and a 12-month prepayment plan, plus opening prepaid balance, the four period cutoffs that group categories into period buckets (1 mo / 1-3 mo / 3-6 mo / >6 mo), and the average-period and prepaid-intensity traffic-light thresholds; a Prepaid Master sheet that pulls each category's period and annual payments, computes its % of panel and period contribution, and labels its period bucket; an Amortisation Schedule sheet with a 12x12 matrix that routes each month's prepayments to the months they are recognised as expense via straight-line amortisation, with a post-period column for amortisation whose recognition lands beyond the 12-month window; a Prepaid Schedule sheet that rolls opening prepaid, new payments, amortisation, and closing prepaid through 12 months with implied months-of-cover each month; and a Dashboard sheet with weighted-average period, peak prepaid balance, prepaid intensity at peak, closing prepaid at M12, annual payments and amortisation, and the amortisation period distribution by bucket. The amortisation routing uses a SUMPRODUCT against the category period range, so a single edit to a category's period or to a bucket cutoff reshapes the entire amortisation matrix and the prepaid balance path. Weighted period is computed properly - each category's share of total annual payments is multiplied by its period months and summed - so big-dollar categories dominate the headline number the way they would in a real income-statement forecast. The model maintains the prepaid identity (opening + payments - amortisation = closing) at every month, and the post-period spill column ensures column sums tie to the underlying prepayments. CFOs, FP&A teams, controllers, and treasury managers use this template for expense recognition timing (drive the operating-expense side of a 3-statement model off the Prepaid Schedule amortisation row), working-capital sizing (read prepaid intensity at peak to size short-term assets carried by the business between cash payment and expense recognition), and year-end close diagnostics (flex one category's period in Assumptions and quantify the income-statement impact on weighted period and peak prepaid before changes hit the books). The straight-line model is intentionally caveman-simple - real prepayments occasionally have non-linear benefit curves - so the trade-off is interpretability and one-edit responsiveness over the false precision of a usage-weighted amortisation schedule. ## What's included - Prepaid panel with amortisation period (months) and 12-month prepayments plan per category - Prepaid Master with annual payments, % of panel, weighted-period contribution, and period bucket - 12x12 amortisation matrix routing each month's prepayments to the months they are recognised as expense - Post-period spill column capturing amortisation deferred past M12 - 12-month prepaid-asset roll-forward: opening, new payments, amortisation, closing, months-of-cover - Weighted-average amortisation period across the panel weighted by annual payments - Peak prepaid balance, the month it occurs, and prepaid intensity (closing / monthly amortisation) at peak - Period distribution: % of annual payments by bucket (1 mo / 1-3 mo / 3-6 mo / >6 mo) - Dashboard with average-period and prepaid-intensity traffic-light status against user-set thresholds - Prepaid panel with amortisation period (months) and 12-month prepayment plan per category - Prepaid Master with annual payments, % of panel, weighted-period contribution, and period bucket per category - 12-month prepaid-asset roll-forward: opening, new payments, amortisation, closing, months-of-cover each month - Peak prepaid balance and the month it occurs, plus prepaid intensity (closing / monthly amortisation) at peak ## Prepaid Expenses Model: How the Template Works This prepaid expenses model template gives a structured way to track prepaid assets from invoice through amortisation. It builds a 24-month roll-forward from contract-level detail, showing how cash outflows and expense recognition diverge. The template includes a sub-ledger, amortisation matrix, journal entries, reconciliation, dashboard, and validation checks. This guide explains the key mechanics for evaluating the template. ### Operating Drivers: Contract Data and Assumptions The model is driven by a contract-level sub-ledger on the Contracts sheet, where each record represents an invoice or contract line. For each line, you enter the vendor, GL account, service start and end dates, payment date, total paid amount, and payment frequency. - A capitalisation threshold on the Assumptions sheet automatically sets a flag to determine whether the item is treated as a prepaid asset or expensed immediately. The Assumptions sheet also holds bucket cutoffs, model start date, status thresholds, and an opening-contracts list with remaining balances and remaining months. - This design means the model can be updated by editing contract rows, and all downstream schedules will reflect the changes, provided the workbook is set to recalculate. ### Calculation Flow: From Payments to Amortisation The core calculation is an amortisation matrix that allocates each capitalised contract's total paid amount across months based on the portion of service days falling in each month. The formula uses the contract's service start and end dates to compute the overlap with each month and divides by total service days. - This creates a date-driven, first-month-prorated expense pattern. Opening contracts from the Assumptions sheet amortise their remaining balance evenly over the remaining months. - The matrix sums to a panel-level monthly amortisation figure that feeds the Prepaid Schedule, which also includes a cash-outflow row based on payment dates, separate from expense recognition. The roll-forward identity is closing balance equals opening plus new capitalisations minus amortisation. ### Outputs: Schedules, Journal Entries, and Dashboard The model produces several outputs. The Prepaid Schedule shows a 24-month roll-forward with opening, new capitalisations, amortisation expense, closing balance, and months-of-cover. - It also splits the closing balance at month 12 into current and non-current portions. A Journal_Entries sheet lists capitalisation and amortisation entries, with an auto-reverse preview for amounts spilling beyond month 24. - The GL_Tieout compares the sub-ledger closing balance by GL account to a trial-balance input, flagging differences. A Dashboard summarises key metrics such as weighted-average period, peak prepaid balance, balance trend, and bucket distribution. The Checks sheet runs four identity tests to help ensure internal consistency. ### Practical Use: Validation and Common Pitfalls The Checks sheet provides four validation identities with a tolerance input to avoid floating-point issues. These check that the amortisation matrix plus spill equals capitalised total, that column sums reconcile to capitalised plus opening amounts, that the roll-forward holds at month 24, and that the weighted-average period ties to category data. - The model is designed to surface common errors, such as reversed service dates, payment dates beyond the 24-month horizon, or capitalisation thresholds that misclassify contracts. It also highlights annual contracts that extend past month 24, showing the residual amount that would be written off. - This structure helps a controller identify and correct data issues before relying on the outputs. ## Built for expense recognition timing When the question is "when does a prepaid insurance, software license, or rent payment actually hit the income statement?", a prepaid-asset roll-forward is the cleanest answer. This template feeds the operating-expense side of a 3-statement model or a budget-vs-actuals tracker without you having to stitch straight-line amortisation together by hand. ## Designed for one-edit responsiveness Every category period, bucket cutoff, threshold, and monthly prepayment line is a named-range or named-cell input. Flex one number and the amortisation matrix re-routes, the prepaid balance path updates, and the dashboard status flips - no formula rewrites. ## Honest about timing precision Real prepaid amortisation occasionally has non-linear benefit curves (a marketing campaign front-loaded in week 1, software adoption curving up over months); this template uses straight-line because the trade-off favours interpretability over false precision. Operators can replicate non-linear curves by splitting a category into two period buckets. ## Built for expense recognition timing When the question is "when does a prepaid insurance, software license, or rent payment actually hit the income statement?", a prepaid-asset roll-forward is the cleanest answer. This template feeds the operating-expense side of a 3-statement model or a budget-vs-actuals tracker without you having to stitch straight-line amortisation together by hand. ## Designed for one-edit responsiveness Every category period, bucket cutoff, threshold, and monthly prepayment line is a named-range or named-cell input. Flex one number and the amortisation matrix re-routes, the prepaid balance path updates, and the dashboard status flips - no formula rewrites. ## Honest about timing precision Real prepaid amortisation occasionally has non-linear benefit curves (a marketing campaign front-loaded in week 1, software adoption curving up over months); this template uses straight-line because the trade-off favours interpretability over false precision. Operators can replicate non-linear curves by splitting a category into two period buckets. ## Workbook structure ### Cover Workbook overview, sheet legend, and tab-colour key for navigation. - Title and scope framing - Sheet-by-sheet purpose summary - Tab-colour legend ### Assumptions Every driver in one sheet: prepaid panel, monthly payments, opening balance, period buckets, thresholds. - 8 categories with amortisation period (months) and a 12-month prepayment plan per category - Opening prepaid balance - Three period bucket cutoffs (short / quarterly / semi-annual) - Average-period and prepaid-intensity traffic-light thresholds (green / amber) ### Prepaid Master Per-category period, annual payments, weighted-period contribution, and period bucket. - One row per prepaid category - Annual payments via SUM across the 12 monthly named ranges - % of panel and period contribution (% of panel times period months) - Bucket label via nested IF on the user-set cutoffs ### Amortisation Schedule A 12x12 matrix routing each month's prepayments to the months they are recognised as expense. - Row r = payment month, column c = amortisation month - Cell (r, c) computed via SUMPRODUCT against category periods (straight-line) - Post-period column captures amortisation deferred past M12 - Column sums feed the Prepaid Schedule amortisation row ### Prepaid Schedule 12-month prepaid-asset roll-forward with implied months-of-cover each month. - Opening rolls from previous month's closing - New payments per panel from the Assumptions monthly named ranges - Amortisation pulled from the Amortisation Schedule column totals - Closing = opening + payments - amortisation - Months of cover = closing prepaid / monthly amortisation ### Dashboard Headline metrics with traffic-light status and amortisation period distribution. - Weighted-average period with On track / Watch / Stretched status - Peak prepaid balance and the month of peak - Prepaid intensity at peak with On track / Watch / Heavy status - Annual payments, in-period amortisation, and post-period spill - Bucket distribution: % of annual payments by period (1 mo / 1-3 mo / 3-6 mo / >6 mo) ### Cover Workbook overview, sheet legend, and tab-colour key for navigation. - Title and scope framing - Sheet-by-sheet purpose summary - Tab-colour legend ### Assumptions Every driver in one sheet: prepaid panel, monthly payments, opening balance, period buckets, thresholds. - 8 categories with amortisation period (months) and a 12-month prepayment plan per category - Opening prepaid balance - Three period bucket cutoffs (short / quarterly / semi-annual) - Average-period and prepaid-intensity traffic-light thresholds (green / amber) ### Prepaid Master Per-category period, annual payments, weighted-period contribution, and period bucket. - One row per prepaid category - Annual payments via SUM across the 12 monthly named ranges - % of panel and period contribution (% of panel times period months) - Bucket label via nested IF on the user-set cutoffs ### Amortisation Schedule A 12x12 matrix routing each month's prepayments to the months they are recognised as expense. - Row r = payment month, column c = amortisation month - Cell (r, c) computed via SUMPRODUCT against category periods (straight-line) - Post-period column captures amortisation deferred past M12 - Column sums feed the Prepaid Schedule amortisation row ### Prepaid Schedule 12-month prepaid-asset roll-forward with implied months-of-cover each month. - Opening rolls from previous month's closing - New payments per panel from the Assumptions monthly named ranges - Amortisation pulled from the Amortisation Schedule column totals - Closing = opening + payments - amortisation - Months of cover = closing prepaid / monthly amortisation ### Dashboard Headline metrics with traffic-light status and amortisation period distribution. - Weighted-average period with On track / Watch / Stretched status - Peak prepaid balance and the month of peak - Prepaid intensity at peak with On track / Watch / Heavy status - Annual payments, in-period amortisation, and post-period spill - Bucket distribution: % of annual payments by period (1 mo / 1-3 mo / 3-6 mo / >6 mo) ## Features - **Straight-line amortisation routing:** Each category's amortisation period in months drives the rate at which a prepayment is recognised as expense: a 12-month annual insurance prepayment of $9,500 amortises $792 each month for 12 months from the payment date. The matrix uses SUMPRODUCT against the category period range, so a single edit to a category's period reshapes the entire amortisation schedule and the prepaid balance path. - **12x12 amortisation matrix:** Each row is a payment month; each column is an amortisation month. Cell (r, c) shows the dollars from month r's prepayments that are recognised as expense in month c, with a post-period spill column for amortisation whose recognition lands beyond M12. Column sums feed the Prepaid Schedule amortisation row directly. - **Weighted period not flat-averaged:** Weighted-average amortisation period is computed as the sum of each category's annual-payment share times that category's period in months, so big-dollar categories dominate the headline number. A panel that is 60% annual insurance and 40% monthly rent shows a weighted period close to the annual category, not the simple 6.5-month midpoint. ## Use cases - **Cash-out vs expense timing for FP&A:** Take a budgeted prepayment plan, push it through the amortisation periods, and read the month-by-month expense recognition off the Prepaid Schedule amortisation row separately from the cash payment row. Pairs directly with a 13-week cashflow forecast (cash side) and a 3-statement model (expense side). - **Working-capital sizing for fundraising:** Prepaid intensity at peak (closing prepaid / monthly amortisation) sizes how much short-term asset the business carries between cash payment and expense recognition; compare that against AP and accrued-expenses sides for a full working-capital bridge. - **Year-end close review:** Controllers flex any prepayment category or amortisation period in Assumptions, watch the weighted period move, the bucket flip, the amortisation matrix re-route, and the peak prepaid balance respond. Useful for quantifying the income-statement impact of a switch from annual to quarterly billing on a major SaaS contract or insurance renewal before the change is booked. ## Frequently asked questions ### What is a prepaid-expenses model? A prepaid-expenses model tracks the timing gap between when cash is paid for a service or asset (the prepayment) and when the expense is actually recognised on the income statement (over the benefit period). It rolls the prepaid-asset balance forward month by month from opening + new payments - amortisation, and it projects the expense-recognition side directly. ### How is amortisation computed here? Straight-line: a prepayment of X in month r over period P months recognises X/P expense in each of the P months starting in month r. The model uses a SUMPRODUCT against the category period range so each cell of the 12x12 matrix sums all category contributions for that (payment_month, expense_month) pair in one formula. ### Why does the dashboard show amortisation deferred past M12? Categories with longer amortisation periods (like annual insurance or software licenses with a 12-month period) can have prepayments in the last few months of the forecast whose recognition lands beyond the 12-month window. The amortisation matrix has a post-period column that captures this spill so the totals tie and you can see how much expense recognition rolls over into the next year. ### How is weighted period computed here? Weighted period is the sum of each category's share of total annual payments multiplied by that category's amortisation period in months. So categories with bigger annual amounts get more weight, and the headline period reflects the panel's actual expense-timing centre of gravity rather than a flat average across categories. ### Can I add more prepaid categories? Yes. Extend the category block on Assumptions, add the corresponding row on Prepaid Master, and extend the Periods_By_Cat and Pay_M1..Pay_M12 named ranges to cover the new rows. The amortisation matrix and Prepaid Schedule will pick up the additions automatically through the named ranges. ## Related templates - [Accrued Expenses](https://finamodel.com/templates/accrued-expenses) - [Working Capital Model](https://finamodel.com/templates/working-capital-model) - [3 Statement Model](https://finamodel.com/templates/3-statement-model)