how do I calculate the annual interest when I know the compound

[dawn2511]

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

 

[Gary L Brown]

'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,

 

[dawn2511]

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]

Sorry, I misunderstood.
You want the Nominal function.

=EFFECT(0.0649,4) = .0665

=NOMINAL(0.0665,4) = .0649

HTH,

 

[dawn2511]

Thank you, you are a lifesaver, I was about to commit hari kari!!!!
LOL

Dawn