Creating a finance calculator

Brass Contributor

Hi all, I work in the finance industry and I want to create a calculator to assist in working how much brokerage / commission is earnt on each transaction.

 

The commission we earn is based on a percentage of the loan amount which is easy enough to do. What I need to do is reverse engineer what the commission is based on certain criteria.

As an example, I know the following.

  1. Loan amount: $18,950.00.
  2. Term: 5 years
  3. Monthly repayments (original): $430.35
  4. Interest rate to the customer is 13.41%.

 

I now need to calculate what the commission is based on.

  1. Other than the interest rates the above parameters remain the same.
  2. The interest rate is now 9.95%
  3. I need to reverse calculate what commission I would get based on receiving the commission upfront.

 

In summary, assuming the loan term, repayments and amount stay the same and the only thing that changes is the interest rate, what would be my commission is it was paid to me upfront.

Notes, I have used an 3rd party software tool to establish that the commission would be 7.1777%, I just cant do it in excel.

 

Asny help would be greatly appreciated.

 

 

2 Replies

@robwill100 

 

I think that Excel's Goal Seek feature might interest you. Go to Data tab, What-if Analysis on the right, and then Goal Seek. You can play with it to perform simple "optimization" problems.

 

There is also the Solver Add-in, which you can include to your Excel if you want to perform it in a more sophisticated way.

 

Is that what you are looking for?

 

 

@MAngosto Thanks for the sugesstion