The variable rate ordinary annuity amortization starter workbook has three parts: the Variable Interest Rate Amortization Inputs box, the Variable Interest Rate Amortization Schedule, and the Balloon Payment Schedule.
Variable Interest Rate 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 schedules use these cell names rather than the cell addresses.
Variable Interest Rate 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 identifier 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, produce the amount of interest expense or interest income for the period. Period interest rates typically are tied to a market-sensitive interest rate index that is based on a widely traded or widely used interest rate, such as the one-year U.S. Treasury bill or the one-year London interbank offer rate. You enter either the actual or the forecasted interest rates for each payment period over the debt term in this column.
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 sources of funds section of a cash flow forecast.
The Total Payment formula for the first period uses the PMT function, as follows:
=-PMT(C11,Amortize_Term,Principal)
The minus sign to the left of the PMT function is needed because, when the Principal amount is positive, the Total Payment amount is negative. C11 contains the first-period interest rate. 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 to dis- play 0 after the debt is paid off. The formula also is modified so that the payment amount that is calculated 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),0)
B12 contains the period identifier. The Amortize_Term-B11 portion of the formula calculates the remaining amortization term over which the amount in cell G11, the previous period’s Principal Balance amount, must be amortized. C12 contains the Period Interest Rate value 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 remaining amortization term, and the previous period’s Principal Balance amount.
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 add this amount in either the financing income or financing expense portion of your profit and loss statement.
Each period’s Interest Component value is the Principal Balance amount times the Period Interest Rate value. The formula for the first period is:
=Principal*C11
However, the formula for subsequent periods is modified to display 0 after the debt is paid off. Starting in the second period, then, the basic formula is enclosed in an IF statement that first verifies that the debt term hasn’t already expired. In the second period of the fore- casting horizon, the Interest Component formula is:
=IF(B12<=Debt_Term,G11*C12,0)
Again, B12 contains the period identifier. G11 contains the Principal Balance amount for the previous period. C12 contains the Period Interest Rate value 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 previous period Principal Balance amount, and the cur- rent 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 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 value 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 end of the period. If you’re using the starter workbook for financial forecasts, this is the amount you include in the balance sheet either as an asset or as a liability.
The Principal Balance amount for each period 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-F11
The formula for subsequent periods is modified to display 0 after the debt is paid off. Starting in the second period, then, the basic formula is enclosed in an IF statement that first 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, theses 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 current Interest Component amount.
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(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 the Full Principal value as the full principal reduction stemming from both regular payments due over the debt term and any balloon payment due 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 per the amortization schedule less any balloon payment made. The schedule uses the True Balance value as the principal balance outstanding, including the principal components of both the regular debt service payment 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