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

D

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
 
G

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

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 !!!
 
G

Gary L Brown

Sorry, I misunderstood.
You want the Nominal function.

=EFFECT(0.0649,4) = .0665

=NOMINAL(0.0665,4) = .0649

HTH,
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top