Financial › IPMT, PPMT123 / 144

Progress is saved on this device. Sign in or sign up free to keep it in your account.

MediumFreeIPMTPPMT

IPMT & PPMT: interest and principal in each payment

Yuni runs a food stall in Purwokerto and borrowed Rp50,000,000 in working capital from a bank at 12% a year for 12 months (annuity). She wonders why the debt seems to shrink so slowly in the first months.

Task

For the months in A6:A11, fill in:

B6:B11: the interest portion with IPMT.
C6:C11: the principal portion with PPMT.

Use the monthly rate from B1, the term in B2 and the loan in B3. Show them as positive numbers. Column D already holds the total payment (B + C).

Cells to fillB6:B11C6:C11

Required functionsIPMTPPMT

Expected result

Loading the expected result…

Check answerCtrlShiftEnter
Fill downCtrlD
Previous / next exerciseAlt←→
Leave the workspaceEscTab

Hint 1
Both share the same shape: IPMT(rate, per, nper, pv) and PPMT(rate, per, nper, pv). The per argument is which month.
Hint 2
The monthly rate is $B$1/12. The month comes from column A (relative), and the loan data is locked with $.
Hint 3
Pattern for B6: =IPMT($B$1/12, A6, $B$2, -…). Swap IPMT for PPMT in column C.

Reveal them one at a time. The last hint nearly gives the answer away.

Give it a try first. The solution unlocks automatically after your first check.

Answer

B6:B11

=IPMT($B$1/12,A6,$B$2,-$B$3)

Write it in B6, then fill down to B11.

C6:C11

=PPMT($B$1/12,A6,$B$2,-$B$3)

Write it in C6, then fill down to C11.

Explanation

=IPMT($B$1/12,A6,$B$2,-$B$3) calculates that month's interest on the remaining balance: month 1 is Rp500,000 (1% × 50 million). =PPMT($B$1/12,A6,$B$2,-$B$3) is the rest of the fixed payment of about Rp4,442,439, which goes to the principal. Each month the interest share falls and the principal share rises, while the total in column D stays the same. That is why paying extra early in the loan saves the most.

Starting worksheet: Sheet1
Row	A	B	C	D
1	Annual interest	12%
2	Term (months)	12
3	Loan	50,000,000
4
5	Month	Interest	Principal	Total payment
6	1
7	2
8	3
9	4
10	5
11	6
12
13
14

Fill in the requested cells, then press Check answer.

Validation result