Excel guide · 5 min read
How to make a prepaid expense amortization schedule in Excel
Sample data
Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.
| Row | A | B | C | D | E | F | G | H | I | J | K | L |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Prepayment | Amount paid | Starts | Ends | Days | Months | Jan 31, 2026 | Feb 28, 2026 | Mar 31, 2026 | Apr 30, 2026 | May 31, 2026 | |
| 2 | Business insurance | 4380 | Mar 15, 2026 | Mar 14, 2027 | 365 | 12 | 0 | 0 | 204 | 360 | 372 | |
| 3 | Accounting software | 2400 | Jan 1, 2026 | Dec 31, 2026 | 365 | 12 | 203.83561644 | 184.10958904 | 203.83561644 | 197.26027397 | 203.83561644 | |
| 4 | Equipment maintenance | 1830 | Apr 10, 2026 | Oct 9, 2026 | 183 | 6 | 0 | 0 | 0 | 210 | 310 | |
| 5 | Trade association | 900 | Jul 1, 2026 | Jun 30, 2027 | 365 | 12 | 0 | 0 | 0 | 0 | 0 | |
| 6 | Total | 9510 | 203.83561644 | 184.10958904 | 407.83561644 | 767.26027397 | 885.83561644 | |||||
| 7 | ||||||||||||
| 8 | As of | Jun 30, 2026 |
Download this sheet as an Excel practice file (.xlsx)
The layout: one row per prepayment, one column per month
When you pay for a year of insurance or software up front, the payment covers months that haven't happened yet. A prepaid schedule spreads the cost over the months it covers, so each month's expenses show the share that belongs to it, and the part not yet used shows as a prepaid balance.
The sample has four prepayments in A:D: amount paid, the first day of cover and the last day. E2 counts the days covered (=D2-C2+1, the +1 because both the first and last day count), and F2 the whole months with =DATEDIF(C2,D2+1,"m"). Row 1 from H1 holds month-end dates: type 31 Jan 2026 in H1 and put =EOMONTH(H1,1) in I1, then fill right to December.
Note the end date. A policy that runs from 15 March 2026 for a year ends on 14 March 2027, not the 15th. Typing the anniversary date adds a day to the cover and makes every month slightly too small.
Result: 12
12 months for the insurance: 15 March 2026 to 14 March 2027. E2 shows 365 days, so 4,380 works out at exactly 12.00 a day.
The monthly expense formula, partial months included
For each cell in the grid, work out how many days of the cover fall inside that month. The overlap starts at the later of the cover start and the first of the month (MAX), ends at the earlier of the cover end and the month end (MIN), and is that difference plus 1. MAX(0, ...) turns months outside the cover into 0 instead of a negative number.
The first of the month is EOMONTH(H$1,-1)+1: the last day of the previous month plus one. Multiply the days by the amount and divide by the total days. Lock the columns of the prepayment ($B2, $C2, $D2, $E2) and the row of the dates (H$1), and one formula fills the whole grid.
Result: 204
204 of insurance expense in March: 17 days (15 to 31 March) at 12.00 a day. April gets 360 for its 30 days, and January and February get 0.
Can I just divide by 12?
Only when the cover starts on the 1st and runs whole months. The accounting software (1 January to 31 December) could be 200.00 a month; the daily formula gives 203.84 for January and 184.11 for February because the months have different lengths. Both add up to 2,400 over the year. Pick one approach with your accountant and use it consistently.
Result: 200
200: the equal-months version for January. It's only right for cover that starts on the first of a month.
The prepaid balance at any month end
The balance still prepaid is the amount paid minus what's been expensed so far. Put the date you're reporting on in an input cell (B8 here) and count the days used up to it with the same MIN and MAX idea: from the start date to the earlier of the end date and the as-of date.
If the cover hasn't started yet on the as-of date, MAX(0, ...) keeps the days at 0, so the whole amount is still prepaid. Once the cover has ended, MIN caps the days at the full term and the balance is 0. Column T does the same for 31 December by subtracting the year's grid from the amount.
Result: 3084
3,084 of insurance still prepaid on 30 June: 108 of the 365 days are used (1,296). U6 totals the prepaid balance across all four items.
Tie the schedule back to what you paid
Every prepayment should be fully expensed by the end of its cover, no more and no less. Cover that crosses the year end won't finish in this year's grid, so the check adds this year's months to the days still to come after 31 December. The result should be 0 for every row.
If a check isn't 0, look for an end date typed as the anniversary, a start date after the end date, or a grid formula that lost a $ when it was filled.
Result: 0
0: 3,504 expensed in 2026 plus 876 for the 73 days in 2027 equals the 4,380 paid.
What should I total each month?
Row 6 adds the grid for each month: that's the amortization for all prepayments together, which is the figure that moves from the prepaid balance to expenses.
Result: 767.26027397
767.26 for April: 360 of insurance, 197.26 of software and 210 of maintenance (21 days from 10 April at 10.00 a day).
Rounding to cents without losing pennies
The daily method gives amounts like 197.26027 that print as 197.26. If you round each month with ROUND(...,2), the twelve rounded months can add up to a cent or two more or less than the payment. The usual fix is to round every month except the last, and make the last month the amount minus everything before it, so the schedule ties exactly.
Or keep the grid unrounded and round only what you post. The cell values stay exact and the check cell stays 0.
Result: 203.84
203.84: January's software expense rounded to cents (31 days of 2,400 over 365).
Common mistakes
End date on the anniversary
A year from 15 March ends on 14 March. Use the last day of cover, and count days with end - start + 1.
Dividing by 12 for cover that starts mid-month
Whole months only work from the 1st. Use the day-based formula, or a rule agreed with your accountant for partial months.
Months typed as text like "Jan"
The grid needs real month-end dates in row 1 for MIN and MAX to compare. Use EOMONTH and format them as mmm yy.
Mixed references missing a $
Lock the prepayment columns ($B2) and the date row (H$1), or the formula drifts when filled.
FAQ
How do you calculate prepaid expense amortization?
Divide the amount by the number of days covered and multiply by the days in each month (or divide by the months for whole-month cover). Each month's share is expensed and the rest stays prepaid.
How do I calculate the remaining prepaid balance in Excel?
Amount paid minus amount paid x days used / total days, where days used is MAX(0, MIN(end, as-of) - start + 1). That handles cover that hasn't started or has already ended.
What is the formula for days in a partial month?
MAX(0, MIN(cover end, month end) - MAX(cover start, month start) + 1). The month start is EOMONTH(month end,-1)+1.
Is this the same as depreciation?
It's similar: both spread a cost over time. Depreciation spreads the cost of an asset over its useful life; see calculate depreciation in Excel. Which method and which items to prepay are questions for your accountant.
Functions used
EOMONTHLast day of a monthDATEDIFWhole years, months or days betweenMINSmallest numberMAXLargest numberSUMAdds numbersROUNDRounds to digitsIFPicks a result from a testANDTrue if all tests are true
Related guides
- How to build an accrued expenses schedule in Excel
- How to build a deferred revenue schedule in Excel
- How to find the last day of the month in Excel
- How to calculate days between two dates in Excel
- How to calculate depreciation in Excel
- How to track expiration dates in Excel
Updated