Simple formula overthinking

[Inquiring_Minds]

I'm working on a checklist for my company. I'm sure I'm overthinking some of this.

I'm trying to find a formula that I can use to determine points/fees out of 100 automatically on the worksheet.
I need this to be for positive and negative numbers
I want to use information keyed in by different cells to auto populate the answer.
D8 (points bought) manually keyed in
B9 (loan amount) manually keyed in
D9 is the total points (D8-100) this should auto populate after keying the points in
D10 is the total costs of the points (D9*B9) this should auto populate after keying the loan amount in
They both should automatically be negative or positive depending on the total points bought, in their own cells

I can make this work (with a simple formula) but it will only return positive (or as a credit), I need mine to be able to do both.

 

[RoryA]

Assuming loan amount is always positive, then the two formulas should always have the same sign anyway. What am I missing?

 

[awoohaw]

It may help to use the xl2bb add in an give a sample of your worksheet, and an example.
With the way you have explained it, this is what I come up with:

Mr Excel Questions2.xlsm
	A	B	C	D	E
7				Points Bought
8				2
9	Loan Amount	100,000.00		-98	Total Points
10				(9,800,000.00)	Total Cost of Points
Sheet6

Cell Formulas
Range	Formula
D9	D9	=IF(D8="","",D8-100)
D10	D10	=D9*B9

 

[etaf]

D9 = d8-100
will be negative if D8 is less than 100

for example
D9 = -99 - if D8 =1

D9*B9
so if D9 is negative - then result will be negative

-99 * B9 - assuming B9 is not negative

 

[Inquiring_Minds]

D9 = d8-100
will be negative if D8 is less than 100

for example
D9 = -99 - if D8 =1

D9*B9
so if D9 is negative - then result will be negative

-99 * B9 - assuming B9 is not negative

Assuming loan amount is always positive, then the two formulas should always have the same sign anyway. What am I missing?

Think of it as buying a house (since that's what I need it for). When you purchase a home, you can purchase "points" which will raise your closing costs but lowers your interest (this will become a negative b/c it is being purchased). Sometimes the lender (my company) will give a credit for those points (this would be a credit/positive because we are giving it to them). I'm able to figure out the amount if I just used the number of points by the loan amount, but my company is looking to be able to key a figure in (points), whether positive or negative, and then auto populate another cell with the costs.

If D8 was 101 then D9 would be - 0.1
If D8 was 99 then D9 would be 0.1
The cost of the point would either be a charge (0.1) or a credit (-0.1). the loan amount (B9) will always be positive.

 

[RoryA]

Yes but D10 will be the same sign as D9. I thought that was the question?

 

[etaf]

If D8 was 99 then D9 would be 0.1

D9 is the total points (D8-100) this should auto populate after keying the points in

99-100
isnt that -1

 

[awoohaw]

@Inquiring_Minds , not everyone here understands mortgage points. You need to explain apple to apples. Points and Mortgage amounts have different decimal positions.

 

[etaf]

As [B] awoohaw[/B]
pointed out - I was just going by the words in the example and post - NO IDEA about mortgage points and decimal places

without a fuller explanation, then i dont see how i can add further value to this post - I will leave to other members who understand the nuances here

 

[Inquiring_Minds]

As [B] awoohaw[/B]
pointed out - I was just going by the words in the example and post - NO IDEA about mortgage points and decimal places

without a fuller explanation, then i dont see how i can add further value to this post - I will leave to other members who understand the nuances here

I'm sorry, i thought it could be the same as a normal math problem, in my mind it is.