-1

First, I apologize if the title is confusing or if there is a better wording. I am unfamiliar with many finance terms, so there may be something accurately describes my problem.

I am trying to calculate how to disburse equally over a period of time. Ordinarily, this would be easy. If I had $100,000 to disburse over 10 years, it would work out to disbursements of $10,000/yr.

Unfortunately, I am also trying to factor in the growth of the balance. For example, if I wanted to disburse that same $100,000 over two years and it grew at 6% APY (see some assumptions below), then I would disburse $51,456.31 each year. After the first year, the balance would be $48,543.69, which would then grow to $51,456.31 after the 6% APY. This makes for equal disbursements of an appreciating asset over two years.

ASSUMPTIONS: I am assuming that the disbursement is made lump-sum at the beginning of the year. In the previous example, this means $51,456.31 is removed on January 1st and does not appreciate in value.

Unfortunately, there is no good LaTeX input for math here, but my formula for the two-year scenario is:

w = (1 + r) * (b - w)

where 'w' is the yearly withdrawal amount, 'r' is the APY, and 'b' is the initial balance. The two-year scenario is easy because the withdrawal in the second year (the entire remaining balance with growth) must equal the initial withdrawal amount.

When I try to extend this to a three-year scenario, I get

3 * w = b + r * (b - w) + r * (r * (b - w) - w)
3 * w = b + r * (b - w) + r^2 * (b - w) - r * w
3 * w = b + r * b - r * w + r^2 * b - r^2 - w - r * w
3 * w = b + b * (r + r^2) - w * (2 * r + r^2)
3 * w + w * (2 * r + r^2) = b + b * (r + r^2)
w * (3 + 2 * r + r^2) = b + b * (r + r^2)
w = (b + b * (r + r^2)) / (3 + 2 * r + r^2)

Unfortunately, this seems to be wrong since given the same values as above, it produces a result of $34,050.45, but evaluating manually shows the correct value to be $35,293.38.

Even extending that formula would be difficult after the three-year simulation. Is there a better way to do this?

Dheer
  • 57,070
  • 18
  • 88
  • 169
Hari
  • 419
  • 2
  • 12

2 Answers2

4

I frequently use Excel's PMT() function for calculating loan payment amounts, it can also be used to calculate annuity payments like you describe, the last parameter supplied allows you to adjust for payments at beginning or end of a term.

3 years with your scenario the formula would look like this:

=PMT(rate, nper, pv, fv, [type])
=PMT(0.06,3,100000,0,1)

At 10 years I get $12,817.73/year

If you want your own implementation, it's an annuity due payment formula:

Annuity Due Payment Formula Formula source: FinanceFormulas.net

So that would be:

Pmt = 100,000 * (.06/(1-(1+.06)^-10))*(1/(1+.06))
Pmt = 100,000 * (.06/(1-.5584))*(.9434)
Pmt = 100,000 * (.06/.4416)*(.9434)
Pmt = 100,000 * (.1359)*(.9434)
Pmt = 12,820  --Without rounding matches PMT() function

Note: If calculating monthly payment instead of yearly, use the annual interest rate divided by 12 and multiply years by 12.

Hart CO
  • 70,420
  • 9
  • 171
  • 214
  • Conveniently I'm working within Excel, so the PMT() function is perfect; however, I really appreciate that you also provided the actual name of the formula I was looking for. Surprisingly, some of the latter guesses I made got pretty close to that! – Hari Nov 06 '18 at 16:49
-1

See if this answer makes you happy: (B + 2 * R * B + B * R^2 ) / (3 + 3 * R + R^2 )

If it does, I'll sort it out tomorrow because it's late and it's time to count sheep (your initial equation is incorrect).

And no, I don't know how to extend it since "A man's got to know his limitations".

Bob Baerker
  • 76,304
  • 15
  • 99
  • 175