Commercial Loan Amortizations

I was playing with some assumptions for commercial loan amortizations and came up with the this Excel spreadsheet. For example:

Assume the borrower wants to make payments quarterly, semi-annually, annually, or bi-monthly, instead of monthly, in order to match the borrowers source of income. You might want to oblige the borrower, but you still want to earn monthly compounding.

You would like the option of calculating interest on the loan based various methods:
We start with the input cells in yellow:The cells shown below include the payment per period, the equivalent compound rate and the prorated first period interest. The equivalent compound rate allows the lender to earn interest on a monthly basis, while allowing quarterly payments. All calculations that include the loan rate use this rate. It is assumed that any prorated interest, due between the settlement and first payment, will be considered as the first interest payment. Depending on the length of the an odd period, this can cause positive or negative principal amortization for the first payment. In my default case, the prorated period is exactly one quarter (90 days, given a 30/360 interest method, so the normal interest payments is used.

Besides the amortization schedule, a summery of the loan is as follows:

These two tables allowed me to simplify the calculations rather than doing redundant lookups in various formulas :

Download: CLA

Don Pistulka

Retired Credit Union CFO - Finance
Background: over 40 years in investments, asset/Liability management, banking, securities trader.
Worked for: California Credit Union, WesCorp, CalFed S&L, Crocker Bank, Carroll McEntee, Federal Home Loan Bank Board (D.C.), Western Asset Management, Security Pacific National Bank.


  1. Hello Don,

    I was curious how would one incorporate loan prepayments and charge-offs into this model?

  2. Hi Don. Thanks for this. I was trying to incorporate one where there’s moratorium on both principal and interest and interest is capitalized at the end of a period. Excel is going bonkers. Will be glad if you can help

  3. Don,

    Did you ever create a commercial loan amortization schedule that includes an I/O period followed by fully amortizing loan? I am trying to adjust the model above to include and Excel is going haywire when I make the changes.



  4. does this spread sheet do US rule vs normal (local rule) amortization ?

  5. Dear Don,
    Thank you once again for sharing your invaluable knowledge and hard work. This is absolutely a great tool to have at one’s disposal. I just had a question: what if there is a grace period in the loan structure? Most commercial loans do have a grace period in the overall term of the loan. How would I reflect that in the model?

    Many Thanks in advance.

    1. Michael,
      Normally, I include the effect of a grace period only in the market pricing or return calculations. There are no such calculations in this spreadsheet. Since the grace period does not affect the calculations in this model, I would simply add another column next to the dates which would be the date plus 14 days (or whatever the grace period is).

Leave a Reply

Your email address will not be published. Required fields are marked *