Excel function to project retirement

[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]

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]

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]

Or use the formula: C10 =FV(D$2,B10-B$4,-E$4,-C$4)

[28fe6]

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.

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.

[FiveK]

See the 'Misc. calcs' tab in the personal finance toolbox spreadsheet, rows 19-25.

[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%.

[28fe6]

Great! I added the NPER variant to my spreadsheet. I would not have thought to make the values negative.

[#Cruncher]

(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)