Calculate expected return

P

petek394

New Member
Joined
Oct 13, 2010
Messages
2
I need a formula for the following:

Year 1 I want to save 500$.
Year 2 I want to increase this saving with 10%
Year 3 I want to increase this saving with 9%
Year 4 I want to increase this saving with 8%
Year 5 I want to increase this saving with 7%
Year 6 I want to increase this saving with 6%
Year 7 I want to increase this saving with 5%
Year 8 I want to increase this saving with 4%
Year 9 I want to increase this saving with 3%
Year 10 I want to increase this saving with 2%
Year 11 I want to increase this saving with 1%
Year 12 I want to maintain previous year's saving

I'm going to have 6% return on my savings each year.

How much will I have saved after 20 years including annual return? :confused:

Thanks!
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Sort by date Sort by votes
I normally set up a table for it when I want to find out something like this. This table assumes you earn 6% for the entire year on your annual contribution.

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 34px"><COL style="WIDTH: 34px"><COL style="WIDTH: 86px"><COL style="WIDTH: 86px"><COL style="WIDTH: 67px"><COL style="WIDTH: 74px"><COL style="WIDTH: 64px"><COL style="WIDTH: 87px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD></TR><TR style="HEIGHT: 42px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: center">Year</TD><TD style="TEXT-ALIGN: center">%</TD><TD style="TEXT-ALIGN: center">Annual Contribution</TD><TD style="TEXT-ALIGN: center">Beginning Balance</TD><TD style="TEXT-ALIGN: center">Interest</TD><TD style="TEXT-ALIGN: center">Ending Balance</TD><TD style="TEXT-ALIGN: right">6%</TD><TD>Interest Rate</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">500.00 </TD><TD></TD><TD style="TEXT-ALIGN: right">30.00 </TD><TD style="TEXT-ALIGN: right">530.00 </TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">10%</TD><TD style="TEXT-ALIGN: right">550.00 </TD><TD style="TEXT-ALIGN: right">530.00 </TD><TD style="TEXT-ALIGN: right">64.80 </TD><TD style="TEXT-ALIGN: right">1,144.80 </TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">9%</TD><TD style="TEXT-ALIGN: right">599.50 </TD><TD style="TEXT-ALIGN: right">1,144.80 </TD><TD style="TEXT-ALIGN: right">104.66 </TD><TD style="TEXT-ALIGN: right">1,848.96 </TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">8%</TD><TD style="TEXT-ALIGN: right">647.46 </TD><TD style="TEXT-ALIGN: right">1,848.96 </TD><TD style="TEXT-ALIGN: right">149.79 </TD><TD style="TEXT-ALIGN: right">2,646.20 </TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right">7%</TD><TD style="TEXT-ALIGN: right">692.78 </TD><TD style="TEXT-ALIGN: right">2,646.20 </TD><TD style="TEXT-ALIGN: right">200.34 </TD><TD style="TEXT-ALIGN: right">3,539.32 </TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: right">6</TD><TD style="TEXT-ALIGN: right">6%</TD><TD style="TEXT-ALIGN: right">734.35 </TD><TD style="TEXT-ALIGN: right">3,539.32 </TD><TD style="TEXT-ALIGN: right">256.42 </TD><TD style="TEXT-ALIGN: right">4,530.09 </TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: right">7</TD><TD style="TEXT-ALIGN: right">5%</TD><TD style="TEXT-ALIGN: right">771.07 </TD><TD style="TEXT-ALIGN: right">4,530.09 </TD><TD style="TEXT-ALIGN: right">318.07 </TD><TD style="TEXT-ALIGN: right">5,619.23 </TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: right">8</TD><TD style="TEXT-ALIGN: right">4%</TD><TD style="TEXT-ALIGN: right">801.91 </TD><TD style="TEXT-ALIGN: right">5,619.23 </TD><TD style="TEXT-ALIGN: right">385.27 </TD><TD style="TEXT-ALIGN: right">6,806.41 </TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: right">9</TD><TD style="TEXT-ALIGN: right">3%</TD><TD style="TEXT-ALIGN: right">825.97 </TD><TD style="TEXT-ALIGN: right">6,806.41 </TD><TD style="TEXT-ALIGN: right">457.94 </TD><TD style="TEXT-ALIGN: right">8,090.32 </TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="TEXT-ALIGN: right">10</TD><TD style="TEXT-ALIGN: right">2%</TD><TD style="TEXT-ALIGN: right">842.49 </TD><TD style="TEXT-ALIGN: right">8,090.32 </TD><TD style="TEXT-ALIGN: right">535.97 </TD><TD style="TEXT-ALIGN: right">9,468.77 </TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="TEXT-ALIGN: right">11</TD><TD style="TEXT-ALIGN: right">1%</TD><TD style="TEXT-ALIGN: right">850.91 </TD><TD style="TEXT-ALIGN: right">9,468.77 </TD><TD style="TEXT-ALIGN: right">619.18 </TD><TD style="TEXT-ALIGN: right">10,938.86 </TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD style="TEXT-ALIGN: right">12</TD><TD style="TEXT-ALIGN: right">0%</TD><TD style="TEXT-ALIGN: right">850.91 </TD><TD style="TEXT-ALIGN: right">10,938.86 </TD><TD style="TEXT-ALIGN: right">707.39 </TD><TD style="TEXT-ALIGN: right">12,497.16 </TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD style="TEXT-ALIGN: right">13</TD><TD style="TEXT-ALIGN: right">0%</TD><TD style="TEXT-ALIGN: right">850.91 </TD><TD style="TEXT-ALIGN: right">12,497.16 </TD><TD style="TEXT-ALIGN: right">800.88 </TD><TD style="TEXT-ALIGN: right">14,148.95 </TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD style="TEXT-ALIGN: right">14</TD><TD style="TEXT-ALIGN: right">0%</TD><TD style="TEXT-ALIGN: right">850.91 </TD><TD style="TEXT-ALIGN: right">14,148.95 </TD><TD style="TEXT-ALIGN: right">899.99 </TD><TD style="TEXT-ALIGN: right">15,899.86 </TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</TD><TD style="TEXT-ALIGN: right">15</TD><TD style="TEXT-ALIGN: right">0%</TD><TD style="TEXT-ALIGN: right">850.91 </TD><TD style="TEXT-ALIGN: right">15,899.86 </TD><TD style="TEXT-ALIGN: right">1,005.05 </TD><TD style="TEXT-ALIGN: right">17,755.81 </TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">17</TD><TD style="TEXT-ALIGN: right">16</TD><TD style="TEXT-ALIGN: right">0%</TD><TD style="TEXT-ALIGN: right">850.91 </TD><TD style="TEXT-ALIGN: right">17,755.81 </TD><TD style="TEXT-ALIGN: right">1,116.40 </TD><TD style="TEXT-ALIGN: right">19,723.13 </TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">18</TD><TD style="TEXT-ALIGN: right">17</TD><TD style="TEXT-ALIGN: right">0%</TD><TD style="TEXT-ALIGN: right">850.91 </TD><TD style="TEXT-ALIGN: right">19,723.13 </TD><TD style="TEXT-ALIGN: right">1,234.44 </TD><TD style="TEXT-ALIGN: right">21,808.48 </TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">19</TD><TD style="TEXT-ALIGN: right">18</TD><TD style="TEXT-ALIGN: right">0%</TD><TD style="TEXT-ALIGN: right">850.91 </TD><TD style="TEXT-ALIGN: right">21,808.48 </TD><TD style="TEXT-ALIGN: right">1,359.56 </TD><TD style="TEXT-ALIGN: right">24,018.96 </TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">20</TD><TD style="TEXT-ALIGN: right">19</TD><TD style="TEXT-ALIGN: right">0%</TD><TD style="TEXT-ALIGN: right">850.91 </TD><TD style="TEXT-ALIGN: right">24,018.96 </TD><TD style="TEXT-ALIGN: right">1,492.19 </TD><TD style="TEXT-ALIGN: right">26,362.06 </TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">21</TD><TD style="TEXT-ALIGN: right">20</TD><TD style="TEXT-ALIGN: right">0%</TD><TD style="TEXT-ALIGN: right">850.91 </TD><TD style="TEXT-ALIGN: right">26,362.06 </TD><TD style="TEXT-ALIGN: right">1,632.78 </TD><TD style="TEXT-ALIGN: right">28,845.75 </TD><TD></TD><TD></TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>E2</TD><TD>=(C2+D2)*$G$1</TD></TR><TR><TD>F2</TD><TD>=C2+E2</TD></TR><TR><TD>C3</TD><TD>=C2*B3+C2</TD></TR><TR><TD>D3</TD><TD>=F2</TD></TR><TR><TD>E3</TD><TD>=(C3+D3)*$G$1</TD></TR><TR><TD>F3</TD><TD>=C3+D3+E3</TD></TR><TR><TD>C4</TD><TD>=C3*B4+C3</TD></TR><TR><TD>D4</TD><TD>=F3</TD></TR><TR><TD>E4</TD><TD>=(C4+D4)*$G$1</TD></TR><TR><TD>F4</TD><TD>=C4+D4+E4</TD></TR><TR><TD>C5</TD><TD>=C4*B5+C4</TD></TR><TR><TD>D5</TD><TD>=F4</TD></TR><TR><TD>E5</TD><TD>=(C5+D5)*$G$1</TD></TR><TR><TD>F5</TD><TD>=C5+D5+E5</TD></TR><TR><TD>C6</TD><TD>=C5*B6+C5</TD></TR><TR><TD>D6</TD><TD>=F5</TD></TR><TR><TD>E6</TD><TD>=(C6+D6)*$G$1</TD></TR><TR><TD>F6</TD><TD>=C6+D6+E6</TD></TR><TR><TD>C7</TD><TD>=C6*B7+C6</TD></TR><TR><TD>D7</TD><TD>=F6</TD></TR><TR><TD>E7</TD><TD>=(C7+D7)*$G$1</TD></TR><TR><TD>F7</TD><TD>=C7+D7+E7</TD></TR><TR><TD>C8</TD><TD>=C7*B8+C7</TD></TR><TR><TD>D8</TD><TD>=F7</TD></TR><TR><TD>E8</TD><TD>=(C8+D8)*$G$1</TD></TR><TR><TD>F8</TD><TD>=C8+D8+E8</TD></TR><TR><TD>C9</TD><TD>=C8*B9+C8</TD></TR><TR><TD>D9</TD><TD>=F8</TD></TR><TR><TD>E9</TD><TD>=(C9+D9)*$G$1</TD></TR><TR><TD>F9</TD><TD>=C9+D9+E9</TD></TR><TR><TD>C10</TD><TD>=C9*B10+C9</TD></TR><TR><TD>D10</TD><TD>=F9</TD></TR><TR><TD>E10</TD><TD>=(C10+D10)*$G$1</TD></TR><TR><TD>F10</TD><TD>=C10+D10+E10</TD></TR><TR><TD>C11</TD><TD>=C10*B11+C10</TD></TR><TR><TD>D11</TD><TD>=F10</TD></TR><TR><TD>E11</TD><TD>=(C11+D11)*$G$1</TD></TR><TR><TD>F11</TD><TD>=C11+D11+E11</TD></TR><TR><TD>C12</TD><TD>=C11*B12+C11</TD></TR><TR><TD>D12</TD><TD>=F11</TD></TR><TR><TD>E12</TD><TD>=(C12+D12)*$G$1</TD></TR><TR><TD>F12</TD><TD>=C12+D12+E12</TD></TR><TR><TD>C13</TD><TD>=C12*B13+C12</TD></TR><TR><TD>D13</TD><TD>=F12</TD></TR><TR><TD>E13</TD><TD>=(C13+D13)*$G$1</TD></TR><TR><TD>F13</TD><TD>=C13+D13+E13</TD></TR><TR><TD>C14</TD><TD>=C13*B14+C13</TD></TR><TR><TD>D14</TD><TD>=F13</TD></TR><TR><TD>E14</TD><TD>=(C14+D14)*$G$1</TD></TR><TR><TD>F14</TD><TD>=C14+D14+E14</TD></TR><TR><TD>C15</TD><TD>=C14*B15+C14</TD></TR><TR><TD>D15</TD><TD>=F14</TD></TR><TR><TD>E15</TD><TD>=(C15+D15)*$G$1</TD></TR><TR><TD>F15</TD><TD>=C15+D15+E15</TD></TR><TR><TD>C16</TD><TD>=C15*B16+C15</TD></TR><TR><TD>D16</TD><TD>=F15</TD></TR><TR><TD>E16</TD><TD>=(C16+D16)*$G$1</TD></TR><TR><TD>F16</TD><TD>=C16+D16+E16</TD></TR><TR><TD>C17</TD><TD>=C16*B17+C16</TD></TR><TR><TD>D17</TD><TD>=F16</TD></TR><TR><TD>E17</TD><TD>=(C17+D17)*$G$1</TD></TR><TR><TD>F17</TD><TD>=C17+D17+E17</TD></TR><TR><TD>C18</TD><TD>=C17*B18+C17</TD></TR><TR><TD>D18</TD><TD>=F17</TD></TR><TR><TD>E18</TD><TD>=(C18+D18)*$G$1</TD></TR><TR><TD>F18</TD><TD>=C18+D18+E18</TD></TR><TR><TD>C19</TD><TD>=C18*B19+C18</TD></TR><TR><TD>D19</TD><TD>=F18</TD></TR><TR><TD>E19</TD><TD>=(C19+D19)*$G$1</TD></TR><TR><TD>F19</TD><TD>=C19+D19+E19</TD></TR><TR><TD>C20</TD><TD>=C19*B20+C19</TD></TR><TR><TD>D20</TD><TD>=F19</TD></TR><TR><TD>E20</TD><TD>=(C20+D20)*$G$1</TD></TR><TR><TD>F20</TD><TD>=C20+D20+E20</TD></TR><TR><TD>C21</TD><TD>=C20*B21+C20</TD></TR><TR><TD>D21</TD><TD>=F20</TD></TR><TR><TD>E21</TD><TD>=(C21+D21)*$G$1</TD></TR><TR><TD>F21</TD><TD>=C21+D21+E21</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Good luck!
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Calculating 1 / 3 / 5 year Returns on a table that varies in length.
Replies
3
Views
276
Anthony47
A
S
  • Solved
Macro not saving exported files to where it should
Replies
2
Views
205
shand
S
D
  • Solved
Number of Coupons per year.
Replies
2
Views
73
dowtym
D
R
  • Question
Interest Calculation
Replies
3
Views
155
SanjayGMusafir
SanjayGMusafir
A
  • Question
Lookup Help! Data table is dynamically dependent on data present in a row against which lookup is needed.
Replies
1
Views
371
asadriaz
A

Forum statistics

Threads
1,214,393
Messages
6,119,261
Members
448,880
Latest member
aveternik

Share this page

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