Unstructured Amortized Loan Schedule

A reader asked if I could help with a loan between family members that did not have specific payment requirements. Payments could be made on any date and any amount. The lender could increase or decrease the loan by any amount on any date. There was also no set maturity date for the loan. The loan would compound semiannually.

A normal amortization would have specific coupon payments expected on specific dates, with a payment that was calculated to amortize the loan to zero after a specific time. You can see how using a normal amortization would be too unwieldy.

After a few ideas, my final shot on such an unstructured loan can be downloaded below.

There naturally are certain basic rules that must be adhered to make the schedule work:

 

You must remember to enter the compounding dates. Your options are Monthly, Quarterly, Semiannually, or Annually.

I have not tested this amortization in the real world, so as usual, use at your own risk.

Download:   http://pistulka.com/Excel_Shared/Odd_Loan.xlsx

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.

2 Comments

  1. Don, I have been a follower for quite some time, and like how you approach solving these issues.

    I have something I’m wrestling with, and hopefully you can help me.’

    I am looking for an Excel calculator to find the OAS on bonds that have yield maintenance penalties (YMP). I can calculate the YMP for any time in the future (assuming current rates), but am having difficulty in creating a spreadsheet that can find OAS for different penalties (over time), ALONG with differing future rates.

    Can you help?

    Thanks, Darren

Leave a Reply

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