ppmt function

[crosby22]

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

 

[AhoyNC]

Take a look at the CUMPRINC function:

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

 

[crosby22]

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>

Spreadsheet Formulas

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

 

[crosby22]

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.

 

[joeu2004]

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

 

[joeu2004]

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.

 

[crosby22]

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