What it does
Use to calculate amortization lines with several different day calculation options (i.e. round periods, by day, etc.). This is especially useful for calculating amortization for non-standard fiscal calendars.

When to use it
For accountants that need to spread prepaid expenses over several periods, this will build the amortization lines for each of the prepaid transactions in one bulk step.  When using a non-traditional calendar (i.e. 4-4-5, 4-5-4, 5-4-4, 13×4, etc.), this can calculate the exact amortization by day for each fiscal period.  It includes options to round to the nearest period or calculate equal period amounts.

Why to use it
It offers several options for how to calculate amortization, accommodating a variety of period types.  It can also help with accruals or prepaids that have installments.  This helps record amortization journal entries, as well as reconcile the related prepaid accounts.

Default shortcut
None

Other Details

• Category: Accounting / Other
• Difficulty: 2/5
• Usage/frequency: 4/5
• Automation factor: 5/5 (estimated 300 seconds saved each time used)
• Type: Bulk task
• Date added: 3/1/2022
• Tags: prepaid, amortization, accounting, journal entries, reconciliations

Related Macros
Create Fiscal Calendar

Other Articles
None

Calculate Amortization Lines Example File_2022-08-26.xlsx

Prerequisites
Have available your fiscal period calendar that will cover the range of amortization start/end dates for your transactions.  To assist with building this, see the Create Fiscal Calendar macro.

Instructions
Run the Create Amortization Lines macro.  The fist time you run this, it will create the Period End Dates and Transaction List sheets if those sheets are not found in the active workbook.  If they are found, their contents will be validated and processed.  To populate the sheets, follow the instructions below.  [TBD: populate period-end schedule, populate yellow cells – options / mapping, copy transactions, run macro]

1. Select the Amortization Type using the yellow box based on these options:

• Round Nearest Period – partial periods are rounded based on whether 50% or more of the days should be included in the amortization term.
• Calculate By Day – calculates the amortization for each day in the term, allowing for partial periods of amortization

2. Select the Calculation Type using the yellow box based on these options:

• Days In Period – calculates amortization based on the actual number of days in the period. Fiscal period calendar companies likely would choose this.
• Equal Period Amounts – calculates amortization evenly across full periods, regardless of the days in each period. Traditional calendar month companies would likely choose this.
• Month-Fiscal Cutoff – divides evenly by # of calendar months and adds fiscal period cutoffs to help with accruals and prepaids with installments. 

3. Fill in your period end schedule on the left of the sheet. Use the comments in the header cells for assistance in filling out each field. Conditional formatting is in place to help ensure there are no date gaps/overlaps.

4. Copy the data (including headers) from the prepaid item source data (G/L details or other data source) to the Transaction List sheet. Any columns included in this list will be copied into the Amortization Lines sheet.

5. In the yellow boxes, enter the Start Date Column, End Date Column, Amount Column based on the columns containing the start/end dates and amortizable amounts in the Transaction List sheet that the amortized lines should be based on.  Optionally, fill in the other yellow input boxes: Invoice Day Column (optional), Add Cutoff Dates (optional).

6. Run the Calculate Amortization Lines macro again once the inputs are filled in, period start/end dates are filled in on the left, and data is filled on the Transaction List sheet. This will use the dates for each line and the amortization type to create amortization lines according to the period end schedule specified. Those amortization lines can then be used for financial modeling, to record journal entries, support account reconciliations, etc.