Excel function to project retirement

Have a question about your personal investments? No matter how simple or complex, you can ask it here.
Post Reply
Topic Author
28fe6
Posts: 547
Joined: Wed Jan 03, 2018 9:01 am

Excel function to project retirement

Post by 28fe6 »

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 rate-of-return formula for this scenario.
cashheavy18
Posts: 155
Joined: Fri Jun 15, 2018 3:19 pm

Re: Excel function to project retirement

Post by cashheavy18 »

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
pshonore
Posts: 6982
Joined: Sun Jun 28, 2009 2:21 pm

Re: Excel function to project retirement

Post by pshonore »

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
sawdust60
Posts: 329
Joined: Tue Jul 17, 2018 12:06 pm

Re: Excel function to project retirement

Post by sawdust60 »

Image

Or use the formula: C10 =FV(D$2,B10-B$4,-E$4,-C$4)
Topic Author
28fe6
Posts: 547
Joined: Wed Jan 03, 2018 9:01 am

Re: Excel function to project retirement

Post by 28fe6 »

pshonore wrote: Mon Mar 11, 2019 11:42 am 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
This doesn't take into account regular contributions.
sawdust60 wrote: Mon Mar 11, 2019 11:51 am Image
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.
User avatar
FiveK
Posts: 10835
Joined: Sun Mar 16, 2014 2:43 pm

Re: Excel function to project retirement

Post by FiveK »

See the 'Misc. calcs' tab in the personal finance toolbox spreadsheet, rows 19-25.
aspiringboglehead
Posts: 104
Joined: Sat Sep 03, 2011 9:28 pm

Re: Excel function to project retirement

Post by aspiringboglehead »

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%.
Topic Author
28fe6
Posts: 547
Joined: Wed Jan 03, 2018 9:01 am

Re: Excel function to project retirement

Post by 28fe6 »

Great! I added the NPER variant to my spreadsheet. I would not have thought to make the values negative.
User avatar
#Cruncher
Posts: 3109
Joined: Fri May 14, 2010 2:33 am
Location: New York City
Contact:

Re: Excel function to project retirement

Post by #Cruncher »

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.)
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. [*]

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
For the lender the price paid for the bond is negative and the coupon interest and redemption principal are positive. For the borrower the signs are reversed. However, as long as the signs have the same relative relationship (i.e., money coming in and going out have the opposite signs), the financial functions work fine as shown below:

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)
* This example is the first TIPS issued in 1997: the 5-year 3.625% TIPS maturing July 15, 2002. For simplicity my table assumes interest is paid at the end of each year instead of semi-annually. This causes my yield-to-maturity to be 3.745% instead of the actual 3.744%.
3.744% = 2 * RATE(5 * 2, 36.25 / 2, -994.62, 1000)
Post Reply