Search code examples
excelmathworksheet-functionfinancial

Computing sum of progressively-increasing values in Excel

I am trying to solve an iterative problem in Excel. I want to be able to calculate the sum of rent for x years. The rent is increasing at a rate of 10 percent every year. I quickly came up with this python code on a REPL for clarity:

year = 6
rent = 192000
total_rent = rent
for x in range(1 , year):
    rent= rent + .1*rent
    total_rent = total_rent + rent
print(total_rent) # 1481397.12 is what it prints

This is a trivial problem in programming but I am not sure the best way to achieve this in excel.

In excel I am doing it this something like this: enter image description here

But all the intermediate rent amount(s) are not really needed. I guess there should be a for loop here as well too, but is there a mathematical representation of this problem which I can use to create the expected result?

Solution

  • Your problem is a geometric series where the initial term is a = 192000 and the common ratio is r = 1.1. (The ratio is not just the 10% added, it includes the 100% that is added to.) To refresh your Algebra II memory, a geometric series is

    total = a + a*r + a*r**2 + ... + a*r**(n-1)

    The closed-form formula for the sum of the geometric series is

    total = a * (r**n - 1) / (r - 1)

    (using Python syntax), or, using something closer to Excel syntax,

    total = a * (r^n - 1) / (r - 1)

    where n is the number of years. Just substitute your values for a, r, and n.