Calculate Amortization Schedule

To calculate amortization schedule lets try the worksheet using a set of loan terms. Assume that Ubermilch Dairy needs to lease an irrigation system for its grazing pasture. The price is $ 90,000, and the manufacturer will finance that amount with a seven-year loan at a 12% annual interest rate, with payments due twice a year at the beginning of each period. Retrieve the original template and enter Irrigation System in cell B3 and 1 in cell B4. Enter the values shown in range D7..D10 of figure 2, and press the CALC key.

Differences between your schedule and the bank's The amounts in this amortization worksheet might not exactly match the schedule the bank provides. The differences are likely to be in one of two places. The first is the exact per-period interest rate. The per-period interest rate of an annuity multiplied by the beginning-of-period balance produces the per-period interest amount. Often this amount differs from the annual interest rate divided by the number of periods per year.

Dividing the annual interest rate by 12 to get a monthly interest rate is tricky; many lenders calculate interest on a daily, or even a continuous, basis. If the bank does this, you can't determine a monthly interest rate because the months vary in length. Under this system, you accrue a greater interest expense in January than in February.

Compounding this discrepancy is the fact that the worksheet assumes that you make debt-service payments at the exact beginning or end of each period. However, depending on the terms of the debt and the bank's bookkeeping procedures, making a payment a few days before or after the date it is due can substantially affect the interest you pay.

A second area of disagreement relates to a debt's outstanding balance. Even when you and the lender agree precisely on the breakdown of payments into their interest and principal components, you may not agree on the amount you must pay to satisfy the loan. The bank may use an interest-calculation method called the rule of 78s, under which you are charged for interest that, under other methods, would be due in later periods. In effect, this accelerated interest becomes part of the outstanding balance.

These discrepancies are usually immaterial and acceptable accounting practice lets you post interest payments and principal payments to your books based on your calculations rather than the banks. It's important, however, to keep thorough records, including all statements from the lender. One day you may have to justify these discrepancies to the IRS.