-2

In Google Sheets, I know the formula for compound interest, for example:

10000*(1+10%)^n

10000: start-up cash

10%: interest rate

n: interest cycle

But what if I also add 10000 cash each month? What will the formula look like?

The interest cycle is per month, and I'm also only looking for per-month basis result

2 Answers 2

2

Use reduce(), like this:

=let( 
  start, 10000, 
  increment, 10000, 
  percent, 10%, 
  n, 12, 
  reduce(start, sequence(n), lambda(acc, i, 
    (acc + increment) * (percent + 100%) 
  )) 
)

For a step-to-step breakdown of how the result progresses from period to period, replace reduce() with scan(), or use this formula that creates a two-column table to show how it goes:

=let( 
  start, 10000, 
  increment, 10000, 
  percent, 10%, 
  n, 12, 
  reduce({ start, 0 }, sequence(n), lambda(acc, i, let( 
    principal, single(chooserows(acc, -1)), 
    newPrincipal, (principal + increment) * (percent + 100%), 
    vstack( 
      acc, 
      hstack(newPrincipal, i) 
    ) 
  ))) 
)
principal period
10,000.00 0
22,000.00 1
35,200.00 2
49,720.00 3
65,692.00 4
83,261.20 5
102,587.32 6
123,846.05 7
147,230.66 8
172,953.72 9
201,249.10 10
232,374.00 11
266,611.41 12

See let(), reduce() and sequence().

5
  • The for replying! The formula is amazing, but I think there is some misunderstanding maybe? May 13 at 23:31
  • For example, the first month is 10000 * 1+10%, and the second month is the previous (answer +10000)*(1+10%), so from the formula the result is not the same as my manual calculation May 13 at 23:33
  • For ref., the answer I got from the formula after 12 months is 62768.6, but from my manual calculation is 235227 May 13 at 23:35
  • Edited the answer to fix a typo and add a breakdown. May 14 at 7:43
  • close enough, that's amazing! May 15 at 8:12
1

TL;DR

=INDEX(FV(10%,SEQUENCE(12),-10000,,1)) 

FV Function

Sheets' FV function calculates the future value of an investment, based on an initial investment, ongoing contributions, or both.

Syntax: 
FV(rate, periods, pmt_amt,[present_value],[end_or_beginning])

Single Result

  1. Lump Sum
    =FV(rate,periods,,present_value,1)
    =FV(10%,12,,-10000,1)
    =31,384.28
    
  2. Monthly Contributions
    =FV(rate,periods,pmt_amt,,1)
    =FV(10%,12,-10000,,1)
    =235,227.12
    

Array of Results

The FV function is placed inside an array function and the periods' argument is supplied as an array comprised of a sequence of numbers from 1-12:

=SEQUENCE(12)
={1;2;3;4;5;6;7;8;9;10;11;12}
  1. Lump Sum
    =INDEX(FV(rate,{periods},,present_value,1))
    =INDEX(FV(10%,SEQUENCE(12),,-10000,1)) 
    ={11000;12100;13310;14641;16105.10;17715.61;19487.17; 
      21435.89;23579.48;25937.42;28531.17;31384.28} 
    
  2. Monthly Contributions
    =INDEX(FV(rate,SEQUENCE(PERIODS),pmt_amt,,1))
    =INDEX(FV(10%,SEQUENCE(12),-10000,,1))  
    ={11000; 23100;36410;51051;67156.10;84871.71; 104358.88;
      125794.77; 149374.25; 175311.67; 203842.84; 235227.12} 
    
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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