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
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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:
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
Did you try joe2004's suggestions?
Copy the example below and use Excel's Formula Evaluate to review the formulas.




Excel 2010
ABCDE
1Rent1,000.00
2Term6
3Escalation3%
4Cumulative
5
611,000.00
721,030.00
831,060.90
941,092.73
1051,125.51
1161,159.27
126,468.416,468.416,468.416,468.41
13
3a
Cell Formulas
RangeFormula
B7=B6*(1+$B$3)
B8=B7*(1+$B$3)
B9=B8*(1+$B$3)
B10=B9*(1+$B$3)
B11=B10*(1+$B$3)
B12=SUM(B6:B11)
C12=FV(B3,B2,-B1,0,0)
D12=B1*SUMPRODUCT((1+B3)^(ROW(X1:INDEX(X:X,B2,1))-1))
E12= (B1* (1+0.03)^0) + (B1 * (1+0.03)^1) + (B1 *(1+0.03)^2)+ (B1 *(1+0.03)^3)+ (B1 *(1+0.03)^4) + (B1 * (1+0.03)^5)
 
Upvote 0
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.
 
Upvote 0
Kellen may not be following his thread but I will post a minor edit.

You can try the alternative or alternatives that you prefer.


Excel 2010
ABCD
1Rent1,000.006,468.41
2Term66,468.41
3Escalation3%6,468.41
46,468.41
3a
Cell Formulas
RangeFormula
D1=FV(B3,B2,-B1,0,0)
D2=B1* (1+0.03)^0+B1 * (1+0.03)^1 + B1 *(1+0.03)^2+ B1 *(1+0.03)^3+ B1 *(1+0.03)^4 + B1 * (1+0.03)^5
D3=B1*SUM((1+B3)^{0;1;2;3;4;5})
D4=B1*SUMPRODUCT((1+B3)^(ROW(X1:INDEX(X:X,B2,1))-1))
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top