Author Topic: deleted  (Read 2192 times)

henders

  • Guest
deleted
« on: December 17, 2014, 08:54:37 PM »
deleted
« Last Edit: June 26, 2017, 09:41:48 PM by henders »

MDM

  • Walrus Stache
  • *******
  • Posts: 9108
Re: Excel calculate interest on regular investment
« Reply #1 on: December 18, 2014, 12:30:15 AM »
You are seeking the "future value of a growing annuity due."  E.g., see http://en.wikipedia.org/wiki/Time_value_of_money, http://www.had2know.com/finance/future-value-growing-annuity.html, etc.

From the wiki article: "For calculations involving annuities, you must decide whether the payments are made at the end of each period (known as an ordinary annuity), or at the beginning of each period (known as an annuity due)."  E.g. home mortgages are usually paid at the end of a period (and are thus "ordinary annuities") while investments are usually assumed to happen at the beginning (and are thus "annuities due").

Unless otherwise noted, an annuity formula is usually based on an ordinary annuity.  You have to multiply by (1 + i), where i is the interest rate, to convert to an annuity due formula.

FV = Future Value
P = amount of the initial payment, $
N = number of payments (or number of years, etc.)
g = payment growth rate (decimal)
r = annual percentage rate (decimal)

FV = P[(1+r)^N - (1+g)^N]/(r-g) * (1+r)      This is the "ordinary annuity" formula multiplied by (1+r) to get the "annuity due" formula.

For your example,
P = $10,000
N = 10
g = 0.25
r = 0.07

FV = $10,000 * (1.07^10 - 1.25^10)/(.07 - .25) * 1.07 = $436,683.

To check this, calculate the results of each year's investment.  The first, $10,000, compounds at 7% for 10 years.  The second, $12,500, compounds at 7% for 9 years, and so on.
InvestmentYears7%
$10,000 10 $19,672
$12,500 9 $22,981
$15,625 8 $26,847
$19,531 7 $31,363
$24,414 6 $36,639
$30,518 5 $42,802
$38,147 4 $50,003
$47,684 3 $58,415
$59,605 2 $68,241
$74,506 1 $79,721
$436,683


Sorry - hope you weren't counting on the half billion dollars. ;)

AJDZee

  • Stubble
  • **
  • Posts: 239
Re: Excel calculate interest on regular investment
« Reply #2 on: December 18, 2014, 12:47:34 PM »

Sorry - hope you weren't counting on the half billion dollars. ;)

haha that made me smile.

Also, OP, are you sure you want to have your contributions as "125% of previous year?"
Don't know your situation, but it seems odd that your contributions would be able to continually compound by 25% year after year....  you see how MDM's table shows you are making a contribution of $75k in your 10th year.

AJDZee

  • Stubble
  • **
  • Posts: 239
Re: Excel calculate interest on regular investment
« Reply #3 on: December 18, 2014, 12:59:15 PM »
Also, thank you MDM for spelling out the formula... I put together a lot of these excel tables, but I do so without the formula, but all my tables look something like below and with your example, I get close to yours ($422,399)...

Year  |  Start Balance  |  Contribution  |    ROI    |  End Balance  |
2014                 $0                $10,000       $350           $10,350
2015        $10,350                $12,500    $1,162           $24,012
etc...

Where...
Year: calendar year or (years from now)
Start Balance: balance at start of the year = End balance of previous year
Contributions: the money you will be contributing annually (usually assume regular intervals)
ROI: (start balance x 7%) + (contributions x 7%)/2
End Balance: balance at end of the year = start balance + contributions + ROI


My way errors on the low side, I'm assuming because it's not factoring in the incremental ROI gains will themselves product gains.

I should probably evaluate how large a discrpancy my way introduces when extrapolated over 30 years   haha

MDM

  • Walrus Stache
  • *******
  • Posts: 9108
Re: Excel calculate interest on regular investment
« Reply #4 on: December 18, 2014, 01:13:52 PM »
My way errors on the low side, I'm assuming because it's not factoring in the incremental ROI gains will themselves product gains.

No errors, just slightly different assumptions. 

With "annuity due" the assumption is that the payments occur at the beginning of each period.  With "ordinary annuity" the assumption is that the payments occur at the end of each period.  Your table assumes the payments occur in the middle of each period.  Any of the above could be true for a given situation.

For a 7% interest, the difference between ordinary annuity and annuity due is 1.07.  Yours differs by ~1.035, or ~(1 + 7%/2) - not surprising given the assumptions.