Hi,
I wonder if anyone can help with the "reverse" of this function.
I use this to calculate how much the monthly repayment on a mortgage will be and it works just fine
ABS(PMT("Int rate %","Years"*12,"Amount outstanding")/12)
e.g. ABS(PMT(5%,25*12,100000)/12) = £416.67 which is correct
But now I need a formula to return the "amount outstanding" if I tell it what the monthly payment is. i.e. I want to know how big a mortgage I can have if I can afford £450 per month. Using Goal Seek I came up with £108000, but I need to write a function to calculate it.
I'm sure I have all the ingredients, I just can't seem to figure out how to put them all together.
Any ideas?
Pat
I wonder if anyone can help with the "reverse" of this function.
I use this to calculate how much the monthly repayment on a mortgage will be and it works just fine
ABS(PMT("Int rate %","Years"*12,"Amount outstanding")/12)
e.g. ABS(PMT(5%,25*12,100000)/12) = £416.67 which is correct
But now I need a formula to return the "amount outstanding" if I tell it what the monthly payment is. i.e. I want to know how big a mortgage I can have if I can afford £450 per month. Using Goal Seek I came up with £108000, but I need to write a function to calculate it.
I'm sure I have all the ingredients, I just can't seem to figure out how to put them all together.
Any ideas?
Pat