simongr
Enthusiast
- Joined
- Jul 10, 2006
- Posts
- 14,307
Hi all
Hopefully an easy one. I am just trying to put together a budget for a new mortgage based on our current mortgage, some upcoming permanent cost savings, various interest rates and loan sizes.
What I want to do is have a formula that looks for the max repayment I think we can afford (based on taking the current repayment and adding in the fixed expected savings) then based on the interest rate picked can do return for the max loan amount.
Data looks something like this:
Rate 4% 5% 6% Capital
Repayment $50 $60 $70 500K
Repayment $55 $65 $75 600K
Repayment $60 $70 $80 700K
Repayment $65 $75 $85 800K
So based on a bunch of factors in the spreadsheet I work out how much we can afford, select a current or expected interest rate. It then looks for the repayment based on that interest rate and picks the Capital amount.
I can get it easily with a vlookup statement on a single interest rate - but I can't figure how essentially vary the table size to flex the interest rate.
I have chosen about 10 interest rates (increment of 20 basis points) starting at current available fixed rates. I could it with nested if statements but that would be ugly and would need 10 repayments area names (i.e. if the rate is 4% vlookup(repayment,4%area,4) and so on.)
It is probably possible in visual basic but my skills there are absent.
I want to factor in a few other variable that change the potential max repayment.
Any help would be appreciated. Also please don't be concerned that I am trying to work out how I can maximise my debt - I am just working out based on some known cost savings if we didnt take the benefit of those how much would our mortgage repayment be and we wouldn't be worse off than we are now.
Cheers
S
Hopefully an easy one. I am just trying to put together a budget for a new mortgage based on our current mortgage, some upcoming permanent cost savings, various interest rates and loan sizes.
What I want to do is have a formula that looks for the max repayment I think we can afford (based on taking the current repayment and adding in the fixed expected savings) then based on the interest rate picked can do return for the max loan amount.
Data looks something like this:
Rate 4% 5% 6% Capital
Repayment $50 $60 $70 500K
Repayment $55 $65 $75 600K
Repayment $60 $70 $80 700K
Repayment $65 $75 $85 800K
So based on a bunch of factors in the spreadsheet I work out how much we can afford, select a current or expected interest rate. It then looks for the repayment based on that interest rate and picks the Capital amount.
I can get it easily with a vlookup statement on a single interest rate - but I can't figure how essentially vary the table size to flex the interest rate.
I have chosen about 10 interest rates (increment of 20 basis points) starting at current available fixed rates. I could it with nested if statements but that would be ugly and would need 10 repayments area names (i.e. if the rate is 4% vlookup(repayment,4%area,4) and so on.)
It is probably possible in visual basic but my skills there are absent.
I want to factor in a few other variable that change the potential max repayment.
Any help would be appreciated. Also please don't be concerned that I am trying to work out how I can maximise my debt - I am just working out based on some known cost savings if we didnt take the benefit of those how much would our mortgage repayment be and we wouldn't be worse off than we are now.
Cheers
S
