## 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 :

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. 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.

Thanks,