FV Function for Investment with square dividend growth rate

[triplesec]

Investing with money 20,000 and after 8 years, principal becomes 40,000 but the condition is the return percentage in year 2 is the square of year 1 and go on until year 8. (The return are compound annually. No tax)

How can I use FV function with non-constant dividend rate ?

 

[Dave Patton]

Please provide more information/explanation, some examples, and specifics on what you require.

T202306a.xlsm
	A	B	C	D	E	F
1			Rate	Term	Future Value
2		$20,000.00	9.05%	8	$40,000.00
3		$20,000.00	9.05%	8	$40,000.00
4		$20,000.00	9.05%	8	$40,000.00
5f

Cell Formulas
Range	Formula
E2	E2	=FV(C2,D2,0,-B2)
C3	C3	=RATE(D3,0,B3,-E3)
E4	E4	=B4*(1+C4)^D4

 

[triplesec]

Example

Google Sheet test.xlsx
	I
36
Math Test

 

[triplesec]

Sorry for eaylier reply, I'm new to post mini-sheet.

Google Sheet test.xlsx
	F	G	H	I
24	Principal (P)	20,000.00
25	Year (n)	% Return ( r )	Return ($)	Future Value (FV)
26	1	1.031367038	206.2734076	20,206.27
27	2	1.063717967	214.9377607	20,421.21
28	3	1.131495913	231.0651699	20,652.28
29	4	1.280283002	264.4075835	20,916.68
30	5	1.639124566	342.8505045	21,259.53
31	6	2.686729342	571.1861494	21,830.72
32	7	7.218514557	1,575.8537427	23,406.57
33	8	52.10695241	12,196.4525416	35,603.03
34	9	5	1,780.1513430	37,383.18
35	10	7	2,616.8224742	40,000.00
Math Test

Cell Formulas
Range	Formula
H26	H26	=G24*(G26/100)
I26	I26	=G24+H26
H27:H35	H27	=I26*(G27/100)
I27:I35	I27	=I26+H27
G27:G33	G27	=G26^2

 

[Dave Patton]

Consider the following.
See Excel's help information for the function.

T202306a.xlsm
	A	B	C			F	G	H	I
23
24	Principal	20,000.00				Principal (P)	20000
25	Rates	Future Value				Year (n)	% Return ( r )	Return ($)	Future Value (FV)
26	1.03137%	40,000.001				1	1.031367	206.27341	20206.273
27	1.06372%					2	1.063718	214.93776	20421.211
28	1.13150%					3	1.1314959	231.06517	20652.276
29	1.28028%					4	1.280283	264.40758	20916.684
30	1.63912%					5	1.6391246	342.8505	21259.534
31	2.68673%					6	2.6867293	571.18615	21830.721
32	7.21851%					7	7.2185146	1575.8537	23406.574
33	52.10695%					8	52.106952	12196.453	35603.027
34	5.00000%					9	5	1780.1513	37383.178
35	7.00000%					10	7	2616.8225	40000.001
36
5f

Cell Formulas
Range	Formula
B26	B26	=FVSCHEDULE(B24,A26:A35)
H26	H26	=G24*(G26/100)
I26	I26	=G24+H26
H27:H35	H27	=I26*(G27/100)
I27:I35	I27	=I26+H27
G27:G33	G27	=G26^2

 

[Dave Patton]

T202306a.xlsm
	F	G	H	I
22				40,000.00
23
24	Principal (P)	20000
25	Year (n)	% Return ( r )	Return ($)	Future Value (FV)
26	1	1.031367	206.27341	20206.2734
27	2	1.063718	214.93776	20421.2112
28	3	1.1314959	231.06517	20652.2763
29	4	1.280283	264.40758	20916.6839
30	5	1.6391246	342.8505	21259.5344
31	6	2.6867293	571.18615	21830.7206
32	7	7.2185146	1575.8537	23406.5743
33	8	52.106952	12196.453	35603.0269
34	9	5	1780.1513	37383.1782
35	10	7	2616.8225	40000.0007
36
5f

Cell Formulas
Range	Formula
I22	I22	=FVSCHEDULE(G24,G26:G35/100)
H26	H26	=G24*(G26/100)
I26	I26	=G24+H26
H27:H35	H27	=I26*(G27/100)
I27:I35	I27	=I26+H27
G27:G33	G27	=G26^2

 

[Dave Patton]

T202306a.xlsm
	F	G	H	I
22				40,000.00
23
24	Principal (P)	20000
25	Year (n)	% Return ( r )	Return ($)	Future Value (FV)
26	1	1.031367	206.27341	20206.2734
27	2	1.063718	214.93776	20421.2112
28	3	1.1314959	231.06517	20652.2763
29	4	1.280283	264.40758	20916.6839
30	5	1.6391246	342.8505	21259.5344
31	6	2.6867293	571.18615	21830.7206
32	7	7.2185146	1575.8537	23406.5743
33	8	52.106952	12196.453	35603.0269
34	9	5	1780.1513	37383.1782
35	10	7	2616.8225	40000.0007
36
5f

Cell Formulas
Range	Formula
I22	I22	=FVSCHEDULE(G24,G26:G35/100)

 

[triplesec]

Thanks a lot.

But there are any formula for the % Return rate from year 1 - 8 and combine it to FV (=FVSCHEDULE(G24,(formula for growth return rate)/100))
(something like if else in JavaScript below)

if (n <= 8)
{
r = r^2
}

(sorry, I only remember a little bit of JavaScript)

 

[Dave Patton]

You can convert the range into an array and the formula will work and it does not require the table of information.
I tried formulas with Power.
There may be a way to build the complete function within a formula but at this time I do not have the answer.
The relatively new Lambda functions may help or someone could build a UDF.


If you have worked with Javascript or other programs, you are probably much closer to expert than myself.
see a recent post by Rick Rothstein or the Lambda section in MrExcel.
www.mrexcel.com/board/threads/hstack-problem.1235793/page-2#post-6077045


Hopefully, one of the experts will read the thread.

T202306a.xlsm
	F	G	H	I	J	K
22		40,000.00	40,000.00
23
24	Principal (P)	20000
25	Year (n)	% Return ( r )	Return ($)	Future Value (FV)
26	1	1.03136704	206.273408	20206.2734		1.031367
27	2	1.06371797	214.937761	20421.2112		1.063718
28	3	1.13149591	231.06517	20652.2763		1.131496
29	4	1.280283	264.407584	20916.6839		1.280283
30	5	1.63912457	342.850504	21259.5344		1.639125
31	6	2.68672934	571.186149	21830.7206		2.686729
32	7	7.21851456	1575.85374	23406.5743		7.218515
33	8	52.1069524	12196.4525	35603.0269		52.10695
34	9	5	1780.15134	37383.1782		5
35	10	7	2616.82247	40000.0007		7
36
37
5f

Cell Formulas
Range	Formula
G22	G22	=FVSCHEDULE(G24,G26:G35/100)
H22	H22	=FVSCHEDULE(G24,{0.0103137;0.0106372;0.011315;0.01280283;0.0163912;0.02686729;0.0721851;0.5210695;0.05;0.07})
G27:G33	G27	=G26^2
K26	K26	=G26
K27	K27	=POWER(K26,2)
K28:K33	K28	=POWER(G27,2)

 

[triplesec]

I'm very appreciated.