Note: I have updated this post with more options.

See Variable Rate Amortization – Day/Year Count & Last Payment Options.

Have you ever wanted an amortization schedule where you can set the rate for one term and then change the rate for another term, and change the rate and term a total of six times? If you have, try the workbook “AmortizationChangeRate”. As usual the yellow cells are input cells. The example below shows a $135,000 loan at 2% for the first 6 months, 3% for the next 6 months and 4% for the last six months. The payment changes each time the rate changes. There is also a column for extra payments.

At the bottom of the input rates is the average weighted rate. In this case 3.00%. Making a loan at 3% for the full 18 months is not the same as this variable rate structure. The present value of the payments for an 18 month, 3% loan discounted at 3% would be $135,000, just as you would expect. This loan structure discounted at 3% is $134,550.90. If we change the structure from 6 month terms to 90 day terms and left the rates the same, the present value difference discounted at 3% is $128,339.81 vs. $135,000.

This might come in handy when considering restructuring a loan to see the changes in present value.

Download workbook “AmortizationChangeRate” from:

http://www.pistulka.com/Excel_Shared/

Downloads Written in Excel 2013

Mr. Pistulka,

My name is Thomas Melton and I am the owner (along with my wife) with a handful of veterinary clinics in North Central West Virginia- along with some other various companies as well. I was an econ major and I am still a bit of a economics/math nerd, with a fair number of spreadsheets of my own devising that I have utilized over the last 30 years or so. I have historically had some issues when engaging with the banks that underwrite some of my SBA loans and have kept track of those debts via Excel spreadsheets. I have been able to have my numbers generally comport to what the bank numbers are, but it has been very time consuming. I suspect you may already have a amortization spreadsheet that would be better than what I have been using- or one that could be readily modified to suit my needs. I was hoping that you might take a small amount of your time and help me with this issue. I would, of course, be willing to pay for your services if you so desired.

Thomas Melton

Thomas,

Banks are not always consistent when it comes to calculating interest, so it can be frustrating trying to match their numbers. My last post back in September, was a workbook with sample amortization schedules.

Take a look and see if any of those will help you. If not, send me a copy of what you are working on and I will see if I can help.

Don

Hello,

Your schedules are very helpful, I have a situation where I am trying to determine the payoff of a HELOC that is shared by 2 people(one person makes payments on the loan and one person pays directly to the other person) – the rate is variable and has changed 6 times in the last 3 years.

I’ve summarized all the activity (Draws and Payments and changes in interest rates) Could I share with you and see if you have a spreadsheet that might help me?

Thank you

Hi Lynda,

My last post has an amortization schedule with variable rates:

http://pistulka.com/Other/?p=3680

Let me know if that works for you.

Don

Hi Don,

The spreadsheet is quite helpful however would you able to assist with one that incorporates an eleven month payment holiday. I’ll appreciate it if you could.

Thando,

What happens to the interest on the loan for the 11-month holiday? Is it added on to the end of the loan (negative amortization) or forgiven? Send me an example along with how the interest is handle to blog@pistulka.com.

Don

Thando,

Have you taken a look at my Skip-a-Payment workbook?

http://pistulka.com/Other/?p=818

Hi Don.

Very informative and useful spreadsheet. Do you perhaps have the amortization schedule with both variable interest rate and extra payments template?. I would like to check how many months i can shave-off my mortgage by making random extra payments. My interest rate various from time to time.

Hi,

I’m confused. This spreadsheet does allow for both rate changes and extra payments.

http://pistulka.com/Excel_Shared/AmortizationChangeRate.xlsx

Don

Hi Don (with due respect)

I have one loan restructuring model in excel given by one global consultant

I am sorry I can not share but would like to understand certain details of it

as follow

Question 1

Type Of Grace Period (What is the definition of the following options)

a.Complete Exemption

b. Interest Only

c.Temporary Reduction

d.No Grace Period

Question 2

How is loan restructuring done for tiered interest rates

Sachin,

I am going to send you a copy of my TDR (Troubled Debt Restructuring) model. I will try to answer your question, however the loan market in India may have different definitions than the U.S. markets.

Don

Hi don,

Really impressive spreadsheets. I’m hoping you can help me with some calculations. I am trying to forecast annual collection rates based on actual historical 10 year annual collection rates. Right now we require a 50% down payment and I’m trying to calculate what the annual collection rate percentages would be if we change to 25% down payment weighted using the same annual collection rates. I need to display the APR by year and the cum total. Could you help me with this calculation? I have worked on it for 2 long and cant get it to add up to 100% collection at the end of year 10. Thanks

Hi Matthew,

I’m having a hard time visualizing what you are trying to do. Could you send me a copy of your calculations so far?

Thanks,

Send it to blog@pistulka.com

Don

Hello Don,

Not sure if my first comment was submitted so I am re-submitting. I have a loan that has variable monthly payments and variable interest rate throughout the year. I have no idea how to track how much I still owe. Any advice would be greatly appreciated.

Thank you,

Kerry

Kerry,

I will send you something that might work for you.

Don

Hello Don,

What you do is amazing. I have loan in which the monthly payments may change and well as sometimes the interest rate changes. I am trying to figure out how to calculate how much I currently owe. Do you have a spreadsheet that could do this type of calculation for me?

I would appreciate any help you could provide,

Thank you,

Kerry

Hi Don,

Thank you very much for your spreadsheets online.

Perhaps you could help me wit a small change on your sheet: AmortzationChangeRate.xlsx.

My situation:

We are 2 persons about to buy property. We will be paying money into a joint account for the mortgage. We plan to make extra paymens when we have the money to do so.

The mortgage will be a variable rate over 40 years, and according to the advisor, the payments will always stay the same, but the term (years) will decrease when we make extra payments.

I need to make it fair for both of us, so the whoever pays more into his own share, the more interest would be saved on his account.

1) Is it possible to change the sheet to choose if you want to reduce the (a) Term, or (b) The monthly amount.

2) Is it possible to have more rows for the interest rate, as it might change quite a lot over 40 years.

3) Is it possible to have a tab to show “Years saved off original loan term”?

I saw another calculator which also would have worked IF it was possible to select different interest rates. See mortgagecalculator.org

Thank you for any help!

Stefan,

It is a little hectic around the holiday, but give me a few days and I will come up with something for you.

Don

this spreadsheet is awesome and exactly what i was looking for! thank you for providing. would you be so kind to let me know how i could adjust for a 10 year length please?

Molli,

The email I sent you came back “not valid”

this spreadsheet is awesome and exactly what i was looking for! how can i make this for a 10 year loan term please?

Molli,

Are you looking to change the rate more than 6 times during the life of the loan or less than six times? For 6 times or less just make sure that cell G9 (Total Months) equals 120. See attachment. Let me know if that works for you.

Don

Can this be adapted for fortnightly payment schedule?

Colin,

Below is a link to a Canadian mortgage amortization that allows for bi-weekly payments. You can download that spreadsheet and use the methodology to adapt the variable rates.

https://www.vertex42.com/Calculators/Canadian-mortgage.html

Don

Do the methodology and excell sheet can be used for FRN instrument ?

Thanks..

Niko,

The basic methodology would work for calculating the internal rate of return, however the payment schedule would need to be changed to quarterly from monthly. The IRR function would also need to be changed to quarterly. The principal paydown would most likely need to be set to zero. In addition, the number of rate changes would probably be needed to be increased.The note’s interest would also need to be based on a 30/360 day count.

You would be better off just using the variable rate concept, and building your own spreadsheet from scratch.

Don