The variable rate, annuity due amortization starter workbook has three parts: the Variable Interest Rate, Annuity Due Amortization Inputs box; the Variable Interest Rate, Annuity Due Amortization Schedule; and the Balloon Payment Schedule.
Variable Interest Rate, Annuity Due Amortization Inputs
The amortization inputs, along with the Period Interest Rate values in the amortization schedule, are the variables you enter, and, unless you turn off cell protection, these 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, and cell B6 contains the amortization in payment periods and is named Amortize_Term. The formulas within the actual schedule use these cell names rather than the cell addresses.
Variable Interest Rate, Annuity Due Amortization Schedule
The amortization schedule has six columns: Period, Period Interest Rate, 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 is stored in cell B11 as the integer 1. Periods that follow are stored as the previous period plus 1.
Period Interest Rate
The period interest rates are the interest rates that, when multiplied by the outstanding balance at the beginning of the payment period, result in the amount of interest expense or interest income for the period. The period interest rates typically are tied to a market-sensitive interest rate index that is based on a widely traded or used interest rate, such as the one-year U.S. Treasury bill or one-year London interbank offered rate. You enter actual or forecasted interest rates for each payment period over the debt term in this column. (Although no time has elapsed and no interest has accrued before the first payment is made, you still need the Period Interest Rate value for the first period to calculate the Total Payment amount for the first period.)
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, 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(C11,Amortize_Term,Principal,0,1)
C11 contains the first-period interest rate. 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. In subsequent periods, this part of the formula changes so that the formula always uses the appropriate period’s interest rate. However, the formula is modified in subsequent periods to display 0 after the debt is paid off. The formula also is modified so that the calculated payment amount not only includes the new period Interest Rate value but also reflects the remaining amortization term and the current Principal Balance amount. Starting in the second period, then, this formula is enclosed in an IF statement that verifies that the debt term hasn’t already expired. In the second period of the forecasting horizon, the Total Payment formula is:
=IF(B12<=Debt_Term,-PMT(C12,Amortize_Term-B11,G11+E12,0,1),0)
B12 contains the period identifier. C12 contains the Period Interest Rate value for the current period. The Amortize_Term-B11 portion of the formula calculates the remaining amortization term over which the amount in cells G11 and E12, the previous period’s Principal Balance and the current period’s Interest Component, must be amortized. In subsequent periods, these parts of the formula are changed so that the formula always uses the current period identifier, the remaining amortization term, and the appropriate principal and accrued interest balances.
Interest Component
The interest component is the amount of income or expense that is accrued over the previous payment period and that is 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 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 add this amount in either the financing income or financing expense portion of your profit and loss statement.
Except for the Interest Component amount for the first period, which is entered as 0, each period’s Interest Component amount is the previous period’s Principal Balance amount times the previous Period Interest Rate value. However, the formula is modified to display 0 after the debt is paid off. Accordingly, the basic formula is enclosed in an IF statement that verifies that the debt term hasn’t already expired. In the second period of the forecasting horizon, the Interest Component formula is:
=IF(B12<=Debt_Term,G11*C11,0)
Again, B12 contains the period identifier. G11 contains the Principal Balance amount for the previous period. C11 contains the Period Interest Rate value for the previous period. In subsequent periods, these parts of the formula change so that the formula always uses the current period identifier, the previous period’s Principal Balance amount, and the previous Period Interest Rate value.
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 to 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 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:
=D11-E11
The formula for the second period is:
=D12-E12
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 amount, plus any accrued interest, is what you include on the balance sheet either as an asset or as a liability.
The Principal Balance amount is the previous period’s Principal Balance minus the Principal Component of the current period’s payment. The formula for the first period is:
=Principal-F11
The formula for subsequent periods is modified to display 0 after the debt term is paid off. Starting in the second period, then, the basic formula is enclosed in an IF statement that verifies that the debt term hasn’t already expired. In the second period of the forecasting horizon, the Principal Balance formula is:
=IF(B12<=Debt_Term,G11-F12,0)
Once again, B12 contains the period identifier. G11 contains the Principal Balance amount for the previous period. F12 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 current Principal Component amount.
Note: Remember that the balances reported on the amortization schedule are as of the beginning of the period. If you need to know the balances for the end of the period— as might be the case if you use the amortization schedule for accounting—you need to add the interest accrued from the previous period to show the principal balance for the end of the period. For example, the ending balance formula for the first period is:=G11+E12The ending balance formula for the second period is:
=G12+E13
and so on. Notice that any balloon payment is assumed to be made at the beginning of the payment period.
Balloon Payment Schedule
You 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 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(B11=Debt_Term,G11,0)
The formula for the second period is:
=IF(B12=Debt_Term,G12,0)
and so on.
Full Principal Payment
The full principal payment is the principal component of the regular payment plus any balloon payment. The schedule uses this value as the total principal reduction stemming from both regular payments made over the debt term and any balloon payment made during the last payment period of the debt term. The formula for the first period is:
=I11+F11
The formula for the second period is:
=I12+F12
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 this value as the principal balance outstanding, including both principal components of the regular debt service payments and the balloon payment. The formula for the first period is:
=G11-I11
The formula for the second period is:
=G12-I12
and so on.
Leave a Reply