# add 5% in every year

#### negi

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

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

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.
Reqd Amount = Amount * (year - 1) * 1.05 + Amount

thanks mr bait

but its not work wrong amt calculate

Not sure what you are doing, but you are not adding the same capital each yr and compounding the result by 5%.
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:
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

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>

thanks mickG
but i have 50K + row in a sheet,
i want a unique fromula for the all calculation,

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

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

Dear mick

Replies
3
Views
485
Replies
4
Views
2K
Replies
6
Views
418
Replies
6
Views
595
Replies
17
Views
695

1,196,328
Messages
6,014,676
Members
441,835
Latest member
rthomas268

### 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.

### Which adblocker are you using?

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

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