Computing sum of progressively-increasing values in Excel

Question

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:

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?

Mathematically its: `initial_rent * ((1 + rate) ^ years)`, where rate is a decimal, eg `0.1` as in your example. — quamrana, Feb 06 '18 at 15:30
it's `sum(rent * 1.1**n for n in range(6))`. Does this help? — Ma0, Feb 06 '18 at 15:31
Possible duplicate of [How to do recurring deposit calculations accurately](https://stackoverflow.com/questions/23626271/how-to-do-recurring-deposit-calculations-accurately) — quamrana, Feb 06 '18 at 15:34
How are you going to point this as a dupe to a closed question? — K.Dᴀᴠɪs, Feb 08 '18 at 22:35

score 3 · Answer 1 · edited Feb 06 '18 at 15:57

If you have a financial problem, you might try the financial functions of excel.

=-FV(0.1, 6, 192000)

or

=FV(0.1, 6, -192000)

the detail: FV on Office Support

Description

FV, one of the financial functions, calculates the future value of an investment based on a constant interest rate. You can use FV with either periodic, constant payments, or a single lump sum payment.

Syntax

FV(rate, nper, pmt, [pv], [type])

For a more complete description of the arguments in FV and for more information on annuity functions, see PV.

The FV function syntax has the following arguments:

Rate Required

The interest rate per period.

Nper Required

The total number of payment periods in an annuity.

Pmt Required

The payment made each period; it cannot change over the life of the annuity. Typically, pmt contains principal and interest but no other fees or taxes. If pmt is omitted, you must include the pv argument.

Pv Optional

The present value, or the lump-sum amount that a series of future payments is worth right now. If pv is omitted, it is assumed to be 0 (zero), and you must include the pmt argument.

Type Optional

The number 0 or 1 and indicates when payments are due. If type is omitted, it is assumed to be 0.

I agree this is the easiest. For the benefit of future readers, would you please add a bit of explanation directly to the answer? That way it will still make sense even if the link becomes invalid (MS has been known to change its links ;) ). — cxw, Feb 06 '18 at 15:44
@cxw Thanks your advice. I repost necessary part of the MS link — Brett7533, Feb 06 '18 at 15:59

Rory Daulton · Accepted Answer · 2018-02-06T18:50:57.200

1

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.

edited Feb 06 '18 at 18:50

answered Feb 06 '18 at 15:39

Rory Daulton

21,934
6
42
50

This is the exact answer to this question, Explained perfectly in the context of mathematics. Thanks :) . Just a suggestion please change it to total = a * (r^n - 1) / (r - 1) . Meaning change ** to ^ – Count Feb 06 '18 at 17:44
@Count: I had considered that. I compromised just now, showing both versions. – Rory Daulton Feb 06 '18 at 18:52

J. Bakker · Answer 3 · 2018-02-06T15:56:42.883

1

As the question is about excel it is possible by

Or by using the FV function. FV returns the future value of an investment based on regular payments and a constant interest rate.

Attributes of the FV function;:

Rate: The interest rate per period.
Nper: The total number of payment periods in an annuity.
Pmt: The payment made each period; it cannot change over the life of the annuity. Typically, pmt contains principal and interest but no other fees or taxes. If pmt is omitted, you must include the pv argument.
Pv: The present value, or the lump-sum amount that a series of future payments is worth right now. If pv is omitted, it is assumed to be 0 (zero), and you must include the pmt argument.
Type: The number 0 or 1 and indicates when payments are due. If type is omitted, it is assumed to be 0.

edited Feb 06 '18 at 15:56

answered Feb 06 '18 at 15:40

J. Bakker

336
1
6

=B1*(1+B2) ^B3 not working for me . it gives 340139.7 for 6 years – Count Feb 06 '18 at 15:52
Perhaps B1* POWER(1+B2, B3) works. I was using libreoffice. Will test and update the screenshot – J. Bakker Feb 06 '18 at 15:55
sorry but =B1*POWER(1+B2,B3) also returns 340139 – Count Feb 06 '18 at 17:50

cxw · Answer 4 · 2018-02-06T15:51:24.153

Yet another way is computing it as a geometric series with the non-financial function SERIESSUM:

=SERIESSUM(1.1,0,1,192000*{1,1,1,1,1,1})

The rate multiplier is 1.1, starting from 1.1^0 == 1 and increasing by 1 each year. The result is 1*a + 1.1*b + 1.1^2*c.... The array 192000*{1,1,...} provides the coefficients a, b, c, ... : one array value for the initial total_rent = rent, and one for each subsequent year 1..5 (from range(1,year)).

Computing sum of progressively-increasing values in Excel

4 Answers4

Description

Syntax