1. I don't need the formula to calculate a loan payment. I know how to do that.

2. I am not using programming languages - I am using a form builder with calculated fields. Therefore, the formulas I need are the same ones as if I were to do the

equation with pen and paper.

3. Excel is used to check my answers against the system I need to replicate (the system is not ours, therefore we have no way of getting the backend details).

__The problem, stated as clearly as I can is this:__
1. Let's say the bank is providing a loan quote to a customer.

2. The bank has a "cost of funds" of 5.3%

3. Of course, the bank must loan funds out above 5.3% to make money.

4. The bank lends the funds to the customer at 6.3% - there is a 1% "spread"

5.

**Express the "spread" in a $ value**
·

*the system I am trying to replicate states the $ spread as $484 (for Q1) and $524 (for Q2) if it helps*
__it's probably easier to write the question down this way:__

Solve for (y)
·

**$20,419 + (y) @ 5.3% over 60 months = $395.53 (payments in advance)**
·

**then for Q2, solve with a $2,000 balloon**
The only figures a bank officer will have to work with in this scenario are:

a) The amount that needs to be borrowed = $20,419

b) The cost of funds = 5.3%

c) The customer rate = 6.3%

d) The finance payment = $395.63

e) The term of the finance arrangement = 60 months

f) A balloon figure if required = $0 in Q1 and $2,000 in Q2

g) The payments are made in advance (ie payment 1 is made on day 1 of the loan)

__A couple of my own notes on this:__
· it seems that the $ spread can be determined by calculating the starting loan amount (PV).

· For example

*$20,903 (Loan amount + $ spread | $20,419 + 484) @ 5.3% over 5 years = $395.53/mth*
o In my mind, the steps are:

§ Using the payment calculated @ 6.3% ($395.53)

§ Calculate the original loan amount using the "cost of funds" rate of 5.3% in a PV formula

§ The answer should give you a loan amount of $20,903 (although, in excel it is slightly off giving an answer of $20,899.xx)

§ Then, simply take $20,903 - $20,419

§ This then equals the spread of $484

## Bookmarks