Why excel PMT functions's outcome is different from my calculation

[Paul21]

Hello all,

Below PMT function is giving me 879,16 for 10%, 12 monthes and 10 000.
=PMT(D2/12,12,D1) with D2=10%,


Can you please explain howdoes excel comes with this 879,16 outcome?
In my mind I do the same calculation like: 10% of 10 000 is 1000. A total amount is 10 000 + 1000 = 11 000.
I divide total amount by 12 in order to know how much I will pay per month = 11 000/12= 916,66.
So my personal PMT is 916,66

Where am I wrong in my interpretaion ?

Thanks

 

[MickG]

Re: Please help understand why excel PMT functions's outcome is different from my calculation

Each monthly repayment consists of an interest payment and a part repayment of the capital.
Because the capital (total Loan) is reducing each month the amount of interest is reducing, and the repayment part of the capital gets bigger.
The total repayments stays the same but the individual parts fluctuate.
So you can't calculate the interest based on the total loan amount .

 

[Dryver14]

Re: Please help understand why excel PMT functions's outcome is different from my calculation

Unless borrowing from bank of mum and dad

 

[MickG]

Re: Please help understand why excel PMT functions's outcome is different from my calculation

Example:-

Loan :-	10000
Mthly Int rate	10%/12
Period :	12 Moths
End of Months	1	2	3	4	5	6	7	8	9	10	11	12
Reducing Loan	9204.1733	8401.715	7592.569	6776.68	5953.993	5124.449	4287.993	3444.566	2594.111	1736.569	871.8802	-0.01417
Interest Paid / Mth	83.333333	76.70144	70.01429	63.27141	56.47234	49.61661	42.70375	35.73328	28.70472	21.61759	14.47141	7.265668
Cap Paid Per/Mth	795.82667	802.4586	809.1457	815.8886	822.6877	829.5434	836.4563	843.4267	850.4553	857.5424	864.6886	871.8943

<colgroup><col width="114" style="width: 85pt; mso-width-source: userset; mso-width-alt: 4039;"> <col width="72" style="width: 54pt; mso-width-source: userset; mso-width-alt: 2560;"> <col width="64" style="width: 48pt;" span="11"> <tbody> </tbody>

 

[joeu2004]

Re: Please help understand why excel PMT functions's outcome is different from my calculation

Below PMT function is giving me 879,16 for 10%, 12 monthes and 10 000.
=PMT(D2/12,12,D1) with D2=10%
Can you please explain howdoes excel comes with this 879,16 outcome?

In my mind I do the same calculation like: 10% of 10 000 is 1000. A total amount is 10 000 + 1000 = 11 000.
I divide total amount by 12 in order to know how much I will pay per month = 11 000/12= 916,66.
So my personal PMT is 916,66

There are indeed types of loans where the interest and periodic payment are calculated the simple way that you did "in your mind", especially loans of 12 months or less. There are other methods, as well.

So you need to go back and understand the terms of the loan.

The PMT function and most other Excel financial functions assume loans (in this case) that are amortized by the actuarial method. That is, with each payment, some portion applies to periodic interest based on the outstanding balance, and some portion reduces the outstanding balance for the next periodic calculation.

This is demonstrated by the following table and formulas.

	A	B	C	D	E
1	Loan	10,000.00
2	Annl rate	10.00%
3	Term	12	months
4	Pmt	879.16			B4: =PMT(B2/12,B3,-B1)
5
6	Pmt#​	Prin​	Int​	Bal​
7				10,000.00
8	1	795.83	83.33	9,204.17	B8: =$B$4-C8 C8: =D7*$B$2/12 D8: =D7-B8
9	2	802.46	76.70	8,401.72
10	3	809.14	70.01	7,592.57
11	4	815.89	63.27	6,776.69
12	5	822.69	56.47	5,954.00
13	6	829.54	49.62	5,124.46
14	7	836.46	42.70	4,288.00
15	8	843.43	35.73	3,444.58
16	9	850.45	28.70	2,594.12
17	10	857.54	21.62	1,736.58
18	11	864.69	14.47	871.89
19	12	871.89	7.27	0.00

<tbody>
</tbody>

Note that the values in the table appear to be rounded, but the actual values are not. In real life, the payment (B4) must be rounded, and the periodic interest might be rounded, or it might be rounded differently, depending on the lender's procedures. Rounding might cause changes in the last period: the payment might be different; the number of payments might be different; and/or the balance might not be zero.

 

[Paul21]

Re: Please help understand why excel PMT functions's outcome is different from my calculation

Joeu2004, appeciate your explanation.
The sum of Interests - column c gives us 549,89. Why does this amount is not corresponding 10% of 10K ?
Thank you all for your answers.
****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">

549,89 549,89 <tbody> </tbody>

<tbody>
</tbody>

549,89

<tbody>
</tbody>

</body>

 

[joeu2004]

Re: Please help understand why excel PMT functions's outcome is different from my calculation

The sum of Interests - column c gives us 549,89. Why does this amount is not corresponding 10% of 10K ?

Each month, we are applying the monthly interest rate 10%/12 to a smaller and smaller outstanding balance because part of the payment pays off some principal.

Consider an interest-only payment: replace the PMT formula in B4 with =B1*B2/12.

Then you will see that =SUM(C8:C19) is indeed 1000, 10% of 10,000.