If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

First, I do not get the monthly payment that you get (and I am using the PMT function in excel and then confirming that result with a monthly calculation of interest and principal amortization).

I get a monthly payment of 397.61, not 395.53. And, as I have said, I have checked that by computing on a monthly basis for all 60 months.

Before we proceed, we had better figure out how you and I are coming to different figures on the monthly payment. Did you check your result with a monthly accounting? (That is always good practice with excel.)

EDIT: When talking about spreadsheets, it is frequently more convenient to pass them back and forth by email. If you choose to send me your email by private message, I can send you what I have done.

You're right - spreadsheets are easier when we can both see what's going on.

May be easier to help if you keep "Excel" out of it;
financial formulas have nothing to do with programming languages;
like, a car travelling at 50 mph travels 100 miles in 2 hours:
no matter if a Ford or Dodge

Your payment of 395.53 is correct IF the 1st payment is IMMEDIATE!
You did not state that.
In other words, what we have is a loan of 20419.00 - 395.53 = 20023.47
repayable over 59 months: understand?

May I suggest you resubmit your problem (keeping Excel OUT of it) clearly.

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

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

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

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

Payments are made in advance. Balloon is made on last day of month 60.

Solve for y-----------------------

the answer is y = $524 - just not sure how to get there

I am not sure how to answer these for a number of reasons.

First, excel is not math. I can share my work in excel. I can review the work of others in excel. But I need to use attachments to email to do that efficiently. I am not going to try to do that here.

Second, we have vocabulary issues. I am quite familiar with US banking: I was an officer of a bank for 32 years and have been on that bank's board for another 9 years. So when I hear the word "spread," I think of a difference in percentages, not a number. I'd say that if you borrow at 5.3% and lend at 6.3%, that is a gross spread of 1%, not a number.

Third, we have quite a few conceptual issues. The most important is that, in both economic terms and under TIL regulations in the US, the idea of interest being due on the day the loan is made makes no sense. Interest is payment for the use of money over time. If you immediately make a payment out of the loan proceeds, the obvious way to address it is to consider that the loan is for less than nominal amount by the first payment. As a practical matter, very few banks make loans of that type. (My bank does not make them at all. They would be a disclosure nightmare.)

So consider a fully amortizing loan of 20419 with payments due by close of business of the first day of the month and 60 payments and an interest rate of 6.3% annually compounded monthly. That means that the loan is outstanding for 59 months, not 60 months. I'd check it as follows.

I'd do the entire accounting month by month.

It is accurate within 1 cent.

Now for the "spread." It is not clear to me what the system being checked means by that so I'll check it two ways. One, the logically correct way, is to determine what the cumulative interest expense is on the loan principal outstanding at 5.3%/12 monthly interest. That is the amount that the bank must borrow to fund the loan. I get 525.88. The alternative is to calculate the interest as though the bank borrowed with payments due on a fully amortizing basis over time, which of course is not how a bank borrows. That way I get 547.07. So I have no idea how they get 484. As for the OP's note, he may have figured out what the system to be checked is doing (although not exactly), but there is no rhyme or reason to that process. In particular, where did the 484 come from in the first place? That is what the OP's question about y is concerned with.

I'd not buy a system with formulas that are not explained and that therefore cannot be checked. Look at Wells, Fargo and the mess they are in with a system that miscalculated on foreclosure decisions.

To sum up, I can validate the monthly payments in the no-balloon scenario, but not the "spread."

Dealing with advance payments and a balloon is a little trickier. (Again, I am not familiar with anyone who structures a loan like that: just asking for trouble with the regulators.) To compute the payment may require numerical methods rather than a formula, but because the purpose is to check what a system is doing, there is no need to do the computation. With that payment, I get a required balloon payment of 1999.85, which is pretty close to 2000 (although you will need to check with a lawyer to determine whether that is within the safe-harbor provisions of Reg Z.)

So, to sum up: I can provide spreadsheet logic to confirm the payments provided by the black box. The spread number is wholly mysterious to me.

There's various ways of looking at this "spread".
An identical loan at rate of 5.3% requires a monthly pay't
of 386.44, compared to 393.53 at 6.3% rate:
(393.53 - 386.44) * 60 = 540 (56 more than your 484).

Then there's the "real-life problem" of explaining all this
to the Credit Committee!
Best way as far as I experienced is along lines of:
on average, half the loan is owing for 5 years;
20420/2 = 10210
10210 @ 6.3 for 5 years = 3216
10210 @ 5.3 fro 5 years = 2706
Spread = 3216 - 2706 = 510

Many ways to look at the 1% spread;
like use term deposits earning 5.3%.
But I'm not here as a consultant!

EDIT: didn't see your reply Jeff; glad to see yer as exasperated as I am

Second, we have vocabulary issues. I am quite familiar with US banking: I was an officer of a bank for 32 years and have been on that bank's board for another 9 years. So when I hear the word "spread," I think of a difference in percentages, not a number. I'd say that if you borrow at 5.3% and lend at 6.3%, that is a gross spread of 1%, not a number.

- Yes you are correct. Spread is used to refer to a percentage in FX and other instruments. As most on here are unlikely to be in banking, I was trying to say "express the 1% spread in dollar terms". That being said, banks, or any company only express their profit in dollar terms, not percentages, so at some point, there needs to be an understanding of what the 1% is in real dollars.

Third, we have quite a few conceptual issues. The most important is that, in both economic terms and under TIL regulations in the US, the idea of interest being due on the day the loan is made makes no sense. Interest is payment for the use of money over time. If you immediately make a payment out of the loan proceeds, the obvious way to address it is to consider that the loan is for less than nominal amount by the first payment. As a practical matter, very few banks make loans of that type. (My bank does not make them at all. They would be a disclosure nightmare.)

- I am in Australia. A customer can choose to make loan payments in advance or in arrears. If they choose to make them in advance, the first payment comes out on day 1 of the loan. All Australian banks make loans of this type.

I think you need to know the real world application to this because it seems to matter.

- The 5.3% is the rate a loan broker can obtain funds at from a bank - call it cost of funds.
- Brokers earn a commission.
- The commission is not derived from the 5.3% cost of funds - it is added into the loan.
- The bank allows them to add 1%.
- In Australia, the bank does not present this as $20,419 + $484 @ 5.3%, rather they present it as $20,419 @ 6.3%.
- This is for loans that are B2B in nature.

So consider a fully amortizing loan of 20419 with payments due by close of business of the first day of the month and 60 payments and an interest rate of 6.3% annually compounded monthly. That means that the loan is outstanding for 59 months, not 60 months. I'd check it as follows.

Now for the "spread." It is not clear to me what the system being checked means by that so I'll check it two ways. One, the logically correct way, is to determine what the cumulative interest expense is on the loan principal outstanding at 5.3%/12 monthly interest. That is the amount that the bank must borrow to fund the loan. I get 525.88. The alternative is to calculate the interest as though the bank borrowed with payments due on a fully amortizing basis over time, which of course is not how a bank borrows. That way I get 547.07. So I have no idea how they get 484. As for the OP's note, he may have figured out what the system to be checked is doing (although not exactly), but there is no rhyme or reason to that process. In particular, where did the 484 come from in the first place? That is what the OP's question about y is concerned with.
- It's simply different in Australia. On these types of loans, the commission is added to the loan and presented to the customer in terms of an increased rate.

I'd not buy a system with formulas that are not explained and that therefore cannot be checked. Look at Wells, Fargo and the mess they are in with a system that miscalculated on foreclosure decisions.

To sum up, I can validate the monthly payments in the no-balloon scenario, but not the "spread."

Dealing with advance payments and a balloon is a little trickier. (Again, I am not familiar with anyone who structures a loan like that: just asking for trouble with the regulators.) To compute the payment may require numerical methods rather than a formula, but because the purpose is to check what a system is doing, there is no need to do the computation. With that payment, I get a required balloon payment of 1999.85, which is pretty close to 2000 (although you will need to check with a lawyer to determine whether that is within the safe-harbor provisions of Reg Z.)

- Lawyers and regulations are not necessary for this application. We just want to provide our loan officers with a way of checking the commission on a deal to make sure the deal is profitable as well as for rough forecasting. If the numbers are out by a few cents because of compound rounding, it doesn't matter as the variance will be less than 1% which is fine for our purpose. We are not selling this software.

So, to sum up: I can provide spreadsheet logic to confirm the payments provided by the black box. The spread number is wholly mysterious to me.

Thanks for the response. I've responded above to some of your comments

I'm going to simplify the question further. All I need is a maths formula to replicate this excel formula = -PV(0.053/12,60,395.53,0,1)

First, excel is not math. I can share my work in excel. I can review the work of others in excel. But I need to use attachments to email to do that efficiently. I am not going to try to do that here.

Second, we have vocabulary issues. I am quite familiar with US banking: I was an officer of a bank for 32 years and have been on that bank's board for another 9 years. So when I hear the word "spread," I think of a difference in percentages, not a number. I'd say that if you borrow at 5.3% and lend at 6.3%, that is a gross spread of 1%, not a number.

Third, we have quite a few conceptual issues. The most important is that, in both economic terms and under TIL regulations in the US, the idea of interest being due on the day the loan is made makes no sense. Interest is payment for the use of money over time. If you immediately make a payment out of the loan proceeds, the obvious way to address it is to consider that the loan is for less than nominal amount by the first payment. As a practical matter, very few banks make loans of that type. (My bank does not make them at all. They would be a disclosure nightmare.)

So consider a fully amortizing loan of 20419 with payments due by close of business of the first day of the month and 60 payments and an interest rate of 6.3% annually compounded monthly. That means that the loan is outstanding for 59 months, not 60 months. I'd check it as follows.

I'd do the entire accounting month by month.

It is accurate within 1 cent.

Now for the "spread." It is not clear to me what the system being checked means by that so I'll check it two ways. One, the logically correct way, is to determine what the cumulative interest expense is on the loan principal outstanding at 5.3%/12 monthly interest. That is the amount that the bank must borrow to fund the loan. I get 525.88. The alternative is to calculate the interest as though the bank borrowed with payments due on a fully amortizing basis over time, which of course is not how a bank borrows. That way I get 547.07. So I have no idea how they get 484. As for the OP's note, he may have figured out what the system to be checked is doing (although not exactly), but there is no rhyme or reason to that process. In particular, where did the 484 come from in the first place? That is what the OP's question about y is concerned with.

I'd not buy a system with formulas that are not explained and that therefore cannot be checked. Look at Wells, Fargo and the mess they are in with a system that miscalculated on foreclosure decisions.

To sum up, I can validate the monthly payments in the no-balloon scenario, but not the "spread."

Dealing with advance payments and a balloon is a little trickier. (Again, I am not familiar with anyone who structures a loan like that: just asking for trouble with the regulators.) To compute the payment may require numerical methods rather than a formula, but because the purpose is to check what a system is doing, there is no need to do the computation. With that payment, I get a required balloon payment of 1999.85, which is pretty close to 2000 (although you will need to check with a lawyer to determine whether that is within the safe-harbor provisions of Reg Z.)

So, to sum up: I can provide spreadsheet logic to confirm the payments provided by the black box. The spread number is wholly mysterious to me.

Thanks for sharing those info Sir Jeff.
I wish this forum has some kind of "Thank You" tab that allows members to express their appreciation for posts that they like as with other forums.

A man can drink a cask of wine in 20 days, but if his wife drinks with him it will take only 14 days—how long would it take for the wife alone?

If one priest can pray a soul out of purgatory in 5 hours, while it takes a second priest 8 hours, how long will it take if the two priests prayed together?