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

1. A balloon payment loan USUALLY works like this:
(example: $5000.00 loan, 1% per month, 6 months, balloon payt =$2000)
Code:
 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.

2. Originally Posted by JeffM
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.

3. Originally Posted by 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.
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

4. Originally Posted by math-genius-not
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.

Originally Posted by math-genius-not
Hi JeffM,

Thanks for the offer to let me send you the spreadsheet from this thread https://www.freemathhelp.com/forum/t...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)

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)

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. 5. 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 6. Originally Posted by JeffM 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:

PMT Workings.jpg

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)

7. Originally Posted by math-genius-not
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

8. Originally Posted by Denis
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

9. 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...

10. Beer soaked gratitude follows.
Originally Posted by JeffM
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.

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•