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:-
[TABLE="width: 667"]
<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>[TR]
[TD="class: xl63, width: 114, bgcolor: transparent"]Loan :-[/TD]
[TD="class: xl63, width: 72, bgcolor: transparent, align: right"]10000[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Mthly Int rate[/TD]
[TD="class: xl63, bgcolor: transparent"]10%/12[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Period :[/TD]
[TD="class: xl63, bgcolor: transparent"]12 Moths[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]End of Months[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]5[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]7[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]8[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]9[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]11[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]12[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Reducing Loan[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]9204.1733[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]8401.715[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]7592.569[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]6776.68[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]5953.993[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]5124.449[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]4287.993[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]3444.566[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]2594.111[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1736.569[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]871.8802[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]-0.01417[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Interest Paid / Mth[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]83.333333[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]76.70144[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]70.01429[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]63.27141[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]56.47234[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]49.61661[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]42.70375[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]35.73328[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]28.70472[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]21.61759[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]14.47141[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]7.265668[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Cap Paid Per/Mth[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]795.82667[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]802.4586[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]809.1457[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]815.8886[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]822.6877[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]829.5434[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]836.4563[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]843.4267[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]850.4553[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]857.5424[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]864.6886[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]871.8943[/TD]
[/TR]
</tbody>[/TABLE]

 

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

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Loan
[/TD]
[TD="align: right"]10,000.00
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Annl rate
[/TD]
[TD="align: right"]10.00%
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Term
[/TD]
[TD="align: right"]12
[/TD]
[TD]months
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Pmt
[/TD]
[TD="align: right"]879.16
[/TD]
[TD][/TD]
[TD][/TD]
[TD]B4: =PMT(B2/12,B3,-B1)
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]

Pmt#​

[/TD]
[TD]

Prin​

[/TD]
[TD]

Int​

[/TD]
[TD]

Bal​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10,000.00
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]795.83
[/TD]
[TD="align: right"]83.33
[/TD]
[TD="align: right"]9,204.17
[/TD]
[TD]B8: =$B$4-C8
C8: =D7*$B$2/12
D8: =D7-B8
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]802.46
[/TD]
[TD="align: right"]76.70
[/TD]
[TD="align: right"]8,401.72
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"]809.14
[/TD]
[TD="align: right"]70.01
[/TD]
[TD="align: right"]7,592.57
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]815.89
[/TD]
[TD="align: right"]63.27
[/TD]
[TD="align: right"]6,776.69
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD="align: right"]5
[/TD]
[TD="align: right"]822.69
[/TD]
[TD="align: right"]56.47
[/TD]
[TD="align: right"]5,954.00
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD="align: right"]6
[/TD]
[TD="align: right"]829.54
[/TD]
[TD="align: right"]49.62
[/TD]
[TD="align: right"]5,124.46
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14
[/TD]
[TD="align: right"]7
[/TD]
[TD="align: right"]836.46
[/TD]
[TD="align: right"]42.70
[/TD]
[TD="align: right"]4,288.00
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15
[/TD]
[TD="align: right"]8
[/TD]
[TD="align: right"]843.43
[/TD]
[TD="align: right"]35.73
[/TD]
[TD="align: right"]3,444.58
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16
[/TD]
[TD="align: right"]9
[/TD]
[TD="align: right"]850.45
[/TD]
[TD="align: right"]28.70
[/TD]
[TD="align: right"]2,594.12
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17
[/TD]
[TD="align: right"]10
[/TD]
[TD="align: right"]857.54
[/TD]
[TD="align: right"]21.62
[/TD]
[TD="align: right"]1,736.58
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18
[/TD]
[TD="align: right"]11
[/TD]
[TD="align: right"]864.69
[/TD]
[TD="align: right"]14.47
[/TD]
[TD="align: right"]871.89
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19
[/TD]
[TD="align: right"]12
[/TD]
[TD="align: right"]871.89
[/TD]
[TD="align: right"]7.27
[/TD]
[TD="align: right"]0.00
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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;">[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]549,89

[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]549,89[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]549,89[/TD]
[/TR]
</tbody>[/TABLE]
</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.