Excel LOG equation + 5% fee

[docfry]

My excel function for an investment that tells me when (on a particular day) the investment will reach 27,400 is:

LOG((27400+1000)/1000, 1.01)

27400 = max investment
1000 = initial investment
1.01 = 1% daily increase

I need to add a 5% fee AFTER the 1% is paid out and represented the following day.

Like this:

Day 1: 1000 x 1% = 10; 10 x 5% = 0.5; 10-0.5 = 9.5 this gets added back in to the original investment

Day 2: 1009.5 x 1% = 10.095 x 5% = 0.50475; 10.095-0.50475 = 9.59025 + 1009.5 = 1019.09025

Day 3: 1019.09025 etc etc etc until investment reaches 27400.

I think this is more a deficiency of my math rather than excel, but maybe both.

Anyone know how to add a 5% fee to this equation LOG((27400+1000)/1000, 1.01) that will show me on what day# the investment will reach 27400?

Thanks!

 

[Dave Patton]

What is your expected result?
Is it about 350.1 days?
What is the start date?

 

[docfry]

Expected result: # of days it takes to get to 27400 investment (i.e. the maximum) taking into account the 1% increase and the 5% fee

I currently get 336.31 days when I run this equation (ignores the 5% fee): LOG((27400+1000)/1000, 1.01)

Start date is always today or Day 0.

Thanks so much for the help!

 

[Dave Patton]

I used Goal Seek with the formula from your earlier post.
Target 27400 change number of periods

You can prepare a schedule to check the results.
Just as an aside I include a revised Log calculation; you will have to determine the correct math.

Future Value.xlsm
	E	F
1	Amount	1000
2	Interest	1%
3	Fee	5%
4	Term	350.13
5		27,400.00
6		27,400.00
7
8		350.13
9
1a

Cell Formulas
Range	Formula
F5	F5	=F1*(1+(F2-F2*F3))^F4
F6	F6	=FV(F2-F2*F3,F4,0,-F1)
F8	F8	=LOG((27400)/1000, 1.01-(0.01*0.05))

 

[docfry]

That worked!

I am annoyed with myself that simple things can stump me! I just didn't know how to write 1.01-(0.01*0.05).

Do you have a way I can send you a couple of bucks to buy you a coffee or something??

Awesome! Thanks again!

 

[Dave Patton]

No payment expected. Your thank you is really appreciated.

The arithmetic proof would be like the following.
I do not have 365 on this computer. With 365, you can use sequence to create the list of numbers from 1-355
You do not need to calculate the rate in G3 but it may be more efficient.

Future Value.xlsm
	A	B	C	D	E	F	G
1			1,000.00		Amount	1000
2	1	9.50	1,009.50		Interest	1%
3	2	9.59	1,019.09		Fee	5%	0.0095
4	3	9.68	1,028.77		Term	350.13
5	4	9.77	1,038.54			27,400.00
6	5	9.87	1,048.41			27,400.00
7	6	9.96	1,058.37
1a

Cell Formulas
Range	Formula
B2,B4:B7	B2	=ROUND(C1*($F$2-$F$2*$F$3),2)
C2:C7	C2	=C1+B2
A3:A7	A3	=A2+1
B3	B3	=ROUND(C2*$G$3,2)
G3	G3	=($F$2-$F$2*$F$3)
F5	F5	=F1*(1+(F2-F2*F3))^F4
F6	F6	=FV(F2-F2*F3,F4,0,-F1)