1

I'd like help with a formula for calculating Future Value given the following:

An amount is invested at the END of every year, starting with amount of PMT at the end of this year, at an interest rate of i% per year, compounded annually,the investment amount doubles every second year (cumulatively).

I'm finding it hard to wrap my head around the last bit, "investment amount doubles every second year" as the Future Value of growing annuity formula make provisions for investment that grows per year and not per second year, how can I proceed?

  • This would be easier to answer if you included more of your work. For example, have you worked out the formula that you would use if it doubled every year? Have you worked out how it would change if the interest rate were i% per half year compounded every half year? As is, I would have to first either look up or work out the formula and then figure out how to modify it. I'm lazy. I'd be far more likely to try to help if could just extend or critique your work. – Brythan Sep 21 '18 at 1:42
  • The investment does grow every year, that's what "compounded annually" means. The other information tells you that in two years the contribution must double (quite unrealistic, that requires growth of 41% per year). – Ben Voigt Sep 21 '18 at 2:22
  • Does “investment amount” refer to the principal invested? If they are investing at the end of “every year”, the principal indeed doubles at the second year - but assuming a two-yearly doubling in perpetuity would be unrealistic. – Lawrence Sep 21 '18 at 11:59
1

Here is a formula for the balance at the beginning of year n

balance = ((1 + i)^n (3 + i) pmt + (1 + i) (-2^Floor[(1 + n)/2] - 
            2^Floor[n/2] (1 + i)) pmt)/(-1 + i + 3 i^2 + i^3)

The Floor function is available in Excel. This is an Excel implementation.

enter image description here

A version without use of Floor is

balance = ((1 + i)^n (3 + i) pmt + 2^(1/4 (-1)^n (-1 + (-1)^n (-1 + 2 n))) (1 + i) 
        (-2^(1/2 (-1)^(2 n)) - 2^((-1)^n/2) (1 + i)) pmt)/(-1 + i + 3 i^2 + i^3)

Formula derivation

Here is the derivation. With interest i at 10% and initial pmt.

i   = 0.1
pmt = 100

The balance at the beginning of year 1 is 0. At the end of year 1 a payment is made so the balance at the beginning of year 2 is pmt. The next payment (the second) is double, etc.

p1 = 0
p2 = p1 (1 + i) + pmt
p3 = p2 (1 + i) + 2*pmt
p4 = p3 (1 + i) + 2*pmt
p5 = p4 (1 + i) + 2^2*pmt
p6 = p5 (1 + i) + 2^2*pmt
p7 = p6 (1 + i) + 2^3*pmt
p8 = p7 (1 + i) + 2^3*pmt
p9 = p8 (1 + i) + 2^4*pmt = 5437.33
p10 = p9 (1 + i) + 2^4*pmt = 7581.06

Another way of expressing p10 is

p[9 + 1] = p[9](1 + i) + 2^Floor[9/2]*pmt

So this can be solved using Mathematica

RSolve[{p[n + 1] == p[n] (1 + i) + 2^Floor[n/2]*pmt, p[1] == 0}, p[n], n]

enter image description here

The formula correctly calculates the balance at the beginning of the specified year.

p[n] = ((1 + i)^n (3 + i) pmt + (1 + i) (-2^Floor[(1 + n)/2] - 
         2^Floor[n/2] (1 + i)) pmt)/(-1 + i + 3 i^2 + i^3)

p[9]  = 5437.33
p[10] = 7581.06

The floor functions can be replaced

Floor[(1 + n)/2] = 1/4 (-1)^n (-1 + (-1)^n + 2 (-1)^n n)

and Floor[n/2] = 1/4 (-1)^n (1 + (-1)^(1 + n) + 2 (-1)^n n)

giving

p[n] = ((1 + i)^n (3 + i) pmt + 2^(1/4 (-1)^n (-1 + (-1)^n (-1 + 2 n))) (1 + i) 
        (-2^(1/2 (-1)^(2 n)) - 2^((-1)^n/2) (1 + i)) pmt)/(-1 + i + 3 i^2 + i^3)

For example, the balance at the beginning of year 9

i   = 0.1
pmt = 100
n   = 9

balance = ((1 + i)^n (3 + i) pmt + 2^(1/4 (-1)^n (-1 + (-1)^n (-1 + 2 n))) (1 + i) 
        (-2^(1/2 (-1)^(2 n)) - 2^((-1)^n/2) (1 + i)) pmt)/(-1 + i + 3 i^2 + i^3)
= 5437.33

Alternative method

The same solution can also be found as the closed form of a summation.

enter image description here

The summation for the example would look like this

i   = 0.1
pmt = 100
n   = 9

balance = 2^0 pmt (1 + i)^(n - 1 - 1) +
          2^1 pmt (1 + i)^(n - 2 - 1) + 2^1 pmt (1 + i)^(n - 3 - 1) +
          2^2 pmt (1 + i)^(n - 4 - 1) + 2^2 pmt (1 + i)^(n - 5 - 1) +
          2^3 pmt (1 + i)^(n - 6 - 1) + 2^3 pmt (1 + i)^(n - 7 - 1) +
          2^4 pmt (1 + i)^(n - 8 - 1)
= 5437.33

Basic future value calculation

The above contrasts with the basic calculation without any doubling like so.

i   = 0.1
pmt = 100
n   = 9

Iterative calculation

p1 = 0
p2 = p1 (1 + i) + pmt
p3 = p2 (1 + i) + pmt
p4 = p3 (1 + i) + pmt
p5 = p4 (1 + i) + pmt
p6 = p5 (1 + i) + pmt
p7 = p6 (1 + i) + pmt
p8 = p7 (1 + i) + pmt
p9 = p8 (1 + i) + pmt = 1143.59

Summation calculation

balance = pmt (1 + i)^0 +
          pmt (1 + i)^1 + pmt (1 + i)^2 +
          pmt (1 + i)^3 + pmt (1 + i)^4 +
          pmt (1 + i)^5 + pmt (1 + i)^6 +
          pmt (1 + i)^7
= 1143.59

Formula derivation

enter image description here

balance = ((-1 + (1 + i)^(-1 + n)) pmt)/i = 1143.59
  • Brilliant!You're a freakonomist!Thanks, this helped a lot. – MikaEl Sep 24 '18 at 6:28
0

If the goal is to get an analytical solution, you can calculate the future value of the investments made in odd and even years separately, and then add them. Each can apply the standard valuation formula in terms of 2-year intervals rather than 1-year intervals, since the unit of time is arbitrary. The investment amount increases by 100% each interval, and the effective interest rate is (1 + 0.01i)^2 - 1. Likewise the time would be measured in 2-year intervals.

0

I came up with a version of this that gets me the incorrect answer, can anyone point me towards where I'm going wrong?

def investment(PMT, n, i):
    for x in range(1,n):
        if x==2:
            invest_even=PMT*2**(x/2)
            FV_even= invest_even*((1+i)-1)/i
        else: 
            invest_odd=PMT*2**((x-1)/2)     
            FV_odd=invest_odd*((1+i)-1)/i
        x+=1
        investment_balance=(FV_even+FV_odd)
        return round(investment_balance, 2)

Answer in Mathematica code

i = 0.1;
pmt = 100;
n = 9;

fvOdd = fvEven = 0;

For[x = 1, x < n, x += 1,
  If[Mod[x, 2] == 0,
   investEven = pmt*2^(x/2);
   fvEven += investEven*(1 + i)^(n - x - 1)
   ,
   investOdd = pmt*2^((x - 1)/2);
   fvOdd += investOdd*(1 + i)^(n - x - 1)
   ]
  ]

balance = fvOdd + fvEven

5437.33

  • Hi, You are not compounding your future values correctly. You need something like FV_even= invest_even*(1 + i)^(n - x - 1). For example, the first payment on x = 1 needs to be compounded for seven years to obtain the future value at the beginning of year 9. Also testing x==2 to check for an even year will only work once. You can use a modulo 2 to check whether x is odd or even. I have added a version of your program in Mathematica to show you how it can be done. – Chris Degnen Sep 27 '18 at 8:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.