Calculating cumulative payments with regular increases

kwestman

New Member
If I had rental income from a house, payment x, and the rent increased y% annually over z years, how can I calculate the cumulative rental income in one formula?

As an example, if the rent was 1000, and annual increases were 3%, I would use:
Cumulative rental income = (1000 * 1+0.03^0) + (1000 * 1+0.03^1) + (1000 * 1+0.03^2)... + (1000 * 1+0.03^5).

How can I automate that in ONE formula where the inputs are just X, Y, and Z?

I suspect it will use one of the financial formulas.

Thank you for your help!
Kellen
 

joeu2004

Well-known Member
Cumulative rental income = (1000 * 1+0.03^0) + (1000 * 1+0.03^1) + (1000 * 1+0.03^2)... + (1000 * 1+0.03^5)
With annual payment in X1 (1000), annual percentage increase in Y1 (3%), and number of years in Z1 (6):

=X1*SUMPRODUCT((1+Y1)^(ROW(X1:INDEX(X:X,Z1,1))-1))
or
=FV(Y1,Z1,-X1)

probably formatted as Currency.

Obviously, the second formula better. The first formula is provided as "proof of concept"; it is the same as your method of calculation.

Question: is the rent truly 1000 per year(!), the same frequency as the annual increases? Or is it 1000 per month, with annual increases?
 
Last edited:

kwestman

New Member
This is exactly the answer I needed, thank you!

Yes, rent would be 1000/month, but I just multiplied by 12 for the FV formula.
 

kwestman

New Member
Upon further review, the FV formula appears to be applying the interest rate to the balance, and not to the regular payment to the account (ie. rent).

FV = PV (1 + r)^n

I need:

Total Rent = (pmt * 1+n^0) + (pmt* 1+n^1) + (pmt * 1+n^2) + ... + (pmt * 1+n^z)
where pmt is payment, n is payment inflation rate per year, and z is the number of years

I don't think those are the same things.

Can anybody offer a solution?
 

Dave Patton

Well-known Member
Did you try joe2004's suggestions?
Copy the example below and use Excel's Formula Evaluate to review the formulas.



<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Rent</td><td style="text-align: right;;">1,000.00</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Term</td><td style="text-align: right;;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Escalation</td><td style="text-align: right;;">3%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Cumulative</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1,000.00</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">2</td><td style="text-align: right;;">1,030.00</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">3</td><td style="text-align: right;;">1,060.90</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;">4</td><td style="text-align: right;;">1,092.73</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;">5</td><td style="text-align: right;;">1,125.51</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;">6</td><td style="text-align: right;;">1,159.27</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;;"></td><td style="text-align: right;;">6,468.41</td><td style="text-align: right;;">6,468.41</td><td style="text-align: right;;">6,468.41</td><td style="text-align: right;;">6,468.41</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:1.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">3a</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B7</th><td style="text-align:left">=B6*(<font color="Blue">1+$B$3</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B8</th><td style="text-align:left">=B7*(<font color="Blue">1+$B$3</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B9</th><td style="text-align:left">=B8*(<font color="Blue">1+$B$3</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B10</th><td style="text-align:left">=B9*(<font color="Blue">1+$B$3</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B11</th><td style="text-align:left">=B10*(<font color="Blue">1+$B$3</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B12</th><td style="text-align:left">=SUM(<font color="Blue">B6:B11</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C12</th><td style="text-align:left">=FV(<font color="Blue">B3,B2,-B1,0,0</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D12</th><td style="text-align:left">=B1*SUMPRODUCT(<font color="Blue">(<font color="Red">1+B3</font>)^(<font color="Red">ROW(<font color="Green">X1:INDEX(<font color="Purple">X:X,B2,1</font>)</font>)-1</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E12</th><td style="text-align:left">= (<font color="Blue">B1* (<font color="Red">1+0.03</font>)^0</font>) + (<font color="Blue">B1 * (<font color="Red">1+0.03</font>)^1</font>) + (<font color="Blue">B1 *(<font color="Red"> 1+0.03</font>)^2</font>)+ (<font color="Blue">B1 *(<font color="Red"> 1+0.03</font>)^3</font>)+ (<font color="Blue">B1 *(<font color="Red"> 1+0.03</font>)^4</font>) + (<font color="Blue">B1 * (<font color="Red">1+0.03</font>)^5</font>)</td></tr></tbody></table></td></tr></table><br />
 

joeu2004

Well-known Member
the FV formula appears to be applying the interest rate to the balance, and not to the regular payment to the account (ie. rent).
FV = PV (1 + r)^n
Only if there are no periodic payments. With periodic payments, the correct formula is (all values are positive):

FV=PV*(1+r)^n + PMT*((1+r)^n - 1)/r

where PV=0, PMT=12000 (1000*12), r=3% and n=6 in your case. See the Excel PV Function help page; simplified here.

I need:
Total Rent = (pmt * 1+n^0) + (pmt* 1+n^1) + (pmt * 1+n^2) + ... + (pmt * 1+n^z)
where pmt is payment, n is payment inflation rate per year, and z is the number of years
The two are equivalent, when you use the same variable names: r is payment inflation rate, and n is number of years. Also use proper parentheses (also note the last exponent): pmt*(1+r)^0 +...+ pmt*(1+r)^(n-1)

(The formula in your original posting was more correct. I did not realize your mistake in parentheses, unconsciously reading 1+0.03 as 1.03.)

I provided a SUMPRODUCT formula that mimics that calculation for the purpose of demonstrating that my FV formula calculates the same result.

I could provide mathematical proof, but I think that will overwhelm you. Refer to Dave's response for a demonstration.
 

Dave Patton

Well-known Member
Kellen may not be following his thread but I will post a minor edit.

You can try the alternative or alternatives that you prefer.

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Rent</td><td style="text-align: right;;">1,000.00</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #92D050;;">6,468.41</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Term</td><td style="text-align: right;;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;">6,468.41</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Escalation</td><td style="text-align: right;;">3%</td><td style="text-align: right;;"></td><td style="text-align: right;;">6,468.41</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">6,468.41</td></tr></tbody></table><p style="width:1.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">3a</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D1</th><td style="text-align:left">=FV(<font color="Blue">B3,B2,-B1,0,0</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D2</th><td style="text-align:left">=B1* (<font color="Blue">1+0.03</font>)^0+B1 * (<font color="Blue">1+0.03</font>)^1 + B1 *(<font color="Blue"> 1+0.03</font>)^2+ B1 *(<font color="Blue"> 1+0.03</font>)^3+ B1 *(<font color="Blue"> 1+0.03</font>)^4 + B1 * (<font color="Blue">1+0.03</font>)^5</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D3</th><td style="text-align:left">=B1*SUM(<font color="Blue">(<font color="Red">1+B3</font>)^{0;1;2;3;4;5}</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D4</th><td style="text-align:left">=B1*SUMPRODUCT(<font color="Blue">(<font color="Red">1+B3</font>)^(<font color="Red">ROW(<font color="Green">X1:INDEX(<font color="Purple">X:X,B2,1</font>)</font>)-1</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 
Last edited:

Some videos you may like

This Week's Hot Topics

  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Syntax errors
    Good Morning, Trying to compile a workbook, I keep getting a few errors. Here are the first two: [code=rich]Syntax Error: Function...
Top