I would like to do an excel calculation where these are my inputs:
current portfolio value
yearly additional contribution to portfolio
growth rate (constant)
The outputs:
how long it will take to hit a certain number
OR
portfolio balance at end of X years
Can somebody help me out with this? I'm sure there is a growth or rateofreturn formula for this scenario.
Excel function to project retirement

 Posts: 154
 Joined: Fri Jun 15, 2018 3:19 pm
Re: Excel function to project retirement
Give this a try and see if it works for you:
Enter your own numbers for the starting net worth field, growth rate & any additional monthly savings.
The formula in the Accumulation field in C8 =C8*(1+$B$4)+$B$5
Starting net worth $
Projected rate of monthly growth (%); this example has an annualized rate of 8% 0.006666667 (cell B4)
Monthly savings $100 (cell B5)
Month Year Accumulation
0 0 $0
1(cell A8) 0.083333 (cell B8) $100.00 (cell C8)
2 0.166667 $200.67
3 0.25 $302.00
4 0.333333333 $404.02
Enter your own numbers for the starting net worth field, growth rate & any additional monthly savings.
The formula in the Accumulation field in C8 =C8*(1+$B$4)+$B$5
Starting net worth $
Projected rate of monthly growth (%); this example has an annualized rate of 8% 0.006666667 (cell B4)
Monthly savings $100 (cell B5)
Month Year Accumulation
0 0 $0
1(cell A8) 0.083333 (cell B8) $100.00 (cell C8)
2 0.166667 $200.67
3 0.25 $302.00
4 0.333333333 $404.02
Re: Excel function to project retirement
Second one is easy
=startvalue*(1+growth rate)^no of years is the formula in any cell
example: =10000*1.05^20 10000 growing at 5% for 20 years will become $26533
=startvalue*(1+growth rate)^no of years is the formula in any cell
example: =10000*1.05^20 10000 growing at 5% for 20 years will become $26533
Re: Excel function to project retirement
Or use the formula: C10 =FV(D$2,B10B$4,E$4,C$4)
Re: Excel function to project retirement
This doesn't take into account regular contributions.
I don't want to write out the contributions or make a table of contributions.
After some research I think I can use the "FV" function. The constraints are that I must use a constant payment amount. Using this, I calculate that starting with $130,000 and contributing $31k/ year for 20 years with 4% growth results in about 1.2M.
Re: Excel function to project retirement
See the 'Misc. calcs' tab in the personal finance toolbox spreadsheet, rows 1925.

 Posts: 104
 Joined: Sat Sep 03, 2011 9:28 pm
Re: Excel function to project retirement
There's nothing wrong with spreading out the calculation into a table, and some people find that easier to look at. But of course problems like this have unified analytical answers, and sometimes (as in this case) those answers are expressed in convenient Excel functions. The answers to your two questions respectively, in terms of Excel's analytical functions, are
(1) =NPER(constant growth rate, yearly contribution, current portfolio value, desired future portfolio value)
(2) =FV(constant growth rate, # years, yearly contribution, current portfolio value)
(Note the signs of the various arguments to these functions; they are necessary to line up your intended inputs with the functions' definitions of their arguments.)
For example:
(1) NPER(5%, 80000, 350000, 2000000) shows it'll take about 12 and a half years to get to $2,000,000 at a yearly rate of 5% with a starting portfolio value of $350,000 and yearly contributions of $80,000.
(2) FV(5%,40,10000,25000) shows that a portfolio starting at $25,000, with $10,000 yearly additions for 40 years, ends up with almost $1.4 million if the portfolio grows at a constant rate of 5%.
(1) =NPER(constant growth rate, yearly contribution, current portfolio value, desired future portfolio value)
(2) =FV(constant growth rate, # years, yearly contribution, current portfolio value)
(Note the signs of the various arguments to these functions; they are necessary to line up your intended inputs with the functions' definitions of their arguments.)
For example:
(1) NPER(5%, 80000, 350000, 2000000) shows it'll take about 12 and a half years to get to $2,000,000 at a yearly rate of 5% with a starting portfolio value of $350,000 and yearly contributions of $80,000.
(2) FV(5%,40,10000,25000) shows that a portfolio starting at $25,000, with $10,000 yearly additions for 40 years, ends up with almost $1.4 million if the portfolio grows at a constant rate of 5%.
Re: Excel function to project retirement
Great! I added the NPER variant to my spreadsheet. I would not have thought to make the values negative.
Re: Excel function to project retirement
To elaborate  the conventional rule with Excel financial functions like IRR, RATE, NPER, PMT, PV, and FV is that negative numbers represent money going out of your pocket and positive numbers represent money going into your pocket. However, for many cases this leaves the question of whose pocket! For example, we can look at the cash flow of a bond from the perspective of the lender (i.e., the buyer of the bond) or of the borrower (i.e., the issuer of the bond). Here is what it looks like from both perspectives. [*]aspiringboglehead wrote: ↑Mon Mar 11, 2019 12:33 pm(Note the signs of the various arguments to these functions; they are necessary to line up your intended inputs with the functions' definitions of their arguments.)
Code: Select all
Row Col A Col B Col C Col D Col E
 Lender   Borrower 
Discounted Discounted
Year Amount @ 3.745% Amount @ 3.745%
    
4 0 (994.62) (994.62) 994.62 994.62
5 1 36.25 34.94 (36.25) (34.94)
6 2 36.25 33.68 (36.25) (33.68)
7 3 36.25 32.46 (36.25) (32.46)
8 4 36.25 31.29 (36.25) (31.29)
9 5 1,036.25 862.24 (1,036.25) (862.24)
   
10 Sum 186.63 0.00 (186.63) 0.00
Code: Select all
From the Perspective of the Lender From the Perspective of the Borrower
 
3.745% = IRR(B4:B9) 3.745% = IRR(D4:D9)
3.745% = RATE( 5, 36.25, 994.62, 1000) 3.745% = RATE( 5, 36.25, 994.62, 1000)
5 = NPER(3.745%, 36.25, 994.62, 1000) 5 = NPER(3.745%, 36.25, 994.62, 1000)
36.25 = PMT(3.745%, 5, 994.62, 1000) 36.25 = PMT(3.745%, 5, 994.62, 1000)
994.62 = PV(3.745%, 5, 36.25, 1000) 994.62 = PV(3.745%, 5, 36.25, 1000)
1,000.00 = FV(3.745%, 5, 36.25, 994.62) 1,000.00 = FV(3.745%, 5, 36.25, 994.62)
3.744% = 2 * RATE(5 * 2, 36.25 / 2, 994.62, 1000)