Excel query - formula to work out how much could be borrowed

[sooze29]

I'm really struggling with this formula. We have a PMT formula, but we need to work out one that says they can afford this much a month, over a period of years at an interest rate and we want the formula to work out how much they could borrow?
Any help greatly appreciated.

 

[SkipVought]

Hi,

"...they can afford this much a month..."

Based on WHAT?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[sooze29]

Say they can afford £1000 per month, the interest rate is 4.19% and they want to borrow it over 25 years. We want to find out what mortgage they could get?

 

[SkipVought]

What's your formula?

What formula does your company use to calculate this kind of data?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[sooze29]

We don't have a formula that is what we are struggling with. Basically we have the PMT formula which works out what the monthly payment would be, but want to find it out the other way. How much would £1000 per month at 5.19% over 25 years get you?

 

[AnotherHiggins]

What's the formula you have that you want reversed?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[sooze29]

We don't have a formula.

We have £1000 per month, at 5.19% interest over 25 years. Based on the PMT function which shows you for example £145,000 borrowed over 25 years at 5.19% - it gives you the monthly payments (capital and interest). We want a similar formula that from the monthly payments they can afford it will show how much they could borrow.

 

[SkipVought]

Have you looked at ALL the Financial formulas in Excel? The description & argument list will give you the answer.

Insert > Function.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[sooze29]

I have but cannot find anything, hence my reason for posting here.

 

[SkipVought]

Well I guess there's no forumla then, if you can't find it.

But you might check out NPV.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Sorry try FV

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[sooze29]

Thanks tried that, but that doesn't work for this calculation.

Thanks for your help in any case.

 

[SkipVought]

HOW did you use it and WHY does it not work?

I got £612,695.71



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[sooze29]

That's what I got, which I know is not how much a £1,000 per month mortgage would get you! I tested it using my own mortgage payments, rate etc and it gave me something like 4 times what I borrowed.

It's to do with the fact PMT knows how to work out capital and interest, whereas FV is based on a constant payment. Whereas a mortgage changes.

 

[xlbo]

To be honest - this is an office forum - not a mortgage rate calculation forum. Someone here may be able to help if they are involved in the same area of business but this is not really to do with excel - the functions used in the formula would probably be pretty basic - this is just a set of logic that YOU need to provide. If you can provide the logic, we can probably sort out the functions needed. You have a business issue, not an excel issue...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[GlennUK]

I have to agree with Geoff that this is a business issue rather than an Excel issue. That said, the reverse of PMT is the PV function, as in:

=PV(4.19%/12,12*25,-1000)

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[gbaughma]

Simple.

You can borrow up to 80% of the value of the home. (sometimes more, but that's a good figure)

You should *not* buy a home that is more than 2x your annual salary.

Your mortgage payment should not be more than 25% of your monthly income.



Just my 2¢
-Cole's Law: Shredded cabbage

--Greg

http://parallel.tzo.com