software

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.

gt; How do I spread a remainning balance of 11 mos gradually upward untill
gt; 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.quot;Jayquot; wrote:

gt; gt; How do I spread a remainning balance of 11 mos gradually upward untill
gt; gt; it is gone with 1 known starting number, and an annual number.
gt;
gt; One way (among many possible) is illustrated in the csv file below.
gt;
gt; ------------------------------- cut here -------------------------------
gt; Annual,10000
gt; Jan,100
gt; Feb,=B2 (B$1-12*B$2)/66
gt; Mar,=B3 (B$1-12*B$2)/66
gt; Apr,=B4 (B$1-12*B$2)/66
gt; May,=B5 (B$1-12*B$2)/66
gt; Jun,=B6 (B$1-12*B$2)/66
gt; Jul,=B7 (B$1-12*B$2)/66
gt; Aug,=B8 (B$1-12*B$2)/66
gt; Sep,=B9 (B$1-12*B$2)/66
gt; Oct,=B10 (B$1-12*B$2)/66
gt; Nov,=B11 (B$1-12*B$2)/66
gt; Dec,=B12 (B$1-12*B$2)/66
gt; Total,=SUM(B2:B13)
gt;

quot;Thuyquot; gt; wrote...
gt;It works great. Can you explain why mul 12, subs the princ., then div. By
gt;66. Is this higher math ?? Is it possible to explain, I am intrigue. So I
gt;guess it can not be done with a function.
gt;
gt;quot;Jayquot; wrote:
....
gt;gt;Annual,10000
gt;gt;Jan,100
gt;gt;Feb,=B2 (B$1-12*B$2)/66
....
gt;gt;Dec,=B12 (B$1-12*B$2)/66
gt;gt;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.
gt; It works great. Can you explain why mul 12, subs the princ., then
gt; 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 quot;incrementquot;.

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