PV of Lease Rents

[ypodber]

IN Excel 2019, how do I calculate the present value of lease rents?

1. The lease is 5 years
2. The rent increases the beginning of each year.
3. The rent is do the beginning of each month, on the 1st.

Example. The rent beginning on 1/1/23 is $1,200 per month:

1. Year 1 monthly rent = $1,200
2. Year 2 monthly rent = $1,260
3. Year 3 monthly rent = $1,320
4. Year 4 monthly rent = $1,380
5. Year 5 monthly rent = $1,425

 

[Mortiray]

Is the increase the same every year? Either a static amount added or a static factor multiplied? Or is it arbitrary?

 

[ypodber]

Is the increase the same every year? Either a static amount added or a static factor multiplied? Or is it arbitrary?

3% increase every January 1st. My numbers don't indicate that.

 

[Mortiray]

You want to multiply the base rent value by 1.03 to the power of the number of years that have passed. If your lease starting year and base rent are in A1 and B1 respectively, and going down the A column increments the year, the formula for the B column after B1 would be

=$B$1 * (1.03 ^ (A2-$A$1))

Alternatively, if you're certain that the years in column A are all sequential, you can simplify the formula by doing

=B1*1.03

This second formula will multiply the value above it by 1.03.

 

[Dave Patton]

Check if XNPV is in your version of Excel.

T201810a.xlsm
	A	B	C	D	E
1
2		10%	Interest rate
3		60,718.24	Present Value
4
5		01-Jan-2023	Tenancy Start Date
6		1,200.00	Rent amount
7		12	Rent increases annually
8		3%	Rent Escalation
9					Annual Escalation
10	1	1,200.00	01-Jan-2018	1,200.00	01-Jan-2024
11	2	1,200.00	01-Feb-2018	1,200.00	01-Jan-2025
12	3	1,200.00	01-Mar-2018	1,200.00	01-Jan-2026
13	4	1,200.00	01-Apr-2018	1,200.00	01-Jan-2027
14	5	1,200.00	01-May-2018	1,200.00
15	6	1,200.00	01-Jun-2018	1,200.00
16	7	1,200.00	01-Jul-2018	1,200.00
17	8	1,200.00	01-Aug-2018	1,200.00
18	9	1,200.00	01-Sep-2018	1,200.00
19	10	1,200.00	01-Oct-2018	1,200.00
20	11	1,200.00	01-Nov-2018	1,200.00
21	12	1,200.00	01-Dec-2018	1,200.00
22	13	1,236.00	01-Jan-2019	1,200.00
5c

Cell Formulas
Range	Formula
B3	B3	=XNPV(B2,B10:B69,C10:C69)
E10:E13	E10	=EDATE($B$5,A10*12)
B10	B10	=B6
B11:B22	B11	=B10*(1+(MOD(A10,12)=0)*Escalation)
D10:D22	D10	=$B$6*(1+$B$8)^INT(IF(C10>$B$5,DATEDIF($B$5,C10,"m"),0)/$B$7)

Named Ranges
Name	Refers To	Cells
Escalation	='5c'!$B$8	D10:D22, B11:B22

 

[Dave Patton]

The same post; unnecessary data was deleted.

T201810a.xlsm
	A	B	C	D
1
2		10%	Interest rate ( rate was not specified)
3		60,718.24	Present Value
4
5		01-Jan-2023	Tenancy Start Date
6		1,200.00	Rent amount
7		12	Rent increases annually
8		3%	Rent Escalation
9
10	1	1,200.00	01-Jan-2018
11	2	1,200.00	01-Feb-2018
12	3	1,200.00	01-Mar-2018
13	4	1,200.00	01-Apr-2018
14	5	1,200.00	01-May-2018
15	6	1,200.00	01-Jun-2018
16	7	1,200.00	01-Jul-2018
17	8	1,200.00	01-Aug-2018
18	9	1,200.00	01-Sep-2018
19	10	1,200.00	01-Oct-2018
20	11	1,200.00	01-Nov-2018
21	12	1,200.00	01-Dec-2018
22	13	1,236.00	01-Jan-2019
23	14	1,236.00	01-Feb-2019
5c

Cell Formulas
Range	Formula
B3	B3	=XNPV(B2,B10:B69,C10:C69)
B10	B10	=B6
B11:B23	B11	=B10*(1+(MOD(A10,12)=0)*Escalation)

Named Ranges
Name	Refers To	Cells
Escalation	='5c'!$B$8	B11:B23