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.

Download “AmortChangeAll“

All spreadsheets created in Excel 2013

Jody SmithTrying to work on a old estate. The problem is that for 30 years one person paid on a property loan. Now heirs are stating they are due monies from property being sold. I need to print an amortization sheet that dates back to 1975(!!! I know this is crazy) with adjustable interest rates each year (loan was for one annual payment a year for 30 years). As you know interest rates jumped from 9.75% to 20.50% in a five year period. Can you help???

Thank You

Jody

Don PistulkaPost authorJody,

Let me restate the problem, so I understand correctly. The person that paid the interest is saying that the amount of interest he/she paid over the 30 years is equal to or greater than the amount of profit that was received when the property was sold?

You probably don’t have the index or the spread, so we can just use one-year Treasury rate and a 3.50% spread. One-year Treasury rates are the easiest historical rates to get. I do need to know what the original value of the loan was, or an estimate of what the original value of home was, and we can assume an 80% loan to value.

Then we can take each of the yearly payments and calculate the future value, as if the payments were invested as an assumed interest rate, instead making the payments on the loan. If the future value of the payments is greater than the gain on the sale of the home, then there is no profit to distribute to the heirs.

Please send the information to blog@pistulka.com.

Don

JANICEThank you for your posts – they are very helpful

I have one question: I am trying to use variable amortization schedule, can you assist me in changing interest formula to actual/360 ??

Thank you

Don PistulkaPost authorJanice,

Are you interested in using actual/360 in a general amortization schedule, or this Amortization – Variable Terms, Rates, & Payments spreadsheet?

Also, if you can send me an Excel workbook with an example of what you are working on, it would help.