financial rate, PTM, PPMT

[Deitimami]

Hey, I need help!!!!

I don't think I am on the right subject, but I hope someone can help me.
I am from România, my English is not well...I hope someone understand what I want. ..


I need someone to tell me how is calculated the monthly total payments ( is 2 different values).

The fact is :
1. The credit : 80000
2. Period 5 years, 60 months
3. The monthly rate is 5% , the annual rate is 5X12= 60 %
4. With the condition to pay the monthly paymant on the contractual date , the bank give one discount of 1% monthly to the monthly 5%. ;ut if the monthly payment is not made in time, the monthly rate stay 5%.
5. Only with this condition the monthly rate is 4% , the annual rate is 48%.
So, I have to calculate the interest at the monthly 5% , annual 60% ? I used the PPMT function for this, I use the PTM function for the monthly basic payment, but to calculate the monthly payment with discount haw I can do that?

Thank you!!!!

[mikeTRON]

Attached should solve what you are looking for (also you dont need the annual interest rate, you need the PERIOD interest rate [which is 5% or 4%]).

Interest Calcs.xlsx

[joeu2004]

I am from Romania, my English is not well.

No apology needed. Your English is better than my Romanian, and it is better than a lot of other English-speakers in this forum.

Nevertheless, I wonder if there is a subtle difference between what you ask for and what you truly need.

I need someone to tell me how is calculated the monthly total payments ( is 2 different values).

The fact is :
1. The credit : 80000
2. Period 5 years, 60 months
3. The monthly rate is 5% , the annual rate is 5X12= 60 %
4. With the condition to pay the monthly paymant on the contractual date , the bank give one discount of 1% monthly to the monthly 5%. ;ut if the monthly payment is not made in time, the monthly rate stay 5%.
5. Only with this condition the monthly rate is 4% , the annual rate is 48%.
So, I have to calculate the interest at the monthly 5% , annual 60% ? I used the PPMT function for this, I use the PTM function for the monthly basic payment, but to calculate the monthly payment with discount haw I can do that?

With those conditions, the bank cannot know whether to charge 5% instead of 4% periodic interest until after you are late with a payment. And a different decision might be made each month.

So I wonder if there is only one payment amount based on 5% for 60 months, namely =ROUND(PMT(5%,60,-80000),2), which is about 4226.25.

But if you always pay 4226.25 on time, a larger part is applied toward principal because of the lower 4% interest rate.

So you would pay off the loan in about 36 months, namely =ROUND(NPER(4%,4226.25,-80000),0).

And your last payment would be different, namely =ROUND(FV(4%,36-1,4226.25,-80000)*(1+4%),2), which is about 4590.98.

Alternatively, we can write =ROUND(FV(4%,1,0,-FV(4%,36-1,4226.25,-80000)),2).

You might want to get clarification on those points from the bank or your instructor.

If my understanding is correct, we cannot use cumulative functions like PPMT because they make different assumptions about the relationship between the periodic payments, periodic interest rate, and number of payments.

Instead:

1. If all payments are on time (at 4% interest):
a. Total payments are 4226.25*(36-1) + 4590.98 = 152,509.73
b. Total interest is 152,509.73 - 80,000.00 = 72,509.73

2. If all payments are late (at 5% interest):
a. Total payments are 4226.25*60 = 253,575.00
b. Total interest is 253,575.00 - 80,000.00 = 173,575.00

Of course, you would use cell references instead of constants in your formulas.

-----

PS.... Be mindful of possible regional differences when writing formulas. You might use semicolon (";") instead of comma (",") to separate parameters. And you might reverse my use of comma (",") and period (".") in numbers, namely: period for 1000s separator, and comma for radix separator (that is, to separate integer and decimal fraction parts).

[Deitimami]

[QUOTE=mikeTRON;4024844]Attached should solve what you are looking for (also you dont need the annual interest rate, you need the PERIOD interest rate [which is 5% or 4%]).


I want to thank you very much!

The situation is not so easy, we are in juridical dispute with the bank ( non bank institution). The reason of dispute is the rate.
I ll try to attach here picture, I'm sure if you see the original graphics for payment you'll figure out what they did.

I think the original grafic is calculated with montly 4% + 1% for delay.
if I can proof that I will win the dispute.
20150312_072153.jpg


Thank you!!!!

[Deitimami]

Thank you very much!
I post one picture with the original graphic, is in romanian, , but the number is universally, thank God.

Thank you!!!

[joeu2004]

Attachment 384962

See the attached file (click on the link below).

Your graphic image, an amortization schedule, shows that the regular on-time payment is calculated as:

=PMT(4%,60,-80000)

which is about 3536.15. The amortization schedule does not seem to round the regular payment.

For any payment, the part allocated to principal is (see column 3, Rata principal):

=3536.15 - balance*4%

Thus, the loan is always amortized (reduced) over the original contractual period, namely 60 months.

The part allocated to interest depends on whether or not the payment is on time:

=balance*4% (on time payment; column 4, Dobanda renumeratorie)
or
=balance*5% (late payment; column 6, Dob renum + dob penalizatorie)

The total payment due is the part allocated to principal plus the part allocated to interest.

Consequently, if the 1% penalty applies, the penalty payment due is more than the regular payment (column 7, Rata cu dob penal).

In summary....

1. If all payments are on time (at 4% interest):
a. Total payments are 60*PMT(4%,60,-80000) = 212,168.86
b. Total interest is 212,168.86 - 80,000.00 = 132,168.86

2. If all payments are late (at 5% interest):
a. Total payments are 245,211.07
b. Total interest is total payments - 80,000.00 = 165,211.07

At the moment, I don't know how to calculate #2a, other than by summing the column I call "Total +Penalty Pmt" in the attached file.

[joeu2004]

1. If all payments are on time (at 4% interest):
a. Total payments are 60*PMT(4%,60,-80000) = 212,168.86
b. Total interest is 212,168.86 - 80,000.00 = 132,168.86

2. If all payments are late (at 5% interest):
a. Total payments are 245,211.07
b. Total interest is total payments - 80,000.00 = 165,211.07

At the moment, I don't know how to calculate #2a, other than by summing the column I call "Total +Penalty Pmt" in the attached file.

See the attached file (click the link below).

I inserted a column (Penalty Pmt) to make it clearer: the difference between #1 and #2 above is just the additional penalty, 1% of the balance before each payment.

So #2 is:
a. Total payments 60*PMT(4%,60,-80000) + total penalty payments = 245,211.07
b. Total interest is total payments - 80,000.00 = 165,211.07

From the amortization schedule, total penalty payments are about 33,042.21. We just need a formula to calculate that. (To be determined.)

[Deitimami]

Good morning!!!!! Here is morning,

I want to thank you for your help.

I attached here another picture, I think this reflect the facts, I applied the 5% monthly, the monthly payment is 4226,25 , that is mean the loan is 4000 , the principal in 226, 25.
So, the principal stay 226, 25 and for the payment with discount I have to calculate the loan at 4% and sume with 226, 25., the payment with discount is 3446, 25.
this operation I have to

I think only like this is reflect the discount, only in this way of calculation is respected the facts.

Am I right?
20150312_072219.jpg

[joeu2004]

I attached here another picture, I think this reflect the facts, I applied the 5% monthly, the monthly payment is 4226,25 , that is mean the loan is 4000 , the principal in 226, 25. So, the principal stay 226, 25 and for the payment with discount I have to calculate the loan at 4% and sume with 226, 25., the payment with discount is 3446, 25.
[....]
Am I right?

Not exactly. I would not say we "calculate the loan at 4%" to determine the discount payment. Instead, I would we "reduce the payment by 1% of the previous balance". It is a small difference in wording; but it can have a big impact on understanding the loan calculations. See below.

I think only like this is reflect the discount, only in this way of calculation is respected the facts.

There are two ways to structure the loan:
a. A 4% loan with a 1% late penalty.
b. A 5% loan with a 1% on-time discount.

The first JPG attachment uses structure #a. The second JPG attachment uses structure #b.

I cannot say which way is "correct". By "correct", I mean: the way the bank actually structured the loan. However, the first JPG says "chart titles issued by the creditor".

So my guess is: the first JPG and #a above are the intended interpretation, regardless of how the loan is described. In US law, we call that "substance before form".

In any case, the following table describes how to calculate the payment for both loan structures for payment #2. I use payment #2 to illustrate the difference between "recalculate the loan at 4%" and "reduce the payment by 1% of the previous balance".

	A	B	C
1	Loan	80,000.00
2	Months	60
3	Monthly interest rate	4.00%	5.00%
4	Regular payment	3,536.15	4,226.25
5	Penalty	1.00%
6	Discount		1.00%
7	Enter payment number	2
8	....Principal	349.59	237.57
9	....Interest	3,186.55	3,988.69
10	....Penalty	796.64
11	....Discount		-797.74
12	....Total payment	4,332.79	3,428.52

Please Login or Register  to view this content.

The point is: the regular loan calculations (principal and interest paid) are based on the contractual interest rate, and the loan balance is reduced by the principal paid.

The total payment is increased by the penalty or decreased by the discount, depending on interpretation, which is 1% of the previous balance.

Again: "penalty" v. "discount" is a matter of interpretation.

[joeu2004]

I think my previous response (#9) has the information you need. (Note that I corrected the formulas in B12 and C12). The following is provided just for completeness.

From the amortization schedule, total penalty payments are about 33,042.21. We just need a formula to calculate that. (To be determined.)

Attachment 385176

The total penalty is:

=B5 * SUMPRODUCT(FV(B2, ROW(INDIRECT("1:" & B3))-1, B4, -B1))

If we change B2 to 5% and we interpret B5 as a discount rate instead of a penalty rate, the same formula calculates the total discount.

See my previous response (#9) for details.

[Deitimami]

Thank you for your explanations.

În Romanian low we have the definition for the interes rate and for penalty rate.
" interest owed by the debtor to give some money to a certain date , calculated for the period before expiration maturity of the obligation is called remunerative interest"

"Interest payable by the debtor money for those failing to maturity is called penalty interest"

Also, în Romanian low we have the definition of the discount, is case is one financial cuts : " financial cuts in forme of discounts granted for payment of the debt settlement BEFORE the normal term of chargeability"

The bank say all the 5% is remunerative interest, but with the definition of the financial discount, the fact that the bank give the "discount " not for paymant BEFORE the normal term, exactly for the payment in the normal term and how the low sad , all payments after normal term is penalty rate and with the fact that the bank grafic is calculated 4% + 1%,
I think I have all the reasons to sad the grafic is the proof that the bank is trying to hide one penalty behind one discount.
if you take in order all the legal definitions, the graphic is calculated by 4+ 1, not 5-1, ...and if you understand my Google translate definitions, what is your opinion? That 1% is discount or penalty?

...is hard, in romanian I know to explain better.

I want to thank you to all!!!

[Deitimami]

Thank you for your explanations.

În Romanian low we have the definition for the interes rate and for penalty rate.
" interest owed by the debtor to give some money to a certain date , calculated for the period before expiration maturity of the obligation is called remunerative interest"

"Interest payable by the debtor money for those failing to maturity is called penalty interest"

Also, în Romanian low we have the definition of the discount, is case is one financial cuts : " financial cuts in forme of discounts granted for payment of the debt settlement BEFORE the normal term of chargeability"

The bank say all the 5% is remunerative interest, but with the definition of the financial discount, the fact that the bank give the "discount " not for paymant BEFORE the normal term, exactly for the payment in the normal term and how the low sad , all payments after normal term is penalty rate and with the fact that the bank grafic is calculated 4% + 1%,
I think I have all the reasons to sad the grafic is the proof that the bank is trying to hide one penalty behind one discount.
if you take in order all the legal definitions, the graphic is calculated by 4+ 1, not 5-1, ...and if you understand my Google translate definitions, what is your opinion? That 1% is discount or penalty?

...is hard, in romanian I know to explain better.

I want to thank you to all!!!

[joeu2004]

if you take in order all the legal definitions, the graphic is calculated by 4+ 1, not 5-1, [...] what is your opinion? That 1% is discount or penalty?

That is a legal question, not an Excel question. And my opinion is not worth the paper I print it on. I do not know Romanian law. I am not even a US lawyer. But I do have some knowledge of US contract law, for whatever that is worth. (Nothing!) What follows is "food for thought".

Refer to the "amort sched" worksheet (amortization schedules) in the attached file. Click on the link below.

First, we must establish the actual facts about how the contract has been executed by both parties. This is based on actual performance, not amortization schedules.

a. If your on-time payments are a fixed amount, and your late payments are a variable amount, and your balance has been declining according to a schedule based on 4% interest (column D), then the bank has been charging or would charge a late payment penalty.

b. If your on-time payments are a variable amount, and your late payments are a fixed amount, and your balance has been declining according to a schedule based on 5% interest (column H), then the bank has been providing or would provide an on-time payment discount.

c. If all of your payments (on-time and late) are a fixed amount based on 5% interest, and with on-time payments, your balance declines faster because a smaller part of the payment applies toward interest at 4% (column L), then the bank has been providing or would provide an on-time payment discount .

#C is the discount model that I guessed in my first response (#3). However, that is different from the (bank's?) amortization schedule in your first JPG attachment. So it seems unlikely that #c applies.

You must look at periodic bank statements, not an amortization schedule, to determine how your balance has been declining over time. Alternatively, ask the bank for the current balance.

The important point is: an amortization schedule establishes intent; it does not establish actual fact. For example, you might make payments that are larger than necessary. The excess reduces the balance faster.

-----

It sounds like you want the discount model (#b) to apply instead of the penalty model (#a).

Be careful what you wish for.

It is to your advantage if the bank executed a penalty model (#a above) like the amortization schedule in columns B:D instead of a discount model (#b above) like the amortization schedule in columns F:H.

Note that with the penalty model, the balance at any time is less than the balance of the discount model at the same time. Moreover, the total interest payments are less with the penalty model than with the discount model.

On the other hand, a discount model like #c above (columns J:L) is to your advantage if all payments are on-time. The balance at any time is less; the loan is paid off sooner; and the total interest payments are less. However, it seems unlikely that #c applies, based on the JPG attachments that you provided.

[Deitimami]

Dear Joeu2004,

I want to thank you.
I told you, we are in juridical dispute with the bank. Your informations help us verry much. The contract with the bank have one another penalty , this one is hidden behind of that "discount".

Thank You again , Romanian law allows us to prove in any way that the bank original calculating mode is not with discount, this fact can make the cancellation of the contract.

I want you to know that your help was more then excel help, the future of my family is depending on this juridical dispute. I want you to know that here, YOU, helped me to understand the way of calculation. It was all i needed.

I wish you all the best!!!