Calculate Principal of Loan from Payment, Rate, Balloon & Term

[math-genius-not]

I have the following details:

Q1

a) Calculate the original loan amount using the following details:

Using the PV function in excel, I get the correct answer ($20,419.00) - how is this expressed long hand?

P	=	Loan Amount (Principal)	?
r	=	Annual Interest Rate	6.3%
n	=	No. of periods per year	12
t	=	Loan Term (years)	5
PMT	=	Monthy Loan Payment	$395.53

b) A bank can access funds at 5.3%, but can loan funds at 6.3%. Calculate the spread in dollars using the figures in a)

the answer is $484.00

Working backwards in Excel, PV = $20,899.43 using -PV(0.053/12,60,395.53,0,1) then $20,899.43 - $20,419 = $480.43

It's as close as I can get with Excel

Loan Amount = $20,419
PMT = $395.53
Bank Rate = 5.3%
Loan Rate = 6.3%
Term = 60 months
Payments are in advance
Spread ($) = $484.00

How would I express this in an equation?

Q2 - Do the same as in Q1 a) and b), however this time there is a balloon added in.

P	=	Loan Amount (Principal)	?
r	=	Annual Interest Rate	6.3%
n	=	No. of periods per year	12
t	=	Loan Term (years)	5
PMT	=	Monthy Loan Payment	$367.24
FV	=	Balloon Payment	$2,000

Hope you guys can help. Thanks in advance

 

[tkhunny]

I have the following details:

Q1

a) Calculate the original loan amount using the following details:

Using the PV function in excel, I get the correct answer ($20,419.00) - how is this expressed long hand?

P	=	Loan Amount (Principal)	?
r	=	Annual Interest Rate	6.3%
n	=	No. of periods per year	12
t	=	Loan Term (years)	5
PMT	=	Monthy Loan Payment	$395.53

b) A bank can access funds at 5.3%, but can loan funds at 6.3%. Calculate the spread in dollars using the figures in a)

the answer is $484.00

Working backwards in Excel, PV = $20,899.43 using -PV(0.053/12,60,395.53,0,1) then $20,899.43 - $20,419 = $480.43

It's as close as I can get with Excel

Loan Amount = $20,419
PMT = $395.53
Bank Rate = 5.3%
Loan Rate = 6.3%
Term = 60 months
Payments are in advance
Spread ($) = $484.00

How would I express this in an equation?

Q2 - Do the same as in Q1 a) and b), however this time there is a balloon added in.

P	=	Loan Amount (Principal)	?
r	=	Annual Interest Rate	6.3%
n	=	No. of periods per year	12
t	=	Loan Term (years)	5
PMT	=	Monthy Loan Payment	$367.24
FV	=	Balloon Payment	$2,000

Hope you guys can help. Thanks in advance

Can you calculate exponentials "long hand"? Maybe you can.

 

[math-genius-not]

Thanks for the unhelpful response.

I'm trying to create a formula for a website.

The formula needs to match some figures I've been given.

I don't study mathematics at school or university, so sarcastic "quoting" of my post is hardly necessary.

If someone could help point me in the right direction, that would be great.

 

[JeffM]

I have the following details:

Q1

a) Calculate the original loan amount using the following details:

Using the PV function in excel, I get the correct answer ($20,419.00) - how is this expressed long hand?

P	=	Loan Amount (Principal)	?
r	=	Annual Interest Rate	6.3%
n	=	No. of periods per year	12
t	=	Loan Term (years)	5
PMT	=	Monthy Loan Payment	$395.53

b) A bank can access funds at 5.3%, but can loan funds at 6.3%. Calculate the spread in dollars using the figures in a)

the answer is $484.00

Working backwards in Excel, PV = $20,899.43 using -PV(0.053/12,60,395.53,0,1) then $20,899.43 - $20,419 = $480.43

It's as close as I can get with Excel

Loan Amount = $20,419
PMT = $395.53
Bank Rate = 5.3%
Loan Rate = 6.3%
Term = 60 months
Payments are in advance
Spread ($) = $484.00

How would I express this in an equation?

Q2 - Do the same as in Q1 a) and b), however this time there is a balloon added in.

P	=	Loan Amount (Principal)	?
r	=	Annual Interest Rate	6.3%
n	=	No. of periods per year	12
t	=	Loan Term (years)	5
PMT	=	Monthy Loan Payment	$367.24
FV	=	Balloon Payment	$2,000

Hope you guys can help. Thanks in advance

With respect to b, why are you messing around with 395.53? That represents amortization and monthly interest at 6.3%/12 on a loan of 20419, but the bank is not paying interest at that rate. Under the premise of this problem, the bank must borrow 20,419 initially, not 20,899.43. I mean think about it: why does the bank have to borrow more than it is lending.

Implied by this problem are a number of quite abstruse issues that I have no time to deal with. For example, because the bank is making a cash gain each month in this scenario, it is in fact able to fund the loan balance in part out of equity after the initial period. Probably these complications have been ignored (unless a loan is threatening a bank's legal loan limit, the complications would be ignored in fact by any sanely managed bank).

 

[math-genius-not]

Thanks Jeff

These are the numbers that are presented and correct. Call it commission, call it whatever you like - the semantics aren't important.

The bank buys money at 5.3% and sells it at 6.3%. Logic says you would do an amortization on 20,419 @ 5.3%, then at 6.3% and simply multiply the difference by 60 periods to give you a spread of $545.81, but this is not the answer.

I've tried using a present value formula in Excel -PV(0.053/12,60,395.53,0,1), but I get $20,899.43 - I need be at $20,903.

Basically, $20,419 + $484 (spread) = $20,903

$20,903 @ 5.3% over 60 months = $395.60/mth

$20,419 @ 6.3% over 60 months = $395.53/mth

The rounding of 7c is not an issue at this stage.

The question is, if we are not given a value for the spread ($484), and just the rate increase of 1%, how do you back calculate what the spread would be?

 

[JeffM]

Thanks Jeff

These are the numbers that are presented and correct. Call it commission, call it whatever you like - the semantics aren't important.

The bank buys money at 5.3% and sells it at 6.3%. Logic says you would do an amortization on 20,419 @ 5.3%, then at 6.3% and simply multiply the difference by 60 periods to give you a spread of $545.81, but this is not the answer.

I've tried using a present value formula in Excel -PV(0.053/12,60,395.53,0,1), but I get $20,899.43 - I need be at $20,903.

Basically, $20,419 + $484 (spread) = $20,903

$20,903 @ 5.3% over 60 months = $395.60/mth

$20,419 @ 6.3% over 60 months = $395.53/mth

The rounding of 7c is not an issue at this stage.

The question is, if we are not given a value for the spread ($484), and just the rate increase of 1%, how do you back calculate what the spread would be?

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.

 

[tkhunny]

Thanks for the unhelpful response.

I'm trying to create a formula for a website.

The formula needs to match some figures I've been given.

I don't study mathematics at school or university, so sarcastic "quoting" of my post is hardly necessary.

If someone could help point me in the right direction, that would be great.

You should hire someone. There was no sarcasm. Just answer the question.

You said "long hand". This is typically defined to mean paper and pencil. Words mean things. I you wanted a formula, you should have said so.

 

[tkhunny]

Thanks Jeff

Call it commission, call it whatever you like - the semantics aren't important.

Logic says you would do an amortization on 20,419 @ 5.3%, then at 6.3% and simply multiply the difference by 60 periods to give you a spread of $545.81, but this is not the answer.

The rounding of 7c is not an issue at this stage.

This is all very wrong. It ALL matters at EVERY point. If you really don't care about the right answer, then maybe you are right - it doesn't matter and it's not important.

You can solve ALL these issues with a relatively simple time chart. Draw it out and lets see where it goes. Exactly what moment does what cash flow move in what direction. Not all such things have pretty, closed solutions.

 

[Denis]

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?

The standard formula to calculate a loan payment is:
p = ai / (1 - v^n) where v = 1 / (1 + i)^n

a = loan amount (20023.47)
i = interest factor (.063 / 12 = .00525)
n = number of payments (59)
p = payment amount (?)

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

 

[Denis]

Q2 - Do the same as in Q1 a) and b), however this time there is a balloon added in.

P	=	Loan Amount (Principal)	?
r	=	Annual Interest Rate	6.3%
n	=	No. of periods per year	12
t	=	Loan Term (years)	5
PMT	=	Monthy Loan Payment	$367.24
FV	=	Balloon Payment	$2,000

On this one, again the immediate payment is not specified.
PLUS the balloon payment is unclear:
is it made at same time as last monthly payment,
or is it made 1 month after the last monthly payment?

 

[Denis]

A balloon payment loan USUALLY works like this:
(example: $5000.00 loan, 1% per month, 6 months, balloon payt = $2000)

 N    PAYMENT  INTEREST  BALANCE
 0                       5000.00
 1    -537.64     50.00  4512.36
 2    -537.64     45.12  4019.84
 3    -537.64     40.20  3522.40
 4    -537.64     35.22  3019.98
 5    -537.64     30.20  2512.54
 6    -537.64     25.10  2000.00
 6   -2000.00       .00      .00

...which means that:
present value of the 6 regular payments + present value of $2000 = $5000.

 

[math-genius-not]

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.

I'll PM you.

 

[math-genius-not]

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?

The standard formula to calculate a loan payment is:
p = ai / (1 - v^n) where v = 1 / (1 + i)^n

a = loan amount (20023.47)
i = interest factor (.063 / 12 = .00525)
n = number of payments (59)
p = payment amount (?)

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

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

 

[JeffM]

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

I received the following private message.

Hi JeffM,

Thanks for the offer to let me send you the spreadsheet from this thread https://www.freemathhelp.com/forum/...Loan-from-Payment-Rate-Balloon-amp-Term/page2

Before I do that, let me see if I can reword the question as I think I have confused everyone on what it is I'm trying to find.

Q1)
Loan Amount = $20,419 + y
Annual Rate = 5.3%
Term = 60 months
PMT = $395.53 (payments made in advance)

Payments are made in advance.

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

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

Q2)
Loan Amount = $20,419 + y
Annual Rate = 5.3%
Term = 60 months
Balloon = $2,000
PMT = $367.24 (payments made in advance)

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.

 

[Denis]

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

 

[math-genius-not]

I received the following private message.

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.

I'd do the entire accounting month by month.

It is accurate within 1 cent.

Agreed and I'm not arguing this point. See working here:



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)

 

[Denis]

All I need is a maths formula to replicate this excel formula =
-PV(0.053/12,60,395.53,0,1)

PV = {p + p*[1 - 1/(1+i)^(n-1)]} / i

p = 395.53
i = .053/12
n = 60

That'll give you PV = 20899.4282...

Note: if 1st payment not immediate:
PV = {p*[1 - 1/(1+i)^n]} / i

 

[math-genius-not]

PV = {p + p*[1 - 1/(1+i)^(n-1)]} / i

p = 395.53
i = .053/12
n = 60

That'll give you PV = 20899.4282...

Note: if 1st payment not immediate:
PV = {p*[1 - 1/(1+i)^n]} / i

Thanks Denis :cool:

 

[Denis]

Welcome.
Problem I see with "margin calculation" is not using the pay'ts
received as being re-invested. But then stranger things happen
in Australia

Btw, if you wanna know the total interest paid on a loan:
p*n - PV (or total payments - amount borrowed).
But you probably knew that li'l short-cut...

 

[jonah2.0]

Beer soaked gratitude follows.

I received the following private message.


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.

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.