Help with a financial funtion

[Big Pat]

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

 

[CraigDolphin]

Pat, not real familiar with these financial functions but I suspect your example may contain an error.

This is from Excel help file on the PMT function:
____________________________

Make sure that you are consistent about the units you use for specifying rate and nper. If you make monthly payments on a four-year loan at an annual interest rate of 12 percent, use 12%/12 for rate and 4*12 for nper. If you make annual payments on the same loan, use 12 percent for rate and 4 for nper.

_____________________________

Your example equation multiplies your loan term by 12 (indicating 12 monthly payments each year) whereas your rate value (5%) either indicates a single annual payment, or monthly payments at an annual interest rate of 60%.

I think the correct form of your equation should be
=ABS(PMT((5%/12),(years*12),100000))
which results in a monthly payment of $584,59

The 'reverse' funtion for PMT is PV, I think.
thus:
=PV((5%/12),years*12,-1*monthypayment)

=PV(0.004167,300,-450)

Note that the monthly payment amount should be a negative since it represents money you're paying out.

This equation suggests a monthly payment of $450 will get you a mortgage of around $77,966.02 at 5% annual rate over a 25 year term (excluding taxes).

Obviously this result is quite different than your Goal Seek software indicates.

Hope it helps.

 

[Big Pat]

Thank you!

Hi Craig,

Thnak you so much for your time on this. You're right of course that the payment has to be a negative amount. I should have seen that.

The calculation I had given (not dividing the rate by 12) had calculated only the interest payment for the mortgage, neglecting to account for the principal too. We have interest-only mortgages here in the UK, mainly for investors who buy a property, rent it out, use the rent to pay the interest, the sell the property (after some years) to pay the principal, thereby pocketing the increase in market-value. But it's a gamble!

Anyway I was using the wrong (but plausible) calculation which was why the numbers SEEMED to make sense.

I had got as far as working out that it was the PV function I needed, but the negative number was the vital part I was missing. I have now stored both of your functions for future reference.

Thank you once again.

Pat.

 

[CraigDolphin]

Glad to help