Hi Vicky!
No formulas are given in help but we can work the process out
reasonably easily.
PPMT:
This is the difference in balance of loan between the nth period and
the (n-1)period.
We find these balances by accumulating the repayments and the loan for
those two periods
A1: Loan
A2: Interest
A3: Term
A4: Period
B1: 100000
B2: 6%
B3: 10
B4: 1
E4:
=-(FV($B$2,B4,PMT($B$2,$B$3,$B$1,0,0),$B$1,0)-FV($B$2,B4-1,PMT($B$2,$B
$3,$B$1,0,0),$B$1,0))
IPMT
The part of the payment that isn't principal repayment is loan
repayment.
Thus we can deduct PPMT calculated above from the repayment to get
IPMT.
D4:
=PMT($B$2,$B$3,$B$1,0,0)-E4
Or combined into one formula:
=PMT($B$2,$B$3,$B$1,0,0)--(FV($B$2,B4,PMT($B$2,$B$3,$B$1,0,0),$B$1,0)-
FV($B$2,B4-1,PMT($B$2,$B$3,$B$1,0,0),$B$1,0))
You can change that -- to a plus if you like.
I've got a sheet that uses these two formulas and checks them out
against IPMT and PPMT and will send if you email the address below.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
