Amortization Model – All Variable

What I mean by “All Variable” is that in previous spreadsheets I included options to make certain variables change throughout the term of the loan. This amortization schedule combines those options into one model. Why have I done this, you say. The answer is because it is my blog, and I can. No other reason that I can think of right now. The variables are rate, CPR, Defaults, and Loss Severity. The inputs in yellow below are self explanatory, except that each “Term (Months”  must total the same number of months, or a warning appears. I put this together quickly, so if you find any errors let me know. Servicing is included, in case someone else is servicing the loans. Also there is a balloon option and an internal rate of return at a specific market rate.

Vary1 Download “AllVary

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. I love your site. I would absolutely pay for access. I’ve learned so much. Thank you.

    I have come back to this spreadsheet and have a question. Can CPR, Defaults, and Loss Severity be used as CRR (voluntary prepayment), CDR and Loss Severity?

Leave a Reply

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