Tell me more ×
Personal Finance & Money Stack Exchange is a question and answer site for people who want to be financially literate. It's 100% free, no registration required.
up vote 5 down vote favorite

I'm looking for some advice on how to model a changing interest rate using a spreadsheet.

I maintain a basic spreadsheet which I use to predict the future state of my home mortgage based on a number of factors I can vary. It works great but it's getting a little limiting, especially as I try to weigh up the merits of various fixed-rate deals vs sticking on my floating rate and waiting for the rise.

What I'd really like to do is have a model where I can test various scenarios such as the following:

  • Sticking on floating rate, factoring in rises at a given rate starting at some date and finishing when the rate reaches x.
  • The same as above but where I bail for a fixed rate at a given threshold (and test various fixed rates).
  • Fixing now for the whole projection period.

My rough idea as to how I might model this would be roughly to take each model and try to produce a chart for each scenario for the interest rates alone. Next I would have to apply those interest rates to the existing base model I have now to produce the different predictions.

Does this approach sound reasonable? Does anyone have any experience or examples of trying to model this kind of data in as much detail as this?

Any ideas / tips welcome. Thanks!

share|improve this question

1 Answer

up vote 2 down vote

It sounds like you need to tinker with a spreadsheet. A series with the ability to change the rate for a given year so you can simulate the worst case, the ARM rising to the maximum each year. Another few rows showing the payments and year end balance for the fixed rate. A bit more effort, and you can calculate the running balance by paying the higher fixed rate payment up front, so you can then get a good idea of the breakeven in time and rate.

spreadsheet

Edit - I wrote a spreadsheet. Please look at it and let me know if it helps. Top row for fixed, then adjust bottom row rates to show change in payments if rates go up. The spreadsheet can use some tinkering, but it's a start. Not sure if you are ahead of me or if this will help you.

share|improve this answer
well, I was already using a spreadsheet...the question was about how I could model what I wanted within that kind of sheet. In the end I wrote a custom function that could calculate the interest rate at a given point in time based on a few input parameters (like the ones I mentioned above) then applied it to my existing mortgage amortisation calculation sheet. Seemed to work quite well for what I wanted - enough for me to make a short term decision at least anyway :) – jkp Jul 11 '12 at 6:10
The layout I had in mind would let you enter the variable rate for each year, eg start at 2.5%, and then calculate the new payments if rates went up over time to the maximum, usually a 5% bump from the start, but capped at 2% per year increase. Last I did this, the ARM made sense as the worst case put a break even at 5 years, and homeowner had 90% certainty to move by year 3. To use real numbers look at historical data to see how quickly rates can rise or fall. – JoeTaxpayer Jul 11 '12 at 16:17

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.