Calculating cumulative payments with regular increases

kwestman

New Member
Joined
Mar 13, 2006
Messages
6
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
Joined
Mar 2, 2014
Messages
2,515
Office Version
2010
Platform
Windows
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
Joined
Mar 13, 2006
Messages
6
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
Joined
Mar 13, 2006
Messages
6
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
Joined
Feb 15, 2002
Messages
3,745
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
Joined
Mar 2, 2014
Messages
2,515
Office Version
2010
Platform
Windows
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
Joined
Feb 15, 2002
Messages
3,745
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:

Forum statistics

Threads
1,077,662
Messages
5,335,564
Members
399,025
Latest member
alce

Some videos you may like

This Week's Hot Topics

Top