I have been given a spreadsheet with figures for a period of one year divided
on a quarterly basis i.e. Q1 is 3 months.



I have been given the compound interest rate for a quarter and need to
establish the annual interest rate. The compound interest rate has been
given as 6.49%, the annual interest rate has been given as 6.65%



I can calculate compound to annual by using the expression
€¦..=EFFECT(0.0649,4)*100 which gives an answer of 6.65 but I need an
expression to do this calculation in reverse.



I would greatly appreciate your help as I am tearing my hair out !!!




"Gary L Brown" wrote:

'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