add 5% in every year

[negi]

Dear All good evening,
i have a query in excel sheet, i have an amount in a column and we want the calculate amt in next col like as...

We want add 5 % in amt in every year.

Amt</SPAN>	Year </SPAN>	Reqd Amt</SPAN>
10000</SPAN>	5</SPAN>	55000</SPAN>
15000</SPAN>	4</SPAN>	64500</SPAN>
20000</SPAN>	3</SPAN>	63000</SPAN>
10000</SPAN>	2</SPAN>	20500</SPAN>
Example-1 1st Year</SPAN>	10000</SPAN>	B7</SPAN>
2nd Year</SPAN>	10500</SPAN>	B7+B7*5%</SPAN>
3rd Year</SPAN>	11000</SPAN>	B8+B7*5%</SPAN>
4th Year</SPAN>	11500</SPAN>	B9+B7*5%</SPAN>
5th Year</SPAN>	12000</SPAN>	B10+B7*5%</SPAN>
Total</SPAN>	55000</SPAN>	SUM(C7:C11)</SPAN>
Example-2
1st Year</SPAN>	15000</SPAN>	B14</SPAN>
2nd Year</SPAN>	15750</SPAN>	B14+B14*5%</SPAN>
3rd Year</SPAN>	16500</SPAN>	B15+B14*5%</SPAN>
4th Year</SPAN>	17250</SPAN>	B16+B14*5%</SPAN>
Total</SPAN>	64500</SPAN>	SUM(C14:C17) </SPAN>

<TBODY>
</TBODY><COLGROUP><COL span=2><COL></COLGROUP>

 

[MickG]

Can you show an example for the 4 modes as per your original thread.

 

[negi]

	FREQUENCY	Year	0.05	Actual Amt	Formula	Diff	Formula
	Annual	5	30,000.00	165000	165000	0	C2*(B2+$C$1*((B2-1)*((B2-1)/2+0.5)))
	Annual	5	10,000.00	55000	55000	0	C3*(B3+$C$1*((B3-1)*((B3-1)/2+0.5)))
	Annual	4	10,000.00	43000	43000	0	C4*(B4+$C$1*((B4-1)*((B4-1)/2+0.5)))
	Monthly	16	833.33	13500	13444	56	C5*(B5+$C$1*((B5-12)*((B5-12)/24+0.5)))
	Monthly	51	833.33	46000	45953	47	C6*(B6+$C$1*((B6-12)*((B6-12)/24+0.5)))
	Monthly	49	2,000.00	105600	105554	46	C7*(B7+$C$1*((B7-12)*((B7-12)/24+0.5)))
	Monthly	54	1,250.00	73500	73406	94	C8*(B8+$C$1*((B8-12)*((B8-12)/24+0.5)))
	Quarterly	18	9,000.00	176400	176175	225	C9*(B9+$C$1*((B9-4)*((B9-4)/8+0.5)))
	Quarterly	17	2,500.00	46000	45953	47	C10*(B10+$C$1*((B10-4)*((B10-4)/8+0.5)))
	Quarterly	18	3,000.00	58800	58725	75	C11*(B11+$C$1*((B11-4)*((B11-4)/8+0.5)))
	Quarterly	17	2,500.00	46000	45953	47	C12*(B12+$C$1*((B12-4)*((B12-4)/8+0.5)))
	SemiAnnual	9	5,000.00	49000	48938	63	C13*(B13+$C$1*((B13-2)*((B13-2)/4+0.5)))
	SemiAnnual	9	5,000.00	49000	48938	63	C14*(B14+$C$1*((B14-2)*((B14-2)/4+0.5)))
	SemiAnnual	9	25,000.00	245000	244688	313	C15*(B15+$C$1*((B15-2)*((B15-2)/4+0.5)))
	SemiAnnual	9	5,000.00	49000	48938	63	C16*(B16+$C$1*((B16-2)*((B16-2)/4+0.5)))
	SemiAnnual	9	5,000.00	49000	48938	63	C17*(B17+$C$1*((B17-2)*((B17-2)/4+0.5)))

<COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 54pt; mso-width-source: userset; mso-width-alt: 2633" width=72><COL style="WIDTH: 24pt; mso-width-source: userset; mso-width-alt: 1170" width=32><COL style="WIDTH: 47pt; mso-width-source: userset; mso-width-alt: 2267" width=62><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><COL style="WIDTH: 40pt; mso-width-source: userset; mso-width-alt: 1938" width=53><COL style="WIDTH: 21pt; mso-width-source: userset; mso-width-alt: 1024" width=28><COL style="WIDTH: 185pt; mso-width-source: userset; mso-width-alt: 8996" width=246><TBODY>
</TBODY>

 

[negi]

Dear MIck
Annual mode means 5% add in 1 time in a year
SemiAnnual mode means 5% add in 2 time in a year
Monthly mode means 5% add in 12 time in a year
Quarterly mode means 5% add in 1 time in a year

 

[MickG]

Thanks for the information, but i really wanted the calculation layout that you originally posted, but for months etc
i.e

Example-1 1st Year</SPAN>	10000</SPAN>	B7</SPAN>
2nd Year</SPAN>	10500</SPAN>	B7+B7*5%</SPAN>
3rd Year</SPAN>	11000</SPAN>	B8+B7*5%</SPAN>
4th Year</SPAN>	11500</SPAN>	B9+B7*5%</SPAN>
5th Year</SPAN>	12000</SPAN>	B10+B7*5%</SPAN>
Total</SPAN>	55000</SPAN>	SUM(C7:C11)</SPAN>
Example-2
1st Year</SPAN>	15000</SPAN>	B14</SPAN>
2nd Year</SPAN>	15750</SPAN>	B14+B14*5%</SPAN>
3rd Year</SPAN>	16500</SPAN>	B15+B14*5%</SPAN>
4th Year</SPAN>	17250</SPAN>	B16+B14*5%</SPAN>
Total</SPAN>	64500</SPAN>	SUM(C14:C17)

<TBODY>
</TBODY>

 

[negi]

yes Mr Mick
this calculation perfect for Annual mode
but monthly calcaulation

For Monthly
Year		28
Amt		833.33
Calculation
833
833
833
833
833
833
833
833
833
833
833
833		1st Year	9999.96	SUM(A7:A18)
875	B7+(B7*5%)	2nd Year	10500	SUM(B19:B30)
875		3rd Year	3675	SUM(B31:B34)
875
875		Total	24175	SUM(E18:E20)
875
875
875
875
875
875
875
875
919	B30+(B30*5%)
919
919
919

<TBODY>
</TBODY>

 

[MickG]

I Have a formula for you ,but I think some of your Answers are not correct.
In the List below (Refering to "monthly" only) the last column on the right (Colour green) are my results, the Yellow Column are yours.
I have check these long hand and My formula appears correct.
With some slight modification you can use the same formula for all Periods.
But please check this first.

Monthly	16	833.33	13500	13444	56	13499.95
Monthly	51	833.33	46000	45953	47	46139.83
Monthly	49	2,000.00	105600	105554	46	105874
Monthly	54	1,250.00	73500	73406	94	73768.17

<COLGROUP><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3498" width=98><COL style="WIDTH: 30pt; mso-width-source: userset; mso-width-alt: 1422" width=40><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 3214" width=90><COL style="WIDTH: 48pt" span=4 width=64><TBODY>
</TBODY>

Formula for "G1" and drag down !!

=(C1*12*((1.05^(INT(B1/12))-1))/0.05+(((C1*12*((1.05^((INT(B1/12))+1)-1))/0.05-(C1*12*(1.05^(INT(B1/12))-1))/0.05)*(MOD(B1,12)/12))))

Regards Mick

 

[negi]

thanks mr mick
but i think there is some confusion.

for Monthly

Year = 51
	Amt		Reqd Amt		Year
1st year	833.33		10000	D12*12	12
2nd year	875.00	D12+(D12*5%)	10500	D13*12	12
3rd year	916.66	D13+(D12*5%)	11000	D14*12	12
4th year	958.33	D14+(D12*5%)	11500	D15*12	12
5th year	1,000.00	D15+(D12*5%)	3000	D16*3	3
			46000	SUM(G12:G16)	51
		Your Result	46139.83

<COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3181" width=87><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3181" width=87><COL style="WIDTH: 48pt" width=64><TBODY>
</TBODY>

 

[MickG]

Try this:-
Formula in "H2"
=D2*B2*(INT(C2/B2)/2)*(2+((INT(C2/B2)-1)*0.05))+(D2*B2*((INT(C2/B2)+1)/2)*(2+((INT(C2/B2))*0.05))-D2*B2*(INT(C2/B2)/2)*(2+((INT(C2/B2)-1)*0.05)))*(MOD(C2,B2)/B2)

A	B	C	D	E	F	G	H
Year		0.05	Actual Amt	Formula	Diff	Formula	New Formula Results
Annual	1	5	30,000.00	165000	165000	0	165000
Annual	1	5	10,000.00	55000	55000	0	55000
Annual	1	4	10,000.00	43000	43000	0	43000
Monthly	12	16	833.33	13500	13444	56	13499.946
Monthly	12	51	833.33	46000	45953	47	45999.816
Monthly	12	49	2,000.00	105600	105554	46	105600
Monthly	12	54	1,250.00	73500	73406	94	73500
Quarterly	4	18	9,000.00	176400	176175	225	176400
Quarterly	4	17	2,500.00	46000	45953	47	46000
Quarterly	4	18	3,000.00	58800	58725	75	58800
Quarterly	4	17	2,500.00	46000	45953	47	46000
Semi-annually	2	9	5,000.00	49000	48938	63	49000
Semi-annually	2	9	5,000.00	49000	48938	63	49000
Semi-annually	2	9	25,000.00	245000	244688	313	245000
Semi-annually	2	9	5,000.00	49000	48938	63	49000
Semi-annually	2	9	5,000.00	49000	48938	63	49000

<COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 40pt; mso-width-source: userset; mso-width-alt: 1877" width=53><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 85pt; mso-width-source: userset; mso-width-alt: 4010" width=113><COL style="WIDTH: 60pt; mso-width-source: userset; mso-width-alt: 2844" width=80><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 55pt; mso-width-source: userset; mso-width-alt: 2616" width=74><COL style="WIDTH: 89pt; mso-width-source: userset; mso-width-alt: 4238" width=119><TBODY>
</TBODY>

 

[negi]

thanks MICk
its working fine.
Thanks a lot

 

[MickG]

Pleasure !!
Regards Mick