Amortization – Variable Terms, Rates, & Payments

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.

AmChange3

 AmChange2

Download “AmortChangeAll

All spreadsheets created in Excel 2013

 

 

 

 

Comments

  1. Bayo says:

    I’m working on a term loan of N1billion at 18% per annum for 12month period. The interest rate changed to 19.5% after 3 monthly payments. Please how do I calculate the monthly principal repayment from the 4th month? Many Thanks

    1. Don Pistulka Don Pistulka says:

      Bayo,

      If I understand you correctly, the 4th payment on a 12 month loan, will be $92,234,186.86. I am sending your an amortization schedule.

      Don

  2. Jody Smith says:

    Trying 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

    1. Don Pistulka Don Pistulka says:

      Jody,
      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

  3. JANICE says:

    Thank 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

    1. Don Pistulka Don Pistulka says:

      Janice,
      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.

Leave a Reply

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

*
*

WordPress spam blocked by CleanTalk.