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!