CODEWITHSUNDEEP
excelmathworksheet-functionfinancial
Count
Count

Reputation: 1423

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?

Upvotes: 0

Views: 1963

Answers (4)

Rory Daulton
Rory Daulton

Reputation: 22544

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.

Upvotes: 1

Brett7533
Brett7533

Reputation: 342

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.

Upvotes: 3

J. Bakker
J. Bakker

Reputation: 356

As the question is about excel it is possible by

excel math solution

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.

Upvotes: 1

cxw
cxw

Reputation: 17041

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)).

Upvotes: 0

Related Questions