Help with "rate" function

[Colson Whitehead]

I'm trying to calculate the rate of return on an investment in bitcoin.
The purchase was made in 2010 when the price was 0.0008 (8/100 of a cent). 10 years later it was worth $21,000.
Using the rate function, NPER 10; PV (0.0008); FV 21000. The result is #NUM!.
I believe the the answer is 315% but excel cant solve it?
The help function says to change the Iterative Calculation, but I'm still getting #NUM! Any thoughts?

[6StringJazzer]

The RATE function designed for a series of payments. The second argument is the payment amount. What are you using for that? You just want to determine effective interest of an investment.

How often do you want to do compounding? Just for a simple case, annual compounding, the rate is just a little higher than your estimate.

Values as displayed

	A	B	C	D	E	F
1	rate	352%	Period	Balance	Interest	Balance
2			1	$ 0.0008	$ 0.0036	$ 0.0044
3			2	$ 0.0044	$ 0.0200	$ 0.0244
4			3	$ 0.0244	$ 0.1102	$ 0.1346
5			4	$ 0.1346	$ 0.6082	$ 0.7428
6			5	$ 0.7428	$ 3.3573	$ 4.1000
7			6	$ 4.1000	$ 18.5321	$ 22.6321
8			7	$ 22.6321	$ 102.2970	$ 124.9291
9			8	$ 124.9291	$ 564.6794	$ 689.6085
10			9	$ 689.6085	$ 3,117.0304	$ 3,806.6389
11			10	$3,806.6389	$17,206.0080	$21,012.6470
Sheet13

Underlying formulas

	A	B	C	D	E	F
1
2					=D2*(1+$B$1)	=SUM(D2:E2)
3				=F2	=D3*(1+$B$1)	=SUM(D3:E3)
4				=F3	=D4*(1+$B$1)	=SUM(D4:E4)
5				=F4	=D5*(1+$B$1)	=SUM(D5:E5)
6				=F5	=D6*(1+$B$1)	=SUM(D6:E6)
7				=F6	=D7*(1+$B$1)	=SUM(D7:E7)
8				=F7	=D8*(1+$B$1)	=SUM(D8:E8)
9				=F8	=D9*(1+$B$1)	=SUM(D9:E9)
10				=F9	=D10*(1+$B$1)	=SUM(D10:E10)
11				=F10	=D11*(1+$B$1)	=SUM(D11:E11)

[alansidman]

crossposted: https://www.mrexcel.com/board/thread...ction.1263932/

Rule 7: Cross-posting Without Telling Us

Your post does not comply with Rule 7 of our Forum RULES. Do not cross-post your question on multiple forums without telling us about your threads on other forums.

Post a link to any other forums where you have asked the same question.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Read this to understand why we ask you to do this.

I have added the crosspost reference for you today. Please comply with this and all our rules in the future

However, if you continue to crosspost, you can expect to have your thread BLOCKED until you update it yourself.

[6StringJazzer]

Here are calculations for ROI rate. I'm not sure why it doesn't match my amortization table but it's probably related to how the compounding works.

Values as displayed

	H	I
1	Initial Cost	$ 0.00
2	Current Value	$21,000.00
3	Years	10
4	Annualized ROI	452%
Sheet13

Underlying formulas

	H	I
1
2
3
4		=(I2/I1)^(1/I3)-1

[josephteh]

Give a guess value to the RATE function and you should get the desired result. Also, either pv or fv should be in the negative.

[josephteh]

=RATE(10,0,-0.0008,21000,,300%), the answer is 452%.

[Colson Whitehead]

Apologies. Thanks for the info and help.