Is there a fuction for this?

[Scott]

I've got two similar problems about loans which Excel
should be able to help me on.
1) In Texas, home equity loans can only be 80% of the
house value, and closing costs can only be 3% of the
amount financed (Loan amount - closing costs)

The problem is easier to understand in an example.
$100,000 house value means we can have an $80,000 loan.
No problem there. You would think closing costs could be
a maximum of 3%*80,000 or $2,400. But the amount financed
is $80,000 - closing costs or 80,000-2,400=$77,600, so
closing costs can only actually be 3%*77,600=$2,328, but
77,600+2,328=$79,928 which is less than the $80,000 loan
amount which we know is right. So closing costs can
actually be higher, but how can we find out exactly what
fits?

2) Sometimes lenders will charge Points. 1 point = 1% of
the loan amount. On a refiance it is sometimes nice to
raise the new loan amount to cover all the fees so no out
of pocket money is used to close, and not is returned.
Here's the problem:

With zero points, let say the loan amount is going to be
exactly $100,000 and that will pay of the old loan,
closing costs, escrows, etc. exactly. If I decide to get
a lower rate by paying one point, how can I find what the
exact loan amount will be? See what happens? $100,000 +
1% ($1,000)= $101,000, so the point changes to 1010, and
I have to bring $10 to close. It gets worse still. What
if I want to pay 1.5% or 2%? The other fees like title
insurance which are based on a lookup table by loan
amounts also goes up.

Summary: I've got to determine one value that is
dependant on a second. To determine the second value I
must find the first. Impossible? Solutions do exists e.g.
Loan with zero points and no out of pocket money to close
= $100,000. If I pay 1.5 points and still have no out of
pocket, the loan is $101,522.84. 101,522.84*1.5%
=$1,522.84.

All ideas are welcome.
Thanks!
Scott

 

[Harlan Grove]

...
...

The problem is easier to understand in an example.
$100,000 house value means we can have an $80,000 loan.
No problem there. You would think closing costs could be
a maximum of 3%*80,000 or $2,400. But the amount financed
is $80,000 - closing costs or 80,000-2,400=$77,600, so
closing costs can only actually be 3%*77,600=$2,328, but
77,600+2,328=$79,928 which is less than the $80,000 loan
amount which we know is right. So closing costs can
actually be higher, but how can we find out exactly what
fits?

...

How can 'we' do it? Algebra would be one idea.

closing costs = 0.03 * financed amount
financed amount = loan amount - closing costs
closing costs = 0.03 * (loan amount - closing costs)
1.03 * closing costs = 0.03 * loan amount
closing costs = loan amount * 0.03 / 1.03

80,000 * 0.03 / 1.03 = 2,330.10 (rounded up to nearest penny)

2,330.10 / (80,000 - 2,330.10) = 2,330.10 / 77,669.90 = 0.03

 

[Scott]

Hi Harlan!

Thanks for the algebra. Now that I see it, it seems so
obvious.

Scott.