'Col- (A) (B)

'Row
'(1) Starting Principal: 160,000
'(2) Annual Interest Rate: 6.13%
'(3) Term (Yrs): 30
'(4) Pd thru (Mo.s): 36


'(5) Principal Pd thru : 6,128.62
'B5 array formula {=-SUM(PPMT($B$2/12,ROW(INDIRECT("1:"
&$B$4)),$B$3*12,$B$1,0))}

'(6) Interest Pd thru : 28,869.75
'B6 array formula {=-SUM(IPMT($B$2/12,ROW(INDIRECT("1:" &
$B$4)),$B$3*12,$B$1,0))}

' Array of 1 to 36 months of payments is created using the
' ROW() and INDIRECT() functions

' Using PPMT() and IPMT(), you can get the current Balance,
' Total Interest Paid and Total Principal paid

'*** to calculate Interest for a particular period of the loan
' ex: interest paid for year 2 - periods 13 thru 24
' {=-SUM(IPMT($B$2/12,ROW(INDIRECT("13:24")),$B$3*12,$B$1,0))}

HTH,
--
Gary Brown

If this post was helpful, please click the ''''Yes'''' button next to
''''Was this Post Helpfull to you?".


"dawn2511" wrote:

I am trying to calculate the annual interest when I know the compound
interest. I would like a formula I can use in excel