How do I create the formula for this calculation?

[Janelle]

Hi,

I am having a difficult time creating the formula for an equation. Any help
would be greatly appreciated!!

I have created a spreadsheet that calculates loan interest rate pricing.
The column I need help with is for a calculation that would tell me what
origination fee percentage (decimal) I would charge a borrower based on
wanting to make $1800 per loan.

I currently have a column set up that calculates what the percentage of the
loan amount entered equals $1800 (ie I input the loan amount $300,000 in A2
and A6 tells me that $1800 of 300k equals .60%)

In column F, it lists the price the broker would pay or receive (positive or
negative percentages, respectively) for each interest rate listed in another
column.

I need a column (D) to calculate how much would be charged to the borrower,
to make $1800. So for instance, if .60 is $1800 of 300k and the price the
loan officer has to pay to get the rate is .50 (of the loan amount), then I
would charge 1.10% fee to borrower (in order to pay for the rate and still
make $1800).

This same calculation can also contain a negative number...for instance....
If the price for the interest rate is a rebate to the loan officer (which
would show on the spreadsheet as a negative percentage)

So an example of how I would calculate the amount to charge a borrower if
the interest rate gives the loan officer a rebate (negative percentage) is:

If the price of the interest rate is -.50, I would charge .10 to the
borrower, in order to make $1800.

Soooo, how can I write a formula to calculate what to charge the borrower,
in order to make $1800, depending on the price of the rate?

A2 is where the loan amount is input
A6 is where the formula calculates the percentage of the loan amount that =
$1800
C2 through 35 is a list of all the possible interest rates
F2 thourhg 35 is a list of all the prices for the interest rates (in both
positive and negative percentages)
Column D (2 - 35) is where I need the the calculation of what percentage to
charge the borrower, in order to make $1800.

Please ask any questions necessary in order to help!! I just cannot figure
this out but I am pretty sure there must be a way to do it.

Thank you in advance for any help you can give!!! =)

 

[JLatham]

Janelle, I'm not certain this is absolutely the answer, but hopefully it is
at least a start.

From reading through your post, it appears to me (and correct me if I am
wrong) that you're only interested in a result when the amount in F2:F35 is
negative?? I'm not certain what to do if it is a positive percentage. If we
need to do something with positive values in F2:F35, give me an example with
Loan amount and cost for a given rate and what you'd expect to have to
charge the borrower for the loan and we'll work from there.

In the meantime, try this formula in D2 (and fill down through D35)
=IF(F2<0,ABS(F2*A$2)+1800)/A$2

Just as a side note: I presume your formula in A6 looks something like:
=1800/A2
I might suggest putting your desired profit (the $1800) in a cell somewhere
and then referring to that cell in all formulas that have to consider that
amount. That way, if you business model (or greed <g>) determines that you
need a higher profit, you can simply change the amount in that 1 cell and all
formulas using that value in them would automatically change. Might save you
from having to hunt down every place where you used 1800 and changing them
and then worrying that you may have missed one somewhere.