Calculating monthly growth percentage from cumulative total growth

Question

I am trying to calculate a constant for month-to-month growth rate from an annual growth rate (goal) in Python.

My question has arithmetic similarities to this question, but was not completely answered.

For example, if total annual sales for 2018 are $5,600,000.00 and I have an expected 30% increase for the next year, I would expect total annual sales for 2019 to be $7,280,000.00.

BV_2018 = 5600000.00
Annual_GR = 0.3
EV_2019 = (BV * 0.3) + BV

I am using the last month of 2018 to forecast the first month of 2019

Last_Month_2018 = 522000.00  
Month_01_2019 = (Last_Month_2018 * CONSTANT) + Last_Month_2018

For the second month of 2019 I would use

Month_02_2019 = (Month_01_2019 * CONSTANT) + Month_01_2019

...and so on and so forth

The cumulative sum of Month_01_2019 through Month_12_2019 needs to be equal to EV_2019.

Does anyone know how to go about calculating the constant in Python? I am familiar with the np.cumsum function, so that part is not an issue. My problem is I cannot solve for the constant I need.

Thank you in advance and please do not hesitate to ask for further clarification.

More clarification:

# get beginning value (BV)
BV = 522000.00

# get desired end value (EV)
EV = 7280000.00

We are trying to get from BV to EV (which is a cumulative sum) by calculating the cumulative sum of the [12] monthly totals. Each monthly total will have a % increase from the previous month that is constant across months. It is this % increase that I want to solve for.

Keep in mind, BV is the last month of the previous year. It is from BV that our forecast (i.e., Months 1 through 12) will be calculated. So, I'm thinking that it makes sense to go from BV to the EV plus the BV. Then, just remove BV and its value from the list, giving us EV as the cumulative total of Months 1 through 12.

I imagine using this constant in a function like this:

def supplier_forecast_calculator(sales_at_cost_prior_year, sales_at_cost_prior_month, year_pct_growth_expected):
    """
    Calculates monthly supplier forecast

    Example:

    monthly_forecast = supplier_forecast_calculator(sales_at_cost_prior_year = 5600000,
                                                sales_at_cost_prior_month = 522000,
                                                year_pct_growth_expected = 0.30)

    monthly_forecast.all_metrics
    """

    # get monthly growth rate
    monthly_growth_expected = CONSTANT

    # get first month sales at cost
    month1_sales_at_cost = (sales_at_cost_prior_month*monthly_growth_expected)+sales_at_cost_prior_month

    # instantiate lists
    month_list = ['Month 1'] # for months
    sales_at_cost_list = [month1_sales_at_cost] # for sales at cost

    # start loop
    for i in list(range(2,13)):
        # Append month to list
        month_list.append(str('Month ') + str(i))
        # get sales at cost and append to list
        month1_sales_at_cost = (month1_sales_at_cost*monthly_growth_expected)+month1_sales_at_cost
        # append month1_sales_at_cost to sales at cost list
        sales_at_cost_list.append(month1_sales_at_cost)

    # add total to the end of month_list
    month_list.insert(len(month_list), 'Total')

    # add the total to the end of sales_at_cost_list
    sales_at_cost_list.insert(len(sales_at_cost_list), np.sum(sales_at_cost_list))

    # put the metrics into a df
    all_metrics = pd.DataFrame({'Month': month_list,
                            'Sales at Cost': sales_at_cost_list}).round(2)

    # return the df
    return all_metrics

Possible duplicate of [Elegant pythonic cumsum](https://stackoverflow.com/questions/9258602/elegant-pythonic-cumsum) — Eb946207, Dec 04 '18 at 23:49
The sum of a geometric series is a one-line equation from high-school algebra; I assume you've look up that one. The harder problem is using that to zero in on the calculation for the missing constant, your monthly interest rate. Here, the question to which you linked *does* include some help, but in the links to one answer: Newton-Raphson iteration will solve it, or simple bisection of the interval. Where, exactly, are you stuck? — Prune, Dec 05 '18 at 00:10
@EthanK: it's a quick solution to the cumulative sum, but the root problem is finding that missing constant. — Prune, Dec 05 '18 at 00:11
@Prune Oh, got it. But wouldn't finding CONSTANT or the formula to find it be a better question on https://math.stackexchange.com, and then after finding that out to try to make the program to solve for it? (and if not, ask a question of how to do it in python) — Eb946207, Dec 05 '18 at 00:15
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/184731/discussion-between-prune-and-ethan-k). — Prune, Dec 05 '18 at 00:44
This question my be more suited for MathStackExchange, but I figured I would see if there was a library in Python for this calculation before reaching out to MathStackExchange. I get stuck when I try to solve this equation: Sn * g = Co(1 + g)**n - Co — Aaron England, Dec 05 '18 at 01:06
You have `(1 + Annual_GR) == (1 + CONSTANT)**12` so `CONSTANT == (1 + Annual_GR)**(1/12) - 1`. — hilberts_drinking_problem, Dec 05 '18 at 01:59
@YakymPirozhenko that formula yields a constant of 0.0221 which is slightly smaller than necessary. The constant in the example provided should be in the ballpark of 0.0229. That number (0.0229) was found by trial and error, not mathematically. I want to calculate that constant mathematically. — Aaron England, Dec 05 '18 at 02:14
@AaronEngland I think I finally managed to read the question correctly. See if the proposed answer addresses your issue. In principle, you should be able to implement a solution by radicals. There may be packages to compute roots of cyclotomic polynomials. — hilberts_drinking_problem, Dec 05 '18 at 03:03

hilberts_drinking_problem · Accepted Answer · 2018-12-05T22:22:33.363

Let r = 1 + monthly_rate. Then, the problem we are trying to solve is

r + ... + r**12 = EV/BV. We can use numpy to get the numeric solution. This should be relatively fast in practice. We are solving a polynomial r + ... + r**12 - EV/BV = 0 and recovering monthly rate from r. There will twelve complex roots, but only one real positive one - which is what we want.

import numpy as np

# get beginning value (BV)
BV = 522000.00

# get desired end value (EV)
EV = 7280000.00


def get_monthly(BV, EV):
    coefs = np.ones(13)
    coefs[-1] -= EV / BV + 1
    # there will be a unique positive real root
    roots = np.roots(coefs)
    return roots[(roots.imag == 0) & (roots.real > 0)][0].real - 1


rate = get_monthly(BV, EV)
print(rate)
# 0.022913299846925694

Some comments:

roots.imag == 0 may be problematic in some cases since roots uses a numeric algorithm. As an alternative, we can pick a root with the least imaginary part (in absolute value) among all roots with a positive real part.
We can use the same method to get rates for other time intervals. For example, for weekly rates, we can replace 13 == 12 + 1 with 52 + 1.
The above polynomial has a solution by radicals, as outlined here.

Update on performance. We could also frame this as a fixed point problem, i.e. to look for a fixed point of a function

x = EV/BV * x ** 13 - EV/BV + 1

The fix point x will be equal to (1 + rate)**13.

The following pure-Python implementation is roughly four times faster than the above numpy version on my machine.

def get_monthly_fix(BV, EV, periods=12):
    ratio = EV / BV
    r = guess = ratio
    while True:
        r = ratio * r ** (1 / periods) - ratio + 1
        if abs(r - guess) < TOLERANCE:
            return r ** (1 / periods) - 1
        guess = r

We can make this run even faster with a help of numba.jit.

Wow. I don’t understand that math. Good job! +1 – Eb946207 Dec 05 '18 at 15:00 — Eb946207, Dec 05 '18 at 15:00

score 1 · Answer 2 · answered Dec 05 '18 at 02:14

1

I am not sure if this works (tell me if it doesn't) but try this.

def get_value(start, end, times, trials=100, _amount=None, _last=-1, _increase=None):
    #don't call with _amount, _last, or _increase! Only start, end and times
    if _amount is None:
        _amount = start / times
    if _increase is None:
        _increase = start / times

    attempt = 1
    for n in range(times):
        attempt = (attempt * _amount) + attempt
    if attempt > end:
        if _last != 0:
            _increase /= 2
            _last = 0
        _amount -= _increase
    elif attempt < end:
        if _last != 1:
            _increase /= 2
            _last = 1
        _amount += _increase
    else:
        return _amount

    if trials <= 0:
        return _amount

    return get_value(start, end, times, trials=trials-1,
             _amount=_amount, _last=_last, _increase=_increase)

Tell me if it works.

Used like this:

get_value(522000.00, 7280000.00, 12)

answered Dec 05 '18 at 02:14

Eb946207

748
8
26

Thank you, but that yields 2.73. I'm looking for something around 0.0229 – Aaron England Dec 05 '18 at 02:21
This is over a year with 12 increments in that year, right? – Eb946207 Dec 05 '18 at 02:41
Yes, 12 months. Thank you – Aaron England Dec 05 '18 at 02:44
Oh, I may have found my problem. I was solving for the first month of 2018 to the first of 2019. Do you want the last of 2018 to the first of 2019? Because if so I will have to tweak my code, not debug it. – Eb946207 Dec 05 '18 at 02:48
But whatever the case, I can't work on it right now. Tomorrow I will keep trying. :) Goodbye for now! – Eb946207 Dec 05 '18 at 02:49

Calculating monthly growth percentage from cumulative total growth

2 Answers2