Search code examples
algorithmsearchbinaryfinance

Designing an algorithm for retirement planning, searching for retirement rate %

My client is a financial advisor and helps people create retirement plans. His current process is to take all their financial data, type them into a spreadsheet and, based on the retiree's goals, discover the rate at which they can withdraw from their savings/assets/investments (as a percentage). That percentage is the solution to a problem, and the way he finds it now is to guess at it (e.g. "Let's try 5% (too high). OK, how about 1% (too low). Hmm, 2.5%? (too high) ... until he finds the percentage that satisfies the conditions of the retiree).

If I were to program this exactly how he does it, then I think it's just a binary search algorithm. But it feels like there's a more clever way to do this. He's basically using the compound interest formula, A=P(1+r/n)^nt, to discover 'r' in that equation, but it has to be done over a period of several decades, and each year requires about a dozen calculations in the backend. So roughly a dozen, times maybe 30 years equals ~300 calculations for one iteration of the binary search.

Sorry if this isn't detailed enough, but to be more specific requires and exhaustive level of detail.

Has anyone, perhaps someone in the financial sector, dealt with this kind of search?

Solution

  • Sorry if I've misunderstood what you're asking. If it's just to find r in the formula you give:

    A = P (1 + r / n) ^ nt
A/P = (1 + r / n) ^ nt
log_nt A/P = 1 + r / n
log_nt A/P - 1 = r / n
n (log_nt A/P - 1) = r

    More generally, if you think you might be able to get a closed-form solution, you should try really hard to write down your model and equations in such a way that you can find such a solution.

    Some benefits to this approach:

    1. The result is easier to implement and to understand. You can put the derivation of the formula in comments if you like.
    2. The result is virtually guaranteed to be more precise than what you'd get from a search technique.
    3. It will run fast.

    Here's how I might model the problem:

    P: principal
r: monthly interest gained on principal
w: amount withdrawn from principal
T: number of months over which the principal is to be withdrawn
B(t): balance at time 0 <= t <= T
B(0) = P
B(T) = 0

    We want to find w. We write our recurrences:

    B(0) = P
B(t+1) = B(t) * r - w

    We can write out a few terms:

    B(0) = P
B(1) = P * r - w
B(2) = (P * r - w) * r - w = P * r^2 - wr - w
B(3) = (P * r^2 - wr - w) * r - w = P * r^3 - wr^2 - wr - w
...
B(t) = P * r^t - w(r^(t-1) + r^(t-2) + ... + 1)
     = P * r^t - w(r^t - 1)/(r - 1)

    Now we set B(T) = 0 assuming we want the money to run out, then solve for w:

    0 = B(T) = P * r^T - w(r^T - 1)/(r - 1)
w(r^T - 1)/(r - 1) = P * r^T
w = P * r^T * (r - 1) / (r^T - 1)

    Suppose that P = $1,000,000, r = 1.0025 (just above 3% annually), and T = 360 (retirement savings to last 30 years). Then we have

    w = $1,000,000 * 1.0025^360 * (1.0025 - 1) / (1.0025 ^ 360 - 1)
  = $4,216

    If you want to model the situation differently, you need only write it down and follow the same steps as this. With any luck, your model will have some closed form solution, as the two problems I've solved in this answer did.