In my last post (PV & FV of Periodic Uneven Cash Flows & Rates) I created uneven cash flows, terms, and rates for the present and future value of an annuity. I decided that I would apply these options to an amortization schedule. Two other posts and spreadsheets that allow multiple changes to amortization schedules are Build your own CPR model and Amortization Schedule With Variable Rates.
This is a little harder than creating a cash flow, because as you change payments you get closer to final payoff of the loan, so you need a payoff amount. In order to close out the amortization, a payoff amount will appear in the column titled “Payoff Needed”
There are two requirements to getting a correct payoff amount:
1. The last payment can only be one payment. The example below show the 72nd payment broken out from the above term which was previously 36 payments.
2. The payment cell needs any number greater than zero entered, before the payoff needed will appear. Once the payoff appears, copy/paste the amount into the payment cell. There is no rounding in the amortization, so copy/paste will get the whole number.
All spreadsheets created in Excel 2013