Which function to use?

[Thuy]

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.

[Jay]

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

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

[Harlan Grove]

"Thuy" <Thuy@discussions.microsoft.com> 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.

[Jay]

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