The fixed rate, ordinary annuity amortization starter workbook has three parts: the Fixed Interest Rate Amortization Inputs box, the Fixed Interest Rate Amortization Schedule, and the Balloon Payment Schedule.
Fixed Interest Rate Amortization Inputs
The amortization inputs are the only four variables you enter, and, unless you turn off cell protection, the four cells containing these values are the only cells in which you can enter data.
Note: These variables are defined by the debt contract.
For convenience and good documentation within the starter workbook, cell B4 contains the starting debt amount and is named Principal, cell B5 contains the debt term in payment periods and is named Debt_Term, cell B6 contains the amortization term in payment periods and is named Amortize_Term, and cell B7 contains the per-period interest rate and is named Interest_Rate. The formulas within the actual schedules use these cell names rather than the cell addresses.
Fixed Interest Rate Amortization Schedule
The amortization schedule has five columns: Period, Total Payment, Interest Component, Principal Component, and Principal Balance.
Period
The period identifier simply numbers the time periods over which the debt is outstanding and paid down. The first period identifier is stored in cell B12 as the integer 1. Periods that follow are stored as the previous period plus 1.
Total Payment
The total payment is the payment for the current period. If you’re using the starter workbook for liability bookkeeping, this is the amount you enter as a credit to the cash account. If you’re using this starter workbook for receivables or investment bookkeeping, this is the amount you enter as a debit to your cash account. If you’re using the starter workbook as part of a financial forecast and, from your perspective, the debt being amortized represents an asset, you can add the payment amount to other debt service payments in the section of a cash flow forecast that details sources of funds.
The Total Payment formula for the first period uses the PMT function as follows:
=-PMT(Interest_Rate,Amortize_Term,Principal)
The minus sign to the left of the PMT function is needed because, when the principal amount included is a positive number, the payment calculated is negative. The formula for subsequent periods is modified to display 0 after the debt is paid off. Starting in the second period, the basic formula is enclosed in an IF statement that first verifies that the debt term hasn’t already expired. Therefore, in the second period of the forecasting horizon, the Total Payment formula is:
=IF(B13<=Debt_Term,-PMT(Interest_Rate,Amortize_Term,Principal),0)
B13 contains the period identifier. In subsequent periods, this part of the formula is changed so that the formula always uses the current period identifier.
Interest Component
The interest component is the amount of income or expense accrued over the payment period. If you’re using the starter workbook for liability bookkeeping, this is the amount you enter as a debit to the interest expense account. If you’re using the starter workbook for receivables or investment bookkeeping, this is the amount you enter as a credit to the interest income account. If you’re using the starter workbook for a financial forecast, you can include this amount in either the financing income or financing expense portion of your profit and loss statement.
Each period’s Interest Component amount is the previous period’s Principal Balance amount times the Interest Rate value. The formula for the first period is:
=Principal*Interest_Rate
The formula for subsequent periods is modified to display 0 after the debt is paid off. Starting in the second period, the basic formula is enclosed in an IF statement that first verifies that the debt term hasn’t already expired. Therefore, in the second period of the forecasting horizon, the total payment formula is:
=IF(B13<=Debt_Term,F12*Interest_Rate,0)
As in the total payment formula, B13 contains the period identifier. F12 contains the ending balance in the previous period. In subsequent periods, these parts of the formula are changed so that the formula always uses the current period identifier and the Principal Balance amount for the previous period.
Principal Component
The principal component is the amount subtracted from the outstanding principal balance when the total payment exceeds the accrued interest. If you’re using the starter workbook for receivables or investment bookkeeping, this is the amount you enter as a credit to the asset account, reflecting a reduction in the amount owed you. If you’re using the starter workbook for liability bookkeeping, this is the amount you enter as a debit to the liability account, reflecting a reflecting in the amount you owe.
For each period, the Principal Component amount is the Total Payment amount less the Interest Component amount. The formula for the first period is:
=C12-D12
The formula for the second period is:
=C13-D13
and so on.
Principal Balance
The principal balance is the outstanding balance of the debt at the end of the period. If you’re using the starter workbook for financial forecasts, this is the amount you include in the balance sheet as either an asset or a liability.
The Principal Balance amount is the previous period’s Principal Balance amount minus the Principal Component amount for the current period. The formula for the first period is:
=Principal-E12
The formula for subsequent periods is modified to display 0 for the Principal Balance amount after the debt is paid off. This modification is necessary for those situations in which a balloon payment is paid. Starting in the second period, the basic formula is enclosed in an IF statement that first verifies that the debt term hasn’t already expired. Therefore, in the second period of the forecasting horizon, the Principal Balance formula is:
=IF(B13<=Debt_Term,F12-E13,0)
Again, B13 contains the period identifier. F12 contains the ending balance for the previous period. E13 contains the Principal Component amount for the current period. In subsequent periods, these parts of the formula are changed so that the formula always uses the current period identifier, the Principal Balance amount for the previous period, and the Principal Component amount for the current period.
Balloon Payment Schedule
Use the Balloon Payment Schedule when you’re working with a debt that contains a balloon payment. The bookkeeping and forecasting method for balloon payments and principal reductions are the same as the methods for the total payment and principal component described on the amortization schedule.
The Balloon Payment Schedule has three columns: Balloon Payment, Full Principal Payment, and True Balance.
Balloon Payment
The balloon payment is the principal balance outstanding when the debt term ends. The formula for the first period is:
=IF (B12=Debt_Term,F12,0)
The formula for the second period is:
=IF(B13=Debt_Term,F13,0)
and so on.
Full Principal Payment
The full principal payment for each period is the principal component of the regular pay- ment plus any balloon payment. The schedule uses the Full Principal Payment value as the full principal reduction stemming from regular payments and any balloon payment due. The formula for the first period is:
=H12+E12
The formula for the second period is:
=H13+E13
and so on.
True Balance
The true principal balance is the principal balance in the amortization schedule less any balloon payment made. The schedule uses the True Balance value as the principal balance outstanding because it includes both the principal components of the regular debit service payments and the balloon payment. The formula for the first period is:
=F12-H12
The formula for the second period is:
=F13-H13
and so on.
Leave a Reply