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 :