How do I spread a remainning balance of 11 mos gradually upward untill it is
gone with 1 known starting number, and an annual number.

How do I spread a remainning balance of 11 mos gradually upward untill
it is gone with 1 known starting number, and an annual number.

One way (among many possible) is illustrated in the csv file below.

------------------------------- cut here -------------------------------
Annual,10000
Jan,100
Feb,=B2+(B$1-12*B$2)/66
Mar,=B3+(B$1-12*B$2)/66
Apr,=B4+(B$1-12*B$2)/66
May,=B5+(B$1-12*B$2)/66
Jun,=B6+(B$1-12*B$2)/66
Jul,=B7+(B$1-12*B$2)/66
Aug,=B8+(B$1-12*B$2)/66
Sep,=B9+(B$1-12*B$2)/66
Oct,=B10+(B$1-12*B$2)/66
Nov,=B11+(B$1-12*B$2)/66
Dec,=B12+(B$1-12*B$2)/66
Total,=SUM(B2:B13)

It works great. Can you explain why mul 12, subs the princ., then div. By
66. Is this higher math ?? Is it possible to explain, I am intrigue. So I
guess it can not be done with a function.


"Jay" wrote:

How do I spread a remainning balance of 11 mos gradually upward untill
it is gone with 1 known starting number, and an annual number.

One way (among many possible) is illustrated in the csv file below.

------------------------------- cut here -------------------------------
Annual,10000
Jan,100
Feb,=B2+(B$1-12*B$2)/66
Mar,=B3+(B$1-12*B$2)/66
Apr,=B4+(B$1-12*B$2)/66
May,=B5+(B$1-12*B$2)/66
Jun,=B6+(B$1-12*B$2)/66
Jul,=B7+(B$1-12*B$2)/66
Aug,=B8+(B$1-12*B$2)/66
Sep,=B9+(B$1-12*B$2)/66
Oct,=B10+(B$1-12*B$2)/66
Nov,=B11+(B$1-12*B$2)/66
Dec,=B12+(B$1-12*B$2)/66
Total,=SUM(B2:B13)

"Thuy" wrote...
It works great. Can you explain why mul 12, subs the princ., then div. By
66. Is this higher math ?? Is it possible to explain, I am intrigue. So I
guess it can not be done with a function.

"Jay" wrote:
....
Annual,10000
Jan,100
Feb,=B2+(B$1-12*B$2)/66
....
Dec,=B12+(B$1-12*B$2)/66
Total,=SUM(B2:B13)

The amounts in months 2 through 12 are higher than the amount in month 1, so
the amounts for all 12 months include at least the month 1 amount. So the
remainder to be spread over months 2 through 12 is the original amount less
12 times the month 1 amount, B1-12*B2. As for the 66, the month 2 amount is
the month 1 amount plus the increment (B$1-12*B$2)/66. The month 3 amount is
the month 2 amount plus the same increment, which equals the month 1 amount
plus 2 times the same increment. The month 4 amount is the month 3 amount
plus the same increment, so the month 1 amount plus 3 times the same
increment. So the month M amount is always the month 1 amount plus (M-1)
times the same increment, or

Annual,10000
Jan,100,Increment,=(B1-12*B2)/66
Feb,=B$2+1*D$2
Mar,=B$2+2*D$2
Apr,=B$2+3*D$2
....
Nov,=B$2+10*D$2
Dec,=B$2+11*D$2
Total,=SUM(B2:B13)

SUM(B2:B13) == (B2) + (B2 + 1*D2) + (B2 + 2*D2) +...+ (B2 + 11*D2)
== B2 + B2 + B2 +...+ B2 + (0 + 1 + 2 +...+ 11)*D2
== 12*B2 + 66*D2
== 12*B2 + 66*(B1 - 12*B2)/66
== 12*B2 + B1 - 12*B2
== B1

The 66 is just the sum of 1 to 11. As for doing this with a function, use
SYD for months 2 through 12.

B3:
=SYD(B$1-12*B$2,0,11,ROWS(B3:B$13))+B$2

Fill B3 down into B4:B13. While it could be done using functions, this is
one instance in which simpler formulas, like Jay's, would be better.

It works great. Can you explain why mul 12, subs the princ., then
div. By 66. Is this higher math ??

High-school math, tops.

Start with a problem statement:

Total = January amount
+ January amount + increment
+ January amount + 2 * increment
+ ....
+ January amount + 11 * increment

and solve for "increment".

This approach assumes that you want the monthly increases to be linear.
Other approaches are possible if the monthly increases are supposed to
follow a different rule.


So I guess it can not be done with a function.

There are lots of approaches. I'm sure some would use a function. If
there's some reason why a function is required, please explain.