T
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.
gone with 1 known starting number, and an annual number.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
J
Jay
How do I spread a remainning balance of 11 mos gradually upward untill
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 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)
T
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.
66. Is this higher math ?? Is it possible to explain, I am intrigue. So I
guess it can not be done with a function.
H
Harlan Grove
Thuy said: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: ....
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.
J
Jay
It works great. Can you explain why mul 12, subs the princ., then
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.
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.
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.