Future Super Amount Formula

B

Biz

Well-known Member
Joined
May 18, 2009
Messages
1,773
Office Version
  1. 2021
Platform
  1. Windows
Hi Guys,

I am trying to created a model to predict future Superannuation amount.

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: 64px"><COL style="WIDTH: 81px"><COL style="WIDTH: 81px"><COL style="WIDTH: 127px"></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></TR><TR style="HEIGHT: 55px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">2%</TD><TD style="FONT-WEIGHT: bold">Super Contribution</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right">36</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"> 150,000 </TD><TD style="TEXT-ALIGN: right"> 153,000 </TD><TD style="TEXT-ALIGN: right"> 13,770 </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right">37</TD><TD style="TEXT-ALIGN: right"> 153,000 </TD><TD style="TEXT-ALIGN: right"> 156,060 </TD><TD style="TEXT-ALIGN: right"> 14,045 </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right">38</TD><TD style="TEXT-ALIGN: right"> 156,060 </TD><TD style="TEXT-ALIGN: right"> 159,181 </TD><TD style="TEXT-ALIGN: right"> 14,326 </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: right">39</TD><TD style="TEXT-ALIGN: right"> 159,181 </TD><TD style="TEXT-ALIGN: right"> 162,365 </TD><TD style="TEXT-ALIGN: right"> 14,613 </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: right">40</TD><TD style="TEXT-ALIGN: right"> 162,365 </TD><TD style="TEXT-ALIGN: right"> 165,612 </TD><TD style="TEXT-ALIGN: right"> 14,905 </TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: right">41</TD><TD style="TEXT-ALIGN: right"> 165,612 </TD><TD style="TEXT-ALIGN: right"> 168,924 </TD><TD style="TEXT-ALIGN: right"> 15,203 </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: right">42</TD><TD style="TEXT-ALIGN: right"> 168,924 </TD><TD style="TEXT-ALIGN: right"> 172,303 </TD><TD style="TEXT-ALIGN: right"> 15,507 </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: right">43</TD><TD style="TEXT-ALIGN: right"> 172,303 </TD><TD style="TEXT-ALIGN: right"> 175,749 </TD><TD style="TEXT-ALIGN: right"> 15,817 </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: right">44</TD><TD style="TEXT-ALIGN: right"> 175,749 </TD><TD style="TEXT-ALIGN: right"> 179,264 </TD><TD style="TEXT-ALIGN: right"> 16,134 </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="TEXT-ALIGN: right">45</TD><TD style="TEXT-ALIGN: right"> 179,264 </TD><TD style="TEXT-ALIGN: right"> 182,849 </TD><TD style="TEXT-ALIGN: right"> 16,456 </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="TEXT-ALIGN: right">46</TD><TD style="TEXT-ALIGN: right"> 182,849 </TD><TD style="TEXT-ALIGN: right"> 186,506 </TD><TD style="TEXT-ALIGN: right"> 16,786 </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD style="TEXT-ALIGN: right">47</TD><TD style="TEXT-ALIGN: right"> 186,506 </TD><TD style="TEXT-ALIGN: right"> 190,236 </TD><TD style="TEXT-ALIGN: right"> 17,121 </TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD style="TEXT-ALIGN: right">48</TD><TD style="TEXT-ALIGN: right"> 190,236 </TD><TD style="TEXT-ALIGN: right"> 194,041 </TD><TD style="TEXT-ALIGN: right"> 17,464 </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD style="TEXT-ALIGN: right">49</TD><TD style="TEXT-ALIGN: right"> 194,041 </TD><TD style="TEXT-ALIGN: right"> 197,922 </TD><TD style="TEXT-ALIGN: right"> 17,813 </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</TD><TD style="TEXT-ALIGN: right">50</TD><TD style="TEXT-ALIGN: right"> 197,922 </TD><TD style="TEXT-ALIGN: right"> 201,880 </TD><TD style="TEXT-ALIGN: right"> 18,169 </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">17</TD><TD style="TEXT-ALIGN: right">51</TD><TD style="TEXT-ALIGN: right"> 201,880 </TD><TD style="TEXT-ALIGN: right"> 205,918 </TD><TD style="TEXT-ALIGN: right"> 18,533 </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">18</TD><TD style="TEXT-ALIGN: right">52</TD><TD style="TEXT-ALIGN: right"> 205,918 </TD><TD style="TEXT-ALIGN: right"> 210,036 </TD><TD style="TEXT-ALIGN: right"> 18,903 </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">19</TD><TD style="TEXT-ALIGN: right">53</TD><TD style="TEXT-ALIGN: right"> 210,036 </TD><TD style="TEXT-ALIGN: right"> 214,237 </TD><TD style="TEXT-ALIGN: right"> 19,281 </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">20</TD><TD style="TEXT-ALIGN: right">54</TD><TD style="TEXT-ALIGN: right"> 214,237 </TD><TD style="TEXT-ALIGN: right"> 218,522 </TD><TD style="TEXT-ALIGN: right"> 19,667 </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">21</TD><TD style="TEXT-ALIGN: right">55</TD><TD style="TEXT-ALIGN: right"> 218,522 </TD><TD style="TEXT-ALIGN: right"> 222,892 </TD><TD style="TEXT-ALIGN: right"> 20,060 </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">22</TD><TD style="TEXT-ALIGN: right">56</TD><TD style="TEXT-ALIGN: right"> 222,892 </TD><TD style="TEXT-ALIGN: right"> 227,350 </TD><TD style="TEXT-ALIGN: right"> 20,461 </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">23</TD><TD style="TEXT-ALIGN: right">57</TD><TD style="TEXT-ALIGN: right"> 227,350 </TD><TD style="TEXT-ALIGN: right"> 231,897 </TD><TD style="TEXT-ALIGN: right"> 20,871 </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">24</TD><TD style="TEXT-ALIGN: right">58</TD><TD style="TEXT-ALIGN: right"> 231,897 </TD><TD style="TEXT-ALIGN: right"> 236,535 </TD><TD style="TEXT-ALIGN: right"> 21,288 </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">25</TD><TD style="TEXT-ALIGN: right">59</TD><TD style="TEXT-ALIGN: right"> 236,535 </TD><TD style="TEXT-ALIGN: right"> 241,266 </TD><TD style="TEXT-ALIGN: right"> 21,714 </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">26</TD><TD style="TEXT-ALIGN: right">60</TD><TD style="TEXT-ALIGN: right"> 241,266 </TD><TD style="TEXT-ALIGN: right"> 246,091 </TD><TD style="TEXT-ALIGN: right"> 22,148 </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">27</TD><TD style="TEXT-ALIGN: right">61</TD><TD style="TEXT-ALIGN: right"> 246,091 </TD><TD style="TEXT-ALIGN: right"> 251,013 </TD><TD style="TEXT-ALIGN: right"> 22,591 </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">28</TD><TD style="TEXT-ALIGN: right">62</TD><TD style="TEXT-ALIGN: right"> 251,013 </TD><TD style="TEXT-ALIGN: right"> 256,033 </TD><TD style="TEXT-ALIGN: right"> 23,043 </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">29</TD><TD style="TEXT-ALIGN: right">63</TD><TD style="TEXT-ALIGN: right"> 256,033 </TD><TD style="TEXT-ALIGN: right"> 261,154 </TD><TD style="TEXT-ALIGN: right"> 23,504 </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">30</TD><TD style="TEXT-ALIGN: right">64</TD><TD style="TEXT-ALIGN: right"> 261,154 </TD><TD style="TEXT-ALIGN: right"> 266,377 </TD><TD style="TEXT-ALIGN: right"> 23,974 </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">31</TD><TD style="TEXT-ALIGN: right">65</TD><TD style="TEXT-ALIGN: right"> 266,377 </TD><TD style="TEXT-ALIGN: right"> 271,704.24 </TD><TD style="TEXT-ALIGN: right"> 24,453 </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">32</TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold"> 558,622</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>C2</TD><TD>=B2*(1+$C$1)</TD></TR><TR><TD>D2</TD><TD>=C2*0.09</TD></TR><TR><TD>B3</TD><TD>=C2</TD></TR><TR><TD>C3</TD><TD>=B3*(1+$C$1)</TD></TR><TR><TD>D3</TD><TD>=C3*0.09</TD></TR><TR><TD>B4</TD><TD>=C3</TD></TR><TR><TD>C4</TD><TD>=B4*(1+$C$1)</TD></TR><TR><TD>D4</TD><TD>=C4*0.09</TD></TR><TR><TD>B5</TD><TD>=C4</TD></TR><TR><TD>C5</TD><TD>=B5*(1+$C$1)</TD></TR><TR><TD>D5</TD><TD>=C5*0.09</TD></TR><TR><TD>B6</TD><TD>=C5</TD></TR><TR><TD>C6</TD><TD>=B6*(1+$C$1)</TD></TR><TR><TD>D6</TD><TD>=C6*0.09</TD></TR><TR><TD>B7</TD><TD>=C6</TD></TR><TR><TD>C7</TD><TD>=B7*(1+$C$1)</TD></TR><TR><TD>D7</TD><TD>=C7*0.09</TD></TR><TR><TD>B8</TD><TD>=C7</TD></TR><TR><TD>C8</TD><TD>=B8*(1+$C$1)</TD></TR><TR><TD>D8</TD><TD>=C8*0.09</TD></TR><TR><TD>B9</TD><TD>=C8</TD></TR><TR><TD>C9</TD><TD>=B9*(1+$C$1)</TD></TR><TR><TD>D9</TD><TD>=C9*0.09</TD></TR><TR><TD>B10</TD><TD>=C9</TD></TR><TR><TD>C10</TD><TD>=B10*(1+$C$1)</TD></TR><TR><TD>D10</TD><TD>=C10*0.09</TD></TR><TR><TD>B11</TD><TD>=C10</TD></TR><TR><TD>C11</TD><TD>=B11*(1+$C$1)</TD></TR><TR><TD>D11</TD><TD>=C11*0.09</TD></TR><TR><TD>B12</TD><TD>=C11</TD></TR><TR><TD>C12</TD><TD>=B12*(1+$C$1)</TD></TR><TR><TD>D12</TD><TD>=C12*0.09</TD></TR><TR><TD>B13</TD><TD>=C12</TD></TR><TR><TD>C13</TD><TD>=B13*(1+$C$1)</TD></TR><TR><TD>D13</TD><TD>=C13*0.09</TD></TR><TR><TD>B14</TD><TD>=C13</TD></TR><TR><TD>C14</TD><TD>=B14*(1+$C$1)</TD></TR><TR><TD>D14</TD><TD>=C14*0.09</TD></TR><TR><TD>B15</TD><TD>=C14</TD></TR><TR><TD>C15</TD><TD>=B15*(1+$C$1)</TD></TR><TR><TD>D15</TD><TD>=C15*0.09</TD></TR><TR><TD>B16</TD><TD>=C15</TD></TR><TR><TD>C16</TD><TD>=B16*(1+$C$1)</TD></TR><TR><TD>D16</TD><TD>=C16*0.09</TD></TR><TR><TD>B17</TD><TD>=C16</TD></TR><TR><TD>C17</TD><TD>=B17*(1+$C$1)</TD></TR><TR><TD>D17</TD><TD>=C17*0.09</TD></TR><TR><TD>B18</TD><TD>=C17</TD></TR><TR><TD>C18</TD><TD>=B18*(1+$C$1)</TD></TR><TR><TD>D18</TD><TD>=C18*0.09</TD></TR><TR><TD>B19</TD><TD>=C18</TD></TR><TR><TD>C19</TD><TD>=B19*(1+$C$1)</TD></TR><TR><TD>D19</TD><TD>=C19*0.09</TD></TR><TR><TD>B20</TD><TD>=C19</TD></TR><TR><TD>C20</TD><TD>=B20*(1+$C$1)</TD></TR><TR><TD>D20</TD><TD>=C20*0.09</TD></TR><TR><TD>B21</TD><TD>=C20</TD></TR><TR><TD>C21</TD><TD>=B21*(1+$C$1)</TD></TR><TR><TD>D21</TD><TD>=C21*0.09</TD></TR><TR><TD>B22</TD><TD>=C21</TD></TR><TR><TD>C22</TD><TD>=B22*(1+$C$1)</TD></TR><TR><TD>D22</TD><TD>=C22*0.09</TD></TR><TR><TD>B23</TD><TD>=C22</TD></TR><TR><TD>C23</TD><TD>=B23*(1+$C$1)</TD></TR><TR><TD>D23</TD><TD>=C23*0.09</TD></TR><TR><TD>B24</TD><TD>=C23</TD></TR><TR><TD>C24</TD><TD>=B24*(1+$C$1)</TD></TR><TR><TD>D24</TD><TD>=C24*0.09</TD></TR><TR><TD>B25</TD><TD>=C24</TD></TR><TR><TD>C25</TD><TD>=B25*(1+$C$1)</TD></TR><TR><TD>D25</TD><TD>=C25*0.09</TD></TR><TR><TD>B26</TD><TD>=C25</TD></TR><TR><TD>C26</TD><TD>=B26*(1+$C$1)</TD></TR><TR><TD>D26</TD><TD>=C26*0.09</TD></TR><TR><TD>B27</TD><TD>=C26</TD></TR><TR><TD>C27</TD><TD>=B27*(1+$C$1)</TD></TR><TR><TD>D27</TD><TD>=C27*0.09</TD></TR><TR><TD>B28</TD><TD>=C27</TD></TR><TR><TD>C28</TD><TD>=B28*(1+$C$1)</TD></TR><TR><TD>D28</TD><TD>=C28*0.09</TD></TR><TR><TD>B29</TD><TD>=C28</TD></TR><TR><TD>C29</TD><TD>=B29*(1+$C$1)</TD></TR><TR><TD>D29</TD><TD>=C29*0.09</TD></TR><TR><TD>B30</TD><TD>=C29</TD></TR><TR><TD>C30</TD><TD>=B30*(1+$C$1)</TD></TR><TR><TD>D30</TD><TD>=C30*0.09</TD></TR><TR><TD>B31</TD><TD>=C30</TD></TR><TR><TD>C31</TD><TD>=B31*(1+$C$1)</TD></TR><TR><TD>D31</TD><TD>=C31*0.09</TD></TR><TR><TD>D32</TD><TD>=SUM(D2:D31)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

<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: 70px"><COL style="WIDTH: 102px"><COL style="WIDTH: 114px"><COL style="WIDTH: 84px"><COL style="WIDTH: 107px"><COL style="WIDTH: 84px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD><TD>L</TD></TR><TR style="HEIGHT: 55px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>Future Years of Working </TD><TD>Salary</TD><TD>Annual Payrise %</TD><TD>AU Super %</TD><TD>Future Salary</TD><TD>Total Super</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">30</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"> 150,000 </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">2%</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">9%</TD><TD style="TEXT-ALIGN: right"> 271,704 </TD><TD style="TEXT-ALIGN: right"> 405,000 </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>H2</TD><TD>=B2</TD></TR><TR><TD>K2</TD><TD>=(H2*(1+C1)^G2)</TD></TR><TR><TD>L2</TD><TD>=(H2*J2)*G2</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

What formula do I need to use cell L2 so that I can avoid the long winded method and get contract result of 558,622.45?

Biz
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
surprisingly, this worked:
=SUMPRODUCT(--(B2:B31)*(1+$C$1)*.09)
 
Upvote 0
Thanks.I have used

FV(I2,G2,-(H2*(1+I2))*J2)

Biz
 
Upvote 0
Biz,
Your total contributions would be
=-FV(0.02,66-36,0.09*153000)
= 558.6K

which is the same as your formula above

If you expected to get a net 5% compuning return on your super earnings then your final balance (as distinct from total contributions) is
=-FV(0.07,30,0.09*153000)
=1300.7K

hth

Dave
 
Upvote 0
brettdj said:
Biz,
Your total contributions would be
=-FV(0.02,66-36,0.09*153000)
= 558.6K

which is the same as your formula above

If you expected to get a net 5% compuning return on your super earnings then your final balance (as distinct from total contributions) is
=-FV(0.07,30,0.09*153000)
=1300.7K

hth

Dave
Click to expand...


Hi Dave,

Do mean 5% on final balance?

=-FV(0.05,G2,J2*(H2*(1+I2)))
= 914,862.93

Thanks for picking up net 5% compuning return on your super earnings.

Biz
 
Upvote 0
No probs

The 7% has two components
- the 2% increase in annual wage that drives the contributions
- a 5% component representing the earnings in the fund

Strictly speaking this would use the Fisher equation to compound the returns
= (1+2%)*(1=5%) -1
= 7.1%

And there would be a time offset of the fund returns on the initial contributions

Regards

Dave
 
Upvote 0
Hi Dave,

Thanks for explanation and ideas. I will go with 5% to be conservative.

Biz
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
 
Upvote 0
Hi,

I was playing around and managed to streamline future Salary formula

Before
=(H2*(1+C1)^G2)

After

=-FV(I2,G2,0,H2)

Biz
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
619
smozgur
smozgur
R
  • Question Question
import specific columns from one workbook to another from matching header with vba
Replies
1
Views
134
Peter_SSs
Peter_SSs
smide
  • Solved
Formula for percentile if data are in multiple columns
Replies
2
Views
121
hagia_sofia
hagia_sofia
D
  • Question Question
Looking for a way to allocate shifts between teams, maybe a LET formula
Replies
0
Views
88
Dazzybeeguy
D
A
  • Question Question
Need a formula
Replies
5
Views
309
AFZAL SOHAIL
A

Forum statistics

Threads
1,224,538
Messages
6,179,412
Members
452,912
Latest member
alicemil

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