reverse compound interest

[angstrom]

Hi, I need a formula to calculate a daily compound interest rate where I know the initial value and the present value. For example, if in a week an investment increased by 30% (let's say $100 becomes $130), how would I display the daily compound interest, ie by what percent did it increase each day over 7 compound periods?
I've tried using the NOMINAL function but can't get a sensible result.
Most grateful for any help!

 

[MickG]

Try:-
let "x" be the daily rate"
100 X (1+x)^7 =130

(130/100)^(1/7)= 1+x

x=[(130/100)^(1/7)]-1

x=3.8%

 

[Marcelo Branco]

Welcome to Mr Excel

30% increase in 7 days
Daily Rate = 1,3^(1/7)-1
Daily Rate = 0,0381919 --> 3,81919%

M.

 

[angstrom]

Hey Mick and Marcelo, thanks for that - two versions of the same idea - just what I wanted, works perfectly!

 

[MickG]

You're welcome

 

[Mania112]

What’s the best way to extend this further and highlight what that value was on day 4?

 

[Dave Patton]

Check out Excel's financial functions.

Excel 2010
	A	B	C
1	Initial	100
2	Days	7
3	Final	130
4	Rate	3.82%
5	Days	4	116.17
7b

Cell Formulas
Range	Formula
B4	=RATE(7,0,B1,-B3)
C5	=FV(B4,B5,0,-B1)

Named Ranges
Name	Refers To	Cells
Rate	='3aa'!$B$2