The fixed rate annuity due amortization starter workbook has three parts: the Fixed Interest Rate, Annuity Due Amortization Inputs box; the Fixed Interest Rate, Annuity Due Amortization Schedule; and the Balloon Payment Schedule.
Fixed Interest Rate, Annuity Due 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.
As with the other debt amortization schedules, 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 schedule use these cell names rather than the cell addresses.
Fixed Interest Rate, Annuity Due 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 current period payment. 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 a liability, you can add the payment amount to other debt service payments in the section of a cash flow forecast that details uses of funds. If you’re using the starter workbook as part of a financial forecast and, from your perspective, the debt being amortized represents an asset, the payment amount would probably be added 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,0,1)
The minus sign to the left of the PMT function is needed because, when the Principal Component amount is a positive number, the Total Payment amount that is calculated is negative.
The formula for subsequent periods is also 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. If it has, the payment amount is zero; the debt has 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,1),0)
B13 contains the period identifier. In subsequent periods, this part of the formula is changed so that it always uses the current period identifier.
Interest Component
The interest component is the amount of income or expense accrued over the previous payment period and paid at the beginning of the current payment period. If you’re using the starter workbook for liability bookkeeping, this is the amount you enter as the 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 is the previous Principal Balance amount times the Interest Rate value. But because no time has passed between the start of the debt term and the first payment, letting interest accrue, the first-period formula is 0. Starting in the second period, the Interest Component amount is the previous period’s Principal Balance times the Interest Rate value. Also, the basic formula is enclosed in an IF statement that 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)
B13 contains the period identifier. F12 contains the previous period’s Principal Balance amount. In subsequent periods, these parts of the formula change so that the formula always uses the current period identifier and the previous period Principal Balance amount.
Note: Remember that the balances reported on the amortization schedule are as of the beginning of the payment period. If you need to know the balance at the end of the payment period—as might be the case if you use the amortization schedule for accounting or financial forecasting—you need to add the interest accrued during the previous period to show the correct balance at the end of the period. For example, the ending balance for the first period is calculated: =F12+D13
The formula for the second period is:
=F13+D14
and so on. Notice also that any balloon payment is assumed to be made at the beginning of the payment 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 the credit to the asset account, reflecting a reduction in the amount owed to you. If you’re using the starter workbook for liability bookkeeping, this is the amount you enter as the debit to the liability account, reflecting a reduction 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 beginning of the period immediately after the payment is made. If you’re using the starter workbook for financial forecasts, this is the amount you enter, along with any interest accrued as of the balance sheet date, on the balance sheet as either an asset or a liability. (For example, if you’re reporting the balance for the end of the first period, you add the beginning Principal Balance amount for the first period and the interest accrued during the first period but paid in the second period.)
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 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 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 Principal Balance amount for the previous period. E13 contains the Principal Component amount for the current period. In subsequent periods, these parts of the formula change 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 debt that contains a balloon payment. The bookkeeping and forecasting methods for balloon payments and principal reduction 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 is the principal component of the regular payment and any balloon payment. The schedule uses the Full Principal Payment value as the total principal reduction stemming from regular payments and any balloon payment made. 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 debt 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