How are annuity payments calculated?

[Sean15]

Hi:

I'm trying tie to an online annuity calculator. Here are details from the site:

Since lottery annuities typically follow a growing annuity structure, where the amount of yearly payout grows by a given rate, the lottery annuity may take the following form:
Pn = -PV / [(1 - (1 + g)t) / g] * (1 + g)n - 1
where:

Pn - Payout in the n-th year;
PV - The gross amount of lottery prize, which is the present value (PV) of the value of the growing annuity;
g - The growth rate of yearly increment;
t - Full term of the lottery annuity in years;
n - Year of interest.

My formula in G18 does not tie to the expected Year 1 payout.
Could you help me correct formula?

How are lottery annuity payments calculated.xlsx
	B	C	D	E	F	G
14					Lottery value	1,000,000,000.00
15					No of years	30
16					% increase of payout	5%
17	Payouts should be
18	Year	Payout	Total payout		Year 1	311,062,990.66
19	1	$15,051,435	$15,051,435
20	2	$15,804,007	$30,855,442
21	3	$16,594,207	$47,449,649
22	4	$17,423,918	$64,873,567
23	5	$18,295,113	$83,168,680
24	6	$19,209,869	$102,378,549
25	7	$20,170,363	$122,548,912
26	8	$21,178,881	$143,727,793
27	9	$22,237,825	$165,965,618
28	10	$23,349,716	$189,315,334
29	11	$24,517,202	$213,832,536
30	12	$25,743,062	$239,575,598
31	13	$27,030,215	$266,605,813
32	14	$28,381,726	$294,987,539
33	15	$29,800,812	$324,788,351
34	16	$31,290,853	$356,079,204
35	17	$32,855,395	$388,934,599
36	18	$34,498,165	$423,432,764
37	19	$36,223,073	$459,655,837
38	20	$38,034,227	$497,690,064
39	21	$39,935,938	$537,626,002
40	22	$41,932,735	$579,558,737
41	23	$44,029,372	$623,588,109
42	24	$46,230,841	$669,818,950
43	25	$48,542,383	$718,361,333
44	26	$50,969,502	$769,330,835
45	27	$53,517,977	$822,848,812
46	28	$56,193,876	$879,042,688
47	29	$59,003,570	$938,046,258
48	30	$61,953,748	$1,000,000,006
Sheet1

Cell Formulas
Range	Formula
G18	G18	=-G14/(1-(1+G16)^G15)/G15*(1+G15)^1-1
D20:D48	D20	=D19+C20

 

[Anonymous1378]

You're missing a bunch of brackets and using the number of years where you should have been using growth rate?

=-G14/((1-(1+G16)^G15)/G16)*(1+G16)^(1-1)?