# ppmt function

#### crosby22

##### New Member
Hi everyone,

Im having trouble coming up with a formula for principle portion of last loan payment.
I Already came up with the formulas for monthly, first payment, the total cost of the loan.

interest 7.75% d4

amortization period in years 18 d5
Principle amount to be borrowed \$1,750,000 d6

monthly loan payment \$15,048.32
Principle portion of the first loan payment \$3,746.24
Principle portion of the last loan payment \$?? here's what i came up with so far =PPMT(d4/12,1,d5*12,d6,0)

Total cost of loan \$3.250,437.08

### Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Take a look at the CUMPRINC function:
Excel Workbook
DEFG
3Principle
47.75%14,951.76Last Period
5183,746.24First Period
61750000
Sheet

Take a look at the CUMPRINC function:

 * D E F G 3 * * Principle * 4 7.75% * 14,951.76 Last Period 5 18 * 3,746.24 First Period 6 1750000 * * *

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:83px;"><col style="width:20px;"><col style="width:94px;"><col style="width:144px;"></colgroup><tbody>
</tbody>

 Cell Formula F4 =-CUMPRINC(D4/12,D5*12,1750000,216,216,0) F5 =-CUMPRINC(D4/12,D5*12,1750000,1,1,0)

<tbody>
</tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

where did the function cumprinc come from

how do you come with function in ppmt. i have an online hw and it wants specific formula in ppmt. i want to thank you for expertise and help.

[...deleted...submitted by mistake, incomplete...]

Im having trouble coming up with a formula for principle portion of last loan payment. I Already came up with the formulas for monthly, first payment, the total cost of the loan.

interest 7.75% d4
amortization period in years 18 d5
Principle amount to be borrowed \$1,750,000 d6

monthly loan payment \$15,048.32
Principle portion of the first loan payment \$3,746.24
Principle portion of the last loan payment \$?? here's what i came up with so far =PPMT(d4/12,1,d5*12,d6,0)

Total cost of loan \$3.250,437.08

You correctly wrote -PPMT(D4/12,1,D5*12,D6) for the first principal payment.

The last principal payment is -PPMT(D4/12,D5*12,D5*12,D6).

I understand that your homework assignment requires that you use PPMT. But that is not a good choice.

In the real world, payments must be rounded to the cent, at least. So the actual payment is =-ROUND(PMT(D4/12,D5*12,D6),2).

That looks the same when formatted with 2 decimal places, namely 15,048.32. But without rounding, -PMT(D4/12,D5*12,D6) actually returns about 15048.3198120013.

Consequently, -PPMT(D4/12,D5*12,D5*12,D6) returns about 14951.7563853459, which is displayed as -14951.76 when formatted with 2 decimal places.

But the actual last principal amount is the balance after the last-minus-one payment, namely:

=ROUND(FV(D4/12,D5*12-1,ROUND(PMT(D4/12,D5*12,D6),2),D6),2)

which returns -14951.67. (Dyslexia alert! :->)

Similarly, the total cost of the loan is:

=-ROUND(PMT(D4/12,D5*12,D6),2)*D5*12

which is 3,250,437.12, not 3,250,437.08 when formatted with 2 decimal places.

That said, you should give your teacher the answer that he/she expects.

Or be a mensh and provide both sets of answers with some explanation. It might earn you some extra points.

Last edited:
Thanks for giving me more of explanation than just helping me out with the formula.

Replies
4
Views
399
Replies
1
Views
1K
Replies
7
Views
2K
Replies
4
Views
790
Replies
0
Views
476

1,202,966
Messages
6,052,839
Members
444,603
Latest member
dustinjmangum

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back