add 5% in every year

negi

Board Regular
Joined
Apr 16, 2009
Messages
87
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>
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Not sure what you are doing, but you are not adding the same capital each yr and compounding the result by 5%.
If your method is correct for what you want the following will give you your answer
Example:- 10000 for 5 yrs , amount to add = 5 % of original amount
NB:- 5% = 0.05
10000(5+0.05((5-1)!)
equals :- 10000(5+0.05(4+3+2+1))
equals:- 10000(5 +0.5) =10000*5.5 = 55000
 
Last edited:
Upvote 0
thanks Mr mackG

i want
if amt=10000
and year =2
reqd amt = amt+ (amt+(amt*5%)
if year =3
reqd amt =amt +(amt+(amt*5%))+((amt+(amt*5%)+amt*5%))
the same as 4,5,6 year
Exmple:- if year=2
reqd amt=10000+10500
year=3
Reqd amt=10000+10500+11000
year=4
reqd amt=10000+10500+11000+11500
Year =5
Reqd amt=10000+10500+11000+11500+12000

thanks negi
 
Upvote 0
Based on you Data and Mine below. I think thats what I gave you !!!

year=3
10000+10500+11000 =31500 31500=10000*(3+0.05*(2+1))
year=4
10000+10500+11000+11500 =43000 43000=10000*(4+0.05*(3+2+1))
Year =5
10000+10500+11000+11500+12000 =55000 55000=10000*(5+0.05*(4+3+2+1))

<COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><COL style="WIDTH: 84pt; mso-width-source: userset; mso-width-alt: 3982" width=112><COL style="WIDTH: 48pt" span=4 width=64><TBODY>
</TBODY>
 
Upvote 0
thanks mickG
but i have 50K + row in a sheet,
i want a unique fromula for the all calculation,
 
Upvote 0
Try this:-
Place first formula in "C2" and drag down
NB:- The 5% in "C1".
Code:
[COLOR="RoyalBlue"][B]Row No [/B][/COLOR] [COLOR="RoyalBlue"][B]Col(A) [/B][/COLOR] [COLOR="RoyalBlue"][B]Col(B) [/B][/COLOR] [COLOR="RoyalBlue"][B]Col(C) [/B][/COLOR] [COLOR="RoyalBlue"][B]Col(D)                                [/B][/COLOR]
1.      Amt     Year    0.05                                          
2.      10000   5       55000   =A2*(B2+$C$1*((B2-1)*((B2-1)/2+0.5))) 
3.      15000   4       64500   =A3*(B3+$C$1*((B3-1)*((B3-1)/2+0.5))) 
4.      20000   3       63000   =A4*(B4+$C$1*((B4-1)*((B4-1)/2+0.5))) 
5.      10000   2       20500   =A5*(B5+$C$1*((B5-1)*((B5-1)/2+0.5)))
Regards Mick
 
Upvote 0
thanks Mick

it's work and really help to us.
i have year in 4 mode,"annual",semiannual",Monthly" & Quarlerly"
this formula work for annual mode but some error in other mode.
i am adding one thing in formula

for semiannual:-
=A2*(B2+$C$1*((B2-2)*((B2-2)/4+0.5)))
For Monthly:-
=A2*(B2+$C$1*((B2-12)*((B2-12)/24+0.5)))
For Quarterly
=A2*(B2+$C$1*((B2-4)*((B2-4)/8+0.5)))
when using this found some error.
If mode=Semiannual
Amt=5000
NOP=9
formula==A2*(B2+$C$1*((B2-2)*((B2-2)/4+0.5)))
Result=48937.5
Acutal amt=49000

request you to please check the formula may be im doing wrong.

thanks negi
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top