| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
how do I calculate the annual interest when I know the compound
I am trying to calculate the annual interest when I know the compound
interest. I would like a formula I can use in excel |
|
#2
|
|||
|
|||
|
'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 |
|
#3
|
|||
|
|||
|
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 |
|
#5
|
|||
|
|||
|
Thank you, you are a lifesaver, I was about to commit hari kari!!!!
LOL Dawn "Gary L Brown" wrote: Sorry, I misunderstood. You want the Nominal function. =EFFECT(0.0649,4) = .0665 =NOMINAL(0.0665,4) = .0649 HTH, -- Gary Brown If this post was helpful, please click the ''''Yes'''' button next to ''''Was this Post Helpfull to you?". "dawn2511" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Effective Annual Interest Rate | Excel Worksheet Functions | |||
| calculate interest between two dates | Excel Worksheet Functions | |||
| How can we use Excel to calculate interest with daily compounding | Excel Discussion (Misc queries) | |||
| APR - Annual Percentage Rate to Actual Interest Rate | Excel Worksheet Functions | |||
| How do you calculate interest expense on bonds? | Excel Discussion (Misc queries) | |||
Linear Mode
