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

Comments

  1. Stan Meyers says:

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

    1. Don Pistulka Don Pistulka says:

      Hi Stan,

      Please explain “U.S vs normal”

      Don

  2. Michael says:

    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.
    Respectfully,
    Michael

    1. Don Pistulka Don Pistulka says:

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

Leave a Reply

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

*
*

WordPress spam blocked by CleanTalk.